Get your free bill of materials template
Both files carry the same filled-in sample product, a fictional oak dining chair, so you see every field and every formula before you use it.
- Download for Excel (.xlsx) (live formulas, also opens in LibreOffice Calc and in Google Sheets once uploaded to Drive)
- Download the printable build sheet (PDF) (static, for posting at a workstation)
No sign-up, no email gate, no time limit.
What's inside the template
The workbook is built around one idea: every part is listed once, and the recipes point at that list. Change a price in one place and every recipe using that part follows.
Components (the parts master list)
Sixty rows: SKU, name, type (raw material, purchased part, sub-assembly or finished good), unit of measure, unit cost, scrap or yield allowance, supplier and notes. Unit cost is typed for anything you buy. For the two things you make, that cell is a formula instead, pulling the rolled-up total from that item's own sheet. That formula is what makes the multi-level part work, and it is the part that needs care.
For the definition and how a bill of materials is built inside Qoblex, see our guide to bills of materials.
Bill of materials sheets (the recipes)
Two sheets, one per thing you make, 20 rows each. You enter a component SKU and a quantity per unit. The name, scrap allowance and unit cost come across from the Components sheet, extended cost calculates as quantity x (1 + scrap allowance) x unit cost, and the bottom row sums that column into the item's materials cost.
Cost rollup and suggested price
Materials cost is not unit cost. This sheet adds labor and overhead to the chair's materials total, then turns a target margin into a suggested price.
Where each component is used
Pick a component SKU and this sheet tells you, for each recipe, whether that part is used and how much goes into one unit. Being straight about it: those formulas name the two BOM sheets in this file, so a third recipe means extending them by hand.
Single-level vs multi-level BOM (and where a spreadsheet needs manual rework)
What a single-level BOM is
A flat list of the parts that go directly into the finished product, with a quantity against each. One product, one list, one total. For plenty of small manufacturers, that is all the structure the product has.
What a multi-level BOM is
A multi-level BOM describes a product built from something you also make. The chair is not just panels and fabric: it is a seat sub-assembly (itself a recipe of panel, foam, fabric and staples) plus legs, a fastener kit and lacquer. That sub-assembly has its own cost, and it has to land in the chair's materials cost correctly, or the chair is priced off a number that was never true.
Where the spreadsheet breaks: the manual re-wiring problem
A flat file holds one level cleanly. A second level works only because somebody wired it: the sub-assembly gets its own sheet, and its row on the Components sheet points at that sheet's total. This template ships that link already made.
Add a third level, a frame that goes into the seat that goes into the chair, and you do the work again. The arithmetic is not the hard part. The hard part is that nothing in the file knows the link is supposed to exist: if a cost changes upstream and nobody re-checks the pointer, the parent BOM keeps showing an old number and looks perfectly healthy doing it. That is the limit of a flat file, not a flaw in how you built it: a spreadsheet stores rows, and a product structure is a tree.
For the deeper picture, including EBOM versus MBOM and what to compare when you look at tools, see our guide to BOM software for small manufacturers.
How component cost rolls up to a finished-good unit cost
Here is the sample product worked end to end, starting at the bottom with the seat sub-assembly. Each line is quantity x (1 + scrap allowance) x unit cost.
| Component | Qty | Scrap allowance | Unit cost | Extended cost |
|---|---|---|---|---|
| Oak seat panel, pre-cut | 1 | 5% | 8.40 | 8.82 |
| Foam padding, medium density | 0.40 kg | 3% | 6.00 | 2.47 |
| Upholstery fabric, grey | 0.60 m | 8% | 9.50 | 6.16 |
| Upholstery staples | 20 | 2% | 0.02 | 0.41 |
| Seat sub-assembly unit cost | 17.86 |
That 17.86 now enters the chair's recipe the way a bought part would.
| Component | Qty | Scrap allowance | Unit cost | Extended cost |
|---|---|---|---|---|
| Seat sub-assembly | 1 | 1% | 17.86 | 18.04 |
| Chair leg, oak, standard | 4 | 2% | 3.25 | 13.26 |
| Chair fastener kit | 1 | 0% | 1.10 | 1.10 |
| Clear wood lacquer | 0.05 L | 10% | 42.00 | 2.31 |
| Materials cost per unit | 34.71 |
The Cost Rollup sheet then adds the two costs easiest to forget:
- Materials cost: 34.71
- Labor: 0.40 hours at 18.75 an hour, so 7.50
- Overhead: 3.00
- Total unit cost: 45.21
At a 45% target margin the file suggests 82.20, the total unit cost divided by (1 minus the margin). Cost rolls up from the bottom level to the top, and price comes last. Price from materials alone and you quote against 34.71 for a chair that costs 45.21 to make.
Scrap and yield allowance: why leaving it out understates cost
Scrap or yield allowance is the extra material a job consumes beyond the recipe quantity: offcuts, a damaged length of fabric, overspray, the staples that miss.
It shows up in the numbers above. The oak seat panel's recipe quantity is 1 at 8.40, and with its 5% allowance the BOM carries 8.82. The lacquer is 0.05 L at 42.00, so 2.10 in the recipe and 2.31 with its 10% allowance.
Individually those look like rounding. A BOM with no scrap or yield line understates true cost, sometimes by several percentage points per unit, and that gap compounds across a production run and every quote based on it.
Bill of materials template by format
Excel (.xlsx)
The working file. Change a unit cost, a quantity or a scrap percentage and the sub-assembly, the chair and the suggested price all update on their own. It opens in Excel, LibreOffice Calc and Google Sheets (upload it to Drive), with no macros.
Printable build sheet (PDF)
The shop-floor version. Two pages, one per level, showing the same components, quantities and costs as static values, plus a blank "qty pulled" column and a checked by and date line. No formulas, because nobody edits a spreadsheet at a workbench.
Download the printable build sheet (PDF)
When a spreadsheet BOM is enough
Who this fits
Plenty of businesses never need more than this file, and that is not a compromise. It is the right tool when you make a small number of products, the component list is short and stable, you have no sub-assemblies (or just the one nested level this file handles), and nobody is asking you to trace a raw-material lot into a finished batch.
What starts to hurt
Three signals, usually in this order:
- A second sub-assembly level. Each one needs its own sheet and its own hand-wired pointer, and somebody has to remember it exists.
- Components shared across products. The same fastener kit goes into four products with no single place that says so, and "what does a 10% oak rise cost us?" becomes an afternoon.
- Costs going stale. A supplier price changes, one sheet gets updated, and the parent BOM still shows last quarter's number without looking wrong.
Where Qoblex fits
Qoblex handles multi-level bills of materials natively: a bill of materials can include another product you make, so the seat sub-assembly nests inside the chair that uses it. You build the sub-assembly on its own production order and its cost carries up into the finished goods, with no pointer to maintain. The recipe is edited in place, and every production order reads the current version.
Wastage is counted on the production order and leftovers go back to stock, so the finished unit cost comes from what the job actually consumed. A lower yield concentrates the same total cost into fewer units at a higher unit cost, the effect the scrap column here approximates by hand.
BOM management and MRP are included on every Qoblex plan, with no separate module to switch on, and MRP reads your BOMs against stock and open orders to tell you what to buy and when. Lot, batch, serial and expiry traceability through production is a paid add-on rather than part of the base plans; see pricing for the structure.
| Capability | This spreadsheet template | Qoblex |
|---|---|---|
| Single-level BOM | Yes, built in | Yes |
| Multi-level BOM (nested sub-assembly) | Yes, one level, hand-wired per sub-assembly | Yes, native, carries cost up automatically |
| Cost rollup to unit cost | Yes, formulas included | Yes |
| Scrap or yield allowance per component | Yes, built in | Yes, tracked on the production order, so unit cost reflects what was consumed |
| Where a component is used, across products | Yes, for the two BOMs in this file, manual to extend | Yes, across every product automatically |
| MRP (what to buy, and when) | No | Yes, included on every plan |
| Lot, batch and expiry trace through production | No | Paid add-on, not bundled |
Two companions: MRP, BOM and lot traceability for small manufacturers, and manufacturing inventory software for raw material, work in progress and finished goods stock.
FAQ
What is a bill of materials template?
A spreadsheet that lists every component, quantity, unit cost and scrap allowance for a finished product and rolls them up into a unit cost. For the definition and how a BOM is built inside Qoblex, see our guide to bills of materials.
What's the difference between a single-level and multi-level BOM?
A single-level BOM is a flat list of the parts that go directly into the finished product. A multi-level BOM describes a product built from a sub-assembly that is itself a recipe, so cost rolls up from the lower level into the finished good.
Can Excel or Google Sheets handle a multi-level BOM?
One nested level, yes, if you wire it by hand: a separate sheet for the sub-assembly and a formula link from the parent recipe back to that sheet's total. This template ships that wiring done. A second nested level means repeating the work, and nothing in the file enforces the link or warns you when it goes stale.
What is scrap or yield allowance, and why does it matter for BOM cost?
The extra material consumed beyond the stated recipe quantity: cut waste, breakage, overspray. In this template it is a percentage per component (5%, 3%, 8% and 2% in the seat sub-assembly, then 2% on the legs and 10% on the lacquer). Leave it out and the BOM understates what a unit costs, and so does every quote built on it.
Is this bill of materials template really free?
Yes. No sign-up, no email gate and no time limit.
When do I need dedicated BOM software instead of a spreadsheet?
When a second sub-assembly level needs its own hand-wired sheet, when a component is used across several products with no single place to see where, or when a cost goes stale because nobody re-checked a manual link. See how Qoblex handles multi-level BOMs and MRP.