← All posts 中文版

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.

Sub-assembly stock Cause 1 Shared sub-assemblies Cause 2 Substitutes Cause 3 In-transit POs Cause 4 Snapshot staleness Cause 5

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.

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.

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

ParentChildTypeQty per
Finished good PSub-assembly AMake1
Finished good PMaterial XBuy2
Sub-assembly ASub-assembly BMake2
Sub-assembly AMaterial YBuy3
Sub-assembly BMaterial ZBuy4
Sub-assembly BMaterial WBuy1

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:

  1. P needs 100. That requires 100 A and 200 X.
  2. A stock = 30, shortage = 70. Build 70 A. That requires 140 B and 210 Y.
  3. B stock = 10, shortage = 130. Build 130 B. That requires 520 Z and 130 W.
  4. Net demand: X = 200, Y = 210, Z = 520, W = 130.
  5. 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:

Side-by-side comparison

MaterialCorrect net demandExcel full explosionOverstated byReal shortageExcel "shortage"
X200200000
Y210300+90110200
Z520800+280320600
W130200+7080150
Total10601500+440510950

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 this example is trying to show: the issue is not whether the Excel formulas are written correctly. It's that full explosion is the wrong approach for multi-level BOMs. Recursive expansion + sub-assembly stock consumed first + in-transit POs netted — if any one of these is missing, the kit result is systematically biased in one direction or the other.

What correct kit logic looks like

In pseudocode, the core logic is about fifteen lines:

function checkKit(productId, qty, stockMap, visited) { // cycle guard if (visited.has(productId)) return []; visited.add(productId); const result = []; for (const item of bomOf(productId)) { const required = item.qty * qty; if (item.type === 'material') { // purchased part: compare against stock + in-transit result.push({ materialId: item.materialId, required, stock: stockMap[item.materialId] || 0, inTransit: inTransitMap[item.materialId] || 0 }); } else { // sub-assembly: consume stock first, recurse only into the shortfall const subStock = productStock(item.subProductId); const shortage = Math.max(0, required - subStock); if (shortage > 0) { result.push(...checkKit(item.subProductId, shortage, stockMap, visited)); } } } visited.delete(productId); return result; }

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:

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

Q: Is Excel good enough for single-level BOMs?
Yes. If a product hangs directly off its materials with no sub-assemblies, SUMIF plus VLOOKUP can produce an accurate kit check in about 20 minutes. Excel starts to fail systematically only when BOM depth reaches three levels or more, or when sub-assembly stock must be consumed before exploding into children.
Q: Why not just explode every level and SUMIF the result?
Because that ignores sub-assembly stock. If sub-assembly A has 30 units on hand, you only need to build 70 more, so its children should be sized for 70 units, not 100. In the worked example in this article, the full-explosion approach overstates the shortage by 86%.
Q: Our kit rate shows 95%+, yet the shop floor stops every day. Why?
Two common causes. First, the kit check does not net in-transit purchase orders, so the shortage list shows a gap that is already covered by a PO on the way — or, worse, the gap is real but the list understates it because in-transit is over-counted. Second, shared sub-assemblies are double-committed: each work order sees the same stock as sufficient, but combined they are not.

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.