Analytical controlling has also long been a core task in public authorities in order to evaluate and improve the effectiveness and efficiency of public services and programs.
The Bern Cantonal Youth Welfare Office (KJA) coordinates various authorities and agencies in the areas of child and youth welfare and child protection. To improve the manual and time-consuming planning and controlling processes, our consultants were asked to create a technical concept and a technical prototype as proof of concept.
Specialist concept for analytical controlling
We examined and redesigned the existing processes and data structures to enable analytical controlling for inpatient services in the area of child and youth welfare and child protection with digital support.
In principle, the services to be provided per care unit are determined on the basis of commercial data (balance sheet and income statements), which is why the data for each facility and its locations are recorded centrally once in the new concept.
Commercial data is updated cyclically so that the service agreements for each facility can be determined and compared at any time on the basis of defined algorithms using the current data situation and the associated tariff classifications.
With the help of standardized recording, evaluation and calculation, performance data is determined and stored in a transparent, comprehensible and comparable manner.
Technical proof of concept
For the technical validation of the concept, the data of each facility is recorded in an Excel template with predefined data structures. The data recorded for each facility is stored in a directory on the shared controlling repository.
The Excel files in the directory serve as the data basis for a data model created with Microsoft Power BI, which enables the data to be analyzed across all facilities.
The solution described here includes the data collection of master data and financial data of the supported facilities as well as the associated analytical reporting in Power BI Desktop.
Analytical controlling has real value
Although the proof of concept was deliberately implemented with Microsoft Office on-board tools as a specialized solution, the solution already delivers the following benefits in this state
- Transparent planning and controlling
- the automation of previously time-consuming manual processes
- multi-year comparison and an analysis based on key figures
The practical work with this tool shows your potential right from the start and not only reduces manual effort, but also avoids errors and creates insights at the click of a mouse that would have taken days to obtain manually.
Further areas of application for analytical controlling in public authorities
Here are some of the most important areas of application for analytical controlling in the public sector that we have come across in our projects:
- budgeting and financial management: by using analytical tools, governments and public agencies can better plan and manage their financial resources. The analysis of data can help to set priorities and assess the costs of programs.
- performance management: Analytical controlling can be used to evaluate and improve the performance of public services and programs. Data on the effectiveness and efficiency of programs can be collected and analyzed to identify potential bottlenecks and prioritize improvements.
- risk management: By analyzing data, the public sector can also identify potential risks and take preventative measures. This includes, for example, analyzing public safety data and monitoring environmental and health risks.
- personnel management: Analytical controlling can also be used to optimize personnel processes. Data on hiring, working hours, absenteeism and employee performance can be collected and analyzed in order to make better decisions regarding personnel requirements and management.
- citizen participation: Analytical tools can also be used to support citizen participation processes by enabling feedback from citizens and other stakeholders to be collected and analyzed quickly and effectively.
- infrastructure management: Analytical tools can also be used to improve infrastructure management processes. Data on traffic density, building maintenance, and energy and resource usage can be collected and analyzed to make better investment and maintenance decisions.
- transparency and accountability: By using analytical tools, governments and public bodies can also improve their transparency and accountability by improving public reporting on their performance and results.