How much should you order each time?
The square root formula gives you one number and tells you nothing about what happens either side of it. That matters, because the bottom of the total cost curve is remarkably flat, and knowing how flat is what lets you round to a whole pallet without guilt. This sweeps ten order sizes and prices all three costs at each one.
Operations Starter Data Table free
After you install, this is the model to open.
How Much to Order Each Time (EOQ)
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
Ten rows of three costs, with the two components crossing exactly where the total bottoms out:
- Economic order quantity
- 600 units
- Total annual cost
- $1,800 at the optimum
- Ordering and holding
- $900 each, exactly equal
- Cost of being 100 out
- under $31 a year
At the optimum the two costs are identical at $900 each, which is the whole idea behind the formula and the thing a single answer never shows you. The practical finding is the shape of the curve rather than its minimum. Ordering 500 costs $1,830 and ordering 700 costs $1,821 against $1,800 at the exact answer, so being 100 units either side costs under $31 a year. Round to a full pallet, a case quantity or a truckload and you lose almost nothing. Get properly wrong and it does bite: 200 units a time costs $3,000 and 1,500 costs $2,610. One caution the sheet spells out: the check cell only agrees with the order quantity because the quantity currently holds the right answer, so after changing demand or either cost, copy the check back into the order quantity to re-center the sweep.
The model
One input block and a one-variable data table. Ordering cost falls as you order less often, holding cost rises as you keep more stock on hand, and total cost is the sum of the two. A separate cell computes the textbook square root answer independently, so the sheet checks itself.
| Annual demand | 12,000 units |
| Cost to place one order | $45 |
| Holding cost | $3 a unit a year |
| Order quantity | 600 units, swept by the data table |
| Order sizes tested | 200 up to 1,500 |
| Reported at each size | ordering cost, holding cost, total cost |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Which plant should ship to which store?Which Plant Ships to Which Store?
- How many servers does the queue actually need?How Long Will the Line Get?
- Can you actually keep your wait-time promise?What Shape Are Your Wait Times?
- Does the night shift really produce less?Shift or Line: What Moves Output?
- Is one line underfilling, or is that just spread?Are Two Fill Lines Filling the Same?
- Two machines, same average. Which one wanders?Is the New Machine More Consistent?
Every model like this one, and the method behind them: What-if analysis in Google Sheets.