EOQ and Reorder Points in Excel: One Row Per SKU, Four Formulas
A reorder sheet needs SQRT, NORM.S.INV, and a case-pack rounding step. Here is the four-column build across four SKUs, the conditional format that flags what to order today, and the input that everyone guesses wrong.
A Reorder Sheet in Four Formulas
Inventory planning in a spreadsheet needs surprisingly little. One square root gives the order quantity, one inverse normal gives the service level factor, and two additions give the reorder point. Everything else is presentation.
The value is in running it across every SKU at once rather than one at a time, and in having the sheet tell you what to order today rather than what the theory says.
Try the economic order quantity calculatorCheck a single SKU against the sheet before rolling the formulas across a catalogue.The input columns
Swipe sideways to compare columns.
| Column | Header | Note |
|---|---|---|
| A | SKU | The key |
| B | Annual demand | Units, not currency |
| C | Order cost | Per purchase order, not per unit |
| D | Holding cost per unit per year | Usually the weakest input |
| E | Lead time, days | Calendar days |
| F | Daily demand standard deviation | Units |
| G | Service level | As a decimal, 0.95 |
| H | Case pack | Minimum practical order multiple |
The four calculated columns
Four SKUs, one sheet
Swipe sideways to compare columns.
| SKU | Annual demand | EOQ | Orders a year | Safety stock | Reorder point |
|---|---|---|---|---|---|
| 1001 | 24,000 | 1,129 | 21.3 | 80 | 869 |
| 1002 | 6,000 | 963 | 6.2 | 56 | 401 |
| 1003 | 132,000 | 8,391 | 15.7 | 585 | 3,117 |
| 1004 | 900 | 141 | 6.4 | 12 | 123 |
SKU 1004 is the interesting row. Nine hundred units a year, a 45-day lead time, and a $28 holding cost give a reorder point of 123 units, which is nearly seven weeks of stock sitting on a shelf. Long lead times drive inventory far harder than demand does.
The column that makes the sheet useful
Add a current on-hand column, and two formulas turn the model into a daily action list.
When the supplier is the unreliable part
If lead times vary, the demand-only safety stock formula understates the buffer badly. Add a lead time standard deviation column and use the combined form.
The input everyone guesses
Annual demand comes from the system. Lead time comes from the supplier. Holding cost gets typed in from intuition, and it sits under a square root, so a factor-of-two error moves the EOQ by 41%.
Swipe sideways to compare columns.
| Component | Typical share of unit value |
|---|---|
| Cost of capital | 5% to 12% |
| Storage and handling | 2% to 8% |
| Insurance and taxes | 1% to 3% |
| Shrinkage and damage | 1% to 3% |
| Obsolescence | 0% to 15%, entirely product dependent |
| Total | Commonly 15% to 30% of unit value |
Compute it as a percentage of unit cost in its own column rather than typing a dollar figure: =UnitCost * HoldingRate. That way one cell change reprices the whole catalogue when interest rates or warehouse costs move.
Try the inventory carrying cost calculatorBuild the holding rate from its components rather than guessing a single percentage.Errors the sheet will not catch
- Mixing calendar and working days between the lead time and the demand rate. A 12 working-day lead time against a 365-day demand rate understates the reorder point by about 30%.
- Order cost entered per unit rather than per order. This is the second input in the numerator, so it moves EOQ by its square root and the result still looks plausible.
- Using a blended annual demand for a seasonal SKU. The formula assumes a steady rate, so run it per season and recompute the reorder point as the rate changes.
- Ignoring the case pack until after the EOQ is computed, then ordering the raw EOQ. Suppliers do not ship 1,129 of anything.
- Copying NORM.S.INV with a hard-coded 1.645. When the target service level changes, half the sheet updates and half does not.
What the sheet does not model
- Quantity discounts. If price drops at 2,000 units, EOQ alone cannot answer the question; you have to compare total cost including purchase price at each break point.
- Joint ordering. Several SKUs from one supplier in one shipment share the order cost, which makes per-SKU EOQ conservative.
- Perishability and shelf life, which can cap the order quantity far below the EOQ regardless of cost.
- Container and pallet efficiency, where shipping a partial container costs the same as a full one.
- Intermittent demand. The safety stock formula assumes roughly normal demand, which spare parts and slow movers do not have.
- Cash constraints. The mathematically optimal order sometimes cannot be funded this month, and that is a real limit the formula does not know about.
What Z value should I use for safety stock?
Do not use a value; use =NORM.S.INV(ServiceLevel) with the level in a cell. That returns 1.282 for 90%, 1.645 for 95%, 2.054 for 98%, and 2.326 for 99%, and it updates everywhere when the target changes.
Should the reorder point include safety stock?
Yes. The reorder point is demand during the lead time plus safety stock. Without the buffer, you are planning to run out exactly half the time, since the lead time demand is an average.
Why is my EOQ larger than a year of demand?
Either the order cost is far too high, often because it was entered per unit, or the holding cost is far too low. Sanity check with orders per year, which is annual demand divided by EOQ. Anything below one should be investigated rather than acted on.
How do I handle a supplier minimum order quantity?
Wrap the practical quantity in a MAX against the minimum: =MAX(ROUNDUP(EOQ/CasePack,0)*CasePack, SupplierMinimum). If the minimum is far above the EOQ, the extra holding cost is a real price of doing business with that supplier and worth quantifying.
Does this work for seasonal products?
Not with an annual average. Split the year into periods with roughly stable demand and run the formulas per period. An annual EOQ on a product that sells 60% of its volume in one quarter is wrong in both directions across the year.
How often should I recompute these?
Monthly for the reorder points, since demand rates and lead times drift. Quarterly or on a rate change for the EOQ, since the cost inputs move slowly and the total cost curve is flat enough that small drift does not matter.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team