Free Bill of Materials (BOM) Excel Template
A multi-level bill of materials in Excel: an indented BOM for one or more finished goods, material cost rolled up through every sub-assembly including scrap, and a where-used lookup for engineering changes.
What you get
A free .xlsx BOM template with no email and no macros: item master, indented multi-level BOM, cost roll-up, cost summary per finished good and a where-used lookup, filled with a two-chair example that shares a sub-assembly.
bill-of-materials-excel-template.xlsx · 5 sheets · No email required · No macros
Sheets: Instructions, Items, BOM, Cost Summary, Where Used
Why manufacturers still use Excel for this
The bill of materials is the recipe for a manufactured product: what goes into it, how much of each component, and which sub-assemblies roll up into the finished good. A single mistake in a BOM spreads through purchasing, inventory, scheduling and costing, which is why experienced manufacturers treat BOM accuracy as non-negotiable.
For small manufacturers, the BOM almost always starts in Excel. A single-level BOM with 20 components is easy in a workbook. The problems start with multi-level BOMs: component A is made from B and C, which are made from raw materials D, E and F, and a price change on D has to flow through every finished good that eventually uses it.
This template handles that correctly. Each make part's cost is calculated from the components listed under it, so a cost change on a purchased part flows up through every level automatically, and the where-used lookup shows every product affected by a change.
What's inside the template
Items: component master
Part number, description, unit of measure, make or buy, unit cost for purchased parts, lead time and supplier.
Indented multi-level BOM
Top item, level, parent and part on every line, with an indented view. Any depth works as long as each component is listed directly under its parent.
Cost roll-up with scrap
Extended cost = quantity per x (1 + scrap %) x unit cost. A make part's unit cost is the sum of its components below it, so costs roll up level by level.
Cost Summary
Rolled-up material cost for each finished good in one place.
Where Used lookup
Type any part number to list every parent and finished good that uses it, with quantity and extended cost.
Revision columns
A revision letter and change date on each BOM line, so you can see which version was current when a batch was built.
How to use this template
A practical walkthrough, from the sample data to a plan built on your own numbers.
- 1
Study the sample BOM
Two chairs share the same base assembly. Follow how the seat and base costs roll up into each chair on the BOM sheet.
- 2
Build your item master
On Items, list every part that appears anywhere in your BOMs, with make or buy and the unit cost of purchased parts.
- 3
Enter the BOM lines in indented order
Finished good at level 0, then each level 1 part followed directly by its own components at level 2, and so on. Fill in top item, level, parent, part, quantity per and scrap %.
- 4
Check the roll-up
The level 0 row and the Cost Summary show the material cost of each finished good. Change a purchased part's cost on Items and every product that uses it updates.
- 5
Use Where Used before any change
Type a part number in Where Used to see every product affected by a substitution, supplier change or price increase.
Worked example: office chair CH-100
The office chair is made from a seat assembly, a base assembly and 12 screws. Purchased parts take their cost from the Items sheet; each assembly's unit cost is the sum of the extended cost of the lines under it.
| Level | Part | Description | Qty per | Scrap | Unit cost | Extended cost |
|---|---|---|---|---|---|---|
| 0 | CH-100 | Office chair | 1 | 0% | $50.42 | $50.42 |
| 1 | SA-10 | Seat assembly | 1 | 0% | $20.31 | $20.31 |
| 2 | FM-01 | Foam cushion | 1 | 0% | $8.50 | $8.50 |
| 2 | FB-02 | Upholstery fabric (m) | 1.2 | 5% | $6.00 | $7.56 |
| 2 | PL-03 | Plywood seat pan | 1 | 0% | $4.25 | $4.25 |
| 1 | BA-20 | Base assembly | 1 | 0% | $29.50 | $29.50 |
| 2 | CS-04 | Caster | 5 | 0% | $1.80 | $9.00 |
| 2 | GL-05 | Gas lift cylinder | 1 | 0% | $11.00 | $11.00 |
| 2 | ST-06 | Star base | 1 | 0% | $9.50 | $9.50 |
| 1 | SC-07 | M6 screw | 12 | 2% | $0.05 | $0.61 |
The chair rolls up to $50.42 of material. Scrap is not a rounding error: the fabric line is 1.2 m x 1.05 x $6.00 = $7.56, so a 5% scrap allowance adds $0.36 to every chair.
The task chair CH-200 uses the same base assembly, and Where Used for BA-20 returns both chairs ($50.42 and $47.91). A $0.20 rise in the caster price therefore adds $1.00 to each chair, because every base uses 5 casters.
When you outgrow this template
Excel is the right answer for early-stage planning, until it isn't. These are the warning signs that you need a real production scheduling tool.
If three or more of these apply, you have outgrown spreadsheet planning. EDGEBIC, the current generation of RMDB, is User Solutions' finite capacity scheduling and production planning software. It imports the same orders, routings, work centers and stock from Excel, CSV or your ERP, schedules them against real machine, labor and material limits, and shows the result on a drag-and-drop Gantt chart. It is a one-time license: EDGEBIC APS is $25,000, and EDGEBIC Complete, which adds MRP, inventory and purchasing, is $35,000.
See EDGEBICFrequently asked questions
Is this BOM template really free?+
Yes. Download the .xlsx directly from this page: no email, no sign-up and no macros. It works in Excel 2016 or later, including Microsoft 365.
How deep can the multi-level BOM go?+
Any depth, as long as each component is listed directly under its parent for the same top item. The roll-up adds up the lines below each make part, so levels 3, 4 and beyond work the same way as the two levels in the example. For very deep structures, RMDB (now EDGEBIC) has been proven with 10+ level BOMs in Li-ion battery production.
How does the cost roll-up avoid circular references?+
A make part's unit cost only adds up extended costs in the rows below it (same parent and top item), and each extended cost depends only on its own row. Because every calculation looks down the sheet, never at itself, Excel has nothing circular to resolve.
Does the template support scrap and yield?+
Yes. Each line has a scrap % column that increases the quantity consumed in the cost roll-up, so a 5% scrap allowance on 1.2 m of fabric costs 1.26 m.
Can I use the same sub-assembly twice in one product?+
List it once per finished good and put the total quantity on that line. Listing the same sub-assembly twice under one finished good double-counts its components in the roll-up.
How is this different from the BOM in an ERP or MRP system?+
ERP and MRP BOMs are multi-user, version-controlled and connected to purchasing and inventory. This template is a standalone workbook: good for prototypes, small product ranges and shops without an MRP system. RMDB, now EDGEBIC Complete, keeps BOMs live in a real database together with MRP, inventory, purchasing and finite capacity scheduling.
Start with the spreadsheet. Move to EDGEBIC when it breaks.
The template handles the math for a small plan. EDGEBIC is what manufacturers move to when orders, routings and capacity change faster than a spreadsheet can follow: published one-time pricing, and implementation measured in days.