The pricing spreadsheet had twelve formulas that were referenced but never written

The pricing workbook for a made-to-order fabrication business had 710 product-feature rows and 633 formula rows in it. Twelve of the formula codes those rows referred to did not exist. They were named in the product mappings, they were relied on to price real jobs, and there was no definition for them anywhere in the file.

Nobody had noticed, because a person was filling the gap. The estimator hit one of those items, recognised it, and priced it from memory. The spreadsheet never complained, because a spreadsheet does not know that a name you typed into a mapping column was supposed to resolve to something.

That is the honest starting point for any project that sets out to replace tribal pricing with software: the spreadsheet is not the specification. It is a partial specification with a human patching the holes at runtime, and until you enumerate the holes you do not know how big the job is.

Why it ends up in a spreadsheet in the first place

It is worth being fair about this. Bespoke pricing goes into a spreadsheet for good reasons, and they are all reasons that ordinary business software fails to provide.

The first is that the price is a calculation, not a lookup. A catalogue business has a price list. A fabrication business has an item that is 72 inches long instead of 60, in one grade of sheet rather than another, with three shelves instead of two, and the price of that specific configuration has never existed before and will never exist again. There is nothing to look up. Something has to compute it.

The second is that the person who knows the calculation is not a developer, and a spreadsheet is the only computing environment they have ever been given where they can write a rule and see the answer immediately. That is a genuinely good property. Most business software takes it away and gives nothing back.

The third is that the rules genuinely change. Sheet rates move. A new variant of a product gets offered. A labour charge goes up. In a spreadsheet, all three are a two-minute edit. In most systems they are a support ticket.

So the spreadsheet is not stupidity. It is the only tool that has ever matched the shape of the problem. The trouble is what it does not do.

What actually breaks

Nothing checks that a reference resolves. That is the twelve missing formulas. In a spreadsheet a name is just text until someone looks at it. There is no moment at which the file says “you pointed at something that is not there”.

Nothing checks that a chain is intact. When the workbook’s formulas were parsed and audited mechanically, 121 separate step-chain problems came out — expressions referring to an intermediate value that the formula never computed. One formula’s second step used a working value that its first step never produced. Again, this had been running. A person reading the sheet supplies the missing step without noticing they are doing it.

Nothing distinguishes a rule from a copy of a rule. The same calculation appears in dozens of rows because each product got its own row. Change the rule and you have to find every copy. Miss one and that product quietly prices on the old rule for as long as nobody checks.

There is no record of what produced a number. A quote goes out. Six weeks later the customer queries a line. The workbook has moved on — rates were updated, a row was edited — and the arithmetic that produced the original figure is simply gone. You cannot reconstruct it, so you either concede the point or argue from memory.

One person is the system. This is the one the business feels. The estimator is the only one who knows which of the four near-identical rows is the right one for this job, which items carry an unwritten extra, and what to do when the customer wants a size the sheet does not really cover. When that person is on leave, quoting stops.

The thing that is easy to get wrong when you replace it

The instinct is to read the spreadsheet, understand the pricing, and write it into the application. Encode the rules as code, ship it, done.

That reproduces every failure above and adds a new one. The rules still change, but now changing them requires a developer, a deploy and a release cycle. The person who actually owns the pricing can no longer see whether the software agrees with them. You have taken the one good property the spreadsheet had — the owner of the rule can edit the rule — and thrown it away in exchange for nothing.

What worked instead was to keep the boundary where the spreadsheet had it and move only the guarantees. The formulas stay as data, authored by the business, evaluated at quote time. The software owns the things a spreadsheet cannot do: that every reference resolves, that every step chain is intact, that a rule exists once and is pointed at from many places, that failure says why it failed, and that the arithmetic behind a number is stored with the number.

Framed that way, the migration stops being a translation exercise and becomes an audit. You are not rewriting the pricing. You are enumerating it, proving each piece resolves, and putting it somewhere with a checker attached.

What the audit actually cost

Enumerating is not free, and it is worth knowing what it involves before quoting the work.

Every formula had to be parsed, not read. Every identifier in every expression had to be classified — is this a dimension the user enters, a material the user picks, a fixed rate code, or a working value from an earlier step — and anything that fell into none of those categories flagged. Broken step chains had to be repaired against a proposed fix and then confirmed with the business rather than guessed at. The twelve unresolvable codes had to be written down as unresolvable and handed back, because the only person who could define them was the person who had been carrying them in their head.

That last part matters. The gaps were reported as source-data gaps, not fixed silently. A pricing rule you invent to make an import succeed is worse than a pricing rule that is missing, because a missing one is visible and an invented one is not.

The rule

Before you write a line of the replacement, parse the spreadsheet mechanically and produce two lists: every reference that does not resolve, and every calculation chain that is broken. Both lists will be longer than the business expects, and neither will have caused a visible problem yet, because a person has been absorbing them.

The size of those two lists is the real scope of the project. Everything else is screens.