How much should you order when demand is uncertain?
Stock out and you lose the sale; over-order and it costs you. This classic newsvendor template shows your profit distribution at a given order quantity, then optimizes the quantity that maximizes expected profit.
Operations Intermediate Monte Carlo Pro engine
After you install, this is the model to open.
Inventory Order Quantity (Newsvendor)
- 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.
This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.
The answer
Profit distribution at an order of 115 units, the newsvendor optimum for these numbers:
- P5
- $101
- Median
- $171
- P95
- $201
- Mean
- $166
At this order quantity, profit ranges from $101 to $201 depending on demand. Switch Sortia to Optimize mode and it searches order quantities to find the one that maximizes expected profit under this exact uncertainty. That is the real decision, made for you.
The model
You sell the lesser of demand and your order, salvage the leftovers, and pay for everything you ordered. Every money figure is per unit, so decide what one unit is and keep the cost, the price and the salvage all counting the same thing. Demand is the uncertain input.
| Cost per unit | $0.75 |
| Price per unit | $2.50 |
| Salvage per unsold unit | $0.10 |
| Order quantity | 115 |
| Demand | 60 – 100 – 150 (uncertain) |
| Profit | simulated |
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
- Should you hold your price, or cut first?Price War: Hold or Cut?
- Which tasks actually set your launch date?Launch Plan: Find the Critical Path
- Which risks actually deserve the money?Project Risk Register, Priced
- Which office wins once you price the commute?Where Should the Office Be?
- Should you announce your format before they do?Standards War: Which Format to Back?
- Which task should you actually chase?Office Move: What Sets the Date?
Every model like this one, and the method behind them: Monte Carlo simulation.