Inventory reorder point planner

The reorder point formula, properly

The simple version of the reorder point formula — average daily sales multiplied by lead time — assumes your supplier is perfectly reliable and your demand is perfectly stable. Neither is true. The complete formula adds a safety stock buffer sized to absorb both kinds of variability.

ROP  = (d × LT) + SS
SS   = z × √( LT × σd² + d² × σLT² )

where  d    = average daily demand
       LT   = average lead time in days
       σd   = standard deviation of daily demand
       σLT  = standard deviation of lead time
       z    = service-level factor (1.65 at 95%)

Why lead-time variability costs more than demand variability

In the combined formula, demand variability is scaled by the square root of lead time, but lead-time variability is scaled by average demand squared. That asymmetry means a supplier whose delivery date swings by a week forces far more safety stock than demand that swings by 20% — and the effect grows with volume.

Practically: if your 3PL receives containers that arrive anywhere from day 28 to day 42, your safety stock should be sized for the variability of the arrival, not the average. Chasing a supplier with a reliable schedule is often more valuable than negotiating a lower unit price.

• Domestic suppliers: lead-time variance of 1–3 days, so safety stock can be small.
• Ocean freight from Asia: 30–45 day transit with 5–10 day variance, plus customs delays.
• Air freight: fast but expensive — model the freight cost difference against the safety stock it removes.
• A supplier with a 5-day shorter lead time frees up working capital worth roughly 5 days of sales.

From reorder point to actual order quantity

The reorder point tells you when to order. The order quantity is a separate decision constrained by MOQ, pack size and the cash you have available.

The workbook calculates a target order quantity that covers your desired days of cover, subtracts the current inventory position, adds cycle stock for the review period, then rounds up to the nearest pack size and applies the MOQ floor. This is why the order quantity often looks larger than you expect — it is covering the gap until the next reorder point arrives.

• Inventory position = on-hand + on order − committed. Use the position, not the on-hand figure.
• Cycle stock covers demand during the review period (how often you place orders).
• Pack size rounding is not waste — it is often cheaper than a partial case.
• If MOQ forces you far above the target, either raise your service level or accept the overstock as a cost of the supplier relationship.

Inventory efficiency metrics worth tracking

Effective inventory management is a balance between stock-outs (lost revenue and lost ranking) and overstock (cash tied up and storage fees). These four metrics keep the balance visible.

• Inventory turns: annual units sold ÷ average inventory. Six or more is healthy for most consumer goods; below four means capital is sitting still.
• Days inventory outstanding: 365 ÷ turns. This is the number that shows up on your cash conversion cycle.
• Average inventory: safety stock + half of cycle stock. The reason cycle stock only counts at half is that it depletes linearly from full to zero.
• Holding cost: typically 20–25% of inventory value per year when you include storage, insurance, shrinkage and the opportunity cost of capital.

The purchase order tab: closing the loop

The third tab turns the reorder maths into a document you can send to a supplier: PO number, order date, SKUs, quantities, unit costs, freight and duty lines, total landed cost and per-unit landed cost.

It also generates a four-cycle ordering schedule so you can see the cash requirement of the next 60–90 days before it hits. Most inventory cash problems are not forecasting failures — they are sequencing failures: three purchase orders landing in the same fortnight while a fourth is being paid.

• PO quantities pre-fill from the reorder point tab, so the document matches the maths.
• Freight and duty are entered as percentages of goods value and rolled into landed cost per unit.
• The four-cycle schedule shows cash out and days of cover after each arrival.
• A 25% working-capital buffer recommendation is included, because arrivals slip.