Track equipment and assets without a spreadsheet graveyard

Every organisation has an asset spreadsheet. Almost none of them are accurate.

The reason is the same as with inventory: the sheet records the current state — where a thing is, who has it — and current state is exactly the part that changes without anyone updating a spreadsheet.

Record the events instead, and the state derives itself.

The model

Assets — one record per physical thing. Asset tag, serial number, category, model, supplier, purchase date, purchase cost, expected life, status (In service, In repair, Retired, Lost).

Locations — buildings, rooms, vehicles, "with employee", "at supplier".

People — who can hold custody.

Custody events — the movement table. Asset, from location/person, to location/person, date, reason (assigned, returned, transferred, sent for repair), and who authorised it.

Maintenance events — asset, date, type (scheduled service, repair, inspection, calibration), cost, supplier, outcome, next due date.

Documents — warranties, manuals, certificates, photos, attached to assets.

The current location and current holder are not fields you edit. They are the "to" values of the most recent custody event — a max-by-date lookup. That single decision is what keeps the register honest.

Why events beat state

With a Current holder field, when it is wrong there is nothing to investigate. With custody events you get, for free:

• Full chain of custody for every asset, which is what auditors, insurers and police reports actually need.
• Who had it when it broke — join maintenance events against custody history by date.
• Average time in each state, so you can see whether repairs are taking longer than they used to.
• Assets out with people who have left — a view crossing current custody against employment status. This is where most "lost" equipment actually is, and no state-based register can find it.

That last view typically finds real money the first time it is run.

Identity matters more here than anywhere

Assets are the one domain where physical labels and database records must agree, and where getting it wrong is expensive.

Use an asset tag, not the serial number, as your reference. Serials are manufacturer-controlled, sometimes duplicated across product lines, occasionally unreadable, and unavailable until the thing arrives. An asset tag is yours: sequential, printed on a durable label, never reused.

Store the serial too — you need it for warranty claims — but do not make it the identity. See why every table needs a stable ID.

Never reuse a tag. When an asset is retired, its tag retires with it. Reusing tags merges two assets' histories, which destroys exactly the audit trail the system exists to provide.

If you can, put a QR code encoding the asset's record URL on the label. Scanning to open the record removes most of the friction that causes registers to go stale.

Depreciation and value

If finance needs book values:

• Age — formula from purchase date.
• Accumulated depreciation — formula from cost, expected life and method.
• Net book value — cost minus accumulated depreciation.

Keep purchase cost frozen. It is a historical fact. Never overwrite it with a current valuation — store that separately if you need it. Same principle as frozen prices on line items.

Also useful: Total maintenance cost as a rollup of maintenance events. Compare it against net book value and you have the replace-or-repair conversation on evidence rather than instinct. An asset whose maintenance has exceeded its remaining value is one you are keeping out of habit.

Scheduled maintenance

Two ways an asset becomes due:

• Time-based — every 12 months. Next due = last service date + interval.
• Usage-based — every 500 hours or 10,000 miles. Requires a usage reading, which is itself an event table: asset, date, reading.

Usage-based is more accurate and more work. Use it only where the cost of over- or under-servicing justifies the extra data entry — vehicles, production machinery, anything safety-critical.

Either way, Next due should be computed, not typed. A typed date is a promise nobody keeps.

The views

• Register — all active assets, grouped by category, with current location and holder.
• Due for service — next due within 30 days, sorted ascending. The maintenance queue.
• Overdue — past due and still in service. Should be empty; usually is not.
• With departed staff — current holder is a person whose status is not Active. Run this monthly.
• In repair — assets whose latest custody event sent them to a supplier, with days elapsed. Finds things that went for repair eleven months ago and were forgotten.
• Replace or repair — total maintenance cost > 50% of net book value.
• Unverified — assets not seen in a physical audit for over a year.

Audits

A physical audit is not an edit to the register. It is a set of verification events: asset, date, verified by, found at location, condition.

Then variance is computable — assets not found, assets found somewhere other than expected. Approving an audit generates the custody events needed to correct the record, each traceable to the audit that caused it.

If audits overwrite the register directly, you lose the ability to measure how accurate your register was, which is the main thing an audit is supposed to tell you.

Automations

1. Next due within 30 days → create a maintenance task and notify the owner.
2. Custody event to a person → send them a summary of what they now hold and the terms. Also creates the paper trail.
3. Person marked as leaving → list their assets and notify their manager, before their last day.
4. Warranty expiry approaching → notify, so a claim can be made while it is still free.

Number 3 is the one with the clearest financial return.

Start here

Assets, Locations, Custody events. Three tables. Current location as a derived value rather than a field.

Add maintenance when something fails that should have been serviced. Add depreciation when finance asks. Add usage readings only for the assets where it genuinely matters.

The structural decision to get right on day one is events over state. Everything else can be added later — but a history you never recorded cannot be reconstructed, and the history is the whole point.