Bill of Materials (BOM) Excel Template & Unit Cost Roll-Up
Sub-assembly component costing, direct labor hours, machine overhead allocation, and batch pricing.
What This Workbook Does
Parts master with part type, unit of measure, unit cost, supplier and lead time
Multi-level BOM structure (finished good, modules, sub-assemblies, components) with quantity per unit
Scrap buffer from Settings built into every effective line cost
Batch costing: direct labor, factory overhead absorption, total production cost and cost per unit
Target wholesale price per unit from your margin setting
Dashboard with component cost breakdown by part type and a cost chart
Worksheets Included
6 TabsUniversal Windows & Mac support with dynamic arrays
Runs in browser on office.com with free Microsoft account
Pure .xlsx formulas. Never blocked by corporate IT antivirus
Receive both a clean blank file and pre-filled sample rows
Free Bill of Materials Template (Excel)
Multi-level BOM format with cost roll-up formulas. No signup, no macros, opens in Excel 2021, Microsoft 365 and Excel for the web.
Bill of Materials Format in Excel
A usable BOM has one row per part and these columns. The first six are required; the rest make it useful for purchasing and planning.
| Column | What goes in it |
|---|---|
| Level | 0 = finished good, 1 = direct component or sub-assembly, 2 = part of a sub-assembly. |
| Part number | Unique SKU or item code; the key every other sheet looks up. |
| Description | Plain-language name of the part, material or sub-assembly. |
| Quantity per | How many of this part one unit of its parent needs. |
| Unit of measure | each, kg, m, m², L — must match how the part is bought or converted. |
| Unit cost | Purchase cost per unit of measure. |
| Scrap % | Expected loss in cutting, setup or rejects; inflates the quantity used. |
| Extended cost | Quantity per × (1 + scrap %) × unit cost. |
| Make / Buy | Whether the line is produced in-house or purchased. |
| Supplier & lead time | Who supplies it and how many days it takes to arrive. |
Bill of Materials Example (Multi-Level)
A wooden dining chair: the seat is a sub-assembly (level 1) built from three level-2 parts, so its cost rolls up before it is added to the chair. Material cost per chair: $45.60.
| Level | Part no. | Description | Qty per | UoM | Unit cost | Extended |
|---|---|---|---|---|---|---|
| 0 | CHR-100 | Oak dining chair (finished good) | 1 | each | $45.60 | |
| 1 | SEAT-10 | Seat assembly | 1 | each | $26.50 | |
| 2 | OAK-BD18 | Oak board 18 mm | 0.35 | m² | $42.00 | $14.70 |
| 2 | FOAM-30 | Seat foam 30 mm | 1 | each | $3.80 | $3.80 |
| 2 | FAB-UPH | Upholstery fabric | 0.5 | m | $16.00 | $8.00 |
| 1 | LEG-OAK | Turned oak leg | 4 | each | $3.25 | $13.00 |
| 1 | HW-KIT | Screws & corner brackets kit | 1 | each | $1.90 | $1.90 |
| 1 | FIN-LAC | Lacquer finish | 0.15 | L | $28.00 | $4.20 |
A single-level BOM lists only the level-1 lines. A multi-level (indented) BOM like this one shows how sub-assemblies are built, which is what you need for costing and MRP.
How to Create a Bill of Materials in Excel
- List the finished product on one row as level 0 with quantity 1.
- Add every direct component and sub-assembly as level 1, each with its quantity per finished unit.
- Break each sub-assembly into its own parts as level 2 (and deeper if needed).
- Enter unit cost and scrap % for purchased parts; calculate extended cost per line.
- Roll costs up: each sub-assembly equals the sum of its child lines, and the finished good equals the sum of its level-1 lines plus labor and overhead.
Want to cost a BOM right now? Use the free BOM calculator and export it to Excel, or see the BOM cost roll-up formula.
Frequently Asked Questions
Does this template require enabling macros or VBA code?
No. All Datasheetly templates are 100% macro-free (.xlsx). They rely on native modern Excel formulas (XLOOKUP, SUMIFS, LET), never trigger antivirus blocks, and are completely safe for corporate computers.
Are there any recurring subscriptions or extra fees?
None whatsoever. You make a single one-time payment of $9 and own the DRM-free Excel workbook files forever. No monthly recurring charges, no per-seat fees, and no cloud lock-in.
What software versions are required to run this workbook?
Datasheetly workbooks are engineered for modern Microsoft Excel: Microsoft 365 (Windows & Mac), Microsoft Excel 2021+, and Excel for Web (free online via office.com with any Microsoft account). Because our models leverage modern dynamic array formulas (LET, XLOOKUP, FILTER, SUMIFS) and native Excel structured tables, they are designed exclusively for Excel. Older legacy releases (Excel 2019/2016) and third-party spreadsheet viewers do not support these modern formula engines.
Do I receive clean blank files for my company data?
Yes! In your download vault, you receive two separate workbooks: a Sample Data Workbook (with realistic pre-filled rows to inspect formulas) and a Clean Blank Workbook (with all formulas and formats ready for your live data).
What if the template doesn't meet my business needs?
Every purchase is backed by our 30-Day Defect & Satisfaction Guarantee. If any formula fails or the template does not perform as documented, simply contact support for an immediate resolution or refund.
Customer Reviews & Ratings
Real feedback from verified purchasers and small business operators.
Be the First to Review This System
Tested this workbook in your company operations? Share your review or formula suggestions directly with our engineering team to help fellow operators.
Datasheetly spreadsheet workbooks and templates are operational productivity calculation tools provided for informational purposes only. They do not constitute certified public accounting (CPA), legal, tax, or medical advice. Templates are not certified or intended for the storage of protected health information (PHI) under HIPAA regulations or fiduciary client trust accounts without independent compliance review. Users are advised to review all financial and operational outputs with a licensed CPA, attorney, or qualified professional before taking formal financial or operational actions.