Inventory Planning Without the Stockout-Overstock Cycle

A pharmaceutical manufacturer once tracked raw material inventory in a spreadsheet updated at the end of each shift. Someone copied the wrong formula while updating the active-ingredient balance. The next morning, a planner started a large production run believing there was enough raw material on hand. Partway through the synthesis process, it turned out 40 kilograms of a component the batch couldn’t proceed without were simply missing. The chemical process couldn’t be paused. The company scrapped tens of thousands of dollars of already-consumed material and shut the line down for eight hours.

The scale is far beyond a typical small manufacturer’s exposure, but the underlying mechanism, a number in the system quietly diverging from reality on the floor, repeats weekly in shops that plan purchasing by feel.

A pattern almost every shop on manual planning recognizes

Nearly every manufacturer running purchasing on intuition ends up with the same odd contradiction: critical parts run short at the exact moment the warehouse is packed with material that hasn’t moved in months. It looks like two separate problems. It’s one problem, viewed from opposite ends.

Without an algorithmic requirement calculation, purchasing runs on instinct and on reacting to a shortage that’s already happened. A component runs out and stops the line, the buyer gets burned once, and the next order for that item comes in padded, just in case. That padding sits unused until a different component runs short, and the cycle starts over with a new part each time.

The cost of that padding is real. Dead stock typically accounts for 20 to 30 percent of a warehouse’s inventory, and carrying it, storage, insurance, obsolescence risk, tied-up capital, runs 25 to 35 percent of that inventory’s value every year.

The arithmetic behind a correct order

A correct requirement calculation isn’t a gut call. It’s specific arithmetic: how many units are planned, how much material each one needs per the bill of materials, how much is already on hand or in transit, and what the supplier’s actual lead time is. The gap between requirement and availability, offset by lead time, produces the order date and quantity.

The complication is components shared across multiple products, which for any manufacturer with a real product range is the rule rather than the exception. Picture a fastener that goes into five different finished products. Calculate its requirement product by product and add the numbers up manually, and an error is nearly guaranteed: someone forgets to include one of the five when the next customer order lands, and the combined total comes up short. Without summing demand for that fastener across every product that consumes it, a buyer sees only part of the picture and orders less than what’s genuinely needed.

Safety stock: a number, not a guess

Supplier lead times are rarely fixed. They vary, sometimes by days, sometimes by weeks, and a planning system needs a buffer that reflects the measured variability of a specific supplier’s delivery history, not a round number that feels safe.

A workable practical formula: safety stock equals average daily consumption multiplied by the gap between the supplier’s maximum and average lead time over the last several order cycles. If a supplier typically delivers in 10 days but sometimes runs to 18, and average daily consumption of the part is 20 units, safety stock should land around 160 units, a specific figure rather than a padded guess. Undersize it and the line stops on a predictable schedule. Oversize it and capital sits frozen exactly the way just-in-case orders already freeze it.

What a shortage actually costs, in numbers

The consequences are concrete. Shortages of needed materials stop lines and force rushed purchasing at inflated prices with expedited freight on top, and industry estimates put the annual revenue impact of stockouts alone somewhere between 2 and 5 percent. On top of the direct cost, missed delivery commitments to customers turn into a separate line item wherever contracts carry penalty clauses for late shipment.

These two figures, stockout losses and overstock losses, rarely show up separately. A company running short on some parts is almost always sitting on excess of others at the same time. That’s not a coincidence. It’s the predictable result of planning blind instead of planning by calculation.

Why a spreadsheet loses this fight as the range grows

Calculating requirement for one product with a short parts list works fine in a spreadsheet. It falls apart once the range grows to dozens of items with overlapping components, where every new product or supplier change forces a rebuild of the entire web of nested formulas holding the calculation together. The most common failure is systematic under-ordering of exactly the components used across several products at once, because no formula built by hand reliably sums demand for all of them correctly.

What changes with calculated planning

When requirement calculation becomes automatic and draws on current stock, open customer orders, and real supplier lead times, a buyer stops working off a list of what’s running low and starts working from an actual plan: what to order, how much, and when, across the entire product range at once.

Bimp’s purchasing module is built around exactly this logic, automatically aggregating demand for shared components across the full product range instead of requiring a buyer to calculate it item by item. For a manufacturer that’s spent years reacting to shortages as they happen, that’s the difference between chasing the problem and staying a step ahead of it.