Photo by AFINIS Group ® - AFINIS GASKET® Production on Unsplash
Why reorder point and safety stock matter
Running out of stock costs sales and damages customer trust. Ordering too early ties up cash. Reorder point (ROP) and safety stock give you a defensible, data-driven trigger: when inventory drops to the ROP, you place a replenishment order and the safety stock acts as a buffer against demand spikes or supplier delays.
This guide shows you how to encode both calculations in Google Sheets, then publish them as an interactive web calculator using GSheetPress so anyone on your team can run the numbers without editing the sheet. If you are new to building published calculators, the GSheetPress Calculator overview explains the publish workflow in general terms.
Key terms explained
- Reorder point (ROP): The inventory level at which you trigger a new purchase order. When stock on hand falls to or below this number, it is time to reorder.
- Lead time: The number of days between placing an order with your supplier and receiving the goods.
- Average daily demand: The mean number of units sold or consumed per day, calculated from historical sales data.
- Standard deviation of daily demand: A measure of how much daily sales vary around the average. A higher number means less predictable demand.
- Safety stock: Extra inventory held to protect against demand variability and lead-time uncertainty. It is an insurance buffer, not part of normal cycle stock.
- Service level: The probability, expressed as a percentage, that you will not run out of stock during a replenishment cycle. Common choices are 90 %, 95 %, and 99 %.
- Z-score: A statistical multiplier that corresponds to your chosen service level. For 95 % service level the z-score is approximately 1.645; for 99 % it is approximately 2.326.
- Lead-time demand: The total expected demand during the lead-time period, equal to average daily demand multiplied by lead time in days.
- Cycle stock: The portion of inventory that is depleted predictably between replenishment orders, before safety stock is consumed.
What this guide covers and what it excludes
This guide calculates a single-item, single-echelon reorder point. It assumes demand is roughly normally distributed, lead time is fixed (not variable), and your service-level z-score is supplied by you based on business policy. It does not cover economic order quantity (EOQ), variable lead times, multi-location inventory, or perishable goods with expiry constraints. Verify service-level assumptions with your operations or supply-chain team before using results for live procurement decisions.
Prerequisites
- A Google account with access to Google Sheets.
- A GSheetPress account. Sign up at gsheetpress.com/admin/signup and ensure your Google account email matches the account you use in Sheets so the add-on recognises your entitlement.
- The GSheetPress add-on installed from the Google Workspace Marketplace. After installation, reload your spreadsheet and confirm that Extensions → GS2Web SaaS (or the installed add-on name shown in your Extensions menu) is visible.
- A dedicated sample workbook created only for this calculator. Do not use a workbook that contains private business data, because the publish step makes the spreadsheet accessible to anyone who holds the link.
- At least 30 days of historical daily sales data for the item you are modelling, so you can calculate a meaningful average and standard deviation.
The formulas you will use
All the arithmetic is straightforward once you name the inputs. The two core formulas are:
Safety Stock = Z * StdDev * SQRT(LeadTime)Reorder Point = (AvgDailyDemand * LeadTime) + SafetyStockWhere:
- Z is the z-score matching your chosen service level.
- StdDev is the standard deviation of daily demand in units.
- LeadTime is the supplier lead time in days.
- AvgDailyDemand is the mean units sold per day.
Google Sheets uses SQRT() for square root. The Google Sheets SQRT function documentation confirms the syntax.
Prepare your demand data
Before building the calculator, derive the two demand statistics you will enter as inputs.
- Open a blank sheet tab (name it DemandData) in your sample workbook.
- Paste your historical daily unit sales into column A, one day per row, starting at A2. Put the header
Daily Units Soldin A1. - In a spare cell, for example C1, enter the label
Averageand in D1 enter:
Adjust the range to match your actual row count.=AVERAGE(A2:A200) - In C2 enter the label
Std Devand in D2 enter:=STDEV(A2:A200) - Note both numbers. Round to two decimal places for readability. These are the values you will type into the calculator later as defaults.
Build the reorder point calculator
You will create a calculator with 7 inputs. The GSheetPress template generates headers in row 1 (columns A through H) automatically; you do not type them. Inputs occupy rows 2 through 8. The Calculate/Reset button is in row 9. The merged Result Fields separator is in row 10. Result headings start at row 11, and result values start at row 12.
Step 1 — Create a new calculator
- Open your dedicated sample workbook in Google Sheets.
- Click Extensions → GS2Web SaaS → Calculator → New Calculator.
- When prompted, enter the name
Reorder Point & Safety Stock. - When asked for the number of inputs, enter 7.
- When asked whether to include graphs, choose No (graphs are not needed for this model).
- The add-on creates a new sheet tab with headers in row 1: Labels, Input Type, Required, Data, Default, ToolTips (some templates show ToopTips), Inputs, Rules. Do not rename or delete these headers.
Step 2 — Configure input row 2 (Average daily demand)
- A2 — Label: Average Daily Demand (units)
- B2 — Input Type: Number
- C2 — Required: Tick the checkbox so this field is mandatory.
- D2 — Data: Leave blank.
- E2 — Default: 50 (replace with your own average after testing)
- F2 — ToolTips: Mean units sold per day from historical data.
- G2 — Inputs: Leave blank (the web form writes here at runtime).
- H2 — Rules: Leave blank.
Step 3 — Configure input row 3 (Standard deviation of daily demand)
- A3 — Label: Std Dev of Daily Demand (units)
- B3 — Input Type: Number
- C3 — Required: Tick the checkbox.
- D3 — Data: Leave blank.
- E3 — Default: 10
- F3 — ToolTips: Standard deviation of daily unit sales. Calculate with STDEV() on your demand history.
- G3 — Inputs: Leave blank.
- H3 — Rules: Leave blank.
Step 4 — Configure input row 4 (Lead time)
- A4 — Label: Lead Time (days)
- B4 — Input Type: Number
- C4 — Required: Tick the checkbox.
- D4 — Data: Leave blank.
- E4 — Default: 7
- F4 — ToolTips: Number of days from placing a purchase order to receiving goods.
- G4 — Inputs: Leave blank.
- H4 — Rules: Leave blank.
Step 5 — Configure input row 5 (Service level)
- A5 — Label: Service Level (%)
- B5 — Input Type: Number
- C5 — Required: Tick the checkbox.
- D5 — Data: Leave blank.
- E5 — Default: 95
- F5 — ToolTips: Enter a whole number, e.g. 95 for 95%. Supported values: 90, 95, 99.
- G5 — Inputs: Leave blank.
- H5 — Rules: Leave blank.
Step 6 — Configure input row 6 (Z-score)
Rather than making the user look up a z-score, ask them to enter it directly. Add a note in the tooltip explaining common values.
- A6 — Label: Z-Score for Service Level
- B6 — Input Type: Number
- C6 — Required: Tick the checkbox.
- D6 — Data: Leave blank.
- E6 — Default: 1.645
- F6 — ToolTips: Z-score matching your service level. Common values: 90% = 1.282, 95% = 1.645, 99% = 2.326.
- G6 — Inputs: Leave blank.
- H6 — Rules: Leave blank.
Step 7 — Configure input row 7 (Current stock on hand)
- A7 — Label: Current Stock On Hand (units)
- B7 — Input Type: Number
- C7 — Required: Tick the checkbox.
- D7 — Data: Leave blank.
- E7 — Default: 500
- F7 — ToolTips: Units currently in your warehouse or store. Used to check whether you should reorder now.
- G7 — Inputs: Leave blank.
- H7 — Rules: Leave blank.
Step 8 — Configure input row 8 (Order quantity)
- A8 — Label: Order Quantity (units)
- B8 — Input Type: Number
- C8 — Required: Tick the checkbox.
- D8 — Data: Leave blank.
- E8 — Default: 300
- F8 — ToolTips: How many units you plan to order per replenishment. Used to calculate days of cover after the order arrives.
- G8 — Inputs: Leave blank.
- H8 — Rules: Leave blank.
Step 9 — Verify the generated button row
Row 9 is the Calculate/Reset button, generated automatically by the add-on. Confirm:
- A9 contains the button label (typically Calculate).
- B9 shows Button.
- D9 shows main.
- E9 may show Reset — this is correct; do not change it.
Do not add any formula to row 9.
Step 10 — Add result labels (rows 11 onward)
Row 10 is the merged Result Fields separator generated by the add-on. Result headings go in row 11 and result values go in row 12 onward. Enter the following labels in column A:
| Cell | Label to type |
|---|---|
| A11 | Lead-Time Demand (units) |
| A12 | Safety Stock (units) |
| A13 | Reorder Point (units) |
| A14 | Reorder Now? |
| A15 | Days of Cover After Order |
Step 11 — Enter result formulas in column B
Column B, same rows, holds the calculated values. Click each cell, type the formula starting with =, and press Enter. All formulas reference the G column (runtime inputs) and E column (defaults used during testing).
Important: G2 through G8 are overwritten by the web form every time a visitor submits values. Never put result formulas in the G column. During testing before publishing, copy the E column default values into G2:G8 using Paste special → Values only, so the formulas return numbers instead of errors.
Click B11, paste:
=IF(G2="","",ROUND(G2*G4,2))This is lead-time demand: average daily demand multiplied by lead time.
Click B12, paste:
=IF(G2="","",ROUND(G6*G3*SQRT(G4),2))This is safety stock: z-score multiplied by the standard deviation of demand multiplied by the square root of lead time.
Click B13, paste:
=IF(B11="","",ROUND(B11+B12,2))This is the reorder point: lead-time demand plus safety stock. It references B11 and B12 rather than repeating the sub-calculations.
Click B14, paste:
=IF(B13="","",IF(G7<=B13,"Yes — place an order now","No — stock is above reorder point"))This compares current stock on hand (G7) to the reorder point. If stock is at or below the ROP, it prompts a reorder.
Click B15, paste:
=IF(G2=0,"Cannot divide — check daily demand",IF(G2="","",ROUND((G7+G8)/G2,1)))This estimates how many days of cover you will have once the new order arrives, assuming it arrives in full. The zero-demand guard prevents a division error.
Step 12 — Test formulas before publishing
- Select cells E2:E8.
- Copy (Ctrl+C or Cmd+C).
- Click cell G2.
- Right-click → Paste special → Values only. This pastes the default numbers into G2:G8 without overwriting any formulas.
- Confirm that B11 through B15 now show numbers, not errors or blank cells.
Worked example
Using the defaults already entered (average daily demand = 50 units, standard deviation = 10, lead time = 7 days, z-score = 1.645, stock on hand = 500, order quantity = 300):
| Result field | Formula applied | Expected value |
|---|---|---|
| Lead-Time Demand | 50 × 7 | 350 units |
| Safety Stock | 1.645 × 10 × SQRT(7) = 1.645 × 10 × 2.6458 | 43.52 units |
| Reorder Point | 350 + 43.52 | 393.52 units |
| Reorder Now? | 500 > 393.52 | No — stock is above reorder point |
| Days of Cover After Order | (500 + 300) / 50 | 16.0 days |
Alternative scenario: If stock on hand falls to 380 units (below the ROP of 393.52), B14 returns Yes — place an order now, and days of cover after the order arrives becomes (380 + 300) / 50 = 13.6 days.
Frequently asked questions
What z-score should I use if I want a 95 % service level?
Enter 1.645 in the Z-Score input. This is the standard normal distribution value at the 95th percentile, meaning there is a 95 % probability of not stocking out during a replenishment cycle. For 90 % use 1.282, and for 99 % use 2.326. If your business requires a non-standard service level, look up the corresponding z-score in a standard normal distribution table or use the Excel/Sheets formula =NORM.S.INV(0.95) to confirm the value.
Can I use this calculator for items with highly irregular demand?
The formula assumes demand is approximately normally distributed. If your item has intermittent demand (many days of zero sales punctuated by large spikes), the standard deviation will be large and the safety stock estimate may be unreliably high. For intermittent demand, consider Poisson-based models or consult a supply-chain specialist. The calculator still runs the arithmetic correctly; the limitation is in the model assumption, not the spreadsheet.
Why does the Reorder Now? cell show a blank instead of Yes or No?
The formula in B14 returns a blank when B13 is blank. B13 is blank when B11 is blank, which happens when G2 is empty. During testing, copy the E column defaults into G2:G8 using Paste special → Values only as described in Step 12. After publishing, the web form populates G2:G8 automatically on each submission, so blanks will not appear to end users.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| B11 shows #VALUE! | A G cell contains text instead of a number | Paste E defaults to G as values only; check for stray spaces in G cells |
| B15 shows the error message | G2 is 0 or empty | Enter a positive average daily demand; zero demand makes days-of-cover undefined |
| Reorder Now? always says No | Stock on hand default is very high | Enter a realistic current stock value in G7 or E7 |
| Safety stock is unexpectedly large | Standard deviation is very high relative to average demand | Recheck your demand data; consider whether outliers are skewing StdDev |
| Calculator tab not visible after add-on run | Add-on did not complete or account mismatch | Reload the sheet, sign in with the same Google account as your GSheetPress account, and retry Extensions → GS2Web SaaS → Calculator → New Calculator |
| Results do not update after publish | Old G values cached from testing | Clear G2:G8 before publishing; the form will fill them on submission |
For add-on installation or account issues, visit the GSheetPress support page. For information on plan entitlements and what the Calculator feature includes, see the pricing page.
Publish and test your web calculator
- Share the workbook broadly (required step). Click Share in the top-right corner of your Google Sheet. Under General access, change the setting to Anyone with the link and set the role to Editor. Click Done. Be aware: this setting permits anyone who holds the spreadsheet URL to edit the sheet directly. Use only the dedicated sample workbook, never a file containing real business or personal data.
- Select the calculator tab. Click the tab the add-on created (named after your calculator, for example Reorder Point & Safety Stock) to make it the active sheet.
- Publish the calculator. Click Extensions → GS2Web SaaS → Calculator → Publish Calculator. Wait for the confirmation message before proceeding.
- Open the Calculator Link sheet. The add-on creates or updates a sheet called Calculator Link (or similar). Find your calculator by matching the Sheet Name column to the tab you just published.
- Open the public link. Click the link in column C (the Link or Public Link column), not the link in column B (the Edit Link). If the list looks out of date, click Get Links to refresh it.
- Test with the worked example inputs. On the public calculator page, enter: Average Daily Demand = 50, Std Dev = 10, Lead Time = 7, Service Level = 95, Z-Score = 1.645, Current Stock On Hand = 500, Order Quantity = 300. Click Calculate and confirm the results match: Lead-Time Demand = 350, Safety Stock = 43.52, Reorder Point = 393.52, Reorder Now? = No — stock is above reorder point, Days of Cover After Order = 16.0.
- Test the alternative scenario. Change Current Stock On Hand to 380 and click Calculate. Confirm Reorder Now? changes to Yes — place an order now and Days of Cover After Order shows 13.6.
- Embed optionally. If you want to embed the calculator on a website, copy the iframe code from column D of the Calculator Link sheet and paste it into an HTML block on your page.
- Republishing. If you update labels or formulas and republish the same calculator, the public link remains unchanged. You do not need to update any embed code or shared links.
Your calculator is now live. Share the public link (column C) with your operations, purchasing, or logistics team so they can calculate reorder points and safety stock for any item without needing access to the spreadsheet itself. For guidance on other calculator types you can build with GSheetPress, see the Calculator feature overview.