Excel guide · 5 min read
How to calculate safety stock and reorder point in Excel
=ROUNDUP(1.645*STDEV.S(B2:B11)*SQRT(3),0) for 95% service and a 3-week lead time, then =ROUNDUP(AVERAGE(B2:B11)*3+safety stock,0). Order when on hand + on order falls to the reorder point.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Week of | Filter cartridge | Shower head | Hose kit | Service level | Factor | ||
| 2 | Aug 24, 2026 | 42 | 12 | 6 | 0.9 | 1.28 | ||
| 3 | Aug 31, 2026 | 38 | 9 | 0 | 0.95 | 1.645 | ||
| 4 | Sep 7, 2026 | 51 | 15 | 3 | 0.975 | 1.96 | ||
| 5 | Sep 14, 2026 | 45 | 11 | 11 | 0.98 | 2.05 | ||
| 6 | Sep 21, 2026 | 40 | 8 | 2 | 0.99 | 2.33 | ||
| 7 | Sep 28, 2026 | 47 | 14 | 0 | ||||
| 8 | Oct 5, 2026 | 55 | 10 | 8 | Target service level | 0.95 | ||
| 9 | Oct 12, 2026 | 39 | 13 | 1 | ||||
| 10 | Oct 19, 2026 | 44 | 9 | 4 | ||||
| 11 | Oct 26, 2026 | 49 | 12 | 5 | ||||
| 12 | ||||||||
| 13 | SKU | Avg per week | Std dev | Lead time (wks) | Safety stock | Reorder point | On hand + on order | Action |
| 14 | Filter cartridge | 45 | 5.5377492419 | 3 | 16 | 151 | 210 | OK |
| 15 | Shower head | 11.3 | 2.3118054513 | 2 | 6 | 29 | 25 | Reorder |
| 16 | Hose kit | 4 | 3.5901098714 | 4 | 12 | 28 | 30 | OK |
Download this sheet as an Excel practice file (.xlsx)
The data: full weeks of demand per SKU
A reorder point answers "at what stock level do I place the next order so it arrives before I run out?" During the lead time you'll sell, on average, average demand x lead time. Safety stock is the extra that covers weeks when demand runs above average before the delivery lands.
The sample has ten full weeks of units sold for three plumbing SKUs in B:D, one column per SKU. If your sales come as a transaction log, build this grid first with SUMIFS on the SKU and the week start date. Use full weeks only, and use units sold or ordered by customers, not units shipped: if you were out of stock for a week, the shipped number is 0 and understates demand.
Below the grid, each SKU gets a summary row: average weekly demand (AVERAGE), its standard deviation (STDEV.S), the supplier's lead time in weeks (typed), then safety stock, reorder point and what you have available.
Average demand and its variability
The filter cartridge sells 45 a week on average. STDEV.S measures how much weeks swing around that average: about 5.5 units. Use STDEV.S (sample) rather than STDEV.P, because ten weeks are a sample of all the weeks you'll ever sell. If you order from a forecast rather than past sales, also check how far that forecast misses and which way it leans: see forecast accuracy and bias.
Ten weeks is a minimum. More history gives a steadier standard deviation, but go too far back and you mix in a season that no longer applies. Many teams use the last 13 to 26 weeks and recheck every quarter.
Result: 5.5377492419 shows as 5.54 with Number format
5.54: the filter cartridge's weekly sales typically sit within about 5 or 6 units of its 45 average.
The service factor from a lookup table
The service level is the chance of not running out during one replenishment cycle. Higher service needs more safety stock, and the cost climbs steeply at the top. The factor for each level comes from the standard normal distribution: about 1.28 for 90%, 1.645 for 95%, 1.96 for 97.5%, 2.05 for 98% and 2.33 for 99%. The table in F1:G6 holds these rounded values, and G8 holds the level you've chosen.
XLOOKUP pulls the factor for G8 so you can change the target in one place. In Excel 2010 and later, =NORM.S.INV(G8) calculates the factor for any level directly, if you'd rather not keep a table.
The service level is a business choice, not a formula. A common pattern is higher targets for fast, high-margin or critical items and lower ones for slow, cheap items. Changing G8 from 95% to 99% raises the filter's safety stock from 16 to 23 units.
Safety stock
Multiply the factor by the standard deviation and by the square root of the lead time. The square root is there because good and bad weeks partly cancel out: over 3 weeks, variability grows by SQRT(3), about 1.7 times, not 3 times. ROUNDUP to whole units so you never round below the target.
For the filter, 1.645 x 5.54 x SQRT(3) = 15.8, rounded up to 16. Multiplying by 3 instead of SQRT(3) would give 28: almost twice the stock for the same service level.
Result: 16
16 units of safety stock for the filter cartridge at 95% service with a 3-week lead time.
My lead time is in days but my demand is weekly. What do I do?
Put both in the same unit. Either divide the lead time in days by 7, or convert the weekly standard deviation to daily by dividing it by SQRT(7) and use the lead time in days. Both give the same answer.
Result: 16
16 again: a daily standard deviation of 2.09 over a 21-day lead time.
Reorder point and the reorder flag
Reorder point = average demand during the lead time + safety stock. The filter sells 45 a week for 3 weeks (135) plus 16 safety stock: reorder at 151.
Compare the reorder point with on hand plus on order, not on hand alone. Stock already on its way will arrive within the lead time, so ignoring it makes you order twice. The shower head has 25 available against a reorder point of 29, so it's flagged.
Result: Reorder
Reorder: the shower head has 25 on hand and on order, below its reorder point of 29 (11.3 a week x 2 weeks + 6 safety stock).
When this formula misleads
The formula assumes weekly demand is roughly bell-shaped, weeks are independent, and the lead time is fixed. The hose kit breaks the first assumption: it averaged 4 a week but swung from 0 to 11, with two zero weeks. Its standard deviation (3.59) is almost as big as its average, so the normal-curve factor can't promise the service level it suggests. For lumpy, slow sellers, set a simple minimum by judgment or look at the largest demand you've seen over a lead time instead.
If your supplier's lead time also varies, this version understates the risk; the extended formula adds a lead-time variability term. And recheck the inputs when a season changes: a summer average won't protect you in December. For how long current stock lasts at today's pace, see weeks of supply, and for turnover and days of inventory, inventory formulas in Excel.
Common mistakes
Mixing weeks and days
Demand, standard deviation and lead time must use the same time unit. Convert the lead time, or scale the standard deviation by SQRT(7).
Multiplying by the lead time instead of its square root
Safety stock uses SQRT(lead time). Average demand during the lead time uses the lead time itself.
Comparing the reorder point with on-hand stock only
Use on hand + on order, or you'll reorder items that are already on their way.
Using shipped units as demand
Stock-outs make shipped units look low and steady. Use orders or sales including back-orders where you can.
FAQ
What is the formula for safety stock in Excel?
=service factor*STDEV.S(demand range)*SQRT(lead time), with demand and lead time in the same unit. Wrap it in ROUNDUP(...,0) for whole units.
How do I calculate the reorder point in Excel?
=AVERAGE(demand range)*lead time + safety stock. Reorder when on hand plus on order is at or below that number.
What service factor should I use for 95%?
About 1.645, from the standard normal distribution. In Excel 2010 or later, NORM.S.INV with your service level as a decimal calculates the factor.
Is safety stock the same as minimum stock?
Not quite. Safety stock is the buffer you expect to dip into only when demand runs high. The reorder point (lead-time demand plus safety stock) is the level that triggers an order.
How do I check whether my safety stock is enough?
Track how often you actually run out. From a daily stock snapshot, the share of days at zero stock per SKU is the stockout rate. If an item keeps running out, raise its safety stock or service level; if it never does, you may be holding more than you need.
Functions used
AVERAGEMean of numbersSTDEV.SStandard deviationSQRTSquare rootROUNDUPRounds away from zeroXLOOKUPModern lookup in any directionIFPicks a result from a test
Related guides
- How to calculate stockout rate in Excel
- How to calculate days of inventory on hand in Excel
- How to calculate economic order quantity (EOQ) in Excel
- ABC inventory analysis in Excel
- Excel formulas for inventory: stock balance, reorder and value
- How to calculate standard error in Excel
Updated