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:

Capital Project Categories

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.

Capital Project Example

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.

Capital Project Types

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:

Capital Project Drivers

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).

Capital Project Drivers vs Categories

This same data can be presented in many different ways with little effort, for example:

Percentatge Capital Project Drivers

Capital Projects ranked by cost

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!



Published by Mike Thomas

Mike Thomas P.Eng. ENV SP, is the author of and Director of Engineering at the City of Revelstoke in the Interior of British Columbia, Canada.

6 replies on “Developing the Capital Planning Process”

  1. Hey Mike,

    I just went to the City of Victoria Strategic Plan and Budget town hall last night; it is really great to see municipalities moving in this direction.

    I did a bunch of research into information displays, and a consistent theme was that pie charts are terrible 99% of the time. The human eye is not so good at determining differences in arc and areas of pie slices.

    A much more communicative chart is a tree map, which utilizes the human capacity to very accurately resolve differences in areas of rectangles.

    1. Hey Ruben, glad to hear you are engaging in the financial plan process in Victoria!

      I try to not use a pie chart with more than three values and try to provide meaningful labels. I often see the “diminishing values” slivers in pie charts with no context other than inferring that they are meaningless categories. Or the other thing I’ve seen is lots of small categories and then one big one, say 10% with a label “misc”!

      I agree that tree maps have superior readability for more categories, but Microsoft excel doesn’t natively provide these out of the box, so most people don’t know where to start.

  2. Great article Mike. We are just in the process of developing a Capital Asset Management program at the City of Selkirk. I can hardly wait until we have tools like this in place to communicate within the management team and through to council and the public. I have found the more we put discussions about assets in the context of their true cost and investment requirements over time, the better our decisions. I’ll be sharing this post with our CAMP project leaders.

  3. Thanks Duane, communication is the challenge, the Socrata software example shows how data can be transformed into contextual information. My efforts are a modest attempt at challenging how we look at spreadsheets!

    While we wrestle with Capital Project priorities on one hand, city staff have been training up on AssetFinda software, which we’ll use for our asset register, PSAB 3150 requirements, work orders, recording inspections and condition, and predictive financial modelling – basically everything associated with every asset the City owns. So far, from my experience with AssetFinda, I highly recommend them –

    I’ll write about our progress here over the next couple of months, it’s been a busy week of servers, iPads, data tables, GIS, learning how to operate the software.

    1. Thanks Mike. Looking forward to reading more about your progress. I’ve sent my team the link. This year we’re collecting the raw data and hopefully next year we’ll be read to transition it to a software application. That’s the plan at this point. Looking forward to seeing what you do with it!

Comments are closed.