Data analytics is the process of examining and interpreting large amounts of data to extract useful information and support strategic decisions. This process includes collecting data from different sources, cleaning it to eliminate errors or inconsistencies, and transforming it into a more usable format. Subsequently, analytical techniques such as statistical models and machine learning algorithms are applied to identify patterns, trends, and correlations. Finally, the results are visualized and interpreted to make informed decisions or predict future developments.
It is essential because it enables organizations to transform large volumes of raw data into valuable insights, thus improving the quality of strategic decisions. In a data-driven world, Data Analytics allows organizations to identify trends, understand customer behavior, optimize business processes, and anticipate market changes. This analytical approach reduces uncertainty, supports innovation, and enables rapid responses to competitive challenges. Additionally, it improves operational efficiency and can lead to significant time and resource savings. In essence, Data Analytics is crucial for maintaining and enhancing competitiveness in a dynamic global market.
Use Case: Financial Distress
The state of financial distress has a significant impact on the finances of the local authority. Among the main consequences, and until the approval of the balanced budget proposal, certain limits and restrictions may be mentioned, such as:
- Limits on new loans: New loans cannot be entered into with some specific exceptions. For example, loans whose costs are covered by the State or regions, or loans aimed at financing projects that receive co-financing from the European Union or other public or private institutions are allowed. These restrictions aim to prevent excessive indebtedness (art. 249).
- Limits on financial commitments: Administrations cannot commit funds beyond what is provided in the last approved budget for the current year. Monthly payments for current expenses are subject to restrictions, with some exceptions (art. 250).
- Increase in tax rates: Entities must increase rates and tariffs of local taxes, except for urban waste tax. This resolution remains in force for 5 years and cannot be annulled (art. 251).
It is clear that the consequences of an entity in distress go far beyond the financial problems of the municipality, causing very serious and negative effects for all stakeholders involved, especially citizens and businesses, as well as the staff itself.
The consequences of distress for administrators can be summarized as:
- Political responsibility: Administrators who caused damages in the 5 years preceding the distress are excluded from public offices for 10 years, including roles such as councilor, auditor, and representative of the local authority.
- Eligibility: Mayors and provincial presidents responsible cannot run for public office for 10 years, including positions such as mayor, provincial president, or regional council president.
- Financial penalties: Administrators and members of the audit board may face significant financial penalties for serious liabilities.
The consequences of distress for the entity’s personnel can be summarized as:
- Staff downsizing: The entity is required to reduce personnel by placing “in availability” employees in excess relative to the average employee/population ratio. The Ministry of the Interior provides a contribution to these employees for 5 years.
- Cessation of interest and revaluation: Unpaid debts at the time of liquidation and sums owed for cash advances no longer generate interest nor are subject to monetary revaluation, stabilizing the debt.
The Implemented Data Analytics Solution
Revelis develops (Big) Data Analytics solutions, and one of these solutions will be described in the continuation of the article: the system developed for Fincalabra S.p.A. and the Calabria Region to monitor the financial sustainability of local authorities, particularly municipalities, using indicators calculated from the financial statements published by the administrations themselves.
The dashboard also provides forecasts of the indicators for the three years following the year for which the latest data is available. Specifically, it will cover:
- Data retrieval, acquisition, and persistence of financial statements
- Calculation of indicators
- Forecasting of indicators
- Web application for displaying indicators and forecasts
The acquired data concerns the final accounts of municipalities reported to the General Accounting Office of the State and published on the OpenBDAP portal. It provides data on Public Finance available in the Public Administrations Database (BDAP).
Specifically, the available data covers:
- Public Accounts
- State Budget
- Finance of Territorial Entities
- European Union Budget
- Public Investments
- Finance of National Health Service Entities
- Public Employment
Data on the Finance of Territorial Entities is available in CSV format as:
- Forecast
- Final Account
- Consolidated
The calculation of indicators uses the Final Account data for the Calabria Region (extendable to all municipalities across the country). These include both raw data and some pre-calculated indicators. The CSV files retrieved from the portal are acquired through a specific procedure and stored in a database.
Calculation of Indicators
The system calculates 12 basic indicators and a summary indicator, divided into three areas:
- Financial Sustainability
- Organizational Sustainability
- Managerial Sustainability
The financial indicators are:
|
Incidence of Unreturned Advances |
Unreturned Advance – Current Expenses |
|
Financial Autonomy |
Tax Revenues (Title I) + Non-Tax Revenues (Title III) – Total Current Revenues (Title I + Title II + Title III) |
|
Indicator of Effective Collection Capacity |
Collections with Compensation + Collections with Residuals – Assessments + Initial Definitive Residuals |
|
Indicator of Effective Collection Capacity (Title 1 only) |
Collections with Compensation + Collections with Residuals – Assessments + Initial Definitive Residuals |
|
Trend of Accumulated Funds |
Amounts Accrued in the Year – Average of Amounts Accrued in Previous Years |
|
Incidence of Equalization Funds on the TOTAL of Title 01 Revenues |
Equalization Funds FSC – Total Revenues Title 1 |
|
Sustainability of the Deficit Actually Charged to the Fiscal Year |
Deficit Recorded in Expenditure of the Budget Account – Assessments + Initial Definitive Residuals |
|
Cash Fund |
Cash Fund as of December 31 – Allocations of Competence for the First Three Titles of Revenues |
The indicators of Organizational Sustainability are:
|
Incidence of Personnel Expenses on Current Revenues |
Commitments (Macro-aggregate 1.1 “Employee Wages”) + pdc 1.02.01.01.000 “IRAP” + FPV personnel exiting 1.1 – FPV personnel entering concerning Macro-aggregate 1.1 – Allocations of Competence for the First Three Titles of Revenues |
|
Incidence of Fixed Expenses |
Fixed Expenses (Deficit, Personnel, and Debt) – Current Revenues |
The indicators of Managerial Sustainability are:
|
Incidence of Recognized and Financed Off-Budget Debts |
Recognized and Financed Off-Budget Debts – Total Commitments Title I and Title II |
|
Incidence of Off-Budget Debts in Financing |
Off-Budget Debts Under Recognition + Recognized Off-Budget Debts in the Process of Financing – Total Commitments Title I and Title II |
Forecasting the Indicators
To forecast the indicators, Linear Regression algorithms are used on the time series of values for each municipality included in the system. This is a statistical function that describes the relationship between a dependent variable yyy (effect) and another independent variable xxx (cause). This function allows for the interpolation and extrapolation of data (yyy) based on the observed data xxx: y=α+βxy = \alpha + \beta xy=α+βx
Where:
- β\betaβ is the slope coefficient of the regression line and measures the association between yyy and xxx.
- α\alphaα is the intercept of the population regression line.
A workflow has been prepared for the calculation in the Rialto™ system.
It acquires data from the previously described database, generates the necessary models for each municipality and each indicator, applies them using historical data as input, and stores the forecasts in the dedicated tables of the database.
Dashboard
Once the municipality of interest is selected, a section is displayed for each indicator (KPI) containing a table with the data used to calculate it for each year, its value, the average of the values for the displayed years in the historical data, and the forecast of its value for the next three years. A gauge-type chart, which also highlights the thresholds of risk categories, displays the value of the indicator for the most recent year for which historical data is available. Finally, below the table, there is a line chart that graphically shows the trend of the indicator’s value, including the forecasts.
The user can generate a report in PDF or Excel format containing the data for the displayed municipality. This report includes sections that provide commentary on the numerical data presented, offering initial explanations related to the degree of risk identified by the indicators. It takes into account combinations of values from multiple interconnected indicators to provide an overview of the economic/financial situation of the municipality.
The values of the indicators are expressed in percentage terms and categorized into four risk bands. However, it may be challenging to assess the municipality’s performance without additional benchmarks. To address this issue, average reference values are calculated and presented at the regional level.
Conclusions
The advanced data analysis tool that utilizes Data Analytics and Artificial Intelligence techniques enables Public Administrations and municipalities to:
- Monitor a series of managerial, organizational, and financial indicators.
- Assess over time, based on changes in the indicators, the effectiveness of the corrective actions implemented by the administration.
For Fincalabra:
- Optimize financial support actions for Public Administrations based on their strengths and weaknesses identified through the indicators.
Author: Luigi Granata
