EOQ, safety stock and reorder point calculator

Economic order quantity, safety stock, reorder point and annual inventory cost for each SKU.

Downloads are included in the $19.85 yearly membership. Sign in

XLSXCSVODSXMLNumbers

	A	B	C	D	E	F	G	H	I	J	K	L
1	EOQ, safety stock and reorder point calculator
2	Example data — replace the blue input cells with your own.
3	Blue cells are inputs. Pick the service level from the Service levels tab; the other columns are formulas.
4	TOTAL ANNUAL INVENTORY COST			AVERAGE INVENTORY VALUE			NUMBER OF SKUS
5	$21,154			$48,825			12
6
7	Settings
8	Working days per year		250	Converts annual demand into average daily demand
9	Check		OK: every SKU has a listed service level and a standard deviation
10	SKU table (100 rows; one row per SKU)
11	SKU	Description	Annual demand (units)	Unit cost ($)	Order cost ($ per order)	Holding cost (% of unit cost per year)	Lead time (days)	Daily demand std dev (units)	Cycle service level	Holding cost per unit per year ($)	EOQ (units)	Orders per year
12	HX-BLT-008	Hex bolt M8 x 30, zinc plated	180,000	$0.14	$65.00	20%	14	160.0	95.0%	$0.03	28,909	6.2
13	SPR-CMP-02	Compression spring, 2 mm wire	42,000	$0.55	$45.00	22%	21	40.0	97.5%	$0.12	5,589	7.5
14	BRK-AL-110	Aluminum bracket, powder coated	26,000	$3.80	$120.00	25%	28	25.0	98.0%	$0.95	2,563	10.1
15	PCB-CTL-04	Control board assembly, rev C	6,000	$42.50	$180.00	30%	35	6.0	99.0%	$12.75	412	14.6
16	CBL-USB-1M	USB cable, 1 m	36,000	$1.90	$80.00	25%	18	35.0	95.0%	$0.48	3,482	10.3

Showing the first 16 of 112 rows and 12 of 20 columns. Cells with formulas show the formula on hover.

	A	B	C
1	Service levels and z-values
2	Cycle service level and the one-sided normal z-value that the Items tab reads with an exact match.
3
4	Cycle service level to z-value
5	Cycle service level	z-value	What the level means
6	80.0%	0.8416	80% of replenishment cycles finish without a stockout
7	85.0%	1.0364	85% of replenishment cycles finish without a stockout
8	90.0%	1.2816	90% of replenishment cycles finish without a stockout
9	95.0%	1.6449	95% of replenishment cycles finish without a stockout
10	97.5%	1.9600	97.5% of replenishment cycles finish without a stockout
11	98.0%	2.0537	98% of replenishment cycles finish without a stockout
12	99.0%	2.3263	99% of replenishment cycles finish without a stockout
13	99.5%	2.5758	99.5% of replenishment cycles finish without a stockout
14	99.9%	3.0902	99.9% of replenishment cycles finish without a stockout
15
16	z-values are standard normal quantiles (one-sided) for the listed cycle service levels. They are fixed reference values; change one only with a documented reason.

Showing the first 16 of 16 rows and 3 of 3 columns. Cells with formulas show the formula on hover.

	A
1	EOQ, safety stock and reorder point calculator
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Calculates the economic order quantity (EOQ), safety stock, reorder point and annual ordering and holding cost for each SKU.
6	Each service level is read from the Service levels tab, which gives the z-value used for safety stock.
7	The example data is fictional: twelve SKUs for a small assembly business.
8
9	How to use it
10	1. On the Items tab, check the working days per year in the settings block.
11	2. Enter each SKU's annual demand, unit cost, order cost, holding cost rate and lead time in the blue columns.
12	3. Enter the daily demand standard deviation in units, then pick a cycle service level from the Service levels tab.
13	4. Read EOQ, reorder point and safety stock for each SKU, and the totals in the tiles.
14	5. If a row shows Pick a listed level, correct the service level to a value in the table.
15
16	Formulas and method

Showing the first 16 of 36 rows and 1 of 1 columns. Cells with formulas show the formula on hover.

What does this template do?

Inventory planners use the economic order quantity (EOQ) to balance ordering cost against holding cost, and safety stock to cover demand and lead time variation. This workbook is for buyers, planners and stores managers who set order quantities and reorder points for a list of SKUs.

For each SKU the sheet calculates the holding cost per unit per year, the EOQ rounded to whole units, the orders per year and the average daily demand. The z-value comes from the cycle service level you pick, read with an exact match from the Service levels tab. Safety stock is z times the daily demand standard deviation times the square root of the lead time. The reorder point is average daily demand times lead time plus safety stock.

The sheet also totals annual ordering and holding cost and shows the average inventory value. The example data is fictional: twelve SKUs for an assembly business.

What’s inside

EOQ = SQRT(2DS/H), rounded to whole units, for each SKU
Safety stock = z x daily standard deviation x SQRT(lead time), rounded up
Reorder point = average daily demand x lead time + safety stock
Service levels table with z-values from 80 to 99.9 percent
Annual ordering cost, holding cost, total cost and average inventory value

Which tabs does the workbook have?

Tab	What it holds
Items	Settings, the SKU table with EOQ, safety stock, reorder point and annual cost, and the totals.
Service levels	Cycle service levels and their z-values, read by the Items tab.
Notes	What the workbook does, the formulas in words, assumptions and limits.

What formulas does this template use?

This template holds 1,109 formulas in 1,314 cells across 3 tabs, so 84% of its cells calculate. They use 12 distinct functions; the longest formula is 238 characters and 100 of them read from another tab.

Function	Uses	What it does
`IF`	2,101	one result when a test is true, another when false
`ISNUMBER`	1,400	true for a number
`AND`	500	true when every test is true
`SQRT`	200	square root
`IFERROR`	100	swaps an error for a fallback value
`ROUND`	100	rounds to a number of digits
`ROUNDUP`	100	rounds away from zero
`VLOOKUP`	100	looks a value up in a column
`SUM`	7	adds numbers
`COUNTA`	1	counts non-empty cells
`COUNTIF`	1	counts cells meeting one condition
`SUMPRODUCT`	1	multiplies matching entries, then adds them

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

Check the working days per year in the settings block on the Items tab.
Enter each SKU's annual demand, unit cost, order cost, holding cost rate and lead time in the blue columns.
Enter the daily demand standard deviation, then pick a cycle service level from the list.
Read EOQ, reorder point and safety stock for each SKU, and the totals in the tiles.
Use the Service levels tab to see which z-value sits behind each service level.

What is it good for?

Setting reorder points for a spare parts store
Comparing order quantities for purchased components
Checking how a higher service level changes safety stock and cost
Preparing an inventory review for a supply planning meeting

Questions about this sheet

Why is the total annual cost lower than the purchase spend?

The total annual cost counts ordering and holding costs only. The purchase price of the units is excluded, because the order quantity does not change it.

What does the service level mean here?

It is the cycle service level: the share of replenishment cycles that finish without a stockout. The z-value is the standard normal quantile for that level.

Why does a SKU show Pick a listed level?

The service level in that row is not one of the values in the Service levels table, so the sheet cannot find a z-value. Choose a level from the list.

Does the sheet assume normal demand?

Yes. The safety stock formula assumes daily demand is normal and independent from day to day, with a fixed lead time.