Civicade

Assets

Starting a municipal asset inventory from a spreadsheet

How to build a usable inventory of municipal assets from the spreadsheets you already have, and what information to capture first.

· 1 min read · Civicade

You probably already have an asset inventory: it's just spread across spreadsheets, a fleet list, a hydrant map and someone's memory. The first step toward better maintenance is putting it in one place.

Decide what counts as an asset

Start with things you inspect or maintain: streetlights, hydrants, vehicles and equipment, building systems (HVAC, generators), playgrounds, pump stations. Leave out small consumables.

Capture the essentials first

For each asset, the useful minimum is:

  • a unique tag you can write on it or find it by;
  • a type (hydrant, vehicle, HVAC unit);
  • where it is, as an address or landmark and, if you have them, coordinates;
  • which department is responsible;
  • its condition, on a simple scale such as 1 (failed) to 5 (excellent).

Purchase date, cost, manufacturer, model and serial number are valuable but can be added over time.

Clean the spreadsheet before importing

  • Make every tag unique.
  • Use the same type names throughout ("Fire hydrant", not "Hydrant", "FH" and "hydrant").
  • Put dates in one format.
  • Split location and coordinates into their own columns.

Import into a system that checks every row first and imports nothing if any row has a problem, so you don't end up with half an inventory.

Set inspection intervals by type

Once assets are in, set how often each type should be inspected. The system can then show what's due, and assets that have never been inspected.

Improve it as you go

An inventory is never finished. Each inspection and work order is a chance to correct a location, add a serial number or update condition.

This article is general information, not legal advice. Public records requirements vary by state and locality. Confirm your obligations with your municipal counsel and official state guidance.

All insights