How Olathe Built a Successful Dashboard with Business Intelligence Tools

ARTICLE | Sep 13, 2016

In 2015 the Budget Office and Performance Management Office teamed up and worked on a project to develop a better way for departments to monitor their expenditure data.

Previously, this expenditure information was being communicated by a very lengthy, multi-tab spreadsheet.  Spreadsheets have their place, and some people (like myself) love the detail but the objective for this project was to simplify the information in a quick to read dashboard and also provide a projection that would show if the department continues to spend as it has for the past three years, where it would end the current year. Departments needed better access to their budgeting information, but also need context.  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.

We worked with ClearPoint Strategies, our BI tool provider along with our 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.

Utilizing ClearPoint, a dashboard was then built.  It was published on the City’s internal website for all employees to view.  It was clean and simple to read.  Based on the projection, a color (Green, Yellow or Red) was given to the departments show how each expenditure category was looking based on where it was projected to end the year.

Before this process began, the Budget and Performance Management offices went and visited with the departments individually.  Example dashboards were shown and actual expenditure measures with charts were reviewed.  Through the research of holding these meetings with key stakeholders, the most useful and easy to understand approach was determined.

Now, we make 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.

From all this feedback there were three main ways identified by departments at Olathe for how best they would like to review their budget expenditure information.  First, a very high level view of their Total Operating Expenditures from the General Fund which includes: Personnel, Commodities, Capital Outlays, Contractual Services, and Transfers. 

With this dashboard, a status color was given based on what percent of the department’s budget was remaining compared to the percent of the year remaining. Also in this view a department could see specific dollar amounts for how much of their budget remained.  All in one quick view.

Second, department’s wanted to see a view of what Olathe likes to describe as Operating Expenditures.  These are basically all the large categories of expenditures except Personnel.  Departments felt they had more ability to adjust these operating expenditures during the course of the year and wanted to see them as a group.  This view was set up in the same fashion as the total expenditures. 

Finally, departments wanted to be able to drill into any expenditure category individually and see where they are projecting to end the year if their spending mirrored what it had been on average the last three years.  They are able to see such projections whether it’s for a specific expenditure or for the grouped expenditures such as Operating Expenditures, or the total expenditures.

Since implementation of these dashboards with key expenditure measures, departments have felt more able to quickly understand their status.  These have also been great management tools for the departments to reference with their staff.  Every month the budget analysts now use these measures in their monthly meetings with their departments.

For any City looking to find a way to connect their departments into using a newly acquired Business Intelligence tool or to get accustomed to using such measurement, strongly consider starting with your budget measures.  All departments have a budget and with the sophisticated ways you can now visually show charts as well as do projections, the value they bring to managing resources is high.

One additional powerful feature ClearPoint helped us leverage was the ability to load in the data needed for these measures in an automated fashion.  The budget analysts’ pull the data from our Enterprise wide financial system, then are able to easily upload it into ClearPoint.  Once in ClearPoint all measures and their corresponding charts or summary views are automatically updated.

The final step is we take this information and publish it on our internal Intranet site.  This allows any employee in the City access to this information without needing a login to ClearPoint or knowledge for how to navigate in the system.

Having this budget expenditure information available to our departments through our Business Intelligence tool, ClearPoint has been an innovative way to get complicated information to them in a very clear way to review.  It has helped departments make adjustments to their expenditures in a given year to avoid an overage.  It has also gained further support for the value of using such tools to present data.

You may also be interested in