How does this template calculate EOQ?

EOQ = square root of (2 x D x S / H), where D is annual demand, S the cost per order and H the holding cost per unit per year (unit cost x carrying rate). With the Economic Order Quantity calculator's example, 24,000 units a year, 75 per order, 12 per unit and 25 percent, H = 3 and EOQ = 1,095 units. Its annual ordering + holding cost is 3,286 against 5,100 for a 3,000-unit lot: a saving of 1,814 a year.

How is safety stock calculated?

Safety stock = z x square root of (LT x sd_d^2 + d^2 x sd_LT^2), which covers variation in both daily demand and lead time. With the Safety Stock Calculator's example, 100 units a day with a standard deviation of 40, a 9-day lead time with a standard deviation of 1 day, and z = 1.645 for a 95 percent service level: square root of (9 x 1,600 + 10,000 x 1) = 156.2, so safety stock is 257 units and the reorder point 900 + 257 = 1,157 units.

What does the Reorder status mean?

Overdue means the inventory position (on hand + on order) is at or below the reorder point: place an order now. Due soon means it will reach the reorder point within the review window you set on Setup, measured in days of average demand. In the example, a circuit breaker with 267 units on hand and a reorder point of 284 reads Overdue; a filter fan 1.1 days above its reorder point reads Due soon.

What service level should I use?

It is a policy choice, usually higher for A items, whose stockouts hurt most, and lower for C items. This workbook uses cycle service level, the chance of not running out in a replenishment cycle: 95 percent gives z = 1.645, 98 percent gives 2.054 and 90 percent 1.282. Raising an item from 95 to 98 percent raises its safety stock by about 25 percent.

My supplier has a minimum order. Is EOQ still useful?

Yes. Total cost is flat near the EOQ, so rounding to a pack size costs little; the cost curve on the Dashboard shows it. For the EOQ calculator's example, ordering 1,500 instead of 1,095 units costs 1,200 + 2,250 = 3,450 a year, only 164 more than at EOQ, while 3,000 units costs 5,100.

Can I use it without Excel?

Yes: a CSV with live formulas of the Item Plan opens in any spreadsheet, with daily demand, z, safety stock, reorder point, EOQ, annual costs, saving, inventory position, cover and status as formulas against the settings listed below the rows. The dashboard, charts, dropdowns and self-check are in the .xlsx.

Planning and Supply Chain

Reorder Point and EOQ Planner Excel Template

For every item, the economic order quantity, the safety stock for your service level, the reorder point, and whether the stock you hold and have on order says order now.

Overview

This reorder point and EOQ planner is a working Excel workbook for buyers, material planners and inventory managers who order the same items again and again. It ships with example data for a fictional control-panel maker in Mexico, 20 purchased items priced in MXN, so the order list, dashboard and charts are populated the moment you open it; replace the rows with your own items and everything recalculates.

For each item the workbook computes the economic order quantity, the square root of 2 x annual demand x order cost / holding cost, and the yearly ordering plus holding cost at that quantity and at the lot you order today, the same formulas as the Economic Order Quantity calculator on this site. It computes safety stock as z x the square root of (lead time x daily demand variance + daily demand squared x lead-time variance), with z from the cycle service level you set for the item's ABC class, and the reorder point as lead-time demand plus safety stock, matching the Safety Stock and Reorder Point calculators. Inventory position, on hand plus on order, is then compared with the reorder point to give a status word for every item.

In the example, four items are at or below their reorder point and four more will reach it within the five-day review window. Ordering economic quantities instead of the current lots would save about 108,700 MXN a year in ordering and holding cost; the largest single saving, about 19,500 MXN, is a touch panel bought ten at a time when its EOQ is 28. The safety stock across the 20 items is worth about 791,000 MXN.

What this template includes

Item Plan table: class, annual demand, daily demand standard deviation, lead time and its standard deviation, unit cost, cost per order, current order quantity, on hand and on order, one row per item
Per item: daily demand, service factor z from the class service level, safety stock, reorder point, holding cost, EOQ, orders per year, annual ordering + holding cost at EOQ and at your current lot, the saving, inventory position and days of cover above the reorder point
A Reorder status word per item: Overdue (at or below the reorder point, order now), Due soon (within your review window) or OK
Your own carrying-cost rate, working days, review window and cycle service level for A, B and C items on Setup
Dashboard KPI tiles: items to order now, items due soon, the yearly saving of ordering EOQ, and safety stock value
Two native charts: days of cover above the reorder point for the twelve most urgent items, and the EOQ cost curve (ordering, holding and total cost against order quantity) for the item you pick
An order list of the twelve most urgent items with EOQ and current lot, a by-class table, and Check columns that name problems in words
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference, including the examples of the EOQ, Safety Stock and Reorder Point calculators (Self-check: PASS)

Suggested use case

Use it to set reorder points and order quantities from your own demand history and lead times, to see each morning which items to order, and to show what ordering in economic lots instead of habitual ones is worth per year.

How to use it

On Setup, enter your company name, currency label, carrying-cost rate, working days per year, review window and the cycle service level for A, B and C items.
On Item Plan, enter each item's class, annual demand, daily demand standard deviation, lead time and its standard deviation, unit cost, order cost and current order quantity.
Update on hand and on order whenever you plan; the Reorder status column turns to Overdue when an item reaches its reorder point.
Open the Dashboard to read the order list and the saving from ordering EOQ, and pick an item to see its cost curve.
Round EOQ to a pack size or supplier minimum where you must; the cost curve shows how little a sensible rounding costs.

Frequently Asked Questions

How does this template calculate EOQ?: EOQ = square root of (2 x D x S / H), where D is annual demand, S the cost per order and H the holding cost per unit per year (unit cost x carrying rate). With the Economic Order Quantity calculator's example, 24,000 units a year, 75 per order, 12 per unit and 25 percent, H = 3 and EOQ = 1,095 units. Its annual ordering + holding cost is 3,286 against 5,100 for a 3,000-unit lot: a saving of 1,814 a year.
How is safety stock calculated?: Safety stock = z x square root of (LT x sd_d^2 + d^2 x sd_LT^2), which covers variation in both daily demand and lead time. With the Safety Stock Calculator's example, 100 units a day with a standard deviation of 40, a 9-day lead time with a standard deviation of 1 day, and z = 1.645 for a 95 percent service level: square root of (9 x 1,600 + 10,000 x 1) = 156.2, so safety stock is 257 units and the reorder point 900 + 257 = 1,157 units.
What does the Reorder status mean?: Overdue means the inventory position (on hand + on order) is at or below the reorder point: place an order now. Due soon means it will reach the reorder point within the review window you set on Setup, measured in days of average demand. In the example, a circuit breaker with 267 units on hand and a reorder point of 284 reads Overdue; a filter fan 1.1 days above its reorder point reads Due soon.
What service level should I use?: It is a policy choice, usually higher for A items, whose stockouts hurt most, and lower for C items. This workbook uses cycle service level, the chance of not running out in a replenishment cycle: 95 percent gives z = 1.645, 98 percent gives 2.054 and 90 percent 1.282. Raising an item from 95 to 98 percent raises its safety stock by about 25 percent.
My supplier has a minimum order. Is EOQ still useful?: Yes. Total cost is flat near the EOQ, so rounding to a pack size costs little; the cost curve on the Dashboard shows it. For the EOQ calculator's example, ordering 1,500 instead of 1,095 units costs 1,200 + 2,250 = 3,450 a year, only 164 more than at EOQ, while 3,000 units costs 5,100.
Can I use it without Excel?: Yes: a CSV with live formulas of the Item Plan opens in any spreadsheet, with daily demand, z, safety stock, reorder point, EOQ, annual costs, saving, inventory position, cover and status as formulas against the settings listed below the rows. The dashboard, charts, dropdowns and self-check are in the .xlsx.