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 feasibility.
Specialist concept for analytical controlling
We were able to examine and redesign 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 supervised 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 saved 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 comprises the data collection of master data and financial data of the supported institutions 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 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 of analytical controlling in the public sector that we have become familiar with in our projects:
- Budgeting and financial management: By using analytical tools, governments and public institutions can better plan and manage their financial resources. Analyzing data can help 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 data on public safety and monitoring environmental and health risks.
- Personnel management: Analytical controlling can also be used to optimize personnel processes. Data on hiring, working hours, absences 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 making it possible to collect and analyze feedback from citizens and other stakeholders quickly and effectively.
- Infrastructure management: Analytical tools can also be used to improve infrastructure management processes. Data on traffic density, building maintenance and the use of energy and resources can be collected and analyzed in order to make better decisions regarding investments and maintenance.
- Transparency and accountability: By using analytical tools, governments and public institutions can also improve their transparency and accountability by improving public reporting on their performance and results.
