BOM in Excel — technical debt with part numbers
A BOM in a spreadsheet isn't a documentation problem. It's the moment a company loses its source of truth about the product, and the bill comes due in procurement and service.
BOM_final_v7_NEW_corrected_Marek.xlsx
That’s not a filename. It’s a change log nobody had the nerve to call by its real name. Seven versions, two “final” tags, one person’s name in the middle, because at some point it was easier to note who last touched it than to establish which version is the one that counts.
A BOM in Excel isn’t a documentation problem. The problem starts when someone makes an irreversible decision based on that file: orders a part, starts a production run, sends out a service technician.
A BOM is a map of the product’s identity, not a shopping list
To procurement, a BOM looks like a list of things to order. To production, an assembly guide. To service, a spare-parts catalog. Everyone’s right. And everyone sees only their own slice.
What a BOM actually answers is one question: what this product is, down to the part number and revision. Not “roughly what”, but exactly what, because in hardware+software, tolerance, enclosure, firmware and power are physically coupled, not just linked on paper. Changing one part number can shift thermals, break a certification, or break compatibility with firmware nobody planned to update.
If that map has no single owner and no single authoritative version, every department draws its own. The gap isn’t visible day to day: files open fine, the numbers add up, the project moves forward. It only shows up once two versions of the map have to meet inside the same physical device.
Four ways Excel starts lying to you
It’s not that a spreadsheet can never be disciplined. Excel in a controlled repository, with version history and a clear owner, can work fine. The point is that in most companies nobody does that, and even fewer check. The file itself has no mechanism that forces a single version of the truth: no revision lock, no change-approval workflow, no alert that someone else already touched it. Those are exactly the functions that ANSI/EIA-649, the configuration management standard, lists as the baseline: identification, change control, status accounting, and audit. Without deliberate discipline, a spreadsheet delivers none of them on its own. Hence four recurring failures.
Two departments have two different versions. Engineering works off the latest revision, because they just fixed a bug. Procurement orders from a version two weeks old, because nobody sent them the update. Both sides are convinced they’re right, and formally, they are, each for their own file.
A supplier changes a component under the same number. The catalog number matches, the physical version of the component doesn’t. The manufacturer changed something internally without changing the number, because to them it’s a minor correction. To your product it’s a different tolerance, a different current draw, or a different mounting dimension.
Production orders “close enough”. A component runs out on the shelf. Someone substitutes an equivalent with the same nominal value, because “that’s what we’ve always done.” The spreadsheet doesn’t change, nobody edited the file, so formally the BOM still matches. Physically, the product left the line in a configuration nobody tested.
Service doesn’t know which part fits. A device comes back after eight months. The technician opens the current BOM and orders what it shows. But the unit shipped in a configuration different from what’s in the file today, because between shipping and now, the sheet quietly moved on, with no record of which version went into which unit.
The cost that grows in silence
None of those four scenarios hurts right away. It hurts later, and disproportionately more.
A 2004 NASA/INCOSE analysis, based on real hardware+software projects (satellite, aircraft, spacecraft), measured how much more expensive a fix gets depending on the stage at which the error is caught. An error caught during integration and testing costs 21 to 78 times more than one caught at the requirements stage. An error that leaks into the field, to the customer, costs 29 to over 1,500 times more. That’s data from aerospace projects, not from a typical company building controllers or sensors, but the mechanism itself (the later you catch an error, the more it costs to undo) isn’t a rocket-specific quirk.
A separate 2022 AFIT analysis of 2,434 real US defense contracts confirms it from another angle: the actual budget reserve for engineering changes turned out higher than the industry rule of thumb (10% during development, 5% during production and sustainment). The real numbers were 13.25% in development, 5.5% in production, and back up to 13.5% during the field/sustainment phase. The development phase is expensive for other reasons, it generates a lot of changes by nature, but for production versus field the direction matches the NASA data: the same change costs more once the product has reached the customer than it would have cost during production.
This isn’t a single-company scale problem. A survey by Arena Solutions across 405 product companies (a PLM vendor, so read this as a direction, not a decimal-point-accurate figure) found that nearly half of them manage their BOM in a spreadsheet or with no system at all. Among those without their BOM connected to supplier data, more than half report product delays, and slightly fewer report ordering the wrong part as a direct consequence.
The well-known “1-10-100 rule” in the industry (an error costs 1x at design, 10x at production, 100x after shipping) has circulated for years as a mental shortcut. Its origin is internal IBM training material from 1981, not a peer-reviewed study. The direction holds. For actual multipliers, NASA and AFIT are a better source than a corporate slogan.
The resistor that changed the product
The product passed full qualification testing. Configuration approved, documentation closed, the run went to production.
A few months later, procurement ordered an alternate resistor: same nominal value, different manufacturer, different thermal characteristics that weren’t on the first page of the datasheet. Nobody made a bad call on purpose. The distributor had flagged it as an equivalent, and purchasing had ordered components the same way for years: same value, same package, buy cheaper if you can.
The internal part number in the BOM never changed. Formally, it’s still the same line item. Nobody logged a change, so nobody had a reason to bump the revision or ask an engineer to sign off. Operationally, the product left the line in a configuration nobody had tested under target conditions, and the process meant to catch that had nothing to catch, because formally nothing had happened. The difference only surfaced in the field, at elevated ambient temperature, in complaints support spent a week trying to pin on anything other than the component everyone assumed was “the same”.
Lesson learned: “equivalent” on a distributor’s datasheet and “equivalent” in the context of a product’s tested configuration are two different words that happen to be spelled the same. One is about a component in isolation. The other is about the system that component runs inside, alongside everything else. A BOM that doesn’t distinguish between the two protects against nothing, even when nobody formally broke it.
The minimum standard before launch
A good BOM answers the questions before someone has to ask them mid-crisis: who owns it, which version is current, what’s allowed as a substitute, and who signed off on it. You don’t need to roll out a PLM system a week before launch. You need to enforce discipline that a spreadsheet can actually sustain.
Eight things that should be unambiguous before a product leaves the factory:
- one person (the owner) is accountable for which version is authoritative
- every change has a date, an author, and a reason in the revision history, not just a new number in the filename
- the list of approved substitutes is explicit, not living in a buyer’s memory
- swapping a component requires engineering sign-off, not just stock availability
- it’s known which certifications are tied to which configuration
- service has access to a BOM that matches what actually shipped, not what’s current today
- components approaching end-of-life (EOL) are flagged before they vanish from the market without warning
- what production sees in the ordering system matches what engineering sees in the BOM
With a handful of products and a single site, that discipline holds up in a spreadsheet. With dozens of SKUs, several locations, and a rotating buying team, Excel alone stops being enough, but what’s described here is the precondition for whatever system eventually replaces it, not a substitute for it. A company that can’t hold this discipline in a spreadsheet won’t hold it in a PLM system either. It’ll just pay more for the same mess.
Your BOM lives in Excel, and the product is about to ship to production or field service? Before you scale, check one thing: does everyone in the company work off the same file, and is the answer to that question known for certain, or just assumed?
If the answer is an assumption, you already have technical debt with a part number attached. Sooner or later, someone’s going to order it.
If your BOM lives in a spreadsheet and the product is growing faster than the discipline around its versions, let’s check it before your customer does.