Case 05of 08
Acre Replacing the spreadsheet that ran the company
A food wholesaler with three warehouses ran its entire stock position out of a forty-tab spreadsheet that one person understood. The software was the easy part. The risk was eleven years of rules nobody had ever written down.
Stock position during the parallel run — variance falling to zeroFig. 01
The problem
Acre’s stock lived in stock_master_v11_FINAL.xlsx: forty tabs, nine thousand formulas and eleven years of accumulated correctness. It worked. It was also a single point of failure with a pension plan.
Physical stock matched the spreadsheet 71% of the time. The gap was costing about £140,000 a year in duplicate orders and short-date waste, and month-end close took four days because two of those days were spent reconciling by hand.
- Stock accuracy 71%, measured against a monthly physical count
- ~£140k a year in duplicate orders and avoidable waste
- Month-end close: four days, two of them manual reconciliation
- One person could explain the sheet, and she was two years from retiring
- Two previous replacement attempts abandoned, both by larger firms
Before and after
Where the truth livedBusiness rules recovered from a spreadsheet and one person’s memory. Eleven of them were wrong, and had been for years.
Instrument before replacing
Both previous attempts had failed the same way: build the obvious model, migrate, discover in month one that the spreadsheet was doing eleven things nobody mentioned, lose confidence, revert.
So we did not replace it. For five weeks the new system read the same inputs and produced its own position alongside the spreadsheet, which stayed authoritative. Every disagreement was a discovered rule. We found forty-seven, of which eleven were wrong and had been quietly costing money for years.
- Five-week parallel run, spreadsheet authoritative throughout
- Every variance triaged daily with the person who owned the sheet
- 47 rules recovered; each became a named, individually testable rule
- 11 of them were wrong — corrections agreed with finance, not assumed
- Cutover only after 20 consecutive days of exact agreement
The parallel run
Five weeks of disagreeingVariance is the absolute value of disagreement between the two systems, not net, so offsetting errors do not cancel out. Week three includes a £4.1k difference that turned out to be the spreadsheet being wrong; it is counted here as variance anyway. Cutover shows the first full week after.
A model that can be re-run
The spreadsheet stored positions and patched them. That is why it drifted: a mistake in March was still in the number in November.
The replacement stores movements — every in, out, waste and correction as an immutable event — and computes position from them. A correction is a new event, never an edit. It means any day’s position can be recomputed from scratch, which is what made the parallel run possible and what makes an audit a query rather than a fortnight.
- Append-only movement log; positions derived, never stored and patched
- Any historical position recomputable exactly, including corrections
- Rules versioned, so last March is evaluated with last March’s logic
- Reorders proposed by the system, always placed by a person
- Month-end close became a report rather than a process
The model
Movements, not positionsResults
Each with its methodTwo firms tried this before and both gave up. The difference was that max spent five weeks proving they understood our sheet before asking us to trust theirs.
LogWeek by week
The build log
Thirteen weeks, five of which produced no new features at all.
Paid discovery. Two days with the spreadsheet open and the person who wrote it talking. Formula audit, not interviews. Scope written around the risk.
Movement model and ingestion built first. No user interface worth showing; the demo was a nightly comparison email.
Parallel run. Every morning a variance list, every afternoon a rule named or a correction agreed. Feature work deliberately paused.
The actual interface, built once the rules were known. Faster to build than it would have been in week one, because nothing had to be guessed.
Reorder proposals and approvals. Explicitly no automatic ordering, at the client’s request and our agreement.
Cutover after twenty clean days. Spreadsheet kept read-only for a quarter as a comfort blanket, and never needed.
Thirty days of fixes. The person who owned the spreadsheet now owns the rules, which is a better job and she says so.
What shipped
Six deliverablesWhat we would do differently
We nearly cut the parallel run. In week two it looked like an expensive way to send emails, and we proposed shortening it from five weeks to three to protect the budget. The client said no. Rules 34 to 47 — including four of the eleven wrong ones — were found in weeks four and five. A parallel run is now a non-negotiable line in the scope whenever we replace a spreadsheet, and it is priced as such rather than offered as an option.
Who did it
CreditsDiscovery, data model, rules extraction, interface, cutover. One person, twelve weeks.
Infrastructure and the nightly comparison pipeline, three weeks, during the parallel run.
Finance director as sponsor; the stock controller who wrote the original spreadsheet and sat through five weeks of being questioned about it daily.
Two build slots openNext start: March