Cellwise Start free

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.

cellwise-bill-of-materials-template.xlsxExcel workbook · 5.2 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup
Download free template

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.

RowABCDEFGHI
1Bill of materials (BOM) and build cost
2ProductOak side tableLabor hours per unit1.50
3Product codeST-200Labor rate per hour$28.00
4Build quantity50Overhead per unit$6.50
5Selling price per unit$189.00
6
7Part #ComponentQty per unitUnitScrap %Unit costCost per unitOn handTo order
8OAK-TOPOak top panel, 20 x 20 in1.0each5%$38.00$40.004013
9OAK-LEGOak leg, 1.75 x 1.75 x 24 in4.0each3%$6.75$27.842100
10OAK-APROak apron rail, 15 in4.0each5%$3.40$14.3215061
11PLY-SHFPlywood lower shelf, 16 x 16 in1.0each8%$9.20$10.00600
12HW-BRKCorner bracket, steel4.0each0%$0.85$3.4012080
13HW-SCRWood screw #8 x 1-1/4 in24.0each2%$0.06$1.471,5000
14GLU-01Wood glue1.5fl oz10%$0.42$0.706420
15FIN-02Oil finish4.0fl oz10%$0.55$2.4418043
16FLT-PDFelt pad4.0each0%$0.12$0.483000
17BOX-STShipping box with foam inserts1.0each2%$7.80$7.963517
18
19
20
21
22
23Material cost per unit$108.61
24Labor cost per unit$42.00
25Overhead per unit$6.50
26Total cost per unit$157.11
27Cost for the whole build$7,855.50
28Gross margin per unit$31.89
29Gross margin %17%
30
31Components short for this build6
32Cost of components to order$934.05
33Biggest material costOak top panel, 20 x 20 in
34Its share of material cost37%

How the formulas work

Cost per unit with a scrap allowance

G8=IF(A8="","",ROUND(C8/(1-E8)*F8,2))

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

I8=IF(A8="","",MAX(0,ROUNDUP(C8*$B$4/(1-E8),0)-H8))

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

G29=IF(G5>0,G28/G5,0)

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

G32=SUMPRODUCT(I8:I21,F8:F21)

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

B33=INDEX(B8:B21,MATCH(MAX(G8:G21),G8:G21,0))

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

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

Related guides

More free templates

Updated