title
Presentation of quarterly forecasting template for feedback
body
Submitted by:
John Ruggini
Department:
Finance Department
A. Issue
Prior to 2020, the Finance Department produced a quarterly financial report projecting actuals and budget variances based on year-to-date financial information. We would like to restart this process in 2025 and receive feedback on the report content.
B. Background/Options
Due to the COVID-19 pandemic and the financial system implementation, the Finance Department stopped preparing quarterly budget to actual reports. They were not restarted due to the cumbersome process required. This is a current weakness in our financial reporting as these reports serve as an early-warning system for staff and elected officials of potential year-end budget issues. We spent 2024 reconfiguring our Municast forecasting system to simplify this reporting and plan on restarting the reporting in 2025. Our goal is to produce the report 45 days after the close of the quarter. So the first quarter report would be presented to the Financial Affairs Committee at their May 6th meeting for the quarter that closes March 31.
Prior to producing the first report staff would like to receive feedback on the graphs and charts we intend to use. We plan to report on approximately 12 revenue categories and 8 expenditure categories with the ability to drill down into further detail. The categories are shown below with some of the drill-down capability displayed.
There are over 3,000 accounts contained within these 20 categories. Each account has been assigned one of three forecasting methodologies:
1. Assume budget
2. Assume a final actual based on the average spending/earning pattern of the prior 2 years.
a. Example: If in the prior 2 years, we earned 80% of the final amount by Oct. 1 and this year, we’ve earned $100,000 by Oct. 1 then we would assume we would earn $125,000. (i.e. $100,000/.8)
3. Pro-rated Budget.
a. Assume actuals to date and then assume the remaining months by pro-rating the budget either straight line (dividing the budget by 12) or based on historical spending.
A stop-light analysis is shown with a positive variance receiving a green light; a negative variance up to 10% would be orange and any variance below negative 10% would be red. In addition, period over period trends would be indicated with an arrow as shown below using 2024 data through October. The legend for the year-over-year trend arrows is also shown below.
In this example, revenues in total based on actuals through October, are projected by year-end to surplus 6.1% (a green light since the percentage is positive) and are $2.2 million above the prior. This 3.2% period over period increase earns a vertical arrow. Under “Law and Ordinance Violations” which is showing red, I’ve drilled down into a lower level of categories to identify where the potential issue is.
A higher-level chart is also available as shown below for General Fund Licenses and Permits. This chart shows cumulative actuals by month as a solid blue bar (January through September). The blue hatched bars show the forecasted month amounts. The lines show different fiscal year time periods. In this example, Licenses and Permits are forecasted to be $2,125,000 at year end which is less than 2022 actuals but greater than the 2024 budget and 2023 actuals. Below the table, the actual variances are shown.
In addition, third party sources will be used. For example, we receive monthly health insurance projections:
and hotel occupancy and rate trends.
In some cases, we may drill into operational data if available. For example, if there is variance in ambulance revenue that we want to explore we might present information on ambulance trips by month as shown below.
The City has an increasing amount of data available and we hope to leverage that data to assist with financial reporting while at the same time, creating an easy reporting structure so that we can consistently provide quarterly reports.
A future goal is to integrate our operational and financial databases so we can report on efficiency measures. For example, what is our trend for the cost of salt per ton per inch of snowfall over time. However, this is a future enhancement with yet-to-be solved technology integration issues.
C. Strategic Plan (Area of Focus)
Financial Resilience
D. Fiscal Impact
Informational only
E. Recommendation
This report is for informational purposes only.