Reorder point and safety stock, with an example

The supplier takes ten days to deliver, but the order goes out when someone notices the shelf is almost empty. Sales continue during those ten days, and for the last week the product is missing. With another item the opposite happens: people order “just in case” and the warehouse piles up months of goods that tie up cash. Both problems share a cause: there is no written rule that says when to order and how much buffer to keep.

That rule has a name: reorder point and safety stock. You calculate it with three pieces of data you already have in your sales and purchases, and it works the same in a spreadsheet as in a system.

The three inputs you need

1. Average daily demand (d). Units sold or consumed in a period, divided by the days in the period. Use at least 8 to 12 weeks and exclude stock-out days: if there was no product, a zero sale is not real demand.
2. Lead time (L). Days from the moment you decide to order until the goods are available to sell, not just until the truck arrives. It includes approval, sending the order, transport, receiving and inspection.
3. Variability. How much demand and lead time move around their averages. You measure it with the standard deviation of daily demand (σd) and of lead time (σL). In Excel, =STDEV() over the daily series.

The reorder point formula

The reorder point (ROP) is the stock level that triggers an order when you reach it:

ROP = d × L + SS

The first part, d × L, is what you expect to sell while the order is on its way. The second, SS, is safety stock: what protects you if more than usual sells in that window or the supplier is late.

One important detail: compare the ROP against your inventory position, not just physical stock.

Inventory position = available stock + orders in transit − committed quantities

If you only look at physical stock, you will reorder something that is already on its way.

Safety stock: two ways to calculate it

Simple version (no statistics)

SS = (maximum daily demand × maximum lead time) − (average demand × average lead time)

It is easy to explain and needs no standard deviations, but it tends to overestimate, because it assumes the worst sales day and the worst delay happen together.

Standard deviation version

If only demand varies:

SS = Z × σd × √L

If both demand and lead time vary:

SS = Z × √(L × σd² + d² × σL²)

Z is the service level factor, that is, the probability of not running out during one replenishment cycle:

Service level	Z
90%	1.28
95%	1.65
98%	2.05
99%	2.33

Going from 95% to 99% looks small, but it increases the buffer by more than 40%. Keep high levels for critical or high-margin products.

Minimum and maximum

Many companies work with a minimum and maximum instead of talking about ROP. It is the same thing with a different label:

• Minimum = reorder point. When inventory position drops below it, you order.
• Maximum = minimum + usual order quantity. It is the ceiling you want to reach.
• Quantity to order = maximum − inventory position at review time.

The usual order quantity can come from the supplier’s minimum lot, from one month of consumption or from an economic order quantity (EOQ) calculation, depending on what matters most in your case.

Worked example

Suppose a product with these hypothetical figures, taken from the last 12 weeks:

Input	Value
Average daily demand (d)	20 units
Standard deviation of demand (σd)	6 units
Maximum observed demand	30 units/day
Average lead time (L)	10 days
Standard deviation of lead time (σL)	2 days
Maximum observed lead time	14 days
Target service level	95% (Z = 1.65)
Usual order quantity	600 units

Step 1. Demand during lead time: 20 × 10 = 200 units.

Step 2. Safety stock, simple version: (30 × 14) − (20 × 10) = 420 − 200 = 220 units.

Step 3. Safety stock, demand variability only: 1.65 × 6 × √10 = 1.65 × 6 × 3.16 ≈ 31 units.

Step 4. Safety stock, variable demand and lead time:

• L × σd² = 10 × 36 = 360
• d² × σL² = 400 × 4 = 1,600
• √(360 + 1,600) = √1,960 ≈ 44.3
• SS = 1.65 × 44.3 ≈ 73 units

Step 5. Reorder point: 200 + 73 = 273 units. That is your minimum.

Step 6. Maximum: 273 + 600 = 873 units.

Step 7. Order: at the weekly review you have 180 on the shelf, 90 in transit and 10 committed. Position = 180 + 90 − 10 = 260, below 273. You order 873 − 260 = 613 units (or 600 if the supplier sells by the case).

Notice the gap between steps 3 and 4: in this case, supplier variability weighs more than sales variability. Ignoring it would leave a 31-unit buffer when you need 73. And the simple version, at 220, triples what you need: safe, but expensive.

When to review the parameters

• Every month or quarter, as routine, for your best sellers (the A items in an ABC classification).
• When the supplier, route or transport mode changes: the lead time is now different.
• Before and after seasons (year end, back to school, campaigns): the full-year average is useless for December.
• When you launch or discontinue a product, or when a substitute takes sales from it.
• When stock-outs or overstock keep repeating for a product: it is the sign the parameter is out of date.

Common mistakes

• Calculating demand with stock-out days. They pull the average down and the spreadsheet “learns” to order less, which causes more stock-outs.
• Measuring lead time to arrival instead of to availability. If receiving and inspection take two days, the calculation is two days short.
• Using the same service level for everything. 99% on slow movers fills the warehouse with goods that do not move.
• Comparing against physical stock instead of inventory position, which duplicates orders.
• Setting the minimum once and forgetting it. Parameters from a year ago describe a different business.
• Ignoring expiry. With perishables, a big buffer becomes shrinkage. Pair it with a sound picking rule (FEFO vs FIFO).

Checklist to start in a spreadsheet

• Daily sales per product for the last 12 weeks, without stock-out days.
• Order and availability dates for each supplier’s latest orders.
• Average and standard deviation of both series.
• Service level defined by category (for example A, B and C).
• SS, minimum and maximum columns using the formulas above.
• A fixed review date to recalculate.

If you want to automate it

In a spreadsheet this works well for dozens of products; with hundreds, keeping parameters up to date becomes the bottleneck. An ERP such as MKA can store each product’s minimums and maximums, import them from your spreadsheet and show them next to the kardex and turnover (see inventory).