Success Story Finance Transformation: Digitalization of Controlling in Regional Rail Passenger Transport

A public authority responsible for regional rail passenger transport (SPNV) faced the challenge of consolidating a large number of heterogeneous data sources. These included, among others:

•    Operating costs
•    Rail network and contract structures
•    Sales and revenue data
•    Volume structures and performance data
•    Penalties and quality indicators
•    Forecasts and planning data

Many of the existing processes had evolved historically and were based on isolated Excel solutions, manual checks, and multiple data processing cycles. The objective was to establish a central, integrated and automated Qlik solution that combines financial planning, forecasting, reporting, regulatory reporting, revenue data and operational performance data within a single system.
 

The Solution: Holistic Controlling Tool Based on Qlik

1. Development of a Comprehensive Data Model

As part of the project, all relevant data sources were systematically identified, structured, and transferred into a standardized Qlik data model. The project implementation included, among other things:

•    Integration of standardized operational and billing data
•    Integration of tariff and ticket key tables, including mapping logics
•    Connection of data from various functional areas, such as regional rail service planning, quality and safety, revenue management, and procurement procedures

The data model was gradually expanded to ensure the consistent representation of volume structures as well as billing, booking, and contractual logics.
 

2. System-Supported and Integrated Financial Planning & Forecasting

Through structured user documentation for financial planning and forecasting, an end-to-end planning process was established:

•    Modules for financial planning (e.g., cost blocks, scenarios, indexations)
•    Forecast modules, including automated plan-actual variance analyses
•    System-supported calculation of key performance indicators, as well as version control and plan management

This fulfilled a key requirement: combining planning and forecasting within a single system and making them both manageable and analytically accessible.
 

3. Transparency Across Operational and Sales Data

The Qlik dashboards now provide, among other things, insights into:

•    Ticket sales and revenue developments
•    Revenue allocation
•    Penalties and quality indicators
•    Timetable analyses and kilometers operated
•    Additional and reduced services per transport contract

This creates a reliable basis for providing transparency in connection with operational performance, financial structures and transport-related impacts.
 

4. Automated Regulatory Reporting & Standardized Data Exports

The previously manual preparation and provision of reporting forms has been fully automated:

•    ETL routines for processing and validating raw data
•    Automated generation of reporting forms and analyses
•    Integration of external interfaces and data exchange formats

This automation improves data quality, significantly reduces manual effort, and accelerates reporting processes.
 

5. Making Public Funding Strategically Predictable

Through the integrated linkage of:

•    planning and forecast data,
•    actual performance indicators,
•    operating costs,
•    sales and revenue data,
•    contractual and regulatory frameworks,

future public funding requirements can now, for the first time, be planned and forecast in a well-founded, transparent and data-driven manner.

The broad use of the tool across different functional areas – from investment funding management to financial controlling – highlights its strategic value.
 

Result: A Strategic Management System for a Regional Rail Service Authority

• Centralized, consolidated database
  All relevant operational, revenue, cost and quality data are integrated into a single system.
• End-to-end planning & forecasting
  Financial plans and forecasts can be prepared quickly, adjusted flexibly and documented transparently.
• High data quality & automation
  Automated ETL processes, calculation logics and reporting forms significantly reduce manual work steps.
• Management dashboards & KPIs
  Real-time transparency for executive management and specialist departments.
• Reliable forecasting of future funding requirements
  Economic, operational and sales-related effects become visible for the first time within an integrated management model.
   

Conclusion

The Qlik-based controlling tool has transformed the regional rail service authority from a fragmented data landscape into a fully integrated management system. With automated processes, clearly structured dashboards, and reliable planning and forecasting functions, SPNV services, sales results and public funding can now be analyzed, managed and strategically developed in an integrated manner.