How many units should you order when demand is uncertain?
Order the quantity that demand stays below 66.7% of the time, the critical fractile: with demand averaging 1,000 units (standard deviation 200) and each unit selling for 10, costing 4 and fetching 1 if left over, that is 1,086.1 units, for an expected profit of 5,345.5. A Monte Carlo search of the example workbook picks 1,090, the step nearest that optimum.
The critical fractile is where the chance of selling one more unit, times what it earns, just matches the chance of it being left over, times what it loses; when a lost sale forgoes more than a leftover unit costs, that point lies above the median demand, the level demand exceeds half the time. In this example demand is normal, so its median is its mean, and amounts are in the workbook’s currency units. The search runs over orders of 600 to 1,600 units in steps of 10; at 1,090 the mean profit is 5,345 ± 31 (95% interval, 5,000 simulated seasons), and demand exceeds the stock in 32.6% of seasons.
The model
This is the newsvendor problem: one order placed before a selling season, demand known only as a range of possibilities, unsold units sold off cheaply at the end, and demand beyond the order lost. The workbook has one sheet, Model, with labels in column A and values in column B:
Order quantity, B1 | 1,000 units (the decision) |
|---|---|
Price, B2 | 10 per unit sold |
Unit cost, B3 | 4 per unit ordered |
Salvage value, B4 | 1 per unit left over |
Demand, B6 | normal, mean 1,000 units, standard deviation 200 |
Four formulas turn an order and a demand into the season’s result: units Sold, =MIN(B1,B6); units left over, Leftover, =MAX(B1-B6,0); Profit, =B2*B8+B4*B9-B3*B1, sales plus salvage minus the cost of the whole order; and Lost sales, =MAX(B6-B1,0), the demand the order could not meet. A stockout here is a season with lost sales: demand above the order. There are no add-in functions.
The downloadable file holds these formulas, the order of 1,000 units and a fixed placeholder in the demand cell. The demand distribution, the two outputs (Profit and Lost sales), the stockout target and the optimization study are set in xellstorm and load with the example, next to the workbook, which stays as you would build it.
A normal distribution allows demand below zero in principle, but zero lies 5 standard deviations below the mean, a chance of about 1 in 3.5 million; the lowest of the 5,000 simulated demands is 200 units. Demand is drawn as a continuous number, so sales, leftovers and the goal seek’s order below can be fractions of a unit; round to whole units when you place the order.
Why order more than the average demand?
Think about the last unit of the order. If it sells, it earns the price minus its cost, 10 − 4 = 6. If it is left over, it loses its cost minus the salvage value, 4 − 1 = 3. Adding the unit pays as long as its chance of selling times 6 is more than its chance of being left over times 3: as long as the chance of selling it is above 3 / (6 + 3) = 33.3%. At an order equal to the average demand, 1,000 units, the next unit sells half the time, so larger orders pay. They stop paying where demand stays below the order 66.7% of the time.
The simulation shows the same arithmetic season by season. On the same 5,000 simulated seasons, ordering 1,090 units instead of 1,000 earns exactly 540 more (90 × 6) in the 32.6% of seasons in which demand reaches 1,090, and exactly 270 less (90 × 3) in the 50% in which demand stays at or below 1,000; in the seasons between, the difference lies between the two. On average the larger order earns 63.5 more a season, as the exact formula below says (63.5).
The exact answer: the critical fractile
The rule has a name, the critical fractile: the best order Q* is the demand level that is not exceeded with probability (price − cost) / (price − salvage), here (10 − 4) / (10 − 1) = 0.667. For normal demand, Q* = mean + z × standard deviation, where z = 0.4307 is the point of the standard normal distribution with 66.7% below it (=NORM.S.INV(6/9) in Excel). So Q* = 1,000 + 0.4307 × 200 = 1,086.1 units.
The expected profit has a closed form too. For any order Q, with z = (Q − mean) / standard deviation, it is (price − salvage) × (mean − standard deviation × L(z)) − (cost − salvage) × Q, where L(z) = φ(z) − z × (1 − Φ(z)) is the standard normal loss function, with φ the standard normal density and Φ its cumulative distribution (in Excel, =NORM.S.DIST(z,FALSE)-z*(1-NORM.S.DIST(z,TRUE))); standard deviation × L(z) is the expected lost sales. At Q* this reduces to (price − cost) × mean − (price − salvage) × standard deviation × φ(z) = 5,345.5, and the chance of a stockout is 1 − 0.667 = 33.3%. Ordering the average demand of 1,000 units instead gives an expected profit of 5,281.9.
Searching the order quantity by simulation
The formulas need a model this simple (one product, one order, fixed prices) and a demand distribution whose quantiles and expected lost sales have formulas; a simulation needs only a way to draw demand, and this example checks that it finds the same answer. The optimization study that loads with the example makes the order quantity (B1) the decision and tries every order from 600 to 1,600 units in steps of 10, 101 candidates, to maximize the mean of Profit. One constraint applies: the chance of a stockout, the share of trials with Lost sales above zero, must be at most 50%. Each candidate is simulated with 1,000 trials, the three best are run again with 5,000, and every run uses the same random draws of demand. These common random numbers make candidates differ only by the order, not by luck in the draws.
| Order, units | Mean profit | Exact expected profit | Chance of a stockout |
|---|---|---|---|
| 1,090 | 5,345 ± 31 | 5,345.4 | 32.6% |
| 1,100 | 5,344 ± 31 | 5,344.0 | 30.9% |
| 1,110 | 5,341 ± 32 | 5,340.9 | 29.1% |
The re-run picks 1,090 units: a mean profit of 5,345 ± 31, against an exact 5,345.4 at that order, and a stockout in 32.6% of seasons (exact: 32.6%). It is the step nearest the exact optimum of 1,086.1, and the exact expected profit of 5,345.5 lies inside its interval.
The intervals of the three best orders overlap almost completely, yet the winner is clear. Each interval is xellstorm’s estimate of the noise in that candidate’s mean, from how much the mean varies between 20 batches of its trials. With Latin Hypercube sampling, xellstorm’s default, which spreads each run’s draws evenly over the demand distribution, a whole run’s mean is steadier still than its interval suggests: run again with 3 other seeds, the mean profit at 1,090 units stays within 0.2 of this run’s.
What decides between the candidates is their difference, and the same draws push all the candidates up or down together, so their differences are much steadier than the intervals. Compared season by season, 1,090 beats 1,100 by 1.43 (95% interval 0.47 to 2.39), an interval that excludes zero: better beyond chance, but by little. The exact difference in expected profit is 1.43. Near the top the profit curve is flat, so being a step off costs almost nothing; at the ends of the range, 600 and 1,600 units, the expected profit falls to 3,585 and 4,199.
The search itself put 1,100 first, with a mean profit of 5,370.3 against 5,369.9 for 1,090. It uses the first 1,000 of the 5,000 trials, and they happen to draw slightly more demand, 1,003.7 units on average against 1,000.0 over all of them, which raises the profit of the larger orders near the top and moves the peak up a step. The re-run with 5,000 trials settles it, and xellstorm says so when the re-run changes the winner.
The constraint, and what fewer stockouts cost
The stockout constraint does not bind here. It rules out orders of 1,000 units or fewer (at 1,000, 51.6% of the search’s trials run out): with normal demand, an order at the mean runs out half the time. The best order already brings the chance of a stockout down to 32.6%.
If a stockout in 32.6% of seasons is too often, for instance because customers who find the shelf empty do not come back, choose the chance you accept and let a goal seek find the order. xellstorm’s goal seek halves the range of orders until the statistic hits the target. With 5,000 trials per evaluation it finds 1,256.25 units after 7 evaluations, where exactly 10% of the trials run out. The exact answer is the demand level with a 90% chance of not being exceeded, 1,000 + 200 × NORM.S.INV(0.9) = 1,256.3 units.
| Order, units | 1,000 | 1,090 | 1,256.25 |
|---|---|---|---|
| Mean profit | 5,282 | 5,345 | 5,146 |
| P10 profit | 3,695 | 3,425 | 2,926 |
| Chance of a stockout | 50% | 32.6% | 10% |
| Mean units left over | 80 | 133 | 266 |
| Mean lost sales, units | 80 | 43 | 9 |
Cutting the chance of a stockout from 32.6% to 10% means ordering 166 more units, of which 133 are left over in an average season, and gives up 199 of mean profit a season (3.7%; the exact difference in expected profit between the two orders is 199.4). It also lowers the P10 profit, the level that 90% of seasons reach, from 3,425 to 2,926: in a weak season, a larger order leaves more stock unsold (see P50, P80 and P90). Ordering just the average demand has the highest P10 of the three but runs out in 50% of seasons. Whether fewer stockouts are worth the price is a business decision; the simulation puts a number on the price.
Try it yourself
- Open the model in xellstorm. The demand distribution, the outputs Profit and Lost sales, the target (Lost sales above zero, the chance of a stockout) and the optimization study are already set; there is nothing to install and no sign-up, and the workbook is calculated in your browser.
- Run it. At the saved order of 1,000 units, with the example’s seed (7) and 5,000 trials, the mean profit is 5,282 and the chance of a stockout 50%.
- Open the Optimize step. The order quantity (
Model!B1) is the decision cell, from 600 to 1,600 in steps of 10; the objective maximizes the mean of Profit, and the constraint keeps the probability of Lost sales above zero at most 50%. Press Optimize: the app evaluates every order with 1,000 trials, re-runs the best three with 5,000, compares the winner with the runner-up trial by trial, and charts mean profit by order quantity with its 95% band. - For the order with a 10% chance of a stockout, choose Goal seek: set the statistic to Probability, the condition to > with threshold 0 on Lost sales, and the target to 0.1; clear the Step field of the order quantity (it holds 10 from the study), set Trials per evaluation to 5,000, and press Seek goal.
- Press Set as fixed cells and run the simulation again for the full results at the chosen order, or Add as scenario to compare it with the saved order on the same draws.
Can I do this in plain Excel?
For this exact case, yes: with normal demand, =NORM.INV((B2-B3)/(B2-B4), 1000, 200) returns the critical-fractile order of 1,086.1 directly. For skewed demand you swap NORM.INV for that distribution’s inverse (LOGNORM.INV, GAMMA.INV, or PERCENTILE.INC on past sales), but the rule itself stops applying once several products share a budget or a warehouse, there is a minimum order size, or a markdown price depends on how much is left. A simulation handles those, but optimizing a simulated model in plain Excel is awkward: every recalculation draws new random numbers, so a data table or Solver compares order quantities on different draws and chases the noise. Evaluating every candidate on one frozen set of draws is what xellstorm does for you. Monte Carlo simulation in Excel shows the general approach.
Questions
What is the newsvendor model?
The newsvendor model is the classic problem of choosing how much to stock for a single selling period before demand is known, when leftover units are sold off below cost and demand beyond the stock is lost. Its best order is the critical fractile of demand: the quantity that demand stays below with probability (price − cost) / (price − salvage value). The name comes from a newspaper seller deciding each morning how many copies to buy; the same problem arises with seasonal goods, fresh food, event capacity and one-off production runs. For stock that is replenished again and again, see which reorder point balances cost against stockouts.
When is the best order below the average demand?
The best order is below the average demand when a leftover unit costs more than a lost sale forgoes, that is, when the unit cost minus the salvage value is larger than the price minus the unit cost. Then the critical fractile is below one half, and for a symmetric demand distribution such as the normal, the best order is below the mean. Thin margins and unsold stock that is worth nothing push the order down; high margins and a good salvage value push it up.
Why evaluate every order quantity on the same random numbers?
Evaluating every order quantity on the same simulated demand, known as common random numbers, makes the differences between candidates come from the orders alone. In this example the 95% intervals of the three best orders overlap almost completely, yet the season-by-season difference between the best two has an interval of 0.47 to 2.39, which excludes zero. Under plain Monte Carlo sampling, fresh draws for each candidate would make a search chase noise: measuring the difference between these two orders as precisely would take about 2,000 times as many trials as on common draws.
What if demand is not normal?
If demand is not normal, the critical fractile still holds, but the order has to come from that distribution’s quantile function, and formulas like the expected profit above no longer apply. A simulation needs only the distribution itself: in xellstorm, choose another distribution for the demand cell, such as lognormal, gamma, PERT, Poisson or negative binomial, fit one to past sales on the Distributions step, or let the workbook compute demand from other uncertain inputs, then run the same optimization.
Related
- Inventory
Which reorder point balances cost against stockouts?
Raising the reorder point from 180 to 250 units cuts the chance of a stockout year from 28.0% to 5.1% and adds 345 to an average annual cost of 2,133. - Guide
P50, P80 and P90: what they mean and which to budget at
P80 is the cost 80% of simulated outcomes stay under. Six building cost items’ P80s add up to 2,843, but their sum’s P80 is 2,756 (USD thousands). - Guide
Monte Carlo simulation in Excel, with or without add-ins
Three ways to run a Monte Carlo simulation on an Excel model: RAND() and a data table, an add-in, or a browser tool with no add-in. PERT formula included.
xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.