How do you calculate safety stock and lead time in a spreadsheet?

Safety stock is extra stock that covers demand swings and late deliveries while an order is on its way. In a spreadsheet, the simple version is daily sales × safety days. The statistical version is Z × standard deviation of monthly demand × square root of lead time in months, where Z comes from NORM.S.INV of your service level, a function both Excel and Google Sheets have.

How do you measure lead time in a spreadsheet?

Use your own receipts rather than the lead time a vendor quotes. Keep a Receipts tab with one row per purchase order: vendor, PO number, order date and the date the goods arrived.

1. Lead time (days) for each PO = received date − order date. Format the result as a number, not a date.
2. Average lead time per vendor: =AVERAGEIFS(E:E, A:A, "Ironwell Tools"), where column E holds the days and column A the vendor.
3. Worst recent lead time per vendor: =MAXIFS(E:E, A:A, "Ironwell Tools"). MAXIFS needs Excel 2019 or Microsoft 365; Google Sheets has it too. A big gap between the average and the worst case means you need more safety stock for that vendor.

The more receipts you have, the more the average means. If a vendor is new, start with its quoted lead time and replace it once you have a few receipts.

What is the simplest safety stock formula?

Days of cover: safety stock = daily sales × the number of days of cushion you want. If an item sells 2 a day and you want 5 days of cushion, keep 10 units.

Pick the days from experience. A common starting point is the difference between the worst and average lead time for that vendor, because that is how long you could be waiting beyond plan. It is easy to explain and to change, but it ignores how much demand jumps around from month to month.

How do you use the statistical safety stock formula?

This version sizes the cushion to how variable each item's demand actually is.

1. Build a table of units sold by item and month for the last 12 to 24 months.
2. Standard deviation of monthly demand: =STDEV.S(B2:M2) across the 12 monthly totals. Both Excel and Google Sheets accept STDEV.S, which treats the months as a sample.
3. Z for your service level: =NORM.S.INV(0.95) returns about 1.645 for a 95% chance of not running out during a lead time. Google Sheets also accepts the older name NORMSINV.
4. Safety stock = Z × standard deviation × SQRT(lead time in months). Convert days to months by dividing by 30.

Example: monthly sales of a socket set vary with a standard deviation of 20 units and the vendor takes 15 days (0.5 months). At 95%, safety stock = 1.645 × 20 × SQRT(0.5) ≈ 23.3, so keep 24 units.

Microsoft notes that NORM.S.INV returns an error for a probability of 0 or 1 or above, so use a service level such as 0.90, 0.95 or 0.98, never 1.

What if the lead time itself varies?

The formula above assumes the lead time is fixed. When deliveries are often late, a common extension adds lead time variation: safety stock = Z × SQRT(lead time × demand variance + average demand² × lead time variance), with demand and lead time in the same unit, such as months. In the sheet that is =Z*SQRT(LT*SD_demand^2 + AVG_demand^2*SD_LT^2), with each name replaced by its cell.

If that feels like too much, the days-of-cover method with cushion days set to the worst minus the average lead time covers most of the same risk.

What mistakes should you avoid?

• Months with no stock. A stockout month shows zero sales and inflates the standard deviation. Leave those months out.
• One service level for everything. Use a higher level for items customers cannot wait for and a lower one for slow, expensive items.
• Mixing units. A monthly standard deviation needs lead time in months.
• Lumpy items. Items that sell a few times a year break the normal-distribution assumption. Plan around a typical order instead.

Can a tool work this out for every item?

ReorderOwl is one option. Upload your item list and sales history, and optionally lead times, and it returns a reorder workbook that shows how each number was worked out. It works in ChatGPT today; Claude setup is coming shortly. It is free to start. See ReorderOwl for spreadsheets.

Frequently asked questions

What is the safety stock formula in Excel?

Safety stock equals NORM.S.INV(service level) times STDEV.S of monthly demand times SQRT(lead time in months). A simpler version is daily sales times cushion days.

Does Google Sheets have NORM.S.INV?

Yes. Google's function list includes NORM.S.INV as another name for NORMSINV.

What service level should I use?

95% is a common starting point. Use more for critical items and less for slow, costly ones. It must be above 0 and below 1.

Should I use the vendor's quoted lead time?

Use your own receipts when you have them. Received date minus order date shows the lead time you actually get.

How is safety stock different from the reorder point?

Safety stock is the cushion. The reorder point is demand during the lead time plus that cushion.

Sources

1. NORM.S.INV function, Microsoft Support, accessed 2026-10-01
2. STDEV.S function, Microsoft Support, accessed 2026-10-01
3. MAXIFS function, Microsoft Support, accessed 2026-10-01
4. NORMSINV, Google Docs Editors Help, accessed 2026-10-01
5. Google Sheets function list, Google Docs Editors Help, accessed 2026-10-01