Author: Kyle Moloney, founder of Nstock, inventory and production software for small manufacturers.
Every small manufacturer starts the same way. One spreadsheet holds the parts list, another holds stock counts, a third tracks what was ordered, and the person who built them knows how they fit together. It works for a long time. Then one month a job stalls because a component everyone assumed was on the shelf was consumed by a different run, or a customer asks which lot of raw material went into the units they received, and nobody can answer without a weekend of digging. That is the moment most shops realize the spreadsheet was never the system. The person was.
This article is about recognizing that moment early, understanding what a bill of materials driven system actually changes, and the groundwork that makes any tool, including a spreadsheet, work better.
The five signs you have outgrown the spreadsheet
- Stock counts drift from reality within days of a count. The sheet says 400 brackets. The bin says 220. Nobody is stealing brackets. Three production runs consumed them and only one was written down, because writing it down is a separate step from doing the work.
- Purchasing is reactive. Orders go out when someone walks past an empty shelf, not when the math says the next run will need them. Expedite fees and air freight become a normal line item.
- Nobody can price a job from real costs. Quotes use the material price from the last time someone looked it up, with a guess for labor. When supplier prices move, margins move with them and nobody notices until the accountant does.
- Traceability is a manual investigation. A supplier issues a recall on one lot. Finding which finished units used it means cross-referencing receiving emails, production notes, and shipping records. Food, supplement, cosmetics, and medical shops feel this first, but every manufacturer with a quality complaint eventually does.
- The sheet has one owner. Only one person can change it safely. When that person is on vacation, purchasing waits.
If three of these describe your shop, the problem is structural. More tabs will not fix it.
What a BOM driven system actually changes
A bill of materials is the recipe for a product: every component, the quantity per unit, and for multi-level products, the sub-assemblies and their components. In a spreadsheet, a BOM is a reference document. In a BOM driven inventory system, it is an instruction. That difference is the whole value.
When a production run of 500 units is started against a BOM, the system knows that 500 units of product A require 1,000 brackets, 500 housings, and 2 kilograms of coating, and it does three things on its own. It checks whether those quantities are on hand before the run starts. It deducts them from stock when the run is recorded, not when someone remembers to. And it records which lots of each material went into that run, so the question “what went into this batch” has an answer that takes seconds.
Purchasing follows from the same data. If the next three scheduled runs need 4,000 brackets and 1,800 are on hand with a two-week supplier lead time, the shortage is visible weeks before the empty shelf. Costing follows too: a BOM with current material prices and a labor figure per unit gives a cost roll-up that quotes can be built on, and it updates when supplier prices change.
None of this requires an ERP. It requires that the parts list, the stock levels, and the production record live in one place and talk to each other.
A worked example
Take a shop making a two-level product: a finished enclosure made from a machined housing sub-assembly plus purchased hardware.
The housing sub-assembly uses one aluminum blank, four inserts, and 0.3 hours of machining. The finished enclosure uses one housing sub-assembly, one gasket, one lid, and six screws, with 0.2 hours of assembly.
With material prices loaded, the roll-up is mechanical. If the blank is $18, inserts are $0.40 each, and machining costs $45 an hour, the housing sub-assembly costs $33.10. Add a $2.20 gasket, a $6.50 lid, screws at $0.08 each, and assembly at $40 an hour, and the finished enclosure costs $50.28 in materials and labor before overhead. A 500 unit order needs 500 blanks, 2,000 inserts, 500 gaskets, 500 lids, and 3,000 screws, which the system can compare against stock and open purchase orders in one view.
In a spreadsheet, that same roll-up is a chain of cell references that breaks the first time someone inserts a row. In a system, it is a property of the BOM.
Fix the groundwork before you buy anything
Software amplifies whatever discipline already exists. Shops that skip this step end up with an expensive spreadsheet.
Clean the item list. One name per item, one unit of measure per item, and no duplicates. A shop that tracks “M4 screw”, “screw M4”, and “M4x12” as three items will have three wrong stock counts.
Write the BOMs down completely. Include packaging, labels, and consumables. The BOM that leaves out the box is the BOM that leaves purchasing short of boxes.
Decide what gets a lot number. Not everything needs lot tracking. Raw materials with expiry dates, anything a customer might trace, and anything a supplier might recall are the usual set. Screws usually are not.
Count once, properly. A full physical count before go-live is the single most predictive factor in whether the new system is trusted after month one.
Assign the two jobs. Someone owns receiving (stock goes up when material arrives) and someone owns production recording (stock goes down when a run is logged). If those are the same person, that is fine. If they are nobody, the system drifts exactly like the spreadsheet did.
What to require from any tool
Whether you choose a lightweight inventory tool or a full MRP, the checklist for a small shop is short.
- Multi-level BOMs with a cost roll-up that updates when prices change.
- Production runs that deduct components automatically and record which lots were used.
- A shortage view that nets upcoming demand against stock and open orders.
- Lot and expiry tracking that can answer a recall question in minutes.
- A CSV import for items, BOMs, and opening stock, so the migration is a day and not a quarter.
- Published pricing you can read without a sales call, and a trial long enough to run a real production cycle.
Skip anything that requires a consultant to configure a work center before you can record your first run. A shop with five people needs to be live in days.
For a deeper walkthrough of how BOMs are structured and how BOM driven inventory works in practice, this guide to the bill of materials for manufacturers covers the terminology and the common mistakes: https://nstock.pro/blogs/bill-of-materials-explained
Migrating without stopping production
The shops that make the switch cleanly do it in three steps. First, run the new system alongside the spreadsheet for two production cycles, recording runs in both, and compare counts at the end of each. Second, when the counts agree twice in a row, stop updating the spreadsheet for stock and keep it only as a read-only archive. Third, move purchasing to the system’s shortage view, because that is where the money is: fewer expedites, fewer stalled jobs, and quotes built on the material prices you are actually paying.
The spreadsheet got you this far because one person was careful. A BOM driven system makes that care structural, so it survives vacations, growth, and the day someone inserts a row in the wrong place.
About the author: Kyle Moloney is the founder of Nstock, inventory and production software built for small manufacturers.
