# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-eoq-and-reorder-point
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the economic order quantity calculator](https://dothecalculation.com/calculators/eoq-calculator) — Check a single SKU against the sheet before rolling the formulas across a catalogue.

## The input columns

**Columns A to H, one row per SKU**
| 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

**Economic order quantity**

```
=SQRT(2*B2*C2/D2)
```
- Two times annual demand times order cost, divided by annual holding cost per unit.
- Units and time must agree: annual demand with annual holding cost.
- The result is rarely a whole case, which the next formula fixes.

**Practical order quantity**

```
=ROUNDUP(I2/H2, 0) * H2
```
- Rounds up to the next whole case pack.
- The total cost curve is flat near the optimum, so rounding costs almost nothing.
- Use =MAX(that, SupplierMinimum) if the vendor enforces a floor.

**Safety stock**

```
=NORM.S.INV(G2) * F2 * SQRT(E2)
```
- NORM.S.INV(0.95) returns 1.645, the service level factor.
- Do not hard-code the Z value; a target change then means editing every row.
- The square root on lead time is because variance adds over days, not standard deviation.

**Reorder point**

```
=(B2/365)*E2 + K2
```
- Demand during the lead time, plus the safety stock.
- Use 365 for calendar-day lead times and your working day count for working-day lead times. Mixing them is the most common error in the sheet.
- Wrap in ROUNDUP if you order in whole units.

## Four SKUs, one sheet

**Calculated output across a small catalogue**
| 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.

**Order flag and days of cover**

```
=IF(OnHand<=ReorderPoint, "ORDER " & PracticalQty, "") · =IFERROR(OnHand/(AnnualDemand/365), "")
```
- The flag names the quantity, so it can be read straight into a purchase order.
- Days of cover gives the urgency: at or below the lead time, you are already late.
- IFERROR catches a SKU with zero demand rather than showing #DIV/0!.

> **Conditional formatting worth adding** — Two rules on the days-of-cover column: red where it is below the lead time, amber where it is below lead time plus seven days. That reads as an urgency ranking rather than a list of yes-or-no flags, and it surfaces the SKU that will be late next week rather than only the one that is late today.

## 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.

**Safety stock with both demand and lead time varying**

```
=NORM.S.INV(G2) * SQRT(E2*F2^2 + (B2/365)^2 * SigmaLT^2)
```
- On SKU 1001, three days of lead time variability takes safety stock from 80 units to 334.
- Supplier variability usually dominates demand variability once it is more than a day or two.
- The extra buffer is a real cost, and it is a specific number to raise with the supplier.

## 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%.

**Building holding cost per unit per year**
| 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.

Tool: [Try the inventory carrying cost calculator](https://dothecalculation.com/calculators/inventory-carrying-cost-calculator) — Build 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-eoq-and-reorder-point). Quote freely with attribution and a link to this page._