The formula map: reading a ten-year-old estimating workbook
The first deliverable of every calculation engagement is not software — it is a document about your spreadsheet. The formula map records what the workbook computes, what it assumes, where it is fragile, and which cells actually carry the price. It exists because the workbook is worth more than most owners think: it is the specification for the tool that replaces it, and the test that tool must pass.
What the sheet computes: from the quotation total back to the input cells
A formula map starts at the end. An estimating workbook exists to produce a handful of numbers — a weight in kilograms, a labour figure in hours, a price at the bottom of a quotation — so we list those outputs first and trace each one backwards, cell by cell, until we reach something a person typed in. In our demonstration stair calculation, the RM 7,025 total resolves into four lines: 260 kg of structural steel, 289 kg of treads and landing plate, 12.7 m of handrail, and 29 shop hours. Every line traces to the same two entries — a 3.00 m rise and a 1.00 m clear width.
The map records that chain on paper: one page per output, showing the cells it passes through and the worksheet each one lives on. Most owners have never seen their own workbook drawn this way. It is common to find that a sheet believed to compute thirty things actually computes six, with the rest carried forward from a job priced years ago — and that discovery alone changes what the replacement software needs to be.
What it assumes: the constants behind every kilogram and shop hour
Every workbook rests on constants somebody typed in once: a maximum riser of 180 mm, a comfort band of 550–700 mm, a section weight of 17.9 kg/m for PFC 150×75, a shop labour rate, a default margin. The map lists each one, where it lives, and when it was last true. Steel prices move, electricity tariffs move, code editions change — a decade of quiet service leaves sheets carrying BS 5950 factors half-migrated to Eurocode, and 2016 tariffs inside a 2026 machine rate.
An assumption is not a fault — every method needs them. The fault is an assumption that cannot be seen. In the rebuilt tool, each constant becomes a named, dated input with its source recorded, so the day the labour rate changes it changes in one place and every quotation after it is priced on the new figure. The formula map is where those constants are first dragged into daylight.
Where it is fragile: the cells one paste away from a wrong quotation
Fragility has a recognisable shape. A formula row that became a typed number the day someone 'fixed' one job, and never became a formula again. A VLOOKUP reading a rate table that has gained three rows the lookup range does not cover. A hidden column doing real work. Units mixed silently — bar beside kPa, mm beside inches — where one wrong entry invalidates a submission. The map marks each of these cells, because each one is a place the sheet will eventually produce a confident, well-formatted, wrong quotation.
The most dangerous fragile cell is the one only the senior estimator knows about — the override applied by habit, the row he knows to ignore. That knowledge is nowhere in the file. Part of mapping the workbook is sitting with the person who runs it and writing down what they do around the sheet, because the workarounds are part of the specification too — and they retire when the estimator does.
Which cells are load-bearing: one riser rule moves the whole BOM
Of the hundreds of formulas in a mature workbook, a short list actually sets the price. In the stair demonstration, the load-bearing cell is the riser count: ceil(3000 / 180) gives 17 risers, which fixes the riser height at 176 mm; the comfort rule turns that into a going of 267 mm, which sets the plan run, the stringer span, and the selection of PFC 150×75 over the heavier section. From there the 14.4 m weld run and the 29 shop hours follow arithmetically. Move that one cell and the whole BOM and quotation move with it.
Identifying that list matters because it is where validation effort belongs. A cell that formats a date can be rebuilt casually; a cell that decides between two channel sections cannot. The map ranks every formula by what it touches downstream, and that ranking becomes the test plan — the load-bearing cells are the ones exercised hardest when the rebuilt tool is run against your past jobs.
Why the workbook is never thrown away: it becomes the acceptance test
The workbook is never discarded and never rebuilt from a blank page. Ten years of a firm's pricing judgement live in it — every rule of thumb that survived contact with real jobs. It is the specification: the new tool must first do what the sheet does, in your vocabulary and your formats, before it earns the right to do anything more. That is why deliverable one is a map of the sheet, not a mock-up of the software.
The workbook's second job is referee. Acceptance on any calculation build is the Re-quote Test: twenty completed jobs you choose, re-computed by the new tool against a tolerance fixed in writing before the build starts. Every discrepancy is examined line by line — tool error, sheet error, or ambiguous input. Tool errors are fixed and the run repeats; the balance is payable when all twenty pass, and not before. Where the sheet itself turns out to be wrong, you learn it here — before it costs you a job. A workbook that can referee that test is an asset, whatever its formatting looks like.
Start here
Bring the workbook.
Bring the workbook to the free workflow review — an hour with the person who runs it, and an honest answer on whether it is worth rebuilding at all.