How does the template roll up quantities?

Quantity per product = quantity per parent x the quantity per parent of every assembly above it. A part used twice in an encoder board that goes once into a wind sensor that goes once into the product has 2 x 1 x 1 = 2 per product; in the Verify sheet's test BOM a level-4 part at 2 per parent under 4, 1 and 2 gives 16 per product.

How are labour and overhead included?

Labour = quantity per product x labour minutes / 60 x labour rate, on the item where the work is done. Overhead = labour x your overhead rate + material x your material burden. At 38 per hour and 120 percent overhead, 14 minutes of final assembly cost 8.87 of labour and 10.64 of overhead per product.

What is the rolled-up unit cost?

For an assembly it is the cost of one unit with everything in it: its own labour and overhead plus the totals of every line below it, divided by its quantity per product. That is the number to use as the item's standard cost or to compare with a supplier's quote for the whole assembly.

How is the price at target margin calculated?

Price = unit cost / (1 - target gross margin). A unit cost of 100 at a 35 percent target margin needs a price of 153.85, because 53.85 of margin is 35 percent of the price, not of the cost.

Can I paste a BOM from my ERP or PLM system?

Yes, if it has a part number, a parent and a quantity per parent on each row; the order of the rows does not matter. Part numbers must be unique, top-level items use the product code from Setup as their parent, and the Check column flags anything that does not connect.

Does it handle more than one product or deeper BOMs?

One product per workbook, up to four levels below it; deeper rows are flagged by the Check column. For a family of products, save a copy per product. Tooling, freight-out and warranty are not included unless you add them as lines under the product.

Manufacturing Cost and Quoting

BOM Cost Rollup Excel Template

Cost an indented bill of materials line by line, roll material, labour and overhead up through every assembly, and see the product's unit cost, the price at your margin and where the cost sits.

Overview

This BOM cost rollup is a working Excel model for cost engineers, estimators, product managers and buyers who need a product's cost from its real bill of materials. It ships with a fictional, fully costed weather station: 26 lines over three levels including a housing, a main board, a wind sensor with its own encoder board, power, cable and packaging, with example prices in euros.

Each line's quantity per finished product is its quantity per parent multiplied by the quantities of every assembly above it, found with lookups on part number and parent, so the BOM can be pasted in any order and stays free of circular references. Material is quantity times unit cost grossed up for scrap; labour is the minutes added at that item times your labour rate; overhead is recovered on labour and, as a material burden, on material. Every assembly gets its rolled-up cost: its own labour plus everything below it.

The dashboard shows the unit cost, the material share, the price at your target margin, a ranking of top-level items by rolled-up cost and a Pareto of cost by commodity, so a cost-down project starts where the money is. The Verify sheet compares the workbook with an independent recursive rollup of a four-level test BOM.

What this template includes

Setup sheet for the currency, product code and name, labour rate, overhead and material burden rates, target margin, annual volume and the commodity list
BOM table with one row per part: part number, parent, quantity per parent, unit of measure, make or buy, commodity, unit cost, scrap, labour minutes and supplier
Per-line level, quantity per finished product, material, labour, overhead and total cost, up to four levels below the product, with no circular references
Rolled-up cost per product and rolled-up unit cost for every assembly, which is the item's standard cost
A Check column that catches duplicate part numbers, parents missing from the BOM, structures deeper than four levels, bought items with components and unknown commodities
Dashboard KPI tiles for unit cost, material share, price at your target margin and the costliest top-level item
Charts of cost by top-level item and a commodity Pareto, a ranking of top-level items with cumulative share, and cost elements per product and per year
Method sheet with every formula, and a Verify sheet that compares the workbook with an independent recursive rollup and the Configurable BOM Cost calculator
CSV with live formulas of the BOM for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to cost a new product before launch, set or check standard costs, quote a customer from a real bill of materials, see which assemblies and commodities to target in a cost-down project, and test what a supplier price change does to the unit cost.

How to use it

On Setup, set the currency, product code and name, your labour rate, overhead and material burden rates, target margin and annual volume.
On the BOM sheet, paste or type one row per part with its parent (the product code for top-level items), quantity per parent, unit cost, scrap and labour minutes.
Fix anything the Check column reports, such as a parent that is not in the BOM or a duplicate part number.
Read each assembly's rolled-up unit cost on the BOM sheet, then the Dashboard for the unit cost, price at your margin, cost by top-level item and the commodity Pareto.

Frequently Asked Questions

How does the template roll up quantities?: Quantity per product = quantity per parent x the quantity per parent of every assembly above it. A part used twice in an encoder board that goes once into a wind sensor that goes once into the product has 2 x 1 x 1 = 2 per product; in the Verify sheet's test BOM a level-4 part at 2 per parent under 4, 1 and 2 gives 16 per product.
How are labour and overhead included?: Labour = quantity per product x labour minutes / 60 x labour rate, on the item where the work is done. Overhead = labour x your overhead rate + material x your material burden. At 38 per hour and 120 percent overhead, 14 minutes of final assembly cost 8.87 of labour and 10.64 of overhead per product.
What is the rolled-up unit cost?: For an assembly it is the cost of one unit with everything in it: its own labour and overhead plus the totals of every line below it, divided by its quantity per product. That is the number to use as the item's standard cost or to compare with a supplier's quote for the whole assembly.
How is the price at target margin calculated?: Price = unit cost / (1 - target gross margin). A unit cost of 100 at a 35 percent target margin needs a price of 153.85, because 53.85 of margin is 35 percent of the price, not of the cost.
Can I paste a BOM from my ERP or PLM system?: Yes, if it has a part number, a parent and a quantity per parent on each row; the order of the rows does not matter. Part numbers must be unique, top-level items use the product code from Setup as their parent, and the Check column flags anything that does not connect.
Does it handle more than one product or deeper BOMs?: One product per workbook, up to four levels below it; deeper rows are flagged by the Check column. For a family of products, save a copy per product. Tooling, freight-out and warranty are not included unless you add them as lines under the product.