Photo by Team Nocoloco on Unsplash
What the EOQ model does and does not cover
Economic Order Quantity is a classic inventory management formula. It finds the order size at which the total of your annual ordering costs and annual holding costs is lowest. Order too rarely and you hold too much stock; order too often and you spend too much on procurement administration. EOQ finds the sweet spot.
Before building the calculator it is worth being clear about what the model assumes and where it falls short.
Model assumptions
- Demand is constant and known throughout the year.
- Lead time (the gap between placing and receiving an order) is fixed and known.
- Each order arrives in a single delivery.
- No stockouts are allowed — you always have enough stock.
- Unit cost does not change with order size (no quantity discounts).
What is excluded
- Quantity discount pricing tiers.
- Probabilistic or seasonal demand.
- Multi-product inventory interactions.
- Safety stock and reorder point calculations (these build on EOQ but are separate).
If your business has strongly seasonal demand or supplier discounts, treat EOQ as a starting estimate and adjust manually. The Inventory Operations EOQ reference gives a thorough discussion of practical limitations.
Key terms explained
- Economic Order Quantity (EOQ): The order size, in units, that minimises total annual inventory cost.
- Annual demand (D): The total number of units you expect to sell or use in one year.
- Ordering cost (S): The fixed cost of placing a single purchase order — includes admin time, delivery charges, and supplier fees — expressed in your currency per order.
- Holding cost (H): The annual cost of keeping one unit in stock — includes warehouse space, insurance, spoilage risk, and the opportunity cost of tied-up capital — expressed in your currency per unit per year.
- Minimum order quantity (MOQ): The smallest number of units your supplier will accept in a single order. If EOQ falls below this, you must order at least the MOQ.
- Order frequency: How many times per year you place orders, calculated as annual demand divided by order quantity.
- Total annual inventory cost: The sum of annual ordering cost and annual holding cost at the chosen order quantity.
- Unit purchase price: The cost per unit you pay to your supplier. Included here to calculate total annual purchase spend alongside inventory costs.
- GS2Web SaaS: The GSheetPress Google Sheets add-on that converts a configured spreadsheet tab into a shareable web calculator form.
- Result fields: Cells below the separator row in the calculator tab where you place formulas; these are displayed to the user after they submit the form.
- G column (runtime inputs): The column GSheetPress writes the user's submitted values into when the form is submitted. Your formulas must read from this column, not from the default column E.
Prerequisites
- A Google account with access to Google Sheets.
- A GSheetPress account. Sign up at gsheetpress.com/admin/signup.html and confirm that your account includes the Calculator entitlement (check your plan on the pricing page).
- The GS2Web SaaS add-on installed in Google Sheets from the Google Workspace Marketplace. Open any spreadsheet, go to Extensions → Add-ons → Get add-ons, and search for GS2Web.
- A dedicated sample spreadsheet created solely for this project. Do not use a file containing private or sensitive business data.
The EOQ formula
The standard EOQ formula is:
EOQ = SQRT( (2 * D * S) / H )Where D is annual demand in units, S is ordering cost per order in your currency, and H is holding cost per unit per year in your currency. The result is in units.
Because your supplier may impose a minimum order quantity, the quantity you actually order is:
Effective order quantity = MAX( EOQ, MOQ )All cost and frequency calculations below use the effective order quantity, not raw EOQ.
Build the EOQ calculator in GSheetPress
Step 1 — Create the calculator tab
- Open your dedicated sample Google Sheet.
- Go to Extensions → GS2Web SaaS → Calculator → New Calculator.
- In the dialog, enter a name such as EOQ Calculator.
- Set the number of inputs to 5. Do not count the button.
- When asked whether to include graphs, choose No unless you want to add an optional cost-curve chart later.
- Click Create. GSheetPress inserts a new tab with pre-built headers in row 1: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules in columns A through H.
Row layout after creation
With 5 inputs the generated layout is:
| Row | Purpose |
|---|---|
| 1 | Auto-generated column headers (do not edit) |
| 2 | Input 1 |
| 3 | Input 2 |
| 4 | Input 3 |
| 5 | Input 4 |
| 6 | Input 5 |
| 7 | Calculate button (auto-generated) |
| 8 | Merged separator row (auto-generated) |
| 9 | Result headings row |
| 10 onward | Individual result rows |
Step 2 — Configure Input 1: Annual demand
- A2 — Label: Annual Demand (units)
- B2 — Input Type: Number
- C2 — Required: Tick the checkbox to make this field mandatory.
- D2 — Data: Leave blank.
- E2 — Default: 10000
- F2 — ToolTips: Total units expected to be sold or used in one year.
- G2 — Inputs: Leave blank. GSheetPress manages this column at runtime.
- H2 — Rules: Leave blank.
Step 3 — Configure Input 2: Ordering cost
- A3 — Label: Ordering Cost per Order (currency)
- B3 — Input Type: Number
- C3 — Required: Tick the checkbox.
- D3 — Data: Leave blank.
- E3 — Default: 50
- F3 — ToolTips: Fixed cost each time you place one purchase order, e.g. admin and delivery fees.
- G3 — Inputs: Leave blank.
- H3 — Rules: Leave blank.
Step 4 — Configure Input 3: Holding cost
- A4 — Label: Holding Cost per Unit per Year (currency)
- B4 — Input Type: Number
- C4 — Required: Tick the checkbox.
- D4 — Data: Leave blank.
- E4 — Default: 2
- F4 — ToolTips: Annual cost to store one unit: warehouse, insurance, capital cost. Enter as a currency amount, not a percentage.
- G4 — Inputs: Leave blank.
- H4 — Rules: Leave blank.
Step 5 — Configure Input 4: Unit purchase price
- A5 — Label: Unit Purchase Price (currency)
- B5 — Input Type: Number
- C5 — Required: Tick the checkbox.
- D5 — Data: Leave blank.
- E5 — Default: 15
- F5 — ToolTips: Cost per unit paid to your supplier. Used to calculate total annual purchase spend.
- G5 — Inputs: Leave blank.
- H5 — Rules: Leave blank.
Step 6 — Configure Input 5: Minimum order quantity
- A6 — Label: Minimum Order Quantity (units)
- B6 — Input Type: Number
- C6 — Required: Tick the checkbox.
- D6 — Data: Leave blank.
- E6 — Default: 1
- F6 — ToolTips: Smallest quantity your supplier accepts per order. Enter 1 if there is no minimum.
- G6 — Inputs: Leave blank.
- H6 — Rules: Leave blank.
Step 7 — Verify the auto-generated button row
Row 7 is the Calculate button, generated automatically. Check that:
- B7 contains Button.
- D7 contains main.
- A7 contains the button label (commonly Calculate). Do not edit E7 — it holds the Reset label.
Step 8 — Add result headings (row 9)
Click cell A9 and type Result. Click B9 and type Value. These become the column headings displayed above your results on the web form.
Step 9 — Enter result labels and formulas (rows 10 onward)
All formulas reference the G column because that is where GSheetPress writes the values submitted by the user. Column E holds the defaults displayed in the form fields before submission — it is not updated by the web form. To test your formulas before publishing, copy E2:E6 and paste into G2:G6 using Paste special → Values only, then revert G2:G6 to blank when finished testing.
Important: when entering each formula below, click the destination cell, type or paste the full formula beginning with =, then press Enter.
Row 10 — Raw EOQ (units)
Click A10 and type: Raw EOQ (units)
Click B10 and enter:
=IF(OR(G2="",G3="",G4=""),"Awaiting inputs",IF(G4=0,"Holding cost cannot be zero",ROUND(SQRT((2*G2*G3)/G4),0)))Row 11 — Effective order quantity (units)
Click A11 and type: Effective Order Quantity (units)
Click B11 and enter:
=IF(OR(G2="",G3="",G4="",G6=""),"Awaiting inputs",IF(G4=0,"Holding cost cannot be zero",MAX(ROUND(SQRT((2*G2*G3)/G4),0),G6)))Row 12 — Orders per year
Click A12 and type: Orders per Year
Click B12 and enter:
=IF(OR(B11="Awaiting inputs",B11="Holding cost cannot be zero",B11=0),"—",ROUND(G2/B11,1))Row 13 — Annual ordering cost
Click A13 and type: Annual Ordering Cost (currency)
Click B13 and enter:
=IF(OR(B12="—",G3=""),"—",ROUND(B12*G3,2))Row 14 — Annual holding cost
Click A14 and type: Annual Holding Cost (currency)
Click B14 and enter:
=IF(OR(B11="Awaiting inputs",B11="Holding cost cannot be zero",G4=""),"—",ROUND((B11/2)*G4,2))This formula uses the average inventory level, which is half the order quantity, multiplied by the annual holding cost per unit.
Row 15 — Total annual inventory cost (ordering + holding)
Click A15 and type: Total Annual Inventory Cost (currency)
Click B15 and enter:
=IF(OR(B13="—",B14="—"),"—",ROUND(B13+B14,2))Row 16 — Annual purchase spend
Click A16 and type: Annual Purchase Spend (currency)
Click B16 and enter:
=IF(OR(G2="",G5=""),"—",ROUND(G2*G5,2))Row 17 — Total annual cost (purchase + inventory)
Click A17 and type: Total Annual Cost incl. Purchase Spend (currency)
Click B17 and enter:
=IF(OR(B15="—",B16="—"),"—",ROUND(B15+B16,2))Row 18 — MOQ constraint notice
Click A18 and type: MOQ Constraint Applied?
Click B18 and enter:
=IF(OR(B10="Awaiting inputs",B10="Holding cost cannot be zero"),"—",IF(G6>B10,"Yes — MOQ exceeds raw EOQ; costs are higher than optimal","No — EOQ meets or exceeds MOQ"))Worked example
Use these values to verify your calculator before publishing. Copy E2:E6 (the defaults already set) and paste as values into G2:G6 to simulate a form submission.
| Input | Value |
|---|---|
| Annual Demand (D) | 10,000 units |
| Ordering Cost (S) | 50 currency units |
| Holding Cost (H) | 2 currency units per unit per year |
| Unit Purchase Price | 15 currency units |
| Minimum Order Quantity | 1 unit |
Expected results
| Result field | Expected value |
|---|---|
| Raw EOQ | 707 units |
| Effective Order Quantity | 707 units |
| Orders per Year | 14.1 |
| Annual Ordering Cost | 707.00 |
| Annual Holding Cost | 707.00 |
| Total Annual Inventory Cost | 1,414.00 |
| Annual Purchase Spend | 150,000.00 |
| Total Annual Cost incl. Purchase Spend | 151,414.00 |
| MOQ Constraint Applied? | No — EOQ meets or exceeds MOQ |
Notice that at the optimal EOQ, annual ordering cost and annual holding cost are equal. This is a useful sanity check: if the two figures are significantly different in your result, re-examine your inputs.
Alternative scenario — MOQ constraint active
Change Minimum Order Quantity to 1,000 (paste 1000 into G6 as a value). Expected changes:
| Result field | Expected value |
|---|---|
| Effective Order Quantity | 1,000 units |
| Orders per Year | 10.0 |
| Annual Ordering Cost | 500.00 |
| Annual Holding Cost | 1,000.00 |
| Total Annual Inventory Cost | 1,500.00 |
| MOQ Constraint Applied? | Yes — MOQ exceeds raw EOQ; costs are higher than optimal |
Total inventory cost rises from 1,414 to 1,500 when the MOQ forces a larger order. This quantifies the premium your supplier's minimum imposes on your business.
Frequently asked questions
What if my holding cost is a percentage of unit value rather than a fixed currency amount?
Many finance teams express holding cost as a percentage of unit value — for example, 20% of the unit purchase price per year. Convert it before entering: multiply the unit price by the percentage. If the unit price is 15 and holding cost is 20%, enter 3 (15 × 0.20) in the Holding Cost field. Do not enter 20 or 0.20 directly, because the formula expects a currency amount per unit per year.
Can I use this calculator for items ordered weekly or monthly rather than annually?
The standard EOQ model works on annual figures. If your demand data is weekly or monthly, scale it to annual before entering: multiply weekly demand by 52, or monthly demand by 12. Similarly, make sure your holding cost is an annual figure. Using consistent annual figures throughout is the simplest way to avoid scaling errors.
Why are my ordering cost and holding cost results not equal even though I expect them to be?
At the theoretical EOQ they are equal, but two things can cause a difference in the calculator. First, the formula rounds EOQ to a whole number of units, which introduces a small rounding gap. Second, if the MOQ constraint is active, the effective order quantity is higher than raw EOQ, which shifts the balance toward higher holding costs and lower ordering costs. Both are expected behaviours. A large difference — more than a few percent — usually means one of the inputs is not on an annual basis or a currency amount has been entered as a percentage.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| B10 shows "Awaiting inputs" | G2, G3, or G4 is blank | Paste E2:E6 as values into G2:G6 to test, or submit the web form |
| B10 shows "Holding cost cannot be zero" | G4 is 0 | Enter a positive holding cost; zero causes division by zero in the formula |
| Results show — in multiple cells | An upstream result cell returned an error string | Fix the earliest error cell; downstream cells depend on it |
| EOQ seems very large or very small | Inputs not on consistent annual basis | Confirm D is annual units, S is per-order currency, H is annual per-unit currency |
| MOQ field ignored | G6 is blank or 0 | Ensure G6 has a value of at least 1; paste the default if testing |
| Web form shows no results after submission | Formulas reference E column instead of G | Replace all E2:E6 references in result formulas with G2:G6 |
| Calculator tab not visible in Extensions menu | Add-on not installed or not authorised | Re-install GS2Web SaaS from Google Workspace Marketplace and re-authorise |
For further help, visit the GSheetPress support page. For information about what the Calculator tool can display, see the calculator feature details.
Publish and test your web calculator
Step 1 — Share the spreadsheet
- In your dedicated sample Google Sheet, click Share in the top-right corner.
- Under General access, change the setting to Anyone with the link.
- Set the role to Editor.
- Click Done.
Important: Setting access to Editor means anyone who obtains the spreadsheet link can modify the file. Use only your dedicated sample workbook for this step — never a file containing real business or personal data.
Step 2 — Publish the calculator
- Make sure the calculator tab is selected (click it at the bottom of the screen).
- Go to Extensions → GS2Web SaaS → Calculator → Publish Calculator.
- Wait for the confirmation message to appear.
Step 3 — Find and open the public link
- In the GS2Web SaaS menu, open the Calculator Link sheet (or the equivalent links sheet created by the add-on).
- Locate the row that matches your Sheet Name (the tab you just published).
- Open the URL in column C — this is the public link. Do not use column B, which is the Edit Link.
- If the row does not appear immediately, click Get Links to refresh the sheet.
Step 4 — Test with the worked example
- Open the public calculator link in a browser.
- Enter the worked example values: Annual Demand 10000, Ordering Cost 50, Holding Cost 2, Unit Purchase Price 15, Minimum Order Quantity 1.
- Click Calculate.
- Confirm the results match the expected values from the worked example table: Raw EOQ 707, Total Annual Inventory Cost 1,414.00, and so on.
- Optionally test the MOQ scenario with Minimum Order Quantity set to 1000 and confirm Total Annual Inventory Cost rises to 1,500.00.
Step 5 — Embed on a website (optional)
If you want to embed the calculator on a web page, return to the Calculator Link sheet, copy the code in column D (Iframe), and paste it into an HTML block on your website. The calculator iframe updates automatically when you republish the same calculator — the public link remains unchanged. For more about embedding options, see the table and embed feature details.
Republishing the same existing calculator tab retains its original link, so you can update formulas and labels without breaking any shared URLs or embedded iframes.