Build a Bill of Materials Cost Rollup

A manufactured product's cost is the cost of everything that goes into it, and everything that goes into its sub-assemblies, all the way down to purchased parts — plus the labour and overhead of each assembly step on the way up. That structure lives in a bill of materials (BOM): a list of parent–component pairs with the quantity of each component per parent. ERP systems calculate the rollup, but the BOM and the costs are often exported to Excel for a pricing review, a quote or a what-if, where nested SUMPRODUCT formulas become unmanageable beyond two levels. A short recursive function in Python handles any depth. This page reads a three-level BOM and a cost list from Excel, rolls costs up from purchased parts to finished products including scrap allowances and each assembly's own labour and overhead, checks for loops and parts with no cost, lists where each part is used, and calculates the effect of a supplier price increase on every finished product. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Two sheets: the BOM, one row per parent and component with the quantity per parent and a scrap allowance — the extra percentage consumed because some parts are lost or damaged in assembly; and the item costs, where a purchased part's cost is its buying price and an assembly's cost is the labour and overhead of assembling it. Two bicycles share wheels and bolts but differ in frame and drivetrain.

Step 1 — Load the BOM and check it before costing

import pandas as pd

bom = pd.read_excel("bom.xlsx")
items = pd.read_excel("item_costs.xlsx").set_index("Item")

children = {p: list(zip(g["Component"], g["Qty per"], g["Scrap %"])) for p, g in bom.groupby("Parent")}
all_items = set(bom["Parent"]) | set(bom["Component"])
no_cost = sorted(all_items - set(items.index))
purchased_with_children = sorted(set(children) & set(items.index[items["Type"] == "purchased"]))

def find_loop(node, path=()):
    if node in path:
        return path[path.index(node):] + (node,)
    for comp, _, _ in children.get(node, []):
        loop = find_loop(comp, path + (node,))
        if loop:
            return loop
    return None

loops = [l for l in (find_loop(p) for p in children) if l]
print("items with no cost:", no_cost, "| purchased items with a BOM:", purchased_with_children, "| loops:", loops)

Before any cost is calculated, three checks protect the result. Every item in the BOM must have a cost record; a missing one would silently count as zero and make products look cheaper than they are. An item marked as purchased should not also have components, which usually means a part has been made in-house and its type was never updated. And the BOM must contain no loops — an assembly that, through some chain of components, contains itself — because a recursive rollup would never finish. The loop search follows each parent down through its components and reports the first cycle it finds as a readable path. This BOM passes all three.

Step 2 — Roll the costs up

from functools import lru_cache

@lru_cache(maxsize=None)
def rolled_cost(item):
    own = float(items.loc[item, "Cost"])
    if item not in children:
        return own                                                    # a purchased part
    materials = sum(qty * (1 + scrap) * rolled_cost(comp) for comp, qty, scrap in children[item])
    return round(own + materials, 4)

rollup = pd.DataFrame({"Item": sorted(children), "Own cost": [items.loc[i, "Cost"] for i in sorted(children)],
                       "Rolled cost": [rolled_cost(i) for i in sorted(children)]})
rollup["Materials"] = (rollup["Rolled cost"] - rollup["Own cost"]).round(2)
print(rollup.to_string(index=False))

The rollup is a recursive function: the cost of a purchased part is its price; the cost of an assembly is its own labour and overhead plus, for each component, the quantity per assembly, increased by the scrap allowance, times the component's own rolled cost. Because the function calls itself for each component, it works to any depth, and lru_cache makes sure each item is costed once even when it appears in many assemblies — the wheel, used in both bikes, is calculated a single time. The result for each assembly separates its own cost from the materials it consumes. A wheel comes to £47.93 — rim, hub, tyre and 32 spokes with a 3% allowance for broken spokes, plus £6.50 to build — and the standard bike to £220.27, of which the two wheels are £95.87, more than two fifths. The pro bike, with its carbon frame and crank, comes to £557.77.

Step 3 — Where is each part used?

from collections import defaultdict

def explode(item, qty=1.0, out=None):
    out = defaultdict(float) if out is None else out
    for comp, per, scrap in children.get(item, []):
        need = qty * per * (1 + scrap)
        if comp in children:
            explode(comp, need, out)
        else:
            out[comp] += need
    return out

finished = ["BIKE-STD", "BIKE-PRO"]
where_used = pd.DataFrame({f: explode(f) for f in finished}).fillna(0).round(3)
print(where_used.to_string())

The same tree read the other way gives the where-used view: for each finished product, the total quantity of every purchased part it consumes, after multiplying quantities down every level. A standard bike uses 64 spokes through its two wheels, and, through the frame fixings and the drive kit, sixteen bolts plus scrap. The table answers the questions purchasing asks — which products does this part go into, and how many do we need for a production run — and it is the input for the price-change analysis that follows.

Step 4 — What a supplier price increase does

def product_costs(price_changes=None):
    rolled_cost.cache_clear()
    original = items["Cost"].copy()
    for item, pct in (price_changes or {}).items():
        items.loc[item, "Cost"] = original[item] * (1 + pct)
    result = {f: rolled_cost(f) for f in finished}
    items["Cost"] = original
    rolled_cost.cache_clear()
    return result

base = product_costs()
scenario = product_costs({"SPOKE": 0.25, "RIM-26": 0.10, "TYRE-26": 0.08})
impact = pd.DataFrame({"Current": base, "After increase": scenario})
impact["Change"] = impact["After increase"] - impact["Current"]
impact["Change %"] = (impact["Change"] / impact["Current"] * 100).round(2)
print(impact.round(2).to_string())

When a supplier announces price increases, the question is what they do to the cost of each finished product. Applying the new prices to the cost table and rerunning the rollup answers it exactly, through every level of the BOM; the cache is cleared before and after so the scenario neither uses stale values nor leaves the changed prices behind. A 25% rise in spokes, 10% in rims and 8% in tyres adds the same amount to both bikes, because they share the wheel, but as a percentage it matters far more to the cheaper standard bike than to the pro. Both bikes go up by £7.43, but that is 3.4% on the standard bike and only 1.3% on the pro. That difference is what a pricing review needs to see: whether the standard bike's selling price still covers the higher cost.

Writing the rollup back to Excel

The rolled costs, the where-used table and the scenario belong in one workbook for the pricing review: a sheet per view, with the rolled cost of every assembly next to its own cost and materials, so a reviewer can follow any figure down one level at a time. It is worth adding an indented BOM sheet too — each finished product exploded into its full tree, one row per line with the level shown by indentation and the extended quantity and cost per line — because that is the view engineers and buyers recognise from their ERP. The recursive explode function produces it with one more column for the level. Writing costs as values rather than formulas keeps the workbook a snapshot of the rollup at the date it was run, which is what a standard cost review needs.

Common pitfalls

• Costing before checking. Missing costs count as zero and loops never finish; check first.
• Forgetting scrap allowances. Small percentages on high-quantity parts add up.
• Recomputing shared sub-assemblies. Cache the rolled cost so each item is calculated once.
• Only one level in Excel formulas. Use recursion for BOMs of any depth.
• What-if changes left in place. Restore the original prices after a scenario.

Conclusion

A bill of materials cost rollup reads the parent–component pairs and item costs, checks for missing costs, mislabelled purchased parts and loops, and then rolls costs up recursively: each assembly's own labour and overhead plus each component's quantity, scrap allowance and rolled cost. The same tree gives the where-used quantities of every purchased part per finished product and, by rerunning the rollup with new prices, the exact effect of a supplier price increase on each product.

Frequently asked questions

How do I calculate a multi-level BOM cost in Python? Write a recursive function that returns a part's price for purchased items and, for assemblies, their own cost plus the sum of component quantities times component costs.

How do I include scrap in a BOM cost rollup? Multiply each component's quantity by one plus its scrap percentage before multiplying by its cost.

How do I detect loops in a bill of materials? Follow each parent down through its components while tracking the path; if an item appears on its own path, there is a loop.

How do I see the effect of a component price change on finished products? Change the component's price, rerun the rollup for each finished product, compare with the current costs and restore the original price.

Related

• Up one level: Building Finance and Operations Reports — finance and operations reports.
• Roll Up Totals Through a Parent-Child Hierarchy — the same recursion for reporting lines.
• Calculate Weighted Average Cost Inventory Valuation — the cost of parts in stock.
• Model the Impact of a Price Increase — the selling-price side.