MRP and the Shared-Component Problem Spreadsheets Can’t Solve

Most explanations of material requirements planning stop at the basic formula: gross requirement minus on-hand stock minus incoming supply equals net requirement, offset backward by lead time to get an order date. That part is genuinely simple, simple enough that a spreadsheet handles it fine for a single product with its own dedicated parts.

The part that breaks isn’t the formula. It’s what happens when the same component feeds into more than one product, which for an assembly manufacturer with any real product range is the normal case, not the exception.

Where the basic formula stops being enough

A single fastener, connector, or common sub-assembly might appear in five different finished products. Getting the net requirement right means summing demand for that component across every product that consumes it, at the correct rate for each, before running the requirement calculation. Get this wrong and the result isn’t a rounding error. It’s a systematic undercount, because each product’s individual calculation looks correct in isolation while the aggregate demand for the shared part never gets properly totaled.

A spreadsheet can be built to do this with nested formulas referencing multiple sheets. It holds together for as long as the product range and supply structure stay fixed. Add a new SKU that uses an existing shared component, or swap a supplier, and the formula web usually needs to be rebuilt by the person who originally built it. If that person isn’t available, planning stalls.

The bullwhip effect starts smaller than people expect

MRP output is only as accurate as the on-hand stock figure it starts from. A modest inventory error at the warehouse doesn’t stay modest as it moves upstream. It compounds at every level of the supply chain, an effect generally known as the bullwhip effect, where a small discrepancy at the point of consumption produces an amplified, distorted order pattern by the time it reaches suppliers further up the chain.

This is the specific reason moving to MRP software without first cleaning up inventory accuracy tends to disappoint. The software doesn’t fix bad data. It scales whatever error was already there.

Planning that ignores capacity isn’t really planning

Basic MRP calculates requirements as though production capacity were infinite. In practice, every work center has a finite throughput, and a materials plan that ignores that constraint looks clean on screen and fails on the floor the moment it meets a bottleneck. Advanced planning and scheduling, APS, layers capacity constraints on top of MRP so the resulting schedule is something the shop can actually execute, not just something that balances on paper.

For a manufacturer running multiple parallel lines or cellular production, this isn’t an optional refinement. Without it, MRP produces a materials plan for a schedule the floor was never going to hit.

Safety stock has to be calculated, not guessed

Supplier lead times are rarely a fixed number; they vary, sometimes by days, sometimes by weeks. A planning system needs a buffer that reflects the real, measured variability of a specific supplier’s lead time, not a round number picked because it felt safe. Undersize the buffer and the line stops. Oversize it and the company is quietly funding a warehouse full of capital it can’t use for anything else.

What an undercounted shared component actually costs

The consequences aren’t abstract. Material shortages stop production lines and force expedited purchasing at a premium, with industry estimates putting the annual revenue impact of stockouts alone somewhere between two and five percent. At the same time, the opposite failure, dead stock accumulated out of fear of running short, typically represents twenty to thirty percent of a warehouse’s inventory, with annual holding costs running twenty-five to thirty-five percent of that inventory’s value.

These two numbers rarely show up separately. A company running short on some components is almost always simultaneously sitting on excess of others. That’s not a coincidence. It’s the predictable result of planning by feel instead of by calculation.

What this means for the system doing the planning

Functional MRP requires three things at once: accurate, real-time stock data, correct multi-level explosion of the bill of materials that properly sums shared-component demand across every product that uses it, and backward scheduling based on real supplier lead times. None of the three holds up reliably in a spreadsheet once the product range passes a few dozen SKUs with meaningful component overlap.

Bimp’s purchasing module is built around exactly this logic, aggregating open demand across the entire product range rather than requiring someone to calculate requirement product by product. For a manufacturer that’s been living in reactive mode, ordering only after something runs out, the shift isn’t cosmetic. It’s the difference between a plan that’s already behind the problem and one that’s ahead of it.