Engineering & PLMFree worksheetPDF, Excel, Word export

BOM Explosion Requirements Worksheet

A BOM explosion turns a finished good demand into the quantity of every component you need to buy or make. This worksheet handles quantity per, scrap and cost, and is useful when you need to sanity check an ERP explosion or plan a one off build in Excel.

Download:

BOM level	Component part number	Parent quantity required (units)	Quantity per parent	Scrap allowance (%)	UoM	Gross component requirement	Unit cost	Extended cost
						51		$938
						50.5		$1,351
						100		$320
						420		$38
						8.25		$103
						11.5		$113

Total component lines quantity

641.25

Total material cost for the build

$2,863

Most expensive component line

$1,351

Material cost per finished unit (set parent quantity to total build)

$57

For a multi level BOM, explode one level at a time: the parent quantity for a level 2 line is the gross requirement of its level 1 parent, not the finished good quantity. Change the 50 in the summary mentally if your build quantity differs, or export and adjust in Excel.

Automate BOM explosions from your ERP

Optional: ask how a read-only BOM explorer over your ERP data could replace this spreadsheet.

Optional. The tool and every export work without this. No spam.

How to use it

List each component on its own row with the BOM level. Enter the parent quantity required, then the quantity per parent from the bill of materials, and the scrap or yield loss as a percentage. The sheet multiplies them and adds the scrap allowance to get gross component requirement, then multiplies by unit cost. For a deeper BOM, work level by level: the gross requirement of a level 1 subassembly becomes the parent quantity of its level 2 children.

Copy BOM lines from your ERP export.
Keep units of measure consistent with the BOM.
Use scrap only where the BOM does not already include it.
Export to Excel for further level by level work.

The formula and standards

Gross requirement equals parent quantity times quantity per times one plus the scrap percentage. Some systems treat scrap as a yield: dividing by one minus the scrap fraction, which gives a slightly larger number for high scrap rates. Check how your ERP defines component scrap versus operation yield before comparing numbers. Phantom assemblies pass requirements straight through without being stocked.

Reading the result

The extended cost column shows which components drive the build cost, which is usually where buyer attention pays off. In the sample, castings and shafts dominate while screws are almost noise. Components with fractional quantities per, such as gasket sheet by the metre, are where rounding rules and minimum purchase multiples matter more than the quantity itself.

Common mistakes

People often apply scrap twice, once on the BOM line and again in the item master. Others forget effectivity dates and explode an engineering revision that is not yet released. A third mistake is multiplying level 2 lines by the finished good quantity instead of the level 1 parent requirement, which overstates demand wildly on multi level bills.

Frequently Asked Questions

What is a BOM explosion?

It is the process of expanding a parent item into all the components, subassemblies and raw materials needed to build it, multiplying quantity per at each level. MRP does this automatically; a worksheet lets you verify it manually.

How do I include scrap in a BOM explosion?

Multiply the base requirement by one plus the scrap percentage, or divide by the yield. Use whichever method your ERP uses. This sheet uses the multiplication form, which is the common component scrap approach.

Do phantom assemblies appear in the explosion?

Phantoms are exploded through: their components are required directly against the parent and the phantom itself is not planned or stocked. List the phantom's children on the parent's level in the worksheet.

Can I use this for a multi level BOM?

Yes, one level at a time. Calculate the level 1 gross requirements, then use each subassembly's gross requirement as the parent quantity for its own components on the next pass.

If you explode bills of materials in a spreadsheet because the ERP view is hard to read, Netray can build a single level and indented BOM explorer on top of your live data.

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Engineering & PLM tools

See all

Back to All Tools