Moved your pricing to new software? 102 rules went missing

You have moved decades of pricing from a spreadsheet into new quoting software. The import finished with no error message. Everyone relaxes. But “no error” only means the program did not crash. It does not mean everything arrived, and a pricing rule that did not arrive is a quote that comes out wrong on some future afternoon.

That is what happened with a fabrication business’s catalogue and pricing rules, loaded from a single master workbook. The import ran clean and produced 2,698 rule mappings. The workbook held 1,400 mappings, each of which should appear twice, once for the internal cost and once for the customer’s price. That is 2,800. The import was 102 short and had said nothing.

Left alone, that is how a gap becomes a wrong price: a quote leaves the building a little low or a little high, and nobody can trace it to a missing line in a spreadsheet loaded months earlier.

What was actually going on

The only thing that proves an import worked is a count-by-count comparison with the source, with every difference explained. For each kind of thing we held two numbers: what the workbook says should exist, and what a direct count of the live data returned.

Priced materials: 358 expected, 358 present. Labour and process items: 47 and 47. Suppliers: 39 and 39. Pricing rules: 349 plain and 13 composite, and exactly 362 present, split the same way. Underneath, 610 calculation steps and 310 material slots. For those two there was no independent figure to compare against, so they were recorded as consistent, not as verified. That is an honest distinction and worth keeping.

Then the mappings, and the 102 gap. The easy response to a shortfall of about four per cent is to call it good enough. A shortfall has a cause, and until you can name it you do not know whether the import is right.

The cause was specific. Twelve rule codes were named in the mappings but defined nowhere in the workbook: not on the rules sheet, not on the materials sheet, not on the services sheet. The importer correctly refused to create a mapping to a rule that does not exist, and every mapping naming one of the twelve was skipped. So the gap was not a fault in the import. It was the import reporting a fault in the source.

A second number confirmed it from the other side. A working file listed 369 rule codes ready to load, and the live data had 362. The difference of seven was exactly the twelve undefined codes that had got as far as that file. Two separate differences, both explained by the same twelve codes, with nothing left over.

What we changed

The check runs in both directions. We also asked whether the live data held any rule codes that do not appear in the workbook at all, and there were none. That matters as much as the first check, because counting only what failed to arrive misses what arrived from somewhere else: a half-finished earlier load, a hand-made row, a test record.

The order of work is the other change. Before loading, every rule’s calculation chain was checked on its own. That found 121 problems, each a step using a working figure that no earlier step had produced. A repair pass proposed corrected versions for around 120 rules, each with a guess at the parent, so a person could confirm rather than invent. Only then was readiness checked for every rule, and none was marked unfit. Importing first and finding problems afterwards mixes every problem with the question of whether your import caused it.

Nobody filled the twelve gaps. They were written down by code, in a file, as referenced but never defined, because the only definition lived in an estimator’s head.

What it did not fix

Those twelve rules are still missing. Only the business can say what they should calculate, and until somebody does, an item that depends on one will fail the first time it is quoted. That is deliberate: an invented pricing rule produces a wrong number silently, for as long as it exists, and looks exactly like a correct one. A missing rule fails loudly and names itself.

The pattern, for anyone moving off a spreadsheet

Ask for a table with three columns: what you had, what you now have, and for every row that does not match, the written reason. The reasons must add up to the differences exactly. A zero is not the target. Explained differences are.

Ask the same question in reverse, what is in the new system that was never in the old one. And do not let anyone close a gap by writing in data the source did not contain, however small it looks next to the total.

Where this ends up

That comparison is how a catalogue is loaded into Quote: every entity counted against the source workbook, and every reference that does not resolve written down as a gap in the source instead of filled in.