What a Spreadsheet Can and Cannot Do for Heat Traceability
· 4 min read
A spreadsheet can record heat numbers accurately and can stay accurate for a long time. It fails at one specific point — when a quantity becomes a sum across heats — because the information needed to separate them is not misfiled but discarded. That failure is structural rather than a matter of discipline, and it is worth understanding before deciding whether to replace anything.
Most forging shops that track heat numbers started in a spreadsheet, and a fair number are still there. It is worth being precise about what that actually costs, because the usual framing — that spreadsheets are error-prone and software is not — is both unconvincing and mostly wrong.
A spreadsheet is not especially error-prone at recording a heat number. A cell
holds H12345 as reliably as any database column. Shops run this way for years
and pass audits doing it.
The failure is narrower and more specific than that, and it is worth naming.
The failure is aggregation, not entry
Traceability breaks at the moment a quantity becomes a sum across heats.
Say you receive 4,000 kg of 4140 across two deliveries, 2,000 kg from heat
H12345 and 2,000 kg from heat H67890. Both are recorded correctly. Then
someone needs to know how much 4140 is on hand, and the answer — 4,000 kg — goes
into a planning sheet, or a WIP total, or a stage summary.
That number is arithmetically correct and completely untraceable. Nothing downstream of it can say which parts came from which melt, because the distinguishing information was not entered wrongly or filed in the wrong place. It was discarded at the moment of summing.
This is why the problem does not respond to being more careful. Care prevents mistakes. This is not a mistake — it is what addition does.
Why it cannot be reconstructed
The instinct when this is discovered is to go back through the records and work it out. Occasionally that succeeds, when physical segregation happened to survive and someone can still see which rack the material came from.
Usually it does not. Once material from two heats has been consumed against a pooled quantity, no record anywhere contains the information needed to split it. You cannot recover data that was never written down, and a report run later is reading the same rows that were already insufficient.
The practical consequence is that traceability has to be preserved continuously, by every operation, or it is not preserved at all. There is no end-of-month reconciliation that fixes it.
What that means for containment
The cost shows up when something goes wrong.
A mill reports a problem with a heat. The question is which parts came from it and where they are — in stock, in process, or shipped. A shop that held quantities per heat produces a list. A shop that pooled has to bound the problem some other way, usually by date range, which means treating every part shipped in a window as suspect.
The difference between those two positions is not a records-keeping preference. It is the difference between recalling forty parts and recalling everything that left the building in six weeks.
What has to be true of a replacement
If a spreadsheet is going to be replaced, the replacement earns its place by making the failure impossible rather than discouraged. Three things do that work:
Quantities held per heat, not per item. On-hand is not a single number against
4140 round bar. It is a set of numbers, one per heat, and the total is derived
from them rather than stored instead of them.
Every stage records what it consumed and what it produced. Cutting takes from a specific heat and produces billets carrying that heat. Forging takes those billets and produces parts carrying it. The identity travels with the material through each transformation rather than being attached at the end.
Stock moves on posting, not on typing. An entry being drafted or corrected should not change inventory. Only a posted entry should. Without that line, an in-progress record and a real movement are indistinguishable, and people learn to distrust the numbers — which returns you to a spreadsheet in a more expensive form.
Ongoing Forge is built around those three, which is the honest reason to mention it here. But the test above is worth applying to anything, including a system you already own. Plenty of ERP installations pool quantities by default and are sold as traceable.
When a spreadsheet is fine
It genuinely can be. If you run a small number of heats, keep them physically segregated, and never take a total across them, a spreadsheet preserves the chain and an audit will find it does.
The question worth asking is not whether your spreadsheet works today. It is whether it works because of how it is built, or because everyone involved remembers not to do the one thing that would break it — and what happens the week someone new does the summing.
Common questions
Can a spreadsheet handle heat number traceability?
For a small operation with disciplined habits, yes — up to the point where quantities are aggregated. A spreadsheet holds a heat number against a row perfectly well. What it cannot do is prevent a total from being taken across rows with different heats, and once that total is the number people work from, the link between part and melt is gone.
What is the single most common way traceability is lost?
Pooling. Two heats added into one on-hand figure. It is not a data entry error and it does not look like one — the resulting number is arithmetically correct. The information that would have separated the two was simply not carried into the total.
Do I need to replace spreadsheets to pass a customer audit?
Not necessarily. What an audit tests is whether you can walk from a shipped part back to a heat and its certificate. If your spreadsheets preserve that chain at every stage, they pass. The question is whether they do so by structure or by everyone remembering to be careful.
What should replace a spreadsheet, if anything?
Whatever makes the failure mode impossible rather than merely discouraged. Concretely: quantities held per heat rather than as totals, every stage recording what it consumed and what it produced, and stock moving only when an entry is posted rather than while it is being typed.