Why Excel Gets Multi-Level BOM Kit Checks Wrong: 5 Reasons + One 5-Level Example
Last week a PMC manager at a small-appliance factory sent me a screenshot of his Excel kit-check sheet. It showed a shortage of 320 units of material Z. Purchasing ordered 320. When the shipment arrived, the real shortage turned out to be 40 — a batch of Z sitting under sub-assembly B had been double-counted by the Excel summary logic.
This is not an isolated case. Almost every Excel kit-check sheet I've seen in the past year fails on the same handful of issues. The problem isn't that the PMC team is careless. It's that Excel's row-and-column structure cannot express two things that multi-level BOM kit checking fundamentally requires: recursive expansion through levels, and stock-first deduction at sub-assembly level.
This article breaks down five root causes, then walks through a fully reproducible 5-level BOM example that compares the manual approach against the recursive one.
Cause 1: Excel explodes everything — it never consumes sub-assembly stock first
How to judge: if your kit sheet flattens every BOM level into a single list and then SUMIFs the total, it is systematically overstating demand. The correct rule is: once a sub-assembly has enough stock, stop exploding into its children.
This is the most common and most overlooked error in material availability checks. The typical Excel approach is: VLOOKUP every BOM level flat, multiply each material's quantity by the order quantity, then SUMIF. This implicitly assumes sub-assembly stock is zero — and inflates demand for everything below.
A simple illustration. Product P needs one sub-assembly A. A needs three units of material Y. Order quantity for P is 100, and A has 30 units on hand.
- Correct logic: P needs 100 A; stock covers 30, so only 70 more A must be built. Y demand is 70 × 3 = 210.
- Excel full explosion: flatten P → A → Y directly. Y demand is 100 × 3 = 300.
That's a 90-unit overstatement. If Y costs $1.20 per unit, this one material ties up about $108 in extra inventory. Across dozens of materials and hundreds of SKUs, it adds up to tens of thousands of dollars of excess purchasing every month. It's also a common reason factories feel "we installed a system and inventory went up" — except the system isn't the culprit. The kit logic that never consumed sub-assembly stock is.
The correct recursive rule is one sentence: when you reach a sub-assembly, deduct its stock first, and only recurse into the shortfall. Writing that in Excel formulas is practically impossible. Writing it in code is three lines of recursion.
Cause 2: Shared sub-assemblies get double-committed
How to judge: when two work orders need the same sub-assembly, Excel sees enough stock for each in isolation. The system must either deduct atomically or serialize allocation by priority.
This happens constantly in high-mix, low-volume factories. Suppose sub-assembly A has 30 units on hand. Two work orders need A today: WO-1 needs 20, WO-2 needs 20.
- Excel, evaluated separately: WO-1 sees 30, enough for 20, green. WO-2 sees 30, enough for 20, also green.
- Reality: 30 units cover only one work order. The other must wait for production.
If the PMC dispatches both based on the Excel result, the second will stop the line waiting for material. This is one of the most common reasons a factory sees "kit rate 100%" while the shop floor stops every day.
There are two correct ways to handle this in a system: serialize deduction by work-order priority (higher priority takes stock first; lower priority sees the reduced balance), or serialize by planned start date (earlier start takes stock first). Either way, the prerequisite is that the system knows all work orders are looking at the same inventory — not that each one runs its own isolated calculation.
Cause 3: Substitute materials and multiple suppliers break VLOOKUP
How to judge: if your BOM allows "material Z can be substituted by Z2 when Z is short," Excel's kit sheet cannot get this right — it matches exactly one material code.
In electronics, hardware, and injection molding, substitutes are the norm: a 10 µF capacitor can be covered by a 22 µF part; a specific steel grade can be covered by an equivalent-strength alternative. VLOOKUP matches one code to one code, so substitute relationships either produce a false shortage (the substitute is in stock) or a false green (the primary is short and the substitute was never approved).
A usable BOM explosion logic needs to support "one BOM position maps to multiple candidate materials" and try them in priority order during kit checking. Excel cannot do this reliably; the workaround is manual judgment, and manual judgment across 150 SKUs and hundreds of materials has a very high error rate.
Cause 4: In-transit POs are not netted — the shortage list is a phantom
How to judge: the shortage quantity must be the net gap after subtracting both current stock and in-transit purchase orders. If it only shows the stock gap, PMC will double-order.
This is the second most common error in material kit scheduling. Excel kit sheets typically compare BOM demand against current stock and ignore purchase orders. Result: material Z shows a shortage of 320, purchasing orders 320, but a 300-unit PO is already in transit and the real gap is only 20. The extra 300 becomes dead stock when it arrives.
The reverse is also true. If the Excel sheet has a manually maintained in-transit column that isn't updated when a PO is partially received, cancelled, or delayed, the kit sheet can show "green" while the material is still on the road. The value a system adds here isn't the arithmetic — it's that in-transit is read live from the PO table, not typed in by hand.
Cause 5: Excel is a snapshot — it expires the moment data changes
How to judge: if stock, BOM, POs, or work orders change after the kit sheet is computed, that sheet should be considered invalid. Excel cannot re-run on demand; a system can.
A PMC's typical morning: export stock at 8 AM, finish the kit sheet at 9, the shop floor pulls material at 10, a delivery arrives at 11. After 11 AM, the 9 AM sheet is already wrong — but PMCs often keep using it. This is why so many factories "calculate very carefully and still run short every day."
In a system, production kit rate should be computed on demand: the moment you open a work order's kit detail, the system reads current stock, current in-transit, and current BOM, and returns a present-tense answer. The difference between snapshot and live computation becomes more visible the faster your factory changes.
A reproducible 5-level BOM example
You can follow this with a calculator and check every number. Compare the manual approach against the recursive one.
BOM structure
| Parent | Child | Type | Qty per |
|---|---|---|---|
| Finished good P | Sub-assembly A | Make | 1 |
| Finished good P | Material X | Buy | 2 |
| Sub-assembly A | Sub-assembly B | Make | 2 |
| Sub-assembly A | Material Y | Buy | 3 |
| Sub-assembly B | Material Z | Buy | 4 |
| Sub-assembly B | Material W | Buy | 1 |
Current stock
A = 30, B = 10, X = 500, Y = 100, Z = 200, W = 50. Order quantity for P = 100.
Correct logic (sub-assembly stock consumed first)
Level by level, stopping expansion once a sub-assembly has enough stock:
- P needs 100. That requires 100 A and 200 X.
- A stock = 30, shortage = 70. Build 70 A. That requires 140 B and 210 Y.
- B stock = 10, shortage = 130. Build 130 B. That requires 520 Z and 130 W.
- Net demand: X = 200, Y = 210, Z = 520, W = 130.
- Net shortage after stock: X covered (500 − 200 = 300 surplus), Y short 110 (210 − 100), Z short 320 (520 − 200), W short 80 (130 − 50).
Excel full explosion (wrong)
Flatten every level and multiply from the order quantity 100:
- X: 100 × 2 = 200 (correct — X hangs directly off P)
- Y: 100 × 1 × 3 = 300 (wrong, +90)
- Z: 100 × 1 × 2 × 4 = 800 (wrong, +280)
- W: 100 × 1 × 2 × 1 = 200 (wrong, +70)
Side-by-side comparison
| Material | Correct net demand | Excel full explosion | Overstated by | Real shortage | Excel "shortage" |
|---|---|---|---|---|---|
| X | 200 | 200 | 0 | 0 | 0 |
| Y | 210 | 300 | +90 | 110 | 200 |
| Z | 520 | 800 | +280 | 320 | 600 |
| W | 130 | 200 | +70 | 80 | 150 |
| Total | 1060 | 1500 | +440 | 510 | 950 |
In this example, the Excel full-explosion approach inflates the total shortage from 510 to 950 — 86% overstated. At an average material cost of $1.50 per unit, that's about $660 of extra purchasing on a single order. Scale that across 150 SKUs and dozens of orders per month and it becomes real tied-up working capital.
What correct kit logic looks like
In pseudocode, the core logic is about fifteen lines:
The function does three things: recursive expansion, stock-first deduction at each sub-assembly, and returning stock plus in-transit for purchased parts. Implementing those three in Excel formulas is practically impossible. Implementing them in code is the fifteen lines above.
Boundary: when Excel is still the right tool
Not every factory needs to replace Excel. In these two cases, staying on Excel is reasonable:
- Single-level BOM — products hang directly off materials with no sub-assemblies. SUMIF + VLOOKUP is accurate enough, and a system may add more overhead than value.
- Low order volume, few SKUs, slow change — a handful of orders per month gives the PMC time to reconcile by hand. The "snapshot goes stale" problem isn't visible.
But if any of the following is true, Excel's kit sheet starts to drift systematically: BOM depth of 3+ levels, sub-assembly stock above 20% of total inventory, more than 50 SKUs, substitutes in use, or in-transit PO value above a third of monthly purchasing. In discrete manufacturing, these conditions usually overlap.
Frequently Asked Questions
Want to run a kit check against your real BOM, live?
On-premise · Recursive multi-level BOM kit check · Sub-assembly stock consumed first · In-transit POs netted · 30-day full-feature trial
Book a multi-level BOM kit-check demo →This article discusses algorithms and methods. All BOM structures, stock quantities, and unit prices in the worked example are illustrative and are not real data from any customer. PMClite is an independent product and has no commercial relationship with any unnamed vendor mentioned in this article.