Excel guide · 6 min read
How to calculate bill of materials (BOM) cost in Excel
=C2/(1-E2)*F2. The product's material cost is the sum of its lines; with several products in one table, use =SUMIFS(G2:G10,A2:A10,"Side table"). Convert prices to the BOM's unit of measure first, or the roll-up is wrong.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Component | Qty per | Unit | Scrap % | Unit cost | Line cost | Qty for plan | Part | Buy unit | Price | |
| 2 | Side table | OAK-TOP | 1 | each | 0.05 | 38 | 40 | 31.578947368 | OAK-TOP | each | 38 | |
| 3 | Side table | LEG-450 | 4 | each | 0 | 5.5 | 22 | 120 | OAK-SEAT | each | 52 | |
| 4 | Side table | SCR-35 | 16 | each | 0 | 0.07 | 1.12 | 480 | LEG-450 | pack of 4 | 22 | |
| 5 | Side table | FIN-OIL | 120 | ml | 0.1 | 0.024 | 3.2 | 4000 | RAIL-900 | each | 9.5 | |
| 6 | Bench | OAK-SEAT | 1 | each | 0.05 | 52 | 54.736842105 | 21.052631579 | SCR-35 | box of 200 | 14 | |
| 7 | Bench | LEG-450 | 4 | each | 0 | 5.5 | 22 | 80 | FIN-OIL | 1 L tin | 24 | |
| 8 | Bench | RAIL-900 | 2 | each | 0 | 9.5 | 19 | 40 | ||||
| 9 | Bench | SCR-35 | 24 | each | 0 | 0.07 | 1.68 | 480 | Product | Build qty | ||
| 10 | Bench | FIN-OIL | 200 | ml | 0.1 | 0.024 | 5.3333333333 | 4444.4444444 | Side table | 30 | ||
| 11 | Bench | 20 |
Download this sheet as an Excel practice file (.xlsx)
Lay out one BOM table and one price list
A costed BOM needs two lists, and keeping them apart is what makes it maintainable. The BOM (A:H) says what goes into each product: one row per product and component, with the quantity per finished unit, the unit that quantity is in, and a scrap allowance. The price list (J:M) says what each part costs to buy, once, however many products use it.
Put every product in the same table with a Product column, rather than one sheet per product. The side table and the bench share legs, screws and finishing oil; with one table, a price change on any of them flows into both products, and you can total, filter and pivot the whole range at once.
Use part numbers to link the two lists, not descriptions. "Oak top 600mm" typed slightly differently in two places breaks the lookup; OAK-TOP doesn't.
If you only need one product with labor, overhead and margin, the free bill of materials template is ready to fill in. This guide goes further: price-list lookups, units of measure, several products and a combined build plan.
Look up unit costs and convert units of measure
Suppliers sell in their units, not yours. Legs come in packs of 4, screws in boxes of 200, oil in 1-litre tins, but the BOM counts single legs, single screws and millilitres. So the price list has a Units per buy unit column (M), and the unit cost in F2 divides the price by it: =XLOOKUP(B2,$J$2:$J$7,$L$2:$L$7)/XLOOKUP(B2,$J$2:$J$7,$M$2:$M$7).
That turns a $22 pack of legs into $5.50 per leg, a $14 box into $0.07 per screw and a $24 tin into $0.024 per ml. Lock the price list with $ signs so the formula copies down. In Excel 2019 or earlier, use VLOOKUP: =VLOOKUP(B2,$J$2:$M$7,3,FALSE)/VLOOKUP(B2,$J$2:$M$7,4,FALSE).
Result: 0.024
0.024: finishing oil costs $0.024 per ml once the $24 tin is divided into 1,000 ml.
The unit-of-measure mistake that inflates a cost
Skip the conversion and the side table's 120 ml of oil is priced at $24 per ml. Nothing errors; the number is just absurd, and in a long BOM nobody spots it. The screws do the same thing more quietly: 16 screws at $14 each is $224 instead of $1.12.
Two habits prevent it. Write the unit next to every quantity (column D) and the buy unit next to every price (column K), so a mismatch is visible. And sanity-check each line cost against what you'd expect to pay for that part in one product; a line that's more than the whole product sells for is a conversion error.
Result: 2880
2880: oil priced per tin instead of per ml, so one table "needs" $2,880 of oil. The correct line is $3.20.
Line cost with scrap, then cost per product
Line cost in G2 is quantity per unit, grossed up for scrap, times unit cost: =C2/(1-E2)*F2. With 5% scrap on the oak top, each finished table has to carry 1/0.95 of a top, so the $38 top costs $40. With 10% lost in brushes and rags, 120 ml of oil becomes 133.3 ml, $3.20. Divide by (1 - scrap %) when scrap % is the share of material that gets lost; multiplying by (1 + scrap %) comes out slightly low.
Then total each product with SUMIFS on the Product column. The side table's material cost is $66.32: top $40, legs $22, screws $1.12, oil $3.20. Without the scrap allowance it would be $64, and you'd underprice every table by $2.32.
For a cost summary of every product at once in Excel 365, put =UNIQUE(A2:A10) in one cell and =SUMIFS(G2:G10,A2:A10,UNIQUE(A2:A10)) beside it: the side table at 66.32 and the bench at 102.75 spill down together, and new products appear automatically.
Result: 66.32
66.32: material cost of one side table, scrap included.
One formula without the helper column
If you'd rather not keep a line cost column, SUMPRODUCT can do the scrap and the multiplication in one go. (A2:A10="Bench") is TRUE or FALSE for each row, which counts as 1 or 0 when multiplied, so only the bench rows add up.
The helper column is still easier to check and to explain to someone else, because you can see each line. Use the one-cell version for a summary sheet, and keep the line costs where people review the BOM.
Result: 102.75017544 shows as 102.75 with Number format
102.75: the bench costs $102.75 in materials: seat $54.74, legs $22.00, rails $19.00, screws $1.68, oil $5.33.
What to buy for a build plan of several products
To order materials, you need the total of each part across everything you plan to make. Put the build quantities in a small table (30 side tables and 20 benches in J10:K11) and work out each BOM line's need in H2: =C2/(1-E2)*XLOOKUP(A2,$J$10:$J$11,$K$10:$K$11). Then add up per part with SUMIFS on the Component column.
Screws come to 30 x 16 + 20 x 24 = 960, which is =ROUNDUP(960/200,0) = 5 boxes. Legs are 200, exactly 50 packs. Oil is the one that catches people: 8,444 ml with scrap, so 9 tins, not 8. Divide by the units per buy unit and round up with ROUNDUP, because you can't buy part of a tin. Subtract stock on hand before rounding if you have some on the shelf.
Result: 9
9: 8,444 ml of oil for the plan means buying 9 one-litre tins.
Missing prices and supplier price changes
A part number with no price list entry makes XLOOKUP return #N/A, and SUMIFS then returns #N/A for the whole product. That's a good thing: a BOM that quietly prices a part at zero is worse. Find every unpriced part at once with =FILTER(B2:B10,ISNA(MATCH(B2:B10,J2:J7,0)),"All priced"), which lists the offenders or says All priced. Don't hide the problem with IFERROR(...,0).
The price list also answers "what does this supplier increase cost us?" If the pack of legs goes from $22 to $26, each leg rises by $1. Both products use 4 legs, so =SUMIFS(C2:C10,B2:B10,"LEG-450") shows 8 legs across one of each, and each product's cost rises by $4. Change L4 and every product that uses the part updates; that's the whole point of looking prices up instead of typing them into each BOM line.
Result: All priced
All priced: every component in the BOM has a price list entry.
Common mistakes
Pricing in the supplier's unit
Divide the price by the units per buy unit (pack of 4, box of 200, 1,000 ml per tin) so cost and quantity use the same unit.
Adding scrap by multiplying by (1 + scrap %)
Divide by (1 - scrap %) when scrap % is the share of material lost; 5% scrap needs 1.053 parts, not 1.05.
One sheet per product with prices typed in
Keep all products in one table and look prices up from one list, so a price change reaches every product.
Rounding up per product instead of per order
Add each part's need across all products first, then divide by the pack size and ROUNDUP once.
FAQ
How do I calculate the total cost of a bill of materials in Excel?
Multiply each component's quantity per unit by its unit cost and add the lines. For one product: =SUMPRODUCT(C2:C5,F2:F5). With several products in one table, add a line cost column and use =SUMIFS(G2:G10,A2:A10,"Side table").
How do I handle different units of measure in a BOM?
Add a conversion column to the price list (how many BOM units are in one buy unit) and divide the price by it. A $14 box of 200 screws becomes $0.07 per screw.
How do I cost a multi-level BOM in Excel?
Cost each sub-assembly first as its own product in the table, then list the sub-assembly as a component of the parent with its rolled-up cost as the unit cost. Work from the lowest level up.
Should a BOM cost include labor and overhead?
The BOM roll-up gives material cost. For a full product cost, add labor (hours x rate) and an overhead allowance per unit, as the bill of materials template does.
Functions used
XLOOKUPModern lookup in any directionVLOOKUPVertical lookupSUMIFSAdds with several conditionsSUMPRODUCTMultiply then addUNIQUERemoves duplicates, spillsROUNDUPRounds away from zeroFILTERKeeps matching rows, spillsISNAIs it #N/A?MATCHPosition of a value
Related guides
- How to calculate cost per unit in Excel (formula and examples)
- How to calculate landed cost in Excel
- SUMPRODUCT in Excel: what it does and how to use it
- How to calculate scrap rate in Excel
- Make or buy analysis in Excel
- Excel interview questions and answers
- How to look up which range a number falls in
Updated