by EOQ (Economic Order Quantity) is a formula-based inventory management technique that calculates the optimal order quantity to minimize total holding and ordering costs.
Where:
-
D = Annual demand
-
S = Ordering cost per order
-
H = Holding cost per unit/year
EOQ Calculator with Safety Stock: Excel Format
📌 Step 1: Input Fields
| Field | Cell (Example) | Description |
|---|---|---|
| Annual Demand (Units) | B2 | Total units required per year |
| Ordering Cost per Order (₹) | B3 | Cost of placing one order |
| Holding Cost per Unit per Year (₹) | B4 | Storage cost per unit per year |
| Lead Time (Days) | B5 | Time between placing and receiving an order |
| Daily Usage Rate (Units/Day) | B6 | Average units used per day |
| Safety Stock (Units) | B7 | Extra stock kept to prevent stockouts |
📌 Step 2: EOQ Calculation Formula
In Excel cell B8 (EOQ Output), use the formula:
=SQRT((2 * B2 * B3) / B4)
Label: Economic Order Quantity (EOQ)
📌 Step 3: Reorder Point (ROP) Formula
In Excel cell B9 (ROP Output), use the formula:
=(B5 * B6) + B7
Label: Reorder Point (ROP)
✅ What You’ll Get
| Output Field | Cell | Formula / Description |
|---|---|---|
| EOQ (Units) | B8 | √(2 × Annual Demand × Ordering Cost) ÷ Holding Cost |
| Reorder Point (Units) | B9 | (Lead Time × Daily Usage) + Safety Stock |
🧠 Example
| Input | Value |
|---|---|
| Annual Demand | 10,000 |
| Ordering Cost per Order (₹) | ₹500 |
| Holding Cost per Unit per Year | ₹25 |
| Lead Time | 5 days |
| Daily Usage | 40 |
| Safety Stock | 100 |
Output:
-
EOQ = 632.45 units
-
Reorder Point = (5 × 40) + 100 = 300 units
One thought on “EOQ Calculator with Safety Stock”