Free Excel templates

A free safety stock calculator in Excel, with the service level priced in money

Safety stock with variable demand and variable lead time, the same stock read at seven service levels, the cycle service level a fill rate target needs, the economic order quantity, and three policies compared on holding and shortage cost: five sheets, no macros.

The file sets safety stock for one product, a fictional filter selling 1,840 units a week with a standard deviation of 462, on a lead time of six weeks give or take 1.9. Demand during the lead time has a standard deviation of 3,675 units, and a 98% cycle service level needs 7,547 units of safety stock. But a 98% fill rate, which is what the customer actually feels, needs only a 94.4% cycle service level and 5,838 units: €48,900 of inventory less. The economic optimum sits at 99.1%, and the whole board argument between 97% and 99.5% is worth €6,198 a year.

Free online calculator, no sign-up: reorder point calculator.

Free online calculator, no sign-up: safety stock calculator.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.

Safety_Stock_Calculator.xlsx (25 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This file is one worked case from Supply Chain Management. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Five sheets. Blue cells on Assumptions are the only inputs.

Assumptions
Weekly demand and its standard deviation, lead time and its standard deviation, price, cost, the four components of the holding rate, ordering cost and the service level voted by the board, then the derived quantities: demand during lead time, EOQ, orders per year, z and units missing.
Service level table
The same stock read at 90%, 95%, 97%, 98%, 99%, 99.5% and 99.9%: z, safety stock, inventory value, holding cost, shortage cost and total cost.
Fill rate solver
The z that delivers a target fill rate, found by bisection laid out one step per column, with its status and the gap to the target.
Three policies
The board's vote, a 98% fill rate and the economic optimum side by side, and the money between them.
Checks
Every figure the book prints, reproduced to the decimal.

What the worked case returns

MeasureValue
Standard deviation of demand during lead time3,675 units
Economic order quantity4,385 units
z at a 98% cycle service level2.054
Safety stock at 98% cycle service level7,547 units
Cycle service level for a 98% fill rate94.4%
Safety stock for a 98% fill rate5,838 units
Inventory released, board vote to fill rate€48,870
Economic optimum cycle service level99.1%
Total cost at a 98% cycle service level€56,850 a year

The two service levels are not the same promise. A cycle service level counts the share of replenishment cycles with no stock-out at all; a fill rate counts the share of units shipped on time. On this product a 98% fill rate is reached with a 94.4% cycle service level, and confusing the two holds 1,709 more units than the customer ever asked for.

How to use it on your own product

  1. Enter your own weekly demand, its standard deviation, the lead time and its variability on Assumptions.
  2. Enter price, cost and the four parts of your holding rate.
  3. Read the Service level table to see what each extra point of service costs in stock and in money.
  4. Set your fill rate target on Fill rate solver to get the cycle service level that actually delivers it.
  5. Compare the three policies before choosing one.

Questions people ask about it

Is this safety stock calculator really free?

Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Does it use macros or circular references?

No macros and no circular reference. The fill rate solver is a bisection laid out across columns, so it converges by formula with no goal seek.

What safety stock formula does it use?

Safety stock is z times the standard deviation of demand during the lead time, where that standard deviation combines demand variance over the mean lead time with lead time variance times mean demand squared. Here that gives 3,675 units.

Can I use it for my own stock?

Yes, by replacing the inputs. The product and every figure in it are fictional.

The rest of the files

This template is one of the four companion workbooks for Supply Chain Management: the others decompose demand during lead time, price supplier reliability, and cover pooling, forecast bias and the holding rate. Free, like this one.

Open the companion files →

Other free templates

This template comes from Supply Chain Management. The book is on Amazon.

If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.