Manage component lists and track materials needed for production with precision
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
A bill of materials is a tree, and spreadsheets are flat. This template stores the parent-child relationship explicitly so multi-level assemblies roll up their cost correctly instead of being flattened by hand.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Parent | Component | Level | Qty | Unit cost | Ext. cost |
| 2 | CHAIR-01 | FRAME-A | 1 | 1 | 18.40 | 18.40 |
| 3 | FRAME-A | TUBE-25 | 2 | 4 | 2.10 | 8.40 |
| 4 | FRAME-A | WELD-KIT | 2 | 1 | 1.75 | 1.75 |
| 5 | CHAIR-01 | SEAT-PAD | 1 | 1 | 6.90 | 6.90 |
Why each one is written the way it is
=[@Qty]*[@[Unit cost]]
Extended cost per line. Kept as its own column rather than folded into the roll-up, so a wrong unit cost is visible at the line rather than only in the total.
=SUMIFS(BOM[Ext. cost], BOM[Parent], [@Component])
Sub-assembly cost. Pointing the parent lookup at the component column is what makes the tree roll upward one level at a time.
=SUMPRODUCT((BOM[Parent]=[@SKU])*BOM[Qty]*BOM[Unit cost])
Single-level cost of an assembly in one expression, useful for a quick check against the roll-up.
=IF(COUNTIFS(BOM[Component],[@Parent])=0,"Top level","")
Flags finished goods — components that are never themselves a child. A part that should be top level and is not usually means a typo in a parent code.
A worked example with real numbers
A furniture maker priced a chair from the cost of its four visible parts and lost money on every unit. Expanding the BOM to two levels added the welding kit, fasteners and packaging that lived inside sub-assemblies — €11.30 a unit, about 9% of the sale price. The list price did not change; the discount authority did.
Tab by tab
| Tab | Contents |
|---|---|
| BOM | Parent, component, level and quantity — the whole structure. |
| Parts | Part master with unit cost, supplier and lead time. |
| Rollup | Fully exploded cost per finished item. |
| Where used | Reverse lookup: every assembly a part appears in. |
| README | How to add a level and how the roll-up walks the tree. |
What is included, and what to look at next
Specific to this workbook, not generic
Four by default, which covers most light assembly. Deeper trees work but the roll-up needs one extra step per level — beyond four or five, an MRP system is the right tool.
The Where-used sheet filters the BOM by component. It is the sheet you need the day a supplier discontinues something.
Add a yield column and multiply the quantity by 1/yield. A 95% yield means you consume 1.053 units for every one that ships, and ignoring it understates cost on every line.
"This BOM template has transformed our production planning process. We now have complete visibility of our material requirements and can optimize our procurement efficiently."