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.
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. | |||||||||||
| Product | Example conveyor drive unit | ||||||||||
| Units to build | 40 | Quantities below are per parent. Units required multiply down the levels. | |||||||||
| Line | Parent line (0 = product) | Level | Part number | Description | Make or buy | Category | Unit | Qty per parent | Supplier | Units required | Unit cost (USD) |
| 1 | 0 | 1 | SUB-GBX | Gearbox assembly | Make | Subassembly | ea | 1.00 | 40.0 | $586.88 | |
| 2 | 0 | 1 | SUB-MTR | Motor assembly | Make | Subassembly | ea | 1.00 | 40.0 | $1,407.36 | |
| 3 | 0 | 1 | SUB-FRM | Frame weldment | Make | Subassembly | ea | 1.00 | 40.0 | $742.57 | |
| 4 | 0 | 1 | SUB-GRD | Guard assembly | Make | Subassembly | ea | 1.00 | 40.0 | $207.48 | |
| 5 | 0 | 1 | SUB-CTL | Control panel assembly | Make | Subassembly | ea | 1.00 | 40.0 | $1,522.30 | |
| 6 | 0 | 1 | PKG-07 | Packing and labels, per unit | Buy | Packaging | ea | 1.00 | Example Packaging | 40.0 | $18.60 |
| 7 | 1 | 2 | GB-HSG-01 | Gearbox housing, cast iron | Buy | Castings | ea | 1.00 | Example Castings Co. | 40.0 | $185.00 |
| 8 | 1 | 2 | GB-GR-02 | Helical gear, 48 teeth | Buy | Gears and shafts | ea | 1.00 | Example Gear Works | 40.0 | $96.50 |
| 9 | 1 | 2 | GB-PN-03 | Pinion, 18 teeth | Buy | Gears and shafts | ea | 1.00 | Example Gear Works | 40.0 | $58.00 |
Showing the first 16 of 45 rows and 12 of 14 columns. Cells with formulas show the formula on hover.
| Price list for purchased parts | |||||
| Example data — replace the blue input cells with your own. | |||||
| Part number | Description | Supplier | Unit | Unit price (USD) | Lead time (days) |
| PKG-07 | Packing and labels, per unit | Example Packaging | ea | $18.60 | 10 |
| GB-HSG-01 | Gearbox housing, cast iron | Example Castings Co. | ea | $185.00 | 28 |
| GB-GR-02 | Helical gear, 48 teeth | Example Gear Works | ea | $96.50 | 35 |
| GB-PN-03 | Pinion, 18 teeth | Example Gear Works | ea | $58.00 | 35 |
| GB-BRG-04 | Deep groove ball bearing 6308 2RS | Example Bearing Supply | ea | $14.80 | 14 |
| GB-OIL-05 | Gear oil, ISO VG 220 | Example Industrial Supply | L | $6.40 | 7 |
| GB-SEAL-06 | Shaft seal 40 x 62 x 8 mm | Example Bearing Supply | ea | $9.75 | 14 |
| SHF-01 | Output shaft, ground steel | Example Machining | ea | $142.00 | 21 |
| KEY-02 | Parallel key 16 x 10 x 70 mm | Example Fasteners Ltd | ea | $2.10 | 7 |
| BRG-04 | Deep groove ball bearing 6210 | Example Bearing Supply | ea | $22.40 | 14 |
| RET-03 | External retaining ring | Example Fasteners Ltd | ea | $0.85 | 7 |
| MTR-01 | Electric motor, 7.5 kW, 4-pole | Example Electrical Supply | ea | $1,240.00 | 42 |
Showing the first 16 of 34 rows and 6 of 6 columns. Cells with formulas show the formula on hover.
| Bill of materials cost summary | ||||||
| Calculated from the BOM and Price list tabs. Change inputs there. | ||||||
| Headline | ||||||
| Product | Example conveyor drive unit | |||||
| Units to build | 40 | |||||
| Material cost, rolled up (total) | $179,407.60 | |||||
| Material cost per unit built | $4,485.19 | |||||
| Purchased cost, all Buy lines | $179,407.60 | |||||
| Check: rolled-up total equals purchased total | OK | |||||
| Buy lines not in the price list | 0 | |||||
| Buy lines | 28 | |||||
| Make lines (sub-assemblies) | 6 | |||||
| Top-level lines (each rolls up its whole sub-assembly) | ||||||
| Item | Level | Units required | Rolled-up unit cost | Extended cost | Share of total |
Showing the first 16 of 65 rows and 7 of 7 columns. Cells with formulas show the formula on hover.
| Bill of materials with cost roll-up |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Holds a three-level bill of materials for an example product, with extended quantities and costs. Purchased parts take their price and supplier from a price list; sub-assemblies roll up the cost of everything beneath them. A summary tab to… |
| How to use it |
| 1. On the BOM tab, enter the product name and the number of units to build. |
| 2. List each line with its parent line number. Use 0 for lines that go straight into the product, and the line number of the sub-assembly for lines inside it. |
| 3. Enter the quantity per parent, the part number and the make-or-buy flag. Put every sub-assembly above its children. |
| 4. On the Price list tab, enter the unit price, supplier and lead time for each purchased part. The part number must match exactly. |
| 5. Read the rolled-up material cost at the foot of the BOM tab and the supplier, category and level totals on the Summary tab. |
| 6. Check that the rolled-up total equals the purchased total, and that no Buy line is missing from the price list. |
| Formulas and method |
| Units required: a top-level line uses qty per parent × units to build. A lower line uses qty per parent × the units required by its parent. |
Showing the first 16 of 35 rows and 1 of 1 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?
| Tab | What it holds |
|---|---|
| BOM | Product, units to build, and the bill of materials: parent lines, quantities, units required, unit and extended costs. |
| Price list | Unit price, supplier, unit and lead time for each purchased part number, looked up by the BOM. |
| Summary | Headline cost per unit, top-level assemblies, and purchased cost by supplier, category and BOM level. |
| Notes | What 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.
| Function | Uses | What it does |
|---|---|---|
IF | 154 | one result when a test is true, another when false |
INDEX | 130 | value at a position in a range |
MATCH | 130 | position of a value in a range |
IFERROR | 62 | swaps an error for a fallback value |
SUMIF | 21 | adds values meeting one condition |
COUNTIF | 16 | counts cells meeting one condition |
COUNTIFS | 16 | counts cells meeting several conditions |
SUMIFS | 16 | adds values meeting several conditions |
SUM | 12 | adds numbers |
ABS | 3 | absolute 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?
- On the BOM tab, enter the product name and the number of units to build.
- Set each line's parent line: 0 for the product, or the line number of its sub-assembly.
- Enter the quantity per parent and mark each line Make or Buy.
- Enter the unit price, supplier and lead time for each purchased part on the Price list tab.
- 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.