In this guide 6 sections
A spreadsheet bill of materials is failing when revisions overwrite each other, substitute parts live in someone’s head, and costing no longer matches what was built. If the shop floor builds from a printout that differs from the file on the stores clerk’s screen, the sheet has been outgrown.
Revisions overwrite each other
A mixer grinder gets a new jar lid, and the designer saves a fresh copy of the sheet while the stores clerk still has the old one open. Production keeps pulling the old lid.
Spreadsheets record no reasons. They hold the latest numbers and little else, so you cannot ask which revision a given serial number was built to or who approved the change.
Try a test. Pick a product shipped last month and ask the designer, the stores clerk and the costing accountant for its bill of materials. Three answers mean it failed.
- File names like final, final2 and latest
- No date or reason attached to a changed line
- Production, stores and costing using different copies
- Service teams unable to see what a returned unit contained
Substitute parts live in someone’s head
A supplier runs short of a 5 mm rivet, so the foreman swaps in a 6 mm one with a washer, and the line carries on without a pause. It works. Nobody writes it down. Nobody tells the stores clerk either. Three months later a handle loosens on a customer’s kitchen counter, and no one can tell which units carry the swap or which supplier lot they came from.
A bill of materials needs approved alternates, each with a reason, an approver and a validity window, so the stores clerk knows which swap is allowed this month. A spreadsheet can list them in a column, but it cannot stop an unapproved one being issued or flag that the alternate costs more.
Stores clerks then guess. Guesses become habits. Habits become defects.
Costing no longer matches what was built
Costing sheets use old rates. Real builds include scrap from pressed steel sheet, rejected heating elements, rework hours and packing material that was never on the list.
When the cost on the sheet and the cost in the books diverge, people stop trusting either one, and pricing decisions then rest on gut feel. Dealers are quoted margins the plant never earns, and the shortfall stays hidden until the quarter closes with a result no one can explain.
Watch the gap between planned and issued quantity for each component over a month, because a large, steady gap points at a missing line or an optimistic yield.
- Planned versus issued quantity per component
- Scrap and rejection by operation
- Material rates updated after each purchase
- Packing items such as cartons, foam and manuals
Multi-level products strain the grid
A kettle is not one list. It is a base assembly, a heating element sub-assembly, a lid, a power cord and a carton, and each sub-assembly carries its own parts list. Some are shared across models.
Spreadsheets use tabs and lookups. One broken lookup quietly returns zero instead of an error, so a missing screw never reaches the purchase list and the line stops on a Tuesday afternoon.
Where one component feeds ten models, a price change should ripple through all ten, yet in a grid that means ten manual edits and each can be missed.
What to do next
Replacing everything at once is unnecessary. Lock the sheet as read-only, move to one master file with a revision column, and agree who may edit it, which buys you a few months of order.
Meanwhile, list your five most complex products and note the revisions, alternates and rework steps that never made it onto the sheet. That note is your requirement for software.
Take that note to any vendor and ask them to enter one of those products live, including a mid-run revision and an approved alternate.
Spreadsheet bill of materials health check
One page, one product per line. Columns record the current revision, the date of last change, the number of copies in circulation, approved alternates, planned versus issued variance, and a mark for pass or fail. Three worst rows go below.
Open a blank worksheet to printQuestions owners ask
How many products can a spreadsheet bill of materials handle?
No fixed limit exists. The answer depends on how often designs change and how many people edit, because a few stable products with one editor can last for years. Frequent revisions break it sooner.
What is the first sign a spreadsheet bill of materials is failing?
Two departments quote two lists. Once production, stores and costing hold separate copies, errors surface as line stoppages, wrong issues to the shop floor and complaints from service teams.
Can software hold substitute parts?
Yes. Good systems store approved alternates with a reason and dates, and record which one was actually issued to each work order, so later faults can be traced.
Do we need to move every product at once?
No. Start with the products that change most or carry the most complaints, then run old and new in parallel and compare issued quantities before switching fully.