Which reorder point balances cost against stockouts?

Worked example · Inventory · Updated October 2026

In this inventory model with 12 months of uncertain demand, raising the reorder point from 180 to 250 units costs 345 more a year on average and cuts the chance of a year with at least one stockout from 28.0% to 5.1%. A Monte Carlo simulation measures both, policy by policy on the same demand: the best reorder point is the one whose extra holding cost is worth the stockouts it prevents.

At 180 units the policy costs 2,133 a year on average (in the workbook’s currency units; it names no currency). Lowering it to 120 saves only 28 a year and makes a stockout likely (70.0%). Ordering 600 units at a time instead of 400 costs about as much as the higher reorder point but still leaves a 21.6% chance.

Open this model in xellstorm Download the workbook (.xlsx)

The model

The workbook is an ordinary monthly stock plan on one sheet, Plan, with plain formulas such as MIN and IF and no add-in functions. The policy and the costs sit at the top:

Policy and costs in the workbook (sheet Plan), and the demand set in xellstorm
Reorder point `B1`	180 units
Order quantity `B2`	400 units
Holding cost `B3`	0.5 per unit left at the end of a month
Lost sale cost `B4`	8 per unit of demand not met
Order cost `B5`	150 per order
Starting stock `B6`	400 units
Monthly demand `B10:M10`	negative binomial, n = 8, p = 0.05: mean 152 units, standard deviation 55.1 (set in xellstorm, not in the workbook)

Below them, each of the 12 months has a column (B to M) that follows the stock: demand, opening stock, units received and available, units sold and lost, the stock left after sales and at closing, the order placed, and the month’s cost. At the end of each month, if the stock left is below the reorder point, an order for the order quantity goes out and arrives at the start of the next month. Demand the stock cannot meet is lost, not back-ordered. A month costs the holding cost on the stock left, the lost sale cost on each unit not sold, and the order cost when an order is placed.

Three cells sum up the year: Total cost (B21), the fill rate (B22), the share of the year’s demand met from stock, and months with lost sales (B23). A stockout here is a month in which demand is higher than the stock available, so some sales are lost; a year with a stockout is a year with at least one such month.

The downloadable file holds the plan with a fixed placeholder demand in the demand row. The demand distribution, the three outputs, the stockout target and the three alternative policies are set in xellstorm and load with the example, next to the workbook, which stays as you would build it.

Demand: a negative binomial count each month

Each month’s demand (Plan!B10:M10) is drawn on its own from a negative binomial distribution with n = 8 and p = 0.05, in scipy’s parameterization (the number of failures before the n-th success). Its mean is n(1 − p)/p = 152 units a month and its standard deviation √(n(1 − p))/p = 55.1. Demand for a stocked item is a count that often varies more than a Poisson count would; here a Poisson distribution with the same mean would have a standard deviation of only 12.3. The negative binomial allows the extra spread and, unlike a normal distribution, never draws negative demand and has the longer tail on the high side.

The 60,000 simulated months (5,000 years of 12 months) average 152.0 units with a standard deviation of 55.1, matching the formulas. The months are independent: there is no trend or season. For demand that drifts, xellstorm also has time-series inputs for a range, such as a random walk or a mean-reverting series.

Four policies on the same demand

The example compares the base policy (reorder point 180, order quantity 400) with three scenarios, each changing one cell: reorder point 120, reorder point 250, and orders of 600 units. Every scenario runs on the same 5,000 years of demand as the base, a technique called common random numbers, so the differences between policies come from the policies and not from luck in the draws.

Four policies on the same 5,000 simulated years of demand (costs per year)
Policy	Mean cost	P90 cost	Fill rate	Years with a stockout
Base policy	2,133	2,377	99.4%	28.0%
Reorder point 120	2,105	2,688	97.4%	70.0%
Reorder point 250	2,478	2,668	99.9%	5.1%
Order quantity 600	2,465	2,727	99.5%	21.6%

Share of simulated years with at least one month of lost sales. The base policy reorders at 180 units and orders 400 at a time.

P90 is the annual cost that 90% of simulated years stay at or below, and the fill rate is the average over the years. The fill rates all look high, from 97.4% to 99.9%, yet the chance of a year with a stockout runs from 5.1% to 70.0%. The fill rate counts units and a stockout month usually loses only a small part of the year’s demand; the stockout chance counts years in which a customer was turned away at least once. Which of the two matters depends on the business, so look at both.

Where the cost goes

Each year’s cost has three parts: holding the stock left at the end of each month, placing orders, and the sales lost when the stock runs out.

Mean cost per simulated year: holding the stock left at each month end, placing orders, and the demand lost when the stock runs out.

A higher reorder point keeps more stock on hand. At 250, holding cost rises by 397 a year while lost sales fall by only 84, so the lost sale cost of 8 a unit alone does not pay for it: the case for the higher reorder point rests on what a stockout costs you beyond that.

Bigger orders work differently. With 600 units an order, the year needs 3.1 orders on average instead of 4.5, which saves 203 in order costs but adds 562 in holding cost. Fewer orders mean fewer months in which the stock sits just above the reorder point with no delivery coming, but each such month is about as exposed as before: the reorder point, not the order size, sets how much stock has to last a month on its own.

What the extra cost buys

The model already charges 8 for every unit of demand lost. If running out costs more than that, for example in rush deliveries, penalties or customers who go elsewhere, divide each policy’s extra cost by the stockout months it avoids to see what one avoided stockout month costs:

Reorder point 250 costs 345 more a year and avoids 0.27 stockout months a year on average (0.32 down to 0.05). It pays if a stockout month costs you more than 1,291 on top of the lost sales.
Reorder point 120 saves 28 a year (1.3%) and adds 0.75 stockout months a year. It pays only if a stockout month costs you less than 37 on top of the lost sales.
Orders of 600 cost 333 more a year and avoid 0.09 stockout months a year: 3,816 per stockout month avoided. For about the same extra cost, the higher reorder point avoids 0.27.

So the answer turns on one number the workbook does not hold: what a stockout month costs you beyond the lost sales. If a stockout month costs between 37 and 1,291 on top of the lost sales, the base reorder point of 180 has the lowest total cost of the four policies; above 1,291, reorder point 250 does, and only below 37 does reorder point 120. Bigger orders never come out best.

Safety stock: the reorder point above an average month

At the end of a month without an order, the stock left has to cover the whole next month on its own: the earliest delivery is at the start of the month after. The reorder point therefore has to cover one month of demand, and the part above an average month of 152 units is safety stock: 28 units at the base reorder point of 180, or 0.51 standard deviations of monthly demand, and 98 units (1.78 standard deviations) at 250. A reorder point of 120 is 32 units below an average month, which is why stockouts become likely.

The textbook formula, safety stock = z × σ, with σ the standard deviation of demand over the time an order must cover (here one month), takes z from a normal table for the chance of running out in one replenishment cycle that you are willing to accept. The simulation measures the consequences directly: how often a year sees a stockout, the fill rate and the cost, on demand that is skewed and never negative.

Same demand, year by year

Because every policy sees the same demand, the years can be compared one by one. Reorder point 120 is cheaper than the base in 59.5% of years, and the median difference is a saving of 150. But it costs more in 30% of years, by up to 2,989: in every one of those years it loses more sales than the base, at 8 a unit.

Each bar counts simulated years by how much more (right) or less (left) the year costs with reorder point 120 than with the base policy, on the same demand. Highlighted: years in which 120 costs more; years with the same cost are in the plain bar next to the line.

That long right tail is why the lowest mean cost comes with the highest P90 of the three reorder points: 2,688, against 2,377 for the base and 2,668 for reorder point 250. Measured at P90, the policy that is cheapest on average is the most expensive of the three.

The same pairing makes the fill rates easy to compare. Reorder point 250 has a fill rate at least as high as the base’s in every one of the 5,000 years, and a higher one in 23.7%; reorder point 120 never has a higher fill rate than the base. Bigger orders are not as clear-cut: they lower the fill rate in 7.2% of years, because the stock then runs low in other months, sometimes just before a month of high demand.

Try it yourself

Open the model in xellstorm. The demand range, the three outputs, the target (months with lost sales above zero) and the three scenarios, named Reorder at 120, Reorder at 250 and Bigger orders, are already set; there is nothing to install and no sign-up, and the workbook is calculated in your browser.
Run it. The base runs first, then each scenario on the same random draws and the same number of trials. With the example’s seed (1) and 5,000 trials you get the numbers on this page.
On the Results step, pick Total cost, Fill rate or Months with lost sales. The Scenarios panel draws each policy’s S-curve on the same axes, and its table gives the mean, P10, P90, the chance of the target (on months with lost sales), the difference from the base, and the share of trials in which each scenario is higher or lower than the base. The Trial by trial panel can put a scenario’s cost against the base’s, trial by trial.
Change a scenario’s reorder point on the Distributions step and run again. To see a whole range of reorder points at once, use the Optimize step: choose Sweep, make the reorder point cell (Plan!B1) the decision, and add the mean of Total cost and the probability that months with lost sales are above zero as statistics.

Can I do this in plain Excel?

Partly. Excel has NEGBINOM.DIST for negative binomial probabilities but no inverse function to draw from it, so each month’s demand needs a workaround, such as a lookup table of cumulative probabilities with RAND(), or for whole n the sum of n geometric draws. Comparing policies on the same demand also means freezing those random numbers, for example by pasting them as values, before switching the reorder point. Monte Carlo simulation in Excel shows the general approach, and what changes when a tool runs the workbook for you.

Questions

What is a reorder point?

A reorder point is the stock level that triggers a new order: when the stock falls below it, you order more. It has to cover the demand expected until the order arrives plus a buffer, the safety stock, for demand above average. In this model the stock is checked only at the end of each month and an order arrives at the start of the next, so stock left without an order has to last a whole month: the reorder point covers one month of demand.

What is the difference between fill rate and the chance of a stockout?

The fill rate is the share of demand met from stock over a period; the chance of a stockout is the share of periods in which the stock runs out at least once. They can tell different stories: in this model the base policy has a fill rate of 99.4% and still a 28.0% chance of a year with a stockout month.

Why not simply choose the policy with the lowest average cost?

The average cost counts only the costs in the workbook and hides how bad the bad years are. Here the lowest average cost, reorder point 120, comes with a 70.0% chance of a year with a stockout and the highest P90 cost of the three reorder points. Check the P90 (see P50, P80 and P90) and the stockout chance next to the mean.

What are common random numbers?

Common random numbers means running every scenario on the same random draws, here the same 5,000 years of monthly demand. Differences between scenarios then come from the scenarios themselves, and can be compared trial by trial, instead of being blurred by different luck in each run. xellstorm runs every scenario this way.

How do I choose the order quantity itself?

The order quantity is a decision under uncertain demand, like the reorder point. For a recurring policy like this one, compare quantities as scenarios or sweep them on the Optimize step, where two decision cells, such as the reorder point and the order quantity, can be swept together. For a single order before a selling season, see how many units to order when demand is uncertain.

xellstorm is a browser-based Monte Carlo simulation tool for Excel models: no add-in, and the workbook never leaves your computer.