Template · Engineering

Bill of materials with cost roll-up

Multi-level bill of materials with extended quantities, supplier prices and cost roll-up by sub-assembly.

See download membership

Preview below without an account. Downloads require the $19.85 yearly membership. Already registered? Sign in. XLSX, ODS and XML keep formulas; CSV and Numbers contain values only.

XLSXCSVODSXMLNumbers
Bill of materials with cost roll-up
Example data — replace the blue input cells with your own.
ProductExample conveyor drive unit
Units to build40Quantities below are per parent. Units required multiply down the levels.
LineParent line (0 = product)LevelPart numberDescriptionMake or buyCategoryUnitQty per parentSupplierUnits requiredUnit cost (USD)
101SUB-GBXGearbox assemblyMakeSubassemblyea1.0040.0$586.88
201SUB-MTRMotor assemblyMakeSubassemblyea1.0040.0$1,407.36
301SUB-FRMFrame weldmentMakeSubassemblyea1.0040.0$742.57
401SUB-GRDGuard assemblyMakeSubassemblyea1.0040.0$207.48
501SUB-CTLControl panel assemblyMakeSubassemblyea1.0040.0$1,522.30
601PKG-07Packing and labels, per unitBuyPackagingea1.00Example Packaging40.0$18.60
712GB-HSG-01Gearbox housing, cast ironBuyCastingsea1.00Example Castings Co.40.0$185.00
812GB-GR-02Helical gear, 48 teethBuyGears and shaftsea1.00Example Gear Works40.0$96.50
912GB-PN-03Pinion, 18 teethBuyGears and shaftsea1.00Example Gear Works40.0$58.00

Showing the first 16 of 45 rows and 12 of 14 columns. Cells with formulas show the formula on hover.

What does this template do?

This workbook rolls up the cost of a product built from a multi-level bill of materials. Each line names its parent, so sub-assemblies can hold sub-assemblies. The example is a conveyor drive unit with five top-level sub-assemblies, one nested sub-assembly, 34 lines and three levels of structure.

Quantities multiply down the levels: a line's units required equal its quantity per parent times the units required by its parent. Purchased parts take their unit price and supplier from a price list with INDEX and MATCH. A sub-assembly's unit cost is the sum of the cost of the lines beneath it, found with SUMIF, so the product total is never counted twice.

The summary tab totals purchased cost by supplier, by category and by BOM level, with shares of the total and cost per unit built. A check confirms that the rolled-up total equals the purchased total, and a count flags any purchased part missing from the price list.

Use it to estimate material cost per unit, compare suppliers, or see which sub-assembly drives the cost. The prices in the example are fictional.

What’s inside

  • Three-level BOM of 34 lines with parent references and extended quantities through each level
  • Units required = quantity per parent × units required by the parent line (INDEX and MATCH)
  • Sub-assembly cost rolls up with SUMIF over the lines beneath it, with no double counting
  • Supplier, category and level summaries use SUMIF, COUNTIF and SUMIFS on purchased lines
  • Check that the rolled-up total equals the purchased total

Which tabs does the workbook have?

TabWhat it holds
BOMProduct, units to build, and the bill of materials: parent lines, quantities, units required, unit and extended costs.
Price listUnit price, supplier, unit and lead time for each purchased part number, looked up by the BOM.
SummaryHeadline cost per unit, top-level assemblies, and purchased cost by supplier, category and BOM level.
NotesWhat the workbook does, how to use it, the roll-up method, the assumptions and the professional-verification note.

What formulas does this template use?

This template holds 366 formulas in 938 cells across 4 tabs, so 39% of its cells calculate. They use 10 distinct functions; the longest formula is 113 characters and 157 of them read from another tab.

FunctionUsesWhat it does
IF154one result when a test is true, another when false
INDEX130value at a position in a range
MATCH130position of a value in a range
IFERROR62swaps an error for a fallback value
SUMIF21adds values meeting one condition
COUNTIF16counts cells meeting one condition
COUNTIFS16counts cells meeting several conditions
SUMIFS16adds values meeting several conditions
SUM12adds numbers
ABS3absolute value

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

  1. On the BOM tab, enter the product name and the number of units to build.
  2. Set each line's parent line: 0 for the product, or the line number of its sub-assembly.
  3. Enter the quantity per parent and mark each line Make or Buy.
  4. Enter the unit price, supplier and lead time for each purchased part on the Price list tab.
  5. Read the rolled-up cost on the BOM tab and the supplier and category totals on the Summary tab.

What is it good for?

  • Estimating material cost per unit for a new product
  • Comparing supplier spend across a bill of materials
  • Finding the sub-assembly that drives most of the cost
  • Costing a design change before supplier quotes arrive

Questions about this sheet

How are sub-assembly costs counted?

A Make line's unit cost is the sum of the cost per parent unit of its child lines. Only purchased (Buy) lines feed the supplier and category totals, so nothing is counted twice.

Why does a line show Not in price list?

The part number on the BOM line is not on the Price list tab. Add the part or correct the number. The count on the BOM tab shows how many lines are affected.

Can I add more levels?

Yes. Give the new line a parent above it and copy the formulas down, and the level and quantity columns follow the parent chain. The level summary covers levels 1 to 3, so add a row for each further level.

Does it include freight, scrap and taxes?

No. Add those as separate lines or into the unit price. The workbook uses one price per part with no volume breaks.