Bill of Materials (BOM) Format: Free Excel Template with a Worked Example

What is a bill of materials?

A BOM is the recipe for a product. It tells the store what to issue, the purchase team what to buy and the accountant what one unit costs in material. If the BOM is wrong, every one of those numbers is wrong too.

A good BOM helps with:

• Costing: material quantity × rate, added up, gives the material cost per unit.
• Purchase: you buy what the orders need, not what someone remembers.
• Production planning: the shop floor knows which sub-assemblies to make first.
• Stock: issues against a work order can be checked against what the BOM says.

What each column in the BOM format means

The Excel format uses six core columns. Add rate, supplier or lead time only if your team will keep them updated.

• Level: 0 is the finished product. 1 is a part or sub-assembly that goes straight into it. 2 is a part that goes into a level 1 sub-assembly, and so on.
• Item code: one fixed code per item, the same code used in your item master, store and purchase orders. Never two codes for one item.
• Description: name plus size and grade, e.g. "MS angle 40×40×5 mm". Enough for the store to pick the right item without asking.
• Qty per unit: how much is needed to make one unit of the parent item, before scrap.
• Unit: the unit you buy and stock in: nos, kg, m, litre. Mixed units (kg in the BOM, metres in the store) are a common cause of shortages.
• Scrap %: the extra you lose to cutting, trimming, spatter or rejection. Planning quantity = qty per unit × (1 + scrap %).

Worked example: a fabricated steel trolley (multi-level BOM)

Below is an Example for a small Indian fabrication unit making a mild steel (MS) shop-floor trolley. All codes, quantities and scrap figures are illustrative. Replace them with your own drawing and shop-floor numbers.

Table 1 (Example): BOM structure

Table 2 (Example): quantities and scrap for one trolley

Read the two tables together. The level 2 rows under SA-BF-01 are needed per base frame, and one trolley takes one base frame. Prefixes: FG finished good, SA sub-assembly, RM raw material, BO bought-out, CN consumable.

Single-level vs multi-level BOM

A single-level BOM lists only the items that go straight into the finished product. For the trolley, that is base frame, handle assembly, castors and paint. It is quick to make and fine when you buy sub-assemblies ready-made.

A multi-level BOM goes down to the raw material inside each sub-assembly, as in the example. Use it when you make sub-assemblies in-house, send them for job work, or keep them in stock. Without it, you cannot work out how much angle or sheet to buy for an order.

You may also hear about other BOM types: engineering BOM (design stage), manufacturing BOM (what the shop floor builds), sales BOM (a kit as sold), service BOM (spares for repair), modular BOM and phantom BOM (a grouping used only for planning). Most small manufacturers need only a manufacturing BOM.

Common BOM mistakes

• No scrap %. The BOM says 4.8 m of angle, the floor uses 5 m, and stock goes short every month.
• Unit mismatch. Sheet in kg in the BOM but in sheets in the store. Pick one unit per item, or record the conversion.
• Duplicate item codes. "MS Angle 40x40" and "Angle 40×40×5" as two items split your stock and your purchase history.
• Consumables left out. Welding rod, paint and gas are small per unit but large per month.
• Old revisions in use. The design changed but the Excel on someone's laptop did not. Keep one master copy and note the revision date.
• Flat lists for multi-level products. Mixing sub-assemblies and raw material on one level double-counts material.

How the BOM feeds material planning

Material requirement planning (MRP) multiplies each BOM line by the order quantity, then compares it with stock. Here is the trolley example for an order of 50 trolleys.

Table 3 (Example): material check for an order of 50 trolleys

In Excel, this works for one order. With ten open orders sharing the same angle and sheet, it gets slow and error-prone.

In TranZact, the MRP engine runs through your orders and BOMs, then tells you what to buy and when. The purchase flow runs MRP report → indent → RFQ → PO → GRN. Your purchase person reviews the list and raises the PO. TranZact's production module supports multi-level BOMs with sub-assemblies and nested components, where the same material can be a raw input at one level and a finished sub-assembly at another.

TranZact BOM and planning facts (as of 22 Oct 2026)