In my role at the City of Revelstoke, council has been working through the financial plan process, and this year I thought it might be useful to deliver the capital project information in a couple of ways that I hadn’t seen before. These ideas have been in my mind for a couple of years now, as I consider how to slice the financial data in meaningful ways for council and the public to understand decisions. Behind this data is often many hours of work by staff for each project in gathering costs, researching alternatives and determining the best timing for doing the projects, but despite that work, sometimes the most difficult part is presenting the data.
To start, I divided the projects into categories of assets under the program areas, for example:
This may seem intuitive to a municipal engineer, but when asking the public to consider over a hundred capital projects in a plan and determine where it fits into the big picture, it is important to be able to divide the data into meaningful chunks. The other benefit of performing this categorization is that it is possible to determine, again in small chunks, (but not down to the individual asset level), how much investment into each category has been made in a given year, and over time, comparing that to the replacement value of the assets and the depreciation cost incurred by the municipality. In the following fictitious example, if an average of $200,000 is invested in sewer collection (valued at say $20M), it would be reasonable to say that we were averaging 1% replacement per year, but in this example, the financial wizards have estimated that the sewer collection assets were depreciating at 1.5% per year, thus losing the City $100,000 in effective net worth each year. To be able to show this level of data across the range of municipal assets by category will be invaluable for decision-makers in the capital planning process.
The second division of projects was by the type of project or “What are we doing”. I played with the following three types, where new assets were those projects that resulted in the expansion of an asset or service, while renewal and replacement projects were those that maintained the asset or level of service, or only incrementally improved it.
The third division of projects was by the project drivers or “Why are we doing it”. This is the toughest division to nail down, and I’m not convinced that lifecycle cost and level of service are really separate drivers, but for the purpose of the exercise, I established these three drivers for capital projects:
Level of service is a big topic in asset management, and as a project driver, I treated those projects that were aiming to maintain or improve a level of service as being categorized by this driver. Risk projects are those that aim to avoid or manage a corporate, financial or legal risk, and projects falling under lifecycle cost are those that aim to reduce the total lifecycle cost of an asset.
Using a pivot table, it is possible to slice the categories against the project drivers against total cost, helping show where the budget is being spent and hopefully why (note that all of these numbers are preliminary data for the City of Revelstoke capital budget).
This same data can be presented in many different ways with little effort, for example:
This is a first cut at attempting to provide some meaning around the capital projects, there is still a lot of work to do on determining if these are the right ways to slice the data, or if there are other, more meaningful divisions. As a related aside, I’ve been interested in what a product like Socrata’s financial transparency suite could do for our budget process, see an example from Redmond, WA here, and see many benefits in how they present the data and all for inquisitiveness from the public; but I think I want more control of how to mix it up!
Project prioritization is another area I’ve been considering, and I’ll likely write about that here soon. Leave a comment below, or contact me if you have any comments or questions, thanks for reading!