43 city councils, one reporting system

To read a German version of this case study, please click here.

As a local consortium, the Lower Rhine Council Data Centre (KRZN) in Kamp-Lintfort, has been the IT service provider for 43 city councils in the Lower Rhine region since 1971. It is the responsibility of KRZN to develop and procure modern IT systems as well as to support its members in setting up and using the technology. Through its data centre, KRZN delivers IT services within a private cloud environment to its members, which include the City of Bottrop, the City of Krefeld, the District of Kleve, the Districts of Viersen and Wesel, and their local authorities. It provides efficient business process solutions for administrative users in different specialist areas, from local co-operation to financial administration, at favourable prices.

New finance management, new challenges

The introduction of the New Council Financial Management system (NKF) created new challenges for the local authorities.

The council budget regulations for North Rhine-Westphalia state that city councils should specify product-oriented goals to fulfil community-oriented tasks, taking into account the resources available and the estimated use of resources. They should also define key performance indicators (KPIs) for achieving these goals. These goals and KPIs form the basis for planning, controlling and monitoring the progress of the annual budget.

NKF incorporates new economic factors into the local budgeting process. It prescribes dual record-keeping, intending to break away from the local fiscal accounting which only records payment streams. NKF’s key requirements are the production of a balance sheet, a profit and loss calculation (or results statement) as well as a financial statement that lists every cent of a city council’s cash flow.

Obstacles to reporting

NKF’s aim is to make local budgets more transparent and therefore increase the ability to control finances. This should mean that cash flow is closely monitored and available resources are controlled more efficiently in local product areas such as security, social benefits, construction and housing, environmental protection and sports promotion. In turn, there are more challenges facing the local reporting system, as Werner Pescher, Head of Department at KRZN, explains: “NKF presented us with the task of introducing a reporting system for the city councils that provides new KPIs. This includes showing the future resource requirements of investment projects such as road construction in regular quarterly, six-monthly and annual reports. The existing systems and applications didn’t meet the reporting requirements and so the project had an air of pioneering spirit about it.”

Existing systems prove inadequate

Just a few years ago, KRZN implemented an SAP ERP system for its financial administration across the entire region. Combined with the application module “DZ-Kommunalmaster^® Doppik” (Baden-Wuerttemberg’s data hub), the SAP system offers the KRZN councils accounting functions for their reporting. However, the system failed to deliver the necessary reporting and analysis functions. “It lacked the systematic processes that are necessary for gathering financial data and enriching it with other key figures for specific KPI reports. This meant it couldn’t show us the future use of resources for specific product areas,” says Beate Riedel, Specialist Area Leader for Accounts.

Required: deeper insights into finances

Deploying a new information system was inevitable for the city councils in the KRZN consortium, and so Beate Riedel’s project team embarked on the search for a business intelligence solution that could meet NKF’s requirements. Beate Riedel explains: “KRZN wanted to centralise and standardise the reporting system and its database for all city councils, in order to keep development and maintenance expenses and costs low. It was vital that the new solution could be easily connected to existing systems. It also needed to be flexible and user-friendly enough to cover the informational needs of each local controlling department.“

“In October 2010 it was decided: we’ll go for IBM Cognos,” says Werner Pescher.

From concept to implementation

In the first project phase, the project team designed and prioritised the various reporting channels and drafted standardised interim and sub-reports together with IBM. The team also reorganised the information infrastructure and implemented an Oracle database as the central data warehouse. All the product data — such as the locally entered forecast data from the controlling software and financial figures from the SAP system — is gathered in the database. Beate Riedel and her colleagues then developed a data model that would transfer the data collected in the Oracle database to the IBM Cognos system.

Centralising the data made it possible to simultaneously arrange full reports and interim reports into a uniform structure, as well as specifying KPIs and standard parameters. These include everything from the type of report to the presentation of costs and investments, to headers and logos. “Centralising and standardising the reporting system saves work and minimises errors,” explains Beate Riedel. “If there are structural changes to be made, we no longer have to implement them in all of the 40-odd reports from all the city councils — now we just have to do it once for each report. Something that would normally take at least half a day for each council now happens once centrally at KRZN, ready for the local authorities to use at the click of a button.”

At the beginning of 2011, the first standardised financial statements were already starting to be configured and after a few test runs the new information system went live in the middle of 2011.

Flexible access

Area managers, controllers and treasurers in the city councils can now access an IBM Cognos front-end system via a Citrix portal and view the data they want, as long as they are authorised to do so. This information is available in the form of KPI reports, ad-hoc analyses and forecasts. All the data is physically stored in the KRZN data centre, and provided via the central data structure. Even data updates can be managed centrally. All users work with a central database, and there is an automated system for importing and processing data to create reports that need to be produced at various times of year.

NKF in practice: investigating financial data

The new reporting system raises the provision of information in the KRZN councils to a whole new level. In compliance with the NKF guidelines, users produce KPI reports to analyse the council’s economic situation, either as a whole or by individual area. KPIs such as expenditure, deficits, depreciation rate, interest charges or staff turnover provide information on the current situation as well as the expected financial situation over the next five years.

Treasurers and controllers can now answer key questions on local control, which could otherwise never have been answered, or at least only with great difficulty using man-made spreadsheet analyses. Regular quarterly and six-monthly reports, for example, provide information about the costs of running inner-city playgrounds in the coming years, how much social benefits have increased, or what extra costs have resulted from environmental damage.

With the IBM Cognos solution, the parties can combine actual figures from the SAP system with the current forecast data for a certain area and compare actual and target figures. As such, they can monitor if the individual councils are meeting set targets. The results of the forecast comparisons can be summarised in tailored reports and submitted to the local authorities.

An all-inclusive forecast thanks to IBM Cognos

The KPI reports then flow into the interim results forecast of each local control area and are finally consolidated in the overall results forecast for that council. This annual forecast covers the expected expenditures and revenues in the financial year and is based on the NKF guidelines: it contains the three factors stipulated by the NKF - balance sheet, results statement and financial statement.

Setting up the entire local control process according to the new NKF guidelines, including producing the overall results forecast, would not have been possible without IBM Cognos. The central reporting and data structure has made it a smooth process across the board, as Beate Riedel explains: “One of the larger cities in the KRZN region compiled its final budget with the help of the IBM Cognos system. The central treasury can now produce the necessary forecasts practically at the push of a button and show the projected cash flow for the coming years. Even the locally managed specialist areas can allocate their funds with much more accuracy than before, and thereby increase the council’s profitability and cost-efficiency.”

About IBM Business Analytics

IBM Business Analytics software delivers actionable insights decision-makers need to achieve better business performance. IBM offers a comprehensive, unified portfolio of business intelligence, predictive and advanced analytics, financial performance and strategy management, governance, risk and compliance and analytic applications.

With IBM software, companies can spot trends, patterns and anomalies, compare “what if” scenarios, predict potential threats and opportunities, identify and manage key business risks and plan, budget and forecast resources. With these deep analytic capabilities our customers around the world can better understand, anticipate and shape business outcomes.

For more information

For further information please visit ibm.com/business-analytics.

Request a call

To request a call or to ask a question, go to ibm.com/business-analytics/contactus. An IBM representative will respond to your inquiry within two business days.