The real cost of spreadsheet-driven operations

A spreadsheet that runs a core process is not free: the reconciliation, the rework and the one person who understands it are charged to the business every month.

Spreadsheets earn their place. They are the fastest way to model a new process, and a team that builds its own tool usually gets closer to the real requirement.

The cost arrives later, and gradually. A file that began as working notes becomes the place three departments read from, and by the time anybody calls it a system it is load-bearing.

Where spreadsheet processes break

The failure modes are predictable, which is the useful part. Across the migrations we have run, a small set of weaknesses accounts for nearly all the pain.

Roughly in the order teams notice them:

1. There is no audit trail, so a figure that changed overnight traces back to nobody and no reason.
2. Copy and paste moves values without their formulas, and the break stays invisible until it reaches a report.
3. Validation is advisory, so a date typed as text or a duplicated reference passes straight through.
4. Access control is all or nothing, so whoever needs to read one column can edit every column.
5. Two people working at once produce two versions, and the merge is settled by judgement.

None of these is a fault in the tool. A spreadsheet was built for a person to model numbers, not for a process to run through it unattended for years.

Key-person risk and version drift

Most long-lived operational spreadsheets have a single author, who knows which tab is authoritative, which columns went stale, and which macro must never run against a part-closed month.

Version drift compounds it. A copy is taken for a board pack, a formula is corrected there, and the correction never travels back. Soon several files carry plausible names, each slightly right.

The exposure shows when the author takes leave. Work either stops or continues on assumptions nobody can check, and both cost more than the migration would have.

The reconciliation tax

The largest recurring cost rarely appears on a budget line: the hours spent each month proving that two systems agree. One team exports from finance, another maintains the sheet, and the gap is explained by hand.

That work produces no new information. It confirms, at expense, that a number with one rightful source has two. Price the day it swallows against the cost of removing the duplicate.

The tax is also a ceiling on growth. Doubling volume does not double the reconciliation effort by itself, but it multiplies the exceptions, and exceptions are where the manual hours go.

A gradual migration

Replacing a spreadsheet in one step tends to fail: the file encodes years of exceptions nobody remembers to specify. We move in stages, leaving the sheet in service until each stage is proven.

• Start with capture: move entry behind a validated form so new records arrive clean while old ones stay in the file.
• Then the calculation: reimplement the formulas in code with tests, and run both versions in parallel until they agree.
• Then the reporting: point reports at the new store so the wider audience stops opening the file at all.
• Then the exceptions: work through the manual overrides one at a time, since each is a rule somebody decided and never recorded.
• Keep an export: hand back a download in the shape the team knows, so nothing built on top of it breaks.

Keeping the spreadsheet where it works

None of this argues for banning spreadsheets. Modelling, one-off analysis and anything still being invented belong in a sheet, where reshaping the problem costs nothing.

The line sits around processes that run repeatedly, feed a customer or a payment, and would embarrass the business if they were wrong. Those need history, permissions and validation behind them.

• operations