Free Excel template
Free bill of materials (BOM) template for Excel
For small manufacturers, makers and workshops that need to know what one product really costs and what to buy before a production run. List each component with its quantity per unit, scrap allowance and unit cost; the template rolls up material, labor and overhead into a cost per unit and a margin, then works out how many of each component to order for the build quantity you enter, after what is already on the shelf.
Preview and try it
This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.
| Row | A | B | C | D | E | F | G | H | I |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Bill of materials (BOM) and build cost | ||||||||
| 2 | Product | Oak side table | Labor hours per unit | 1.50 | |||||
| 3 | Product code | ST-200 | Labor rate per hour | $28.00 | |||||
| 4 | Build quantity | 50 | Overhead per unit | $6.50 | |||||
| 5 | Selling price per unit | $189.00 | |||||||
| 6 | |||||||||
| 7 | Part # | Component | Qty per unit | Unit | Scrap % | Unit cost | Cost per unit | On hand | To order |
| 8 | OAK-TOP | Oak top panel, 20 x 20 in | 1.0 | each | 5% | $38.00 | $40.00 | 40 | 13 |
| 9 | OAK-LEG | Oak leg, 1.75 x 1.75 x 24 in | 4.0 | each | 3% | $6.75 | $27.84 | 210 | 0 |
| 10 | OAK-APR | Oak apron rail, 15 in | 4.0 | each | 5% | $3.40 | $14.32 | 150 | 61 |
| 11 | PLY-SHF | Plywood lower shelf, 16 x 16 in | 1.0 | each | 8% | $9.20 | $10.00 | 60 | 0 |
| 12 | HW-BRK | Corner bracket, steel | 4.0 | each | 0% | $0.85 | $3.40 | 120 | 80 |
| 13 | HW-SCR | Wood screw #8 x 1-1/4 in | 24.0 | each | 2% | $0.06 | $1.47 | 1,500 | 0 |
| 14 | GLU-01 | Wood glue | 1.5 | fl oz | 10% | $0.42 | $0.70 | 64 | 20 |
| 15 | FIN-02 | Oil finish | 4.0 | fl oz | 10% | $0.55 | $2.44 | 180 | 43 |
| 16 | FLT-PD | Felt pad | 4.0 | each | 0% | $0.12 | $0.48 | 300 | 0 |
| 17 | BOX-ST | Shipping box with foam inserts | 1.0 | each | 2% | $7.80 | $7.96 | 35 | 17 |
| 18 | |||||||||
| 19 | |||||||||
| 20 | |||||||||
| 21 | |||||||||
| 22 | |||||||||
| 23 | Material cost per unit | $108.61 | |||||||
| 24 | Labor cost per unit | $42.00 | |||||||
| 25 | Overhead per unit | $6.50 | |||||||
| 26 | Total cost per unit | $157.11 | |||||||
| 27 | Cost for the whole build | $7,855.50 | |||||||
| 28 | Gross margin per unit | $31.89 | |||||||
| 29 | Gross margin % | 17% | |||||||
| 30 | |||||||||
| 31 | Components short for this build | 6 | |||||||
| 32 | Cost of components to order | $934.05 | |||||||
| 33 | Biggest material cost | Oak top panel, 20 x 20 in | |||||||
| 34 | Its share of material cost | 37% |
How the formulas work
Cost per unit with a scrap allowance
Quantity per unit divided by (1 - scrap %), times the unit cost. With 5% scrap on the oak top, each table has to carry 1/0.95 = 1.053 tops, so the top costs $40.00 per table instead of $38.00. Dividing by (1 - scrap %) is right when scrap % is the share of issued parts that gets lost; multiplying by (1 + scrap %) comes out slightly short.
How many to order for the build
Quantity per unit times the build quantity in B4, grossed up for scrap and rounded up to whole parts, minus what is on hand. MAX(0,...) shows 0 instead of a negative number when stock already covers the build. For 50 tables you need 211 apron rails (200 / 0.95 = 210.5, rounded up); with 150 on the shelf you order 61. Ordering 1.05 x 200 = 210 would leave you short after 5% scrap.
Full cost and margin per unit
Material cost ($108.61, the sum of the Cost per unit column) plus labor (1.5 hours at $28 = $42.00) plus overhead ($6.50) gives a total cost of $157.11 per table. At a $189 selling price that leaves $31.89, a 16.9% gross margin. Change the labor hours or a unit cost and the margin updates, so you can see what a price increase from a supplier does before you accept it.
Cost of the components to order
SUMPRODUCT multiplies each To order quantity by its unit cost and adds the results: $934.05 of purchases to make 50 tables with the stock in the sample, spread over 6 components that are short. Blank rows hold an empty text result in column I, which SUMPRODUCT treats as zero.
Name the biggest cost driver
MAX finds the most expensive component per unit, MATCH finds its row and INDEX returns its name. The oak top is 36.8% of material cost (B34), so a better price or a lower scrap rate on that one part moves the cost far more than shaving cents off screws.
Make it yours
- Add components inside rows 8 to 21 (right-click a row number > Insert) so the totals, the shortage count and SUMPRODUCT keep including them. Copy the Cost per unit and To order formulas down into the new row.
- Keep units consistent. If you buy glue by the gallon but use it by the fluid ounce, enter the unit cost per fluid ounce: a $54 gallon is $54 / 128 = $0.42 per US fl oz.
- Keep prices on a second sheet and look them up: with part numbers in a Prices sheet,
=XLOOKUP(A8,Prices!A:A,Prices!B:B)in F8 (Excel 2021 or Microsoft 365; use VLOOKUP in older versions) means a price change updates every product that uses the part. - For a product made of sub-assemblies (a multi-level BOM), cost each sub-assembly on its own copy of this sheet, then list it as one component here with its total cost per unit as the unit cost.
FAQ
What is a bill of materials?
A list of every part, raw material and packaging item needed to make one unit of a product, with the quantity of each. Add unit costs and it becomes a costed BOM, which tells you the material cost of the product.
How do I calculate BOM cost in Excel?
Multiply each component's quantity per unit by its unit cost and add up the lines: =SUMPRODUCT(C8:C17,F8:F17). This template also grosses each line up for scrap, then adds labor and overhead for a full cost per unit.
How do I add a scrap or waste allowance to a BOM?
Divide the quantity by (1 - scrap %): =C8/(1-E8). With 5% scrap you need 1.053 parts per finished unit. Round the total for a production run up with ROUNDUP, because you cannot order part of a component.
Functions used
SUMAdds numbersROUNDRounds to digitsIFPicks a result from a testMAXLargest numberROUNDUPRounds away from zeroCOUNTIFCounts cells that matchSUMPRODUCTMultiply then addINDEXValue at a positionMATCHPosition of a value
Related guides
- How to calculate bill of materials (BOM) cost in Excel
- How to calculate cost per unit in Excel (formula and examples)
- How to calculate scrap rate in Excel
- SUMPRODUCT in Excel: what it does and how to use it
- ROUND vs ROUNDUP vs ROUNDDOWN in Excel
- How to calculate landed cost in Excel
- How to use the INDEX MATCH formula in Excel
More free templates
- Employee expense report template for ExcelFor employees who need to claim back business costs, and for the managers and bookkeepers who check those claims.Free .xlsx
- Purchase order template for Excel (PO form with sample)A simple purchase order form for small businesses, office managers and buyers who send POs to suppliers and want to track what actually arrives.Free .xlsx
- Accounts receivable aging report templateFor owners, bookkeepers and credit controllers who invoice customers on terms and need to see who owes what and for how long.Free .xlsx
- Weekly employee shift schedule templateFor managers of shops, cafes, restaurants and small teams who build the rota in a spreadsheet.Free .xlsx
Updated