Reorder on average sales and you run out every other cycle — working out safety stock in Excel

A building-supplies shop sells an average of 20 boxes of one adhesive a day. The supplier delivers about a week after the order. The reorder arithmetic feels obvious: 20 boxes a day times 7 days is 140 boxes — when stock falls to 140, call in an order.

That sounds rigorous. And it runs out of stock in about half of all replenishment cycles.

Not because the average is wrong. Because 140 boxes covers an average week — and half of all weeks sell more than average. This piece shows how to size the buffer for the other half using six Excel cells, and a factor that usually matters more than sales but that few people include.

Why half

Daily sales are not fixed. Some days 12 boxes, some days 31, averaging 20. Over the seven days waiting for stock, total sales also vary around 140 — one week 120, another 165.

Reorder at exactly 140 boxes and the shop only has enough in weeks that sell at or below average. For a quantity that varies evenly both ways, that is about half the weeks.

To check this rather than just argue it, we simulated 200,000 replenishment cycles: daily sales averaging 20 boxes with a standard deviation of 8 — a normal day falls roughly between 12 and 28 — and a lead time of exactly 7 days. Reordering at 140 boxes ran out before the delivery arrived in 50.2% of cycles.

The average is a number for estimating, not for ordering. Ordering on the average means accepting a stock-out in half of all cycles — a decision almost no shop owner makes on purpose.

Safety stock: the buffer for heavy weeks

The answer is to hold an extra quantity called safety stock — a buffer for weeks that sell above average. The reorder point then becomes:

• Reorder point = average daily sales × lead-time days + safety stock

How much safety stock depends on how rarely you want to run out. That target is called the service level: the share of replenishment cycles without a stock-out. A 95% service level means accepting roughly one stock-out in every 20 deliveries.

With a fixed lead time, the formula is:

• Safety stock = z × standard deviation of daily sales × √(lead-time days)

Here z is a factor derived from the service level — Excel computes it with NORM.S.INV. For the adhesive shop, here is each level along with the simulated result over 200,000 cycles:

• 90% — z = 1.28 — safety stock 27 boxes, reorder at 167. Simulated: stock-out in 10.0% of cycles.
• 95% — z = 1.64 — safety stock 35 boxes, reorder at 175. Simulated: 5.1%.
• 98% — z = 2.05 — safety stock 43 boxes, reorder at 183. Simulated: 2.0%.
• 99% — z = 2.33 — safety stock 49 boxes, reorder at 189. Simulated: 1.0%.

The simulated results match the targets on all four lines — a sign the formula is doing its job.

One thing worth noticing in this series: going from 90% to 99% raises safety stock from 27 to 49 boxes, about 80%, to cut stock-outs from one in ten to one in a hundred. Each final percentage point costs more than the one before. That is why 99% should not be applied to every item — a later section covers how to choose.

The factor usually left out: suppliers do not deliver on the day

The formula above assumes stock arrives in exactly 7 days every time. In practice the supplier sometimes delivers after 5 days, sometimes 9, and when there is traffic or a shortage, 11.

We reran the simulation with lead time varying around 7 days with a standard deviation of 2 days — fairly typical for domestic suppliers. Keeping the 35-box safety stock calculated for 95%:

• Actual stock-out rate: 21.8% — more than four times the 5% target.

So the spreadsheet says 95%, but the shop runs out nearly one delivery in five. Nothing tells the owner the spreadsheet is wrong, because the formula is correct — it just does not account for this.

The full formula adds lead-time variability:

• Safety stock = z × √( avg lead time × (sales SD)² + (avg sales)² × (lead-time SD)² )

For the adhesive at 95%, the result is 74 boxes — more than double the earlier 35. Simulating again with 74: stock-out 5.7%, close to target.

The two terms under the square root show why. The part due to varying sales is 7 × 8² = 448. The part due to varying delivery is 20² × 2² = 1,600 — nearly four times larger. For this shop, a supplier delivering on uneven days is the main source of risk, not customers buying erratically.

And that opens a lever that adding stock cannot match. At the same 95% service level:

• Delivery varies by 2 days: needs 74 boxes of buffer.
• Delivery varies by 1 day: needs 48 boxes.
• Delivery exactly on time: needs 35 boxes.

Working with a supplier to make delivery more consistent — or choosing a supplier who is more consistent even if slightly slower — can cut a third of the buffer without changing the service level. That is working capital released without selling a single extra box.

Doing it in Excel: six cells

The whole calculation fits on one sheet using built-in functions. Depending on your system's language settings, function arguments may be separated by commas or semicolons.

Prepare the data. From your sales software, export units sold per day for the item over the last 90 days — including zero-sales days — say into column B. From your purchasing records, note the order and receipt dates of the last 10 to 20 deliveries and take the difference to get each lead time, say into column E.

The six cells:

• Average daily sales: =AVERAGE(B2:B91)
• Sales standard deviation: =STDEV.S(B2:B91)
• Average lead time: =AVERAGE(E2:E21)
• Lead-time standard deviation: =STDEV.S(E2:E21)
• z for 95%: =NORM.S.INV(0.95)
• Safety stock: =z*SQRT(L*sd^2 + d^2*sL^2) — replacing z, L, sd, d and sL with the addresses of the five cells above.

The reorder point is average daily sales times average lead time, plus safety stock. When on-hand stock in your software reaches it, reorder.

A data trap: stock-out days record zero sales

One data error makes the formula underestimate itself, and it hits exactly the shops already running out most.

On days the shelf is empty, the software records zero sales. Not because nobody wanted to buy — because there was nothing to sell. Include those days in the sales column and the average comes out below real demand. If 10% of days are stock-out days, for instance, the recorded average falls to about 18 instead of 20. Calculate stock from 18, order less, run out more, and next period's data is lower still — a loop that drives itself down.

The simple fix: flag stock-out days and remove them from the sales column before calculating the average and standard deviation. If your software does not record daily stock levels, at least note by hand the days customers asked for something you did not have — the most valuable data almost no system captures on its own.

This is also a concrete case of a principle from our piece on digitising data into a single source you can trust: a number recorded accurately can still measure the wrong thing.

Choosing service levels by item group

Because each final percentage point gets more expensive, service levels should differ by item group. A simple split:

• Items carrying most of the revenue, or where a stock-out sends the customer elsewhere for good: 97% to 98%.
• Items that sell steadily but where customers will wait, or a substitute sits on the shelf: 90% to 95%.
• Slow-moving, high-value items: consider ordering to demand instead of holding stock.

To see how much capital sits in the buffer, multiply safety stock by unit cost. For the adhesive at an assumed cost of 200,000 dong per box: 35 boxes is 7 million dong, 74 boxes is 14.8 million — for the same 95% service level, the difference entirely due to uneven supplier delivery.

When the formula does not apply

The formula rests on a few assumptions, and it is worth knowing when they fail:

• Seasonal demand. The month before Tết, back-to-school, rainy season for waterproofing products — the last 90 days of sales do not represent the next 30. For these periods, calculate separately from the same period last year rather than the recent average.
• Very slow movers. An item selling one or two a week has mostly zeros in its daily sales, and a formula built on the normal distribution becomes unreliable. For these, work weekly or order to demand.
• New items with no history. Start from a similar item's figures, then recalculate after a month of real data.
• Promotions. Promotion days spike sales then drop. Remove them from the calculation data and plan stock for the promotion separately.

And one point on interpretation: the 95% here is the share of cycles without a stock-out, not the share of orders filled. A stock-out cycle usually means running short for a few hours or the last day or two, so the share of customers actually served is higher than this figure.

Closing

Ordering on average sales is the most intuitive approach, and so the most common. It is also a way of accepting stock-outs half the time without anyone having decided to.

Six Excel cells are enough to turn that intuition into a number with an explicit service level. Two things make that number trustworthy: removing stock-out days from the data, and measuring the supplier's real lead time instead of using the one they promise. The second often shows the biggest risk is not the customer but the late delivery.

And once you have the number, it is only worth something if someone looks at it at the right moment — a point we made in our piece on the dashboard nobody opens. An automatic alert when stock hits the reorder point is usually more useful than an attractive report.

If you would like someone to build the safety-stock calculation on your own sales data, or connect it to automatic reorder alerts, get in touch or see KonexForge's Data Analytics capability.