~ 6 MIN READ

How to Forecast your Budget Spend: An Interview with the City of Olathe, KS

It’s common in organizations to see inconsistent budget spending throughout the year. Departments ramp up spending when they think they are not going to spend their budgeted amount and then pull back on their spending

Joseph, Director of Customer Success at ClearPoint, has over 10 years of experience working with customers to create efficient performance management and strategy execution processes.

It’s common in organizations to see inconsistent budget spending throughout the year. Departments ramp up spending when they think they are not going to spend their budgeted amount and then pull back on their spending when it appears they could go over budget.

At the end of the year, that approach typically plays out in one of two ways. 1) A department has too much money left over and they are forced to ramp up their projects or lose those funds. 2) All projects go on hold until more budget is available next year.

Why does it always have to happen that way? Well, it doesn’t have to. With a concrete methodology, departments can have more visible access to budget information and make informed budgetary decisions throughout the year.

The City of Olathe, KS, a leader in the municipal performance management space, developed a budget forecasting process to give their departments a better way to manage their financial resources. Any organization in any sector that sets budgets could really benefit from adopting Olathe’s approach.

We recently caught up with Ed Foley from Strategic Planning & Research Services at Olathe, to learn more about the process they created and how they utilize ClearPoint to bring their budget information to life.

Show a visual comparison of projected budget to the real cost of implementing initiatives. Use a Budget vs Actual Dashboard.

JL: Tell me about your original budget forecasting process.

EF: In 2011, we decided departments needed better access to their budgeting information, but also needed context. We took the three year average for spending patterns so departments could look at their current spending for the year and compare that to their average spending for each month over the last three years.

Departments can see where they will end up if their current spending continues and they spend like they have for the past three years. We found this approach accurately captured seasonality in spending since departments compare their spending to their own past spending.

Our original forecasting process was Excel based and we had a lot of tabs with one tab being a rollup. It was stored on a shared drive and on an internal website. The information was a wall of data so to speak. To see an expenditure category, the viewer had to move to a different tab, and they had to know where to go. I don’t want to say it was a data dump, but 90% of the information in the Excel file, most users would not need. It was valuable information, but we had a bit too much detail for some audiences.

JL: It sounds like you had a solid concept and a working process, but there was some room for improvement. What were your main challenges with your former process?

EF: We really wanted to make sure analysts had a better tool in their meetings so info could be more easily presented and consumed. It was difficult for analysts to run their meetings. We were entirely spreadsheet based so it was all data. We needed charts to visually represent everything to make a greater impact.

All of our budget information is stored in Enterprise One from JD Edwards, which is Oracle based. To build these reports, budget analysts would go into the system to run several reports. The reports would be downloaded into Excel and analysts would do some data cleanup before using a lot of pivot tables to get the information into the final format.

Ultimately, we really wanted to help departments have a tool they could easily understand and could clearly illustrate to them where they were potentially going to end up from a budget perspective if they did not course correct before the end of the year. Our spreadsheet just could not quite give them that visual. We were close though.

JL: With your main challenges identified, how did you go about solving them?

EF: I could have never done this without the commitment from the budget space and the budget staff. They had a process in place that worked, but they were willing to climb the high hill to put in the work to get to the greater process.

In order to understand how best to get this info to staff, the budget team sat down individually with departments to better grasp their needs. We were using ClearPoint to track our organizational scorecard and performance measures, and we agreed it would be best to bring this information into ClearPoint.

We worked with the ClearPoint Team and the departments to figure out what charts would be best to visualize the data. We spoke with all eleven departments and other key stakeholders for input on everything from data formats to chart preferences.

It took a few months of planning and working, but it saved us time later because when we rolled out our new process, we already had staff buy-in since they had been involved all along. For some departments, this was their first introduction to ClearPoint and we wanted them to see what is possible.

We made it easier for analysts to run their meetings and now they can show departments the different spending scenarios. Not all audience members are numbers people, so the visuals and scenarios are key to getting the message across. ClearPoint presents the summary and the high-level information for everyone to easily digest. Plus, the amount of work our analysts need to put into prepping the reports and preparing for the meetings has been greatly reduced.

JL: That is so great to hear. You have mentioned a bit about ClearPoint, so tell us: what does this look like in ClearPoint now?

EF: Well, each department has seven measures within their own budget scorecards. Each measure represents a different budget category. The operating budget is the most nimble for departments to adjust since it does not include personnel.

We are using the Data Loader to pull our data directly into ClearPoint automatically to populate our budget measures. Once the data is in ClearPoint, we are using a number of calculations and formulas to find our three year averages and percent spends. By using calculations, we are able to turn only a few data points into a lot of additional information. ClearPoint does most of the work for us in terms of taking our data inputs and finding the averages, percent spends, and percent remaining in our forecasting process.

On the measure pages, each department can see charts clearly illustrating the projected year-end if historical spending patterns continue, the month-to-month 3 year average spending pattern, the 3 year average year-to-date versus the current year-to-date spending, as well as the percent of the year complete compared to the percent of budget spent. It’s very powerful and much more impactful. Any user can look at the charts and see the story. They can adjust spending behavior accordingly.

The color statuses for the measures has been helpful too. Staff and managers can easily see how they are trending. The way all the information is presented actually makes people from the city want to look at it. We’re publishing the information directly from ClearPoint to our internal site we call Ozone, and we are using ClearPoint live during the budget meetings with departments as we talk through their current state.

Everyone wants information easily and no one wants to have to go through too many steps to get it. ClearPoint gives it to our staff simply and in an easy to digest format.

JL: I’ve seen it all in action, so I can attest that it is very impressive. What impact has this had on your departments at Olathe?

EF: We have had a number of success stories across departments. In one case, a department was on pace to severely exceed their commodities budget. They could now see this in a graph mid-year and recognize how they were trending. At the end of each year, they often needed to spend a lot of money, so they would need to make a change to prevent going over budget. Being able to spot that early meant they could course correct before it was too late.

We had another department that was able to recognize they were overspending and correct early enough to get back on track. If it was not monitored in this way, it might not have been caught in time.

There is a lot of data, but it’s very consumable. The connection between data and decision making is very clear. It adds a lot of credibility to what we are trying to do with performance management and the intelligence space at the city, and it has really strengthened our relationships with departments.

JL: That's fantastic. Any final thoughts on the journey you would like to share?

EF: We have departments asking for information now. Their budgets are important to them and we involved them in creating the entire process, which created a lot of goodwill. The Data Loader as well as the projections and formulas have been real time savers. The entire experience helped us see how far we could take ClearPoint and it went as far as we wanted to go.

I really cannot thank the budget team enough. As I said, we had a process in place that worked, but they were willing to go beyond the status quo. They could see the long-term benefit and were not afraid to put in the upfront work to save time in the long-run and make the process better. Luckily, they have that mindset and they like to innovate and stay forward thinking.

Ultimately, our shared commitment led us to being able to complete one of the most impactful things we have done in ClearPoint to date.

How to Forecast your Budget Spend: An Interview with the City of Olathe, KS