Reorder Point & EOQ Calculator in Excel
Last updated: 27 June 2026
The reorder point (ROP) tells you the exact stock level at which to place a new purchase order; the economic order quantity (EOQ) tells you how much to order each time to minimise total inventory cost. Both are straightforward to build in Excel using a handful of inputs — daily usage, lead time, holding cost and ordering cost — and the result is a living calculator that updates whenever your demand or costs change.
Key takeaways
- Reorder Point = (Average Daily Usage × Lead Time in days) + Safety Stock — once closing stock hits this number, raise a purchase order immediately.
- EOQ = √(2 × Annual Demand × Ordering Cost ÷ Holding Cost per unit per year) — minimises the combined cost of ordering too often vs. holding too much.
- Safety stock absorbs demand spikes and supplier delays; calculate it from maximum and average usage and lead time.
- Build one calculator tab per SKU category, or use a master table with a single formula row duplicated down.
- The template works in Excel 2010 and later — no macros needed.
- Neither ROP nor EOQ replaces judgment: seasonal items, long-lead imports and perishables need manual overrides.
Fact box. For an Indian SMB ordering from a domestic supplier, a typical lead time is 3–7 days. For imports or items from distant suppliers, lead time can be 30–90 days. A higher lead time dramatically raises your ROP — which means more working capital tied up in safety stock. Reducing supplier lead time is often more valuable than any formula optimisation.
What is the reorder point formula?
The reorder point is the quantity at which you must place a new order so that the new stock arrives just as your current stock runs out (plus a safety buffer).
Formula:
ROP = (Average Daily Usage × Lead Time in Days) + Safety Stock
Safety Stock formula (basic):
Safety Stock = (Maximum Daily Usage − Average Daily Usage) × Lead Time in Days
Or, more conservatively:
Safety Stock = (Maximum Daily Usage × Maximum Lead Time) − (Average Daily Usage × Average Lead Time)
Worked example
| Input | Value |
|---|---|
| Average daily sales | 20 units |
| Maximum daily sales | 30 units |
| Average supplier lead time | 7 days |
| Maximum supplier lead time | 10 days |
Safety Stock = (30 × 10) − (20 × 7) = 300 − 140 = 160 units
ROP = (20 × 7) + 160 = 140 + 160 = 300 units
When your stock level hits 300 units, place the next order.
How do I build the ROP calculator in Excel?
Step 1 — Set up your inputs table
Create a named inputs block on a sheet called ROP_EOQ:
| Cell | Label | Value |
|---|---|---|
| B2 | Average Daily Usage (units) | (enter your number) |
| B3 | Maximum Daily Usage (units) | (enter your number) |
| B4 | Average Lead Time (days) | (enter your number) |
| B5 | Maximum Lead Time (days) | (enter your number) |
| B6 | Annual Demand (units) | (enter your number) |
| B7 | Ordering Cost per order (₹) | (enter your number) |
| B8 | Unit Cost (₹) | (enter your number) |
| B9 | Holding Cost Rate (% per year) | (enter your number) |
Step 2 — Calculate Safety Stock
In cell B12:
=(B3*B5)-(B2*B4)
Label B11 as "Safety Stock (units)".
Step 3 — Calculate Reorder Point
In cell B13:
=(B2*B4)+B12
Label: "Reorder Point (units)".
Step 4 — Calculate EOQ
First, calculate Holding Cost per unit per year in B14:
=B8*(B9/100)
Then in B15:
=SQRT((2*B6*B7)/B14)
Label: "Economic Order Quantity (units)". Round up with =CEILING(B15,1) if you need whole units.
Step 5 — Add a live status column to your stock summary
On your Stock Summary sheet, add a "Status" column:
=IF([@[Closing Stock]] <= VLOOKUP([@[Item Code]], ROP_Table, 2, 0), "REORDER NOW", "OK")
Apply conditional formatting: red fill when "REORDER NOW".
What is EOQ and when should I use it?
Economic Order Quantity (EOQ) is the order size that minimises total inventory cost — the sum of ordering cost (every time you place an order) and holding cost (every unit you store).
EOQ = √(2 × D × S ÷ H)
Where:
- D = Annual demand (units)
- S = Ordering cost per order (₹) — includes staff time, freight, paperwork
- H = Holding cost per unit per year (₹) — includes storage, insurance, spoilage, capital cost
Fact box. The holding cost rate for Indian SMBs typically ranges from 15% to 30% of unit value per year, reflecting warehousing rent, insurance, spoilage risk and the cost of capital (opportunity cost of cash tied up in stock). Use 20% as a starting default if you have no better data, then confirm the rate against your own cost structure.
EOQ assumptions and limits
EOQ assumes constant demand and instant replenishment. In practice:
| Situation | EOQ adjustment needed |
|---|---|
| Seasonal demand | Recalculate EOQ for each season separately |
| Perishable goods | Use a shorter holding horizon; reduce EOQ |
| Minimum order quantities from supplier | Round EOQ up to supplier MOQ |
| Import / long-lead items | Combine with a higher safety stock |
| Items with GST credit eligibility | EOQ may be worth increasing to reduce invoice frequency and ITC matching effort |
How do I build a multi-SKU ROP table in Excel?
For businesses with many SKUs, build a table rather than a single calculator:
Master ROP table structure
| Item Code | Item Name | Avg Daily Usage | Max Daily Usage | Avg Lead Time | Max Lead Time | Safety Stock | ROP | EOQ | Current Stock | Status |
|---|
For each formula column, use structured table references so the formula fills down automatically when you add rows.
Safety Stock column formula:
=([@[Max Daily Usage]]*[@[Max Lead Time]])-([@[Avg Daily Usage]]*[@[Avg Lead Time]])
ROP column formula:
=([@[Avg Daily Usage]]*[@[Avg Lead Time]])+[@[Safety Stock]]
Status column formula:
=IF([@[Current Stock]]<=[@[ROP]],"REORDER NOW","OK")
Connect "Current Stock" to your Stock Summary sheet using VLOOKUP or XLOOKUP so it updates automatically when you record new transactions.
Free reorder point & EOQ calculator template
The free Excel template includes:
- ROP/EOQ calculator tab with all formulas pre-built.
- Multi-SKU master ROP table.
- Conditional formatting on the Status column.
- Instructions sheet with example data.
Free to download, no sign-up.
Download the Reorder Point & EOQ Excel Template »
How Ankeshan helps: Ankeshan calculates reorder points automatically from your live stock transactions inside Excel and raises a visible alert on the dashboard — no separate calculator sheet required. It's launching soon; join the waitlist.
Frequently asked questions
What is a good safety stock level for a small Indian trader? Start with one to two weeks of average demand. Then review actual stockouts over three months and adjust upward for items that stocked out, and downward for items that never moved below ROP. Safety stock is not a one-time calculation — revisit it each quarter.
Should EOQ be rounded to whole units?
Yes. Use =CEILING(EOQ_formula, 1) to round up to the nearest whole unit, or =CEILING(EOQ_formula, 5) to round to the nearest five if your supplier sells in pack sizes.
My supplier has a minimum order quantity (MOQ) larger than my EOQ — what do I do?
Order at the MOQ. Your EOQ is the cost-optimal quantity, but you cannot order below the supplier's minimum. Record the MOQ in a separate column and use =MAX(EOQ, MOQ) as your practical order quantity.
How do I account for GST payment timing in my reorder decision? GST on purchases is an ITC credit, not a cost (for registered businesses). It does not change your EOQ formula. However, the cash outflow for GST must be funded until ITC is credited to your ledger — this affects working capital planning, not the EOQ calculation itself.
Can I use the same formula for services or non-stocked items? ROP and EOQ are designed for physical, stocked inventory. For services or on-demand procurement, use lead time analysis instead.
Sources
- Operations management principles: EOQ model (Wilson formula, 1913).
- CGST Rules 2017, Rule 56 — Accounts and Records (cbic.gov.in).
- CBIC GST rate notifications effective 22 September 2025 (56th GST Council).
General information, not professional advice. Verify on the official portal for your case. Reviewed by a Chartered Accountant; last updated 27 June 2026.
Related: Inventory Management pillar » · Low-Stock Alert Formula in Excel » · Stock Register / Inventory Template » · FIFO & Weighted-Average Valuation »