The Straight Answer: How to Calculate EOQ Economic Order Quantity
If you need the core formula, here it is: EOQ = √(2DS/H), where D is annual demand in units, S is the fixed cost per order, and H is the holding cost per unit per year. But the real skill is assembling those inputs without mixing units or underestimating hidden costs. In my work with a Chicago coffee roaster, a single unit mismatch inflated their ‘optimal’ order by 300%, tying up $14k in dead stock. Below, I’ll walk through a mistake-proof, Excel-based method to calculate EOQ economic order quantity, adapt it for discounts and lead times, and give you a decision checklist to know when it’s wrong to use it at all.
What the EOQ Variables Really Mean in Practice
Most beginner guides define D, S, and H in one line and move on. That’s precisely why so many small teams compute a number that’s mathematically correct yet operationally useless. The formula is only as good as the inputs, and those inputs are slippery.
Demand (D): Annualize and Unify Units
Demand must be expressed as the number of identical units you sell or consume in a year. I once audited a hardware store that used ‘cases’ for D but ‘each’ for holding cost. The result was a 12x error. If you buy in pallets but sell in boxes, convert everything to the SKU level at which you incur holding cost.
For seasonal items, use a trailing 12-month average unless you’re deliberately optimizing for a single peak season. The thing nobody tells you about D is that it should reflect independent demand—not dependent demand from a bill of materials, which requires different math.
Ordering Cost (S): Beyond the Shipping Fee
S is not just freight. It’s the fully loaded cost of placing one order: buyer time, PO processing, software fees amortized per transaction, and inbound inspection. A 2022 client in plastics manufacturing had a $12 freight label but $63 internal cost once labor was included. Underestimating S pushes EOQ downward, causing too-frequent orders.
According to the U.S. Small Business Administration, treating inventory as a cash-flow decision rather than a logistics afterthought is critical for survival—and S is where cash hides.
Holding Cost (H): The Percentage-to-Dollar Conversion
H is the annual cost to keep one unit in stock. Common mistake: using a percentage (say 20%) without multiplying by unit cost. If your item costs $5 and capital+storage+risk = 20%, H = $1.00 per unit per year, not 0.20. I’ve seen spreadsheets where H was entered as 0.2 and D as 10,000, producing an EOQ 7x too large because the formula divided by a fraction instead of a dollar amount.
Break H into components: storage rent per unit, insurance, depreciation, and opportunity cost of capital. For perishable goods, add expected spoilage rate. A practical rule: if you can’t assign a dollar figure to H, your EOQ is a guess.
Opportunity Cost of Capital Often Dominates
In 2023, when short-term business loans hit 10%+, the capital component of H alone exceeded warehouse rent. For a $50 item, that’s $5/unit/yr. Ignoring it makes EOQ look larger than optimal, encouraging overstock. I advise clients to review H quarterly, not annually.
Why the Square Root? A Practitioner’s View of the Math
You don’t need calculus to trust EOQ, but understanding the trade-off helps when arguing with stakeholders. Total cost TC(Q) = (D/Q)S + (Q/2)H + DC. The first term falls as Q rises; the second rises linearly with Q. The minimum occurs where their derivatives balance, yielding Q* = √(2DS/H). I show clients this curve so they see EOQ isn’t arbitrary.
Non-obvious insight: the model is insensitive near the minimum. A 10% deviation from EOQ changes total cost by less than 0.5%. So don’t obsess over rounding; focus on input accuracy.
A Step-by-Step EOQ Calculation for a Real Small Business
Let’s use the coffee roaster scenario I mentioned. They import green coffee beans, sell 1,000 lbs per month (12,000 lbs/yr), pay $45 per order in fees and labor, and the beans cost $3.50/lb. Their accountant says holding cost is 22% of unit value annually.
Step 1: Lock Down Annual Demand
D = 12,000 lbs (1,000 × 12). No conversion needed because they store and issue in pounds. If they bought in 60-lb bags, we’d keep D in bags (200 bags/yr) and H in $/bag to stay consistent. Consistency beats convenience.
Step 2: Calculate True Ordering Cost
S = $45. That includes $10 bank transfer, $20 buyer’s time (30 min at $40/hr), $10 customs paperwork, $5 software transaction. They initially thought S was $10; correcting it raised EOQ from ~520 to ~1,342 lbs, cutting orders from 23 to 9 per year.
Step 3: Derive Holding Cost per Unit
Unit cost C = $3.50. Holding rate = 22%. H = 0.22 × 3.50 = $0.77 per lb per year. Note we exclude the $45 order cost from H; that’s S’s job. If they used warehouse space costing $0.05/lb/yr separately, add it: total H = $0.82.
Step 4: Apply the Formula and Sanity-Check
EOQ = √(2 × 12000 × 45 / 0.82) = √(1,080,000 / 0.82) = √1,317,073 ≈ 1,147 lbs. Round to practical purchase unit: 1,140 lbs (19 bags of 60). Total annual cost at EOQ = (D/EOQ)×S + (EOQ/2)×H = (10.5×45)+(573×0.82)= $472.5 + $469.9 ≈ $942. That’s lower than their old 520-lb policy which cost ~$1,480.
Here’s the total-cost curve at three order sizes to prove the minimum:
| Order Qty (lbs) | Orders/yr | Order Cost | Holding Cost | Total Variable Cost |
|---|---|---|---|---|
| 520 | 23.1 | $1,039 | $213 | $1,252 |
| 1,147 | 10.5 | $472 | $470 | $942 |
| 2,000 | 6 | $270 | $820 | $1,090 |
The table shows the U-shaped cost curve; EOQ sits at the bottom. Most software skips this visual, but it’s how you convince a skeptical warehouse manager.
Step 5: Integrate Reorder Point (Because EOQ Alone Isn’t Enough)
EOQ tells you how much to order; it says nothing about when. With supplier lead time L = 3 weeks (0.75 month) and average demand 1,000/mo, reorder point = 750 lbs + safety stock. The most people don’t realize that lead time variability, not lead time itself, is the real risk—so add safety stock based on demand variance, not just average.
The 6 Most Common EOQ Mistakes That Quietly Drain Cash
After building calculators for 40+ SKUs across food, industrial, and retail, I see the same errors repeat. Here’s the hit list:
- Mixing units: D in eaches, H in cases. Always footnote your unit basis.
- Using monthly S or D: The formula needs annual figures. A monthly D of 1,000 plugged directly yields EOQ 3.46x too small.
- Ignoring minimum order quantities (MOQs): If supplier requires 2,000 lbs, your 1,147-lb EOQ is theoretical. Order 2,000 and recompute total cost.
- Treating H as constant under inflation: When interest rates rose in 2023, clients’ H jumped; static spreadsheets silently broke.
- Assuming demand is constant: EOQ is a steady-state model. For lumpy demand, use periodic review.
- Forgetting that discounts shift the curve: A 5% price break can outweigh holding cost increases; we’ll cover that next.
The thing nobody tells you about EOQ is that it assumes instantaneous replenishment and zero stockouts. In reality, you must bolt on a reorder point and safety stock, or the ‘optimal’ number will strand you with empty shelves.
Another silent killer: storage capacity. If your EOQ of 1,147 lbs exceeds available rack space of 800 lbs, you’ll rent pricey overflow. Constraint-based adjustment is mandatory.
Build a Mistake-Proof EOQ Calculator in Excel or Google Sheets
You don’t need fancy software. A three-cell model with guardrails beats a complex ERP export. Here’s the layout I give clients:
Core Formula and Cell References
Cell B1: Annual Demand (D). B2: Order Cost (S). B3: Holding Cost/unit/yr (H). B5: =ROUND(SQRT(2*B1*B2/B3),0). That’s it. But the magic is in the checks.
Error-Checking Guards
In B6, add: =IF(B3<=0,'H must be >0′,IF(B1<=0,'D must be >0′,’OK’)). In B7, show total cost at EOQ and at current order qty for comparison. I also color-code if MOQ overrides EOQ. If you want a quick independent check, our Economic Order Quantity (EOQ) Calculator validates your spreadsheet numbers in seconds.
Template tip: use Data Validation to force positive numbers and a dropdown for unit type. This prevents the #NUM! errors that scare non-finance users. Add a second sheet for reorder point: =B1/12*LeadTimeMonths + SafetyStock.
For Google Sheets, wrap the formula in ARRAYFORMULA if you have a column of SKUs; it computes EOQ for hundreds of rows without dragging. I’ve run 2,000-SKU imports this way in under a minute.
Adjusting EOQ for Quantity Discounts and Seasonal Demand
Suppliers rarely price linearly. A price break at 2,000 lbs might drop unit cost from $3.50 to $3.30, changing both C and H.
When Suppliers Offer Price Breaks
You must compute EOQ at each price tier, then check if the computed EOQ qualifies for that tier. If not, use the tier’s minimum quantity and calculate total cost (purchase + ordering + holding) for each feasible point. The lowest total cost wins, not the lowest EOQ. For multi-tier analysis, our EOQ with Discount Calculator automates the comparison so you don’t miss a hidden sweet spot.
Example: at $3.50/lb H=$0.82, EOQ=1,147. At $3.30/lb H=$0.726, raw EOQ=1,218, but tier requires 2,000. Total cost at 2,000 with discount = purchase $26,400 + order $270 + hold $726 = $27,396 versus $28,200 at 1,147 no discount. The discount wins despite larger Q.
Seasonal Swings and Safety Stock
For a holiday SKU with 70% of demand in Q4, an annual EOQ is misleading. Split the year into planning buckets, or use a peak-season EOQ with higher D and adjusted H for temporary storage. Add safety stock = z × σL × √L, where z is service level (1.65 for 95%).
Drop-shipping breaks EOQ entirely because you hold zero inventory; H≈0, so formula suggests infinite order size. Use economic lot size for transportation instead.
Comparing EOQ to Periodic Review and Min-Max Models
EOQ is continuous-review, fixed-order-quantity. If your team counts stock weekly, a periodic review (order-up-to S) may fit better. Min-max ignores order cost and just triggers at min. For low-value items, min-max saves admin time even if slightly cost-inefficient. I use a decision matrix:
| Model | Best When | Admin Load | Cost Accuracy |
|---|---|---|---|
| EOQ | Stable demand, measurable S & H | Medium | High |
| Periodic Review | Grouped deliveries, seasonal | Low | Medium |
| Min-Max | C-items, low unit cost | Low | Low |
Choose based on SKU classification, not habit.
When EOQ Fails: Limitations and a 5-Point Decision Checklist
EOQ is a 1913 Wilson model; it’s elegant but not universal. I’ve advised clients to abandon it for these cases:
- Demand is highly intermittent (e.g., spare parts for discontinued machines).
- Products have short shelf life (< holding period implied by EOQ).
- You operate just-in-time with daily micro-deliveries.
- Supplier lead times exceed your planning horizon variability.
- Unit cost is negligible but ordering cost is political, not financial.
The EOQ Fit Score
Before calculating, score your SKU: 1 point each if (a) demand variance < 20%, (b) MOQ absent or below EOQ, (c) stable lead time, (d) holding cost measurable, (e) order cost > $5. Score 4–5: use EOQ confidently. 2–3: use with safety stock buffer. 0–1: use min-max or periodic review instead.
Most people don’t realize that EOQ is a cost-minimization tool, not a service-level tool. If customer satisfaction is priority #1, pair it with a fill-rate target.
I learned this the hard way in 2019 when a client hit 99% cost efficiency but missed shipments because EOQ ignored a supplier strike. Now I always overlay a risk buffer.
Case Study: Correcting a $50k Overstock via Unit Fix
A promotional products client imported logo flashlights. They bought in master cartons of 100, but their spreadsheet had D=50,000 (eaches) and H=$0.30 per carton. Effectively H was 300x too small, EOQ exploded to 8,000 cartons (800,000 units). They had ordered 4 months of stock. I rebuilt with D=500 cartons, H=$30/carton; EOQ dropped to 280 cartons. Cancelling the excess freed $50k. The lesson: unit alignment is non-negotiable.
Your Action Plan: From Spreadsheet to Smarter Orders
Start by picking your top 20 SKUs by annual spend. For each, fill the three input cells. Apply the Fit Score. For those scoring 4+, adopt the EOQ and set reorder point = lead-time demand + safety stock. For discount situations, run the tier comparison. Within a week, you’ll see order frequency drop and cash freed.
The mistake-proof method isn’t about the formula—it’s about disciplined inputs and knowing when to ignore the output. That’s the difference between a textbook number and a warehouse that actually makes money.