Cash Flow Inventory
Menu
Blog

Reorder Point Spreadsheet: ROP and Safety Stock for Every SKU at Once

A free spreadsheet that calculates safety stock and a reorder point for every SKU at once. It includes the formulas explained, an eight-SKU worked example, and the edge cases that generic answers skip.

Spreadsheet with one row per SKU showing daily sales, lead time, service level, safety stock and reorder point columns
On this page
  1. Key takeaways
  2. The reorder point and safety stock formulas, explained
  3. What the service level actually means
  4. What is in the reorder point Excel template
  5. Worked example: reorder points for eight SKUs
  6. What the example tells you
  7. How to fill in the spreadsheet: step by step
  8. Common reorder point mistakes the formula will not catch
  9. Edge cases: slow movers, seasonality, new SKUs and periodic ordering
  10. You only place orders once a week or once a month
  11. Slow and lumpy sellers
  12. Seasonal products
  13. New SKUs with no history
  14. Minimum order quantities and pack sizes
  15. Several locations
  16. Monthly reorder point review checklist
  17. How to do this in Cash Flow Inventory
  18. Frequently asked questions
  19. How do I calculate a reorder point in Excel?
  20. What service level should I use for safety stock?
  21. Why does my safety stock come out higher than the simple formula?
  22. How often should I recalculate reorder points?
  23. Does this template work in Google Sheets?
  24. What is the difference between a reorder point and a minimum stock level?

A reorder point Excel template works out, for each SKU on its own row, the stock level at which you should place the next order. The formula is reorder point = average daily sales × lead time + safety stock. Safety stock is calculated from how much your daily sales swing, how much your supplier’s lead time swings, and the service level you choose. Excel’s NORM.S.INV function turns that service level into a Z score.

This page gives you a spreadsheet that does this for your whole catalogue at once. Each row takes five inputs and returns a safety stock, a reorder point, a “Reorder” or “OK” flag and the cash tied up in safety stock. Every formula is explained below so you can check it or rebuild it yourself.

You also get a worked example with eight products, a step-by-step routine for filling in the sheet, and the mistakes that quietly break reorder points. The last section shows where the same logic lives in Cash Flow Inventory.

Free download: Reorder point and safety stock template

An Excel file (.xlsx, 12 KB) with the formulas already in place and the example from this guide filled in. It opens in Excel, Google Sheets (File › Import), Numbers and LibreOffice. No sign-up needed.

Download the spreadsheet

Email me this template + the next 4 guides on barcodes and scanning

Optional: the download above is free and needs no e-mail.

Optional. Before any news, we e-mail you once to confirm.

We use your e-mail only for what you ask here. Privacy notice

Key takeaways

  • Reorder point = average daily sales × lead time + safety stock. Work it out per SKU, never for the catalogue as a whole.
  • Safety stock = Z × √(lead time × sales std dev² + daily sales² × lead time std dev²). If you ignore supplier delays, you can underestimate it by half or more.
  • Set the service level by SKU. High-value, slow items at 98–99% can tie up more cash than everything else combined.
  • Compare the reorder point with stock on hand plus stock on order, not with on-hand stock alone.
  • Recalculate monthly, and before seasonal peaks, from clean sales history that excludes days when you were out of stock.

The reorder point and safety stock formulas, explained

A reorder point has two parts. The first part covers what you expect to sell while you wait for the delivery. The second part, safety stock, covers what you might sell on a bad day or if the supplier is late. If you want the background first, read our guides to the reorder point definition and formula and to safety stock and its formulas.

Lead-time demand = average daily sales × lead time (days)

Safety stock = Z × √(lead time × std dev of daily sales² + average daily sales² × std dev of lead time²)

Reorder point = lead-time demand + safety stock

Here is what each part means:

  • Average daily sales (d): units sold per day over a recent, representative window, usually 60–90 days.
  • Std dev of daily sales (σd): how much daily sales vary around that average. In Excel, use STDEV.S on a column of daily totals. See the Microsoft reference for STDEV.S.
  • Lead time (L): the average number of days from placing the order to having stock you can sell, not just to dispatch. Our article on what lead time includes covers the hidden days, such as customs and goods-in checks.
  • Std dev of lead time (σL): how much that delay varies. Use 0 for a supplier who has never been late.
  • Z: the number of standard deviations of cover that matches your chosen service level. In Excel, =NORM.S.INV(0.95) returns about 1.645. See the Microsoft reference for NORM.S.INV.

The square root combines two independent sources of risk: demand noise during the lead time, and the lead time itself running long. The method assumes these behave roughly like a normal distribution. The NIST Engineering Statistics Handbook explains that assumption well. It holds best for steady sellers, and later sections cover what to do when it does not.

What the service level actually means

A 95% service level here is a cycle service level. It means that in 95 out of 100 replenishment cycles, you do not run out before the delivery lands. It does not mean 95% of customer orders are filled. Choosing it per product is a commercial decision, and our guide to setting a desired service level walks through the trade-offs.

What is in the reorder point Excel template

The file has three sheets.

ROP by SKU sheet

The main sheet, ROP by SKU, has one row per product with these columns:

Columns of the ROP by SKU sheet: header, what it holds, the example in row 2 and the formula
Col Header What it holds Example row 2 Formula row 2
A, B SKU and Product Typed. A KW-PAN-24
B Non-stick frying pan 24 cm
—
C–G Avg daily sales, Std dev daily sales, Lead time (days), Lead time std dev (days), Service level Your inputs. Enter the service level as a percentage, such as 95%. C 6
D 2.50
E 14
F 3
G 95.0%
—
H Z score Calculated — =NORM.S.INV(G2)
I Safety stock Rounded up because you cannot hold part of a frying pan. — =ROUNDUP(H2*SQRT(E2*D2^2+C2^2*F2^2),0)
J Lead-time demand Calculated 84 =C2*E2
K Reorder point Calculated — =ROUNDUP(J2+I2,0)
L, M On hand and On order Typed from your stock records. L 95
M 0
—
N Stock position Calculated 95 =L2+M2
O Action Calculated — =IF(N2<=K2,"Reorder","OK")
P Unit cost You type it 9.50 —
Q Safety stock value Calculated — =I2*P2
R Safety cover (days) Calculated — =IFERROR(I2/C2,0)

Filling down: each formula is shown for the first row of data. Copy it down the column and the spreadsheet moves the row numbers for you (B2 becomes B3, B4 …); references with $ signs stay fixed.

The Z lookup sheet lists common service levels next to their Z scores, so you can see how quickly cover grows as you approach 100%. The Summary sheet counts SKUs to reorder and totals the units and money held as safety stock. If you also need a general stock list with in and out movements, pair this file with our inventory spreadsheet template with reorder alerts.

Download the spreadsheet.xlsx, 12 KB · formulas and the worked example filled in · Excel, Google Sheets, Numbers, LibreOffice

Worked example: reorder points for eight SKUs

Example: a shop selling kitchenware online and from one high-street store. The business is hypothetical, and the figures are illustrative. The owner pulled 90 days of daily sales per SKU and the last eight purchase orders per supplier. Cast iron and knives come from a distant importer with long, uneven lead times. Tea towels and mugs come from a reliable local wholesaler.

SKU Product Avg daily sales Std dev daily sales Lead time (days) Lead time std dev Service level Z
KW-PAN-24 Non-stick frying pan 24 cm 6 2.5 14 3 95% 1.645
KW-KNF-8 Chef’s knife 8 in 3 1.8 21 4 95% 1.645
KW-MUG-4 Mug set of 4 4 2 7 0 90% 1.282
KW-TOW-2 Tea towel 2-pack 10 4 5 1 95% 1.645
KW-CAST-28 Cast iron casserole 28 cm 0.8 0.9 30 6 98% 2.054
KW-SPAT-S Silicone spatula 12 5 10 2 99% 2.326
KW-SCALE-D Digital kitchen scale 2 1.2 14 0 95% 1.645
KW-JAR-500 Glass storage jar 500 ml 8 3 10 2 90% 1.282

Take the frying pan as the worked line. Demand noise over the lead time is 14 × 2.5² = 87.5. Lead-time noise is 6² × 3² = 324. Their sum is 411.5, and its square root is 20.29. Multiplied by 1.645, that gives 33.37, which rounds up to a safety stock of 34. Lead-time demand is 6 × 14 = 84, so the reorder point is 84 + 34 = 118.

SKU Safety stock Lead-time demand Reorder point On hand On order Stock position Action Unit cost Safety stock value
KW-PAN-24 34 84 118 95 0 95 Reorder 9.50 323.00
KW-KNF-8 24 63 87 60 40 100 OK 14.00 336.00
KW-MUG-4 7 28 35 50 0 50 OK 6.20 43.40
KW-TOW-2 23 50 73 40 0 40 Reorder 2.40 55.20
KW-CAST-28 15 24 39 20 12 32 Reorder 32.00 480.00
KW-SPAT-S 67 120 187 210 0 210 OK 1.80 120.60
KW-SCALE-D 8 28 36 30 0 30 Reorder 7.50 60.00
KW-JAR-500 24 80 104 110 0 110 OK 3.10 74.40
Total 202 4 to reorder 1,492.60

Z is shown to three decimals here. The sheet uses the full value from NORM.S.INV, and after rounding up the results are the same. Amounts are in your own currency.

What the example tells you

  • Supplier variability dominates. If you ignore the frying pan’s three-day lead time wobble, you get 1.645 × 2.5 × √14 = 15.39, which rounds up to 16 units. That is less than half of the 34 the full formula gives.
  • The knife is not reordered, even though 60 is below 87. The 40 already on order bring its position to 100. If you check on-hand stock alone, you will double-order.
  • The casserole costs the most to protect. At 98% and 32.00 a unit, its 15 units of safety stock hold 480.00. That is nearly a third of the 1,492.60 total, for a product that sells less than one a day. The spatula sits at 99% for only 120.60.
  • Safety cover in days is a useful sense check. The pan holds 34 ÷ 6 = 5.7 days of cover, and the casserole holds 15 ÷ 0.8 = 18.75 days. When one SKU shows weeks of cover, ask whether its inputs are right.

How to fill in the spreadsheet: step by step

  1. Export daily sales per SKU for the last 60–90 days, with one column per SKU and one row per day. Put a 0 on days with no sales, because blank days inflate the average.
  2. Remove days when you were out of stock. Those zeros show missing stock, not missing demand.
  3. Calculate the average and standard deviation of each column with AVERAGE and STDEV.S, then paste the values into columns C and D.
  4. Measure lead times from real purchase orders. For each supplier, take the last 5–10 orders and count the days from the order date to goods available for sale. Enter the average in E and the STDEV.S of those days in F.
  5. Set a service level per SKU. A simple rule: 97–99% for traffic drivers and cheap fast movers, 95% for core range, 90% for slow or expensive items and anything easy to substitute.
  6. Enter on-hand and on-order quantities from today’s records, after any pending adjustments are posted.
  7. Sort by Action, then by Safety stock value. Raise orders for the “Reorder” lines, and review any SKU where safety stock value looks out of proportion.
  8. Recalculate monthly, and again about a lead time before any seasonal peak.

Expert tip: Keep your units consistent. If lead time is in days, sales and their standard deviation must be per day too. Mixing weekly sales with daily lead times is the most common reason a reorder point comes out seven times too high or too low.

Common reorder point mistakes the formula will not catch

  • Converting a monthly standard deviation to daily by dividing by 30. Variability scales with the square root of time, so a monthly σ divided by 30 understates daily σ badly. Calculate σ from daily figures directly.
  • Using the worst-case lead time and adding lead-time variability as well. That counts the same risk twice. Use the average lead time in E and let F carry the spread.
  • Entering 100% as the service level. NORM.S.INV(1) returns #NUM! because full certainty needs infinite stock. Cap it at 99.5%.
  • Comparing the reorder point with on-hand stock only. Always use on hand + on order − stock already promised to open orders.
  • Forgetting your review cycle. The formula assumes you check stock continuously. If you only order on Mondays, see the edge cases below.
  • Applying one service level to every SKU. As the casserole shows, that pushes cash into the wrong products. Pairing service levels with XYZ analysis of demand variability gives you a defensible rule.
  • Letting the sheet drift. A reorder point set in spring and never revisited is just a guess with decimals.

Edge cases: slow movers, seasonality, new SKUs and periodic ordering

You only place orders once a week or once a month

If you review stock every R days, a shortage can start just after a review and go unnoticed until the next one. Protect the full lead time plus the review period. Replace E with (E + R) in the safety stock and lead-time demand formulas:

Safety stock = Z × √((lead time + review days) × std dev of daily sales² + average daily sales² × std dev of lead time²)

Slow and lumpy sellers

For items selling less than about one a day, such as the casserole, daily sales are mostly zeros with the odd sale. The normal-distribution assumption is weak there. A plain minimum and maximum, or a two-bin rule, is often easier to run, and you should sense-check the sheet’s answer against your last few months’ sales.

Seasonal products

A 90-day window in September knows nothing about December. For seasonal lines, base the average and σ on the same weeks last year, adjusted for growth. Raise the reorder point at least one lead time before the peak starts, then lower it again afterwards so you do not carry the peak level into a quiet month.

New SKUs with no history

Borrow d and σd from the closest existing product, such as a 28 cm pan for a new 24 cm pan. Use a conservative service level, then replace the borrowed figures after 30–60 days of real sales.

Minimum order quantities and pack sizes

The reorder point tells you when to order, not how much. If a supplier only sells in cartons of 24, your order quantity rounds up to cartons, but the reorder point itself stays as calculated. For the quantity, our free reorder point calculator and the EOQ approach help you check one SKU at a time.

Several locations

Calculate one row per SKU per location, using each location’s own sales and the lead time to that location. Pooling sales across branches understates the safety stock each branch actually needs.

Monthly reorder point review checklist

  • Sales export covers the last 60–90 days, daily, with stock-out days removed.
  • Lead times updated from the latest purchase orders, measured to goods available for sale.
  • Units match: per-day sales with lead times in days.
  • No service level at 100%; high levels reserved for items that justify them.
  • On-order quantities current; cancelled purchase orders removed.
  • Seasonal SKUs switched to last year’s peak-period figures where relevant.
  • Top five SKUs by safety stock value reviewed and justified.
  • Every “Reorder” line either ordered or noted with a reason.

How to do this in Cash Flow Inventory

The spreadsheet is a good way to understand and sense-check your numbers. Once your catalogue grows, keeping the inputs current by hand becomes the hard part. In Cash Flow Inventory, stock on a product is never typed in. Every receipt, sale, transfer, adjustment and count is a movement in one log, so on-hand figures reflect what actually happened.

  1. Set up the product properly. In Inventory › Products, choose New product. Add the Supplier and Cost, and choose the locations under Stocked at. For things you produce, switch on Made in-house (no supplier) instead. The products and inventory guide covers every field.
  2. Use Inventory › Planning for the reorder figures. Planning holds each item’s forecast, reorder point and safety stock, and a nightly planning run saves a plan for each item.
  3. Work from the Restock tab. On Inventory › Products, the Restock tab shows what is low or out, with what to reorder. From a row’s menu, choose New purchase order to raise it. On a single product page, See what to reorder opens the same restock view.
  4. Refill to a maximum where that suits you. Inventory › Replenishment shows items below their minimum. For items you refill by making, it can order up to the maximum as work orders.
  5. Plan made-in-house items the same way. Production › Demand plans uses the same reorder engine as purchasing. Choose a Demand window and location, then choose Make it on a row to open a work order for the shortfall. The production demand planning guide explains how demand, on hand and on order are combined.

Many businesses run the spreadsheet alongside the system for a month or two. They compare the two answers SKU by SKU before trusting either one fully.

Free download: Reorder point and safety stock template

An Excel file (.xlsx, 12 KB) with the formulas already in place and the example from this guide filled in. It opens in Excel, Google Sheets (File › Import), Numbers and LibreOffice. No sign-up needed.

Download the spreadsheet

Email me this template + the next 4 guides on barcodes and scanning

Optional: the download above is free and needs no e-mail.

Optional. Before any news, we e-mail you once to confirm.

We use your e-mail only for what you ask here. Privacy notice

Frequently asked questions

How do I calculate a reorder point in Excel?

Multiply average daily sales by lead time in days, then add safety stock: =C2*E2+I2. Calculate safety stock with =ROUNDUP(NORM.S.INV(G2)*SQRT(E2*D2^2+C2^2*F2^2),0), where G2 is your service level. Copy both formulas down so every SKU gets its own result.

What service level should I use for safety stock?

Most small product businesses use 90–95% for the bulk of the range. They go higher only for items whose absence loses the whole basket, or that are cheap to hold. Expensive slow movers rarely justify 98% or more, because each extra point of service costs progressively more stock.

Why does my safety stock come out higher than the simple formula?

The simple formula, Z × σd × √L, assumes your supplier is never late. Once you add lead-time variability, safety stock often doubles, as the frying pan example shows (16 units versus 34). If your supplier really is that consistent, set the lead time std dev to 0 and the two formulas agree.

How often should I recalculate reorder points?

Monthly is a sensible default for most SKUs. Recalculate sooner when a supplier changes, when lead times shift, or about one lead time before a seasonal peak or a planned promotion.

Does this template work in Google Sheets?

Yes. Google Sheets supports NORM.S.INV, SQRT, ROUNDUP, IF and IFERROR with the same syntax, so you can upload the file and the formulas carry over unchanged.

What is the difference between a reorder point and a minimum stock level?

A reorder point is calculated from sales, lead time and variability, so it moves as those change. A minimum stock level is usually a fixed number someone chose. Many teams use the calculated reorder point as their minimum and review it on a schedule.

A spreadsheet is the right place to learn how reorder points behave. Keeping hundreds of rows current every month is where it starts to cost you time and stock-outs. When you want reorder points, safety stock and restock suggestions worked out from real stock movements, see how Cash Flow Inventory’s inventory management software handles it.

Mohammad Ali

Led by Mohammad Ali (15+ years in inventory management software), the Cash Flow Inventory Content Team empowers SMBs with clear financial strategies. We translate complex financial concepts into clear, actionable strategies through a rigorous editorial process. Our goal is to be your trusted resource for navigating SMB finance.

About the team