The workbook that priced every job in a machine shop
Manufacturing · 14 stagesA precision machine shop quoted every job from one Excel workbook with macros underneath. It had been growing for years: a sheet added whenever a question came up, a column added whenever a customer asked something new. It decided the price of everything that left the building, and one person could safely open it.
| A | B | … | BW | |
|---|---|---|---|---|
| 9870 | AISI 430 | 7.75 | … | #REF! |
| 9871 | Flat bar | 7.85 | … | 6.12 |
| 9872 | … |
- Two live price lists.
MaterialsandMaterialsNewboth open, both in use. Quotes were being written from whichever one the person happened to click, and only one of them was current. - Eight duplicate sheets, kept in step by hand. A full parallel set ending in
Demo. Every price change had to be made twice, from memory, by the same person — and there was no way to tell whether it had been. - A formula reaching row 9,872, column seventy-five. Nobody could see the whole of it, so nobody checked it. When a reference below it broke, the workbook did not stop: it returned a number that was simply wrong.
- A
#REF!sitting in the material cost column. Not an alarm — just a cell, in a sheet nobody scrolls to, feeding a total somebody quoted from. Foglio1, the default name, never changed. Nobody remembered what was on it and nobody dared delete it, so it stayed, and it was still being recalculated.- Macros written in 2024 that nobody had opened since, and that nobody still at the company could read.
The workbook was not going to fail loudly one morning. It was going to keep producing prices that looked exactly like the right ones.
Fourteen stages, in the order the shop needed them rather than the order that was easiest to build. The costing engine first, with the arithmetic pulled out into small pieces that can each be tested on their own — which is why there are 861 automatic checks and not forty. Then price lists and machine records, customers, quote documents, cost drift over time, estimate against actual, and last the job sheets that go out to the floor.
- One price list, with history. The duplicate sheets are gone. A price change happens once, and the old value is not overwritten — it is kept with the date it stopped applying.
- Prices carry a date, so old quotes still explain themselves. A quote written in March can be reconstructed in November, and a price increase goes out with a letter that shows the customer the arithmetic.
- A quote starts from the drawing. The title block is read out of the PDF and the stock bar out of the STEP file, instead of two people retyping both and one of them mistyping.
- The shop floor got a screen of its own, written separately from the office one. Different hands, different light, a different question being asked.
- Nothing fails silently. Where the workbook returned a plausible wrong number, the software refuses and says which input is missing.
- Several sites on one system, each seeing only its own numbers.
- Sheets to keep in step
- 21 → 1
- Unchecked formulas
- 1,376 → 0
- Automatic checks
- 861
- People who can safely change it
- 1 → all
The point was never the app. It was that the shop stopped having one person it could not afford to lose.