Build a Bill of Materials in Excel With Costs

A bill of materials in Excel is a simple table: one row for every part you need to build a product, with its quantity and cost. Add a line cost for each row and a total at the bottom, and you know exactly what one unit costs to make. This guide is for engineers, makers, small manufacturers and purchasing staff, and it builds a costed BOM step by step, then scales it to a full production batch.

What goes in a bill of materials?#

A bill of materials (BOM) lists every part, material and sub-assembly needed to build one finished unit. The columns most BOMs use are:

Keep one part per row and no blank rows inside the table. That makes the table easy to total, sort and look up later.

Build the BOM table and line costs#

Start with the parts, quantities and unit costs. Here is a BOM for a desk lamp in A1:D7:

1. In D2, type =B2*C2 and press Enter.
2. Select D2:D7 and fill down (Windows: Ctrl+D; Mac: ⌘D).
3. In D8, add the total with =SUM(D2:D7), or press Alt+= (Mac: ⌘⇧T) for AutoSum.

The total is 12.99, the material cost of one lamp. The SUM function guide covers the details of SUM.

Total the cost in one cell with SUMPRODUCT#

SUMPRODUCT multiplies two ranges row by row and adds the results, so you don't need the line cost column at all:

=SUMPRODUCT(B2:B7,C2:C7)

This returns 12.99, the same as the SUM of the line costs. Both ranges must be the same size, or SUMPRODUCT returns #VALUE!. Many people keep the line cost column anyway, because it shows which parts drive the cost: here the base and the shade are more than half of the total. The SUMPRODUCT function guide and the article on how SUMPRODUCT works explain more uses.

Pull unit costs from a price list with VLOOKUP#

Typing prices into every BOM gets out of date fast. Keep one price list instead, on a sheet called Prices, with part numbers in column A and unit costs in column B. Then put the part number in column A of the BOM and look up the cost:

=VLOOKUP(A2,Prices!$A$2:$B$100,2,FALSE)

• A2 is the part number to find.
• Prices!$A$2:$B$100 is the price list. The $ signs keep it fixed when you fill down.
• 2 returns the second column, the unit cost.
• FALSE asks for an exact match, which part numbers need.

When a supplier changes a price, update it once in the price list and every BOM that looks it up changes too. If a part is missing, VLOOKUP returns #N/A, which is a useful warning. The VLOOKUP guide and the VLOOKUP function guide cover the common #N/A causes, and price lists with discounts shows how to add volume pricing.

Scale the BOM to a production batch#

To cost a batch, put the build quantity in one cell and lock it. With 50 in F1:

The $F$1 reference stays on F1 when you fill the parts formula down a "Batch qty" column. Without the $ signs, the reference would slide to F2, F3 and so on, and return zeros. Press F4 (Mac: ⌘T) while the cursor is on F1 to add them.

You can then compare the batch quantities with stock on hand. The inventory tracker guide shows how to keep that stock list up to date.

Add scrap allowance and round up purchase quantities#

Real builds lose some parts to damage and waste, so many BOMs add a scrap allowance. With 2.5% in G1, the screws to buy for the batch are:

=ROUNDUP(B7*$F$1*(1+$G$1),0)

That is 6 × 50 × 1.025 = 307.5, rounded up to 308. ROUNDUP with 0 always goes up to the next whole number, so you never order half a screw short. For costs, round to cents instead: =ROUND(SUMPRODUCT(B2:B7,C2:C7)*1.05,2) adds a 5% cost buffer and returns 13.64 per lamp.

Handle sub-assemblies with a multi-level BOM#

A multi-level BOM has parts that are themselves built from parts. The lamp's arm, for example, might be two tubes, a hinge and a spring. To cost it in Excel:

1. Build a small BOM for the sub-assembly, on its own sheet or in its own block.
2. Total it with SUMPRODUCT, as above.
3. Use that total as the unit cost of the Arm line in the main BOM, with a simple link such as =ArmBOM!D8.

Add a Level column (1 for the finished product's direct parts, 2 for parts inside a sub-assembly) if you keep everything on one sheet. Don't add level-2 lines into the level-1 total, or you will count the arm's parts twice. BOMs like this are everyday work for many engineering roles, as the guide to Excel skills for engineers explains.

Common BOM mistakes#

These slips cause most wrong BOM costs:

1. Mixed units. A cable priced per metre but counted in pieces gives the wrong line cost. Keep a unit column and check it.
2. Unlocked references. A batch cell without $ signs gives zeros when filled down.
3. Numbers stored as text. Imported quantities aligned to the left are often text, and SUMPRODUCT treats them as 0.
4. Duplicate part numbers in the price list. VLOOKUP returns the first match only, so a second, newer price is ignored.
5. Totals that miss new rows. If you add parts below the SUM range, extend the range or format the BOM as a table so it grows by itself.

Practise it#

A BOM is mostly three skills: lookups, totals and locked references. Practise VLOOKUP in a live spreadsheet, then total columns with the SUM lesson and learn when to add $ signs in the absolute references lesson. Each one checks your formula instantly. Then try the exercise on this page to total the lamp BOM with SUMPRODUCT.