How to Calculate Reorder Point in Excel (Formula + Free Template Logic)

To calculate a reorder point in Excel, give each SKU one row, work out its average daily demand and lead time, add a safety stock figure and combine them with =C2*D2+F2. A status column then compares the stock position against that trigger and shows which lines need a purchase order today.

The formula is the easy part. The practical problem is keeping the inputs honest: demand has to come from real sales history, safety stock has to reflect real variability, and the stock figures have to be current when someone acts on them. This guide covers the workbook layout, the exact formulas and the controls that keep a spreadsheet trustworthy for as long as it is the right tool.

A reorder point workbook is only as reliable as the sales, lead time and stock figures typed into it. Illustrative photograph, not an OpsMavix client.

Image credit: Bluestonex, Unsplash License. No edits.

Key Takeaways

• The core formula is lead time demand plus safety stock: =C2*D2+F2 for one SKU on row 2.
• Calculate average daily demand from dated sales history with SUMIFS, including days with zero sales.
• STDEV.S and NORM.S.INV turn daily demand variability and a target service level into a safety stock figure.
• Compare the reorder point with stock on hand plus open purchase orders, not stock on hand alone.
• Data validation, locked formula cells and Excel tables reduce errors but do not make the sheet live.
• Move to software when refreshing inputs takes longer than acting on the results.

In this guide:

• 1. Set Out the Formula Before You Open Excel
• 2. Lay Out the Workbook
• 3. Calculate Average Daily Demand From Sales History
• 4. Add Safety Stock With STDEV.S and NORM.S.INV
• 5. Work Through a Hypothetical Three SKU Example
• 6. Add a Status Column and an Order Quantity
• 7. Protect the Workbook With Simple Controls
• 8. Know Where the Spreadsheet Breaks Down
• Frequently Asked Questions
• How OpsMavix Can Help

1. Set Out the Formula Before You Open Excel

The two parts of a reorder point

A reorder point is the stock level at which you place a replenishment order. It has two parts:

• Lead time demand: average daily demand multiplied by lead time in days. This is what you expect to use while the order is on its way.
• Safety stock: a buffer for demand that runs higher than average during the wait.

So the reorder point is average daily demand × lead time in days + safety stock. This guide assumes you already understand why those parts matter and want them built in a spreadsheet. For the method itself, with a step by step derivation, see how to calculate reorder point.

Agree the units first

Most spreadsheet errors start with mixed units rather than wrong formulas. Before building anything, agree three things:

If you sell five days a week, either measure demand per trading day and lead time in trading days, or use calendar days for both. Mixing the two quietly shifts every reorder point.

2. Lay Out the Workbook

Use three sheets

Keeping raw data, assumptions and results apart makes the workbook easier to check.

Columns on the Reorder sheet

Put headers in row 1 and the first SKU in row 2.

Shade input columns one colour and leave formula columns plain, so anyone opening the sheet knows which cells they may change.

Fill the formulas down

Type the formulas in row 2, then drag the fill handle down to the last SKU. Excel adjusts relative references, so row 3 calculates =C3*D3 and so on. If you format the range as an Excel table (section 7), new rows pick up the column formulas without dragging.

3. Calculate Average Daily Demand From Sales History

Total units over a fixed window

If the Sales sheet holds one row per order line, with dates in column A, SKUs in column B and quantities in column C, put the window start date in a cell such as Reorder!M1 and the end date in Reorder!M2. Average daily demand for the SKU in A2 is then:

=SUMIFS(Sales!$C:$C,Sales!$B:$B,$A2,Sales!$A:$A,">="&$M$1,Sales!$A:$A,"<="&$M$2)/($M$2-$M$1+1)

Microsoft’s SUMIFS documentation confirms the argument order: the range to sum comes first, followed by pairs of criteria ranges and criteria. Joining ">=" to a cell with & builds the date condition.

Dividing by ($M$2-$M$1+1) counts every calendar day in the window, including days with no sales. That matters: averaging only the days on which the SKU sold overstates demand for slow lines.

As a hypothetical check, 1,200 units sold over a 30 day window gives 1,200 / 30 = 40 units a day.

Build daily totals for variability

Safety stock needs the spread of daily demand, not just the average. On the Daily sheet, list every date in the window down column A and SKUs across row 1 starting in column B. In B2:

=SUMIFS(Sales!$C:$C,Sales!$A:$A,$A2,Sales!$B:$B,B$1)

Fill it across and down. Days with no sales return 0, which is exactly what you want in the standard deviation.

Choose the window deliberately

Write the window on the sheet next to the dates. For seasonal or promotional lines, a static average is weak; see demand forecasting for small business for better options.

4. Add Safety Stock With STDEV.S and NORM.S.INV

The formula used here

A common method, when lead time is fairly stable and daily demand varies, sizes safety stock as:

safety stock = z × standard deviation of daily demand × √lead time in days

Here z is the number of standard deviations that matches the target cycle service level. It assumes daily demand is roughly normally distributed and that days are independent. If supplier lead times vary as well, the buffer needs a lead time term too; safety stock calculation and supplier lead time management cover that.

The Excel functions

• STDEV.S estimates standard deviation from a sample. Microsoft notes that, in a range, it ignores empty cells, text and logical values, so keep the Daily sheet numeric with zeros rather than blanks.
• NORM.S.INV returns the z value for a probability. It returns #NUM! if the probability is 0 or less, or 1 or more, so a service level of 100% is not possible in this formula.

Put the target service level in column L as a decimal, such as 0.95. With the SKU’s daily totals in column B of the Daily sheet (rows 2 to 91 for a 90 day window), safety stock for row 2 is:

=ROUNDUP(NORM.S.INV(L2)*STDEV.S(Daily!B$2:B$91)*SQRT(D2),0)

ROUNDUP to zero decimal places keeps safety stock in whole units. Each SKU needs its own Daily column, so in practice many workbooks calculate the standard deviation on the Daily sheet and reference that cell from the Reorder sheet.

Pick service levels by importance

Higher service levels raise safety stock faster than they raise availability, so reserve them for lines that justify the extra cash.

5. Work Through a Hypothetical Three SKU Example

All figures in this section are hypothetical and chosen to show the arithmetic.

Every row in the workbook stands for physical stock that has to be counted and received accurately. Illustrative photograph, not an OpsMavix client site.

Image credit: JaumeJaquet, Pixabay Content License. No edits.

The inputs

The calculation

FLT-500:

• Lead time demand: 40 × 7 = 280 units.
• Safety stock: 1.6449 × 8 × √7 = 1.6449 × 8 × 2.6458 = 34.81, rounded up to 35 units.
• Reorder point: 280 + 35 = 315 units.

FLT-750:

• Lead time demand: 12 × 14 = 168 units.
• Safety stock: 1.6449 × 5 × √14 = 1.6449 × 5 × 3.7417 = 30.77, rounded up to 31 units.
• Reorder point: 168 + 31 = 199 units.

VLV-200:

• Lead time demand: 5 × 21 = 105 units.
• Safety stock: 2.0537 × 3 × √21 = 2.0537 × 3 × 4.5826 = 28.23, rounded up to 29 units.
• Reorder point: 105 + 29 = 134 units.

The resulting rows

VLV-200 sells an eighth as fast as FLT-500, yet its reorder point is over 40% as high because its three week lead time and higher service level both add stock. This is why a single reorder level for every product rarely works.

6. Add a Status Column and an Order Quantity

Compare the stock position, not just stock on hand

If you compare only stock on hand, the sheet keeps flagging a line after the order has been placed, and someone may order it twice. Compare the stock position in column J (on hand plus on order) instead. In K2:

=IF(J2<=G2,"ORDER",IF(J2<=G2*1.2,"WATCH","OK"))

This shows ORDER at or below the reorder point and WATCH within 20% above it. Add conditional formatting to colour ORDER rows so they stand out.

Hypothetical status check

FLT-750 has only 150 units on hand, below its reorder point, but 100 units are already on order. Without the On order column, the sheet would have prompted a second order.

Add an order quantity

A reorder point says when to order. To suggest how much, add a maximum level in column M and an order quantity in column N:

=IF(J2<=G2,M2-J2,0)

If FLT-500 has a hypothetical maximum of 700, the suggested order is 700 - 290 = 410 units. Round to supplier pack sizes before placing it. That approach is a min/max inventory system; for a cost based order size, see economic order quantity. Plotted over time, the reorder point, maximum and buffer levels form a stock control chart, which shows at a glance when a line is being ordered too late or too much.

7. Protect the Workbook With Simple Controls

Controls Excel provides

Sheet protection guards against accidental edits. Microsoft’s worksheet protection guidance is clear that it is not a security feature, so do not rely on it to control who can change sensitive data.

A weekly refresh checklist

1. Paste the latest sales export into the Sales sheet and update the window dates.
2. Update stock on hand from a recent count or system export.
3. Update the On order column from open purchase orders, removing received lines.
4. Review any lead times that suppliers have changed.
5. Check the refresh log and the exception column.
6. Raise orders for ORDER rows and review WATCH rows.
7. Save a dated copy if you need to see what the sheet said at the time.

For a wider look at spreadsheet risk, see spreadsheet controls.

8. Know Where the Spreadsheet Breaks Down

Signs it is reaching its limit

The workbook calculates what you last gave it. It does not see a sale, a receipt or a supplier delay until someone updates it. That is fine for a stable range refreshed on schedule, and it becomes a problem when:

• Updating inputs takes longer than acting on the ORDER rows.
• Lines flagged OK still run out, because stock or demand changed after the last refresh.
• Several people edit copies of the file and nobody is sure which is current.
• You hold stock in more than one location and need a reorder point per site.
• Open purchase orders live in another system and are copied across by hand.

For a fuller cost view, see the cost of running inventory on spreadsheets.

Compare the next steps fairly

Whichever route you take, the arithmetic stays the same. What changes is whether stock, demand and open orders update as they happen, so the reorder point is current when you act on it. The reorder point system guide covers how the trigger fits into a full replenishment routine.

Frequently Asked Questions

What is the reorder point formula in Excel?

For a row with average daily demand in C2, lead time in D2 and safety stock in F2, use =C2*D2+F2. Excel multiplies before it adds, so no brackets are needed, although =(C2*D2)+F2 is easier to read.

How do I calculate safety stock in Excel?

One common approach is =ROUNDUP(NORM.S.INV(service level)*STDEV.S(daily demand range)*SQRT(lead time),0). It assumes a fairly stable lead time and daily demand that is roughly normally distributed.

Should zero sales days be included?

Yes. Leaving out days with no sales overstates average demand and understates variability for slow lines. Divide by the number of days in the window and include zeros in the daily range.

Why compare stock position rather than stock on hand?

Stock on hand ignores purchase orders already placed. Adding units on order prevents the sheet from prompting a second order for the same shortfall.

Can I use a 100% service level?

Not with this formula. NORM.S.INV returns an error for a probability of 1, and in practice the safety stock needed rises steeply as the service level approaches 100%.

Does this work in Google Sheets?

Google Sheets has equivalent functions, but names and behaviour can differ. Check each function in the Google Sheets help before copying formulas across.

How often should I refresh the inputs?

Refresh stock and open orders before every ordering session, and demand and lead times on a fixed schedule such as weekly or monthly. Record the refresh date on the sheet.

When should I stop using a spreadsheet for reorder points?

When maintaining it takes more time than using it, when flagged OK lines still run out, or when several people and locations depend on it. At that point compare existing software features, packaged tools, integration and a custom system.

How OpsMavix Can Help

OpsMavix can review how your reorder workbook gets its sales, stock and purchase order data, then show which inputs could update automatically and which decisions still need a person.

That may mean connecting the tools you already use, adding an inventory automation system, introducing ecommerce inventory automation across sales channels or linking reorders to a wholesale order management system.

Contact OpsMavix with your current reorder workbook, one recent stockout and the steps someone takes to refresh the sheet. We can use that evidence to define requirements and a practical pilot.