Monte Carlo Simulation Analysis
of HealthCeuticals
By Sam Savage
© Copyright 2002, Sam Savage
Introduction
The following is a discussion of Monte Carlo simulation analysis as
applied to the HealthCeuticals example discussed in Dr. Savage’s November
2002 article in the Harvard Business Review. If, after completing the
explanation below, you wish to run the described analysis yourself,
click here to
download a trial version of XLSim® and here
to download the Excel model, HealthCeuticals.xls, which contains more detailed
instructions.
HealthCeuticals sells a perishable antibiotic. Although monthly demand fluctuates, there is no perceptible seasonality. For many years the average demand has been 5,000 units so this is the quantity that HealthCeuticals stocks. Their inventory operating costs are calculated in the following worksheet.
There is a per unit expiration cost for inventory remaining at the end
of the month, and a per unit air freight cost when demand exceeds inventory.
Note that when demand equals the average of 5,000, their operating
cost is zero, so it would be natural to assume that in the long run, their
average cost will be zero.
Simulating Cost
Monte Carlo simulation in effect bombards the demand cell (A2) with hundreds
or thousands of random demand quantities, while recording the values of
the overall cost cell (C8). In this example the random numbers will be
drawn automatically from historical demand data in a process known as
resampling. The histogram below displays the distribution of the demand
data that will be randomly entered into cell A2.
The average demand of 5000, is where this figure would balance if it were made of a solid material. Note in this case, this is not the same as the mode (the location of the highest bar).
The resulting distribution of overall cost recorded from cell C8 is shown below. Because a positive cost is incurred whether demand is greater or less than average, zero is not the average case, but the best case.
Thus we see that the cost associated with the AVERAGE demand of 5,000 units ($0), is NOT the AVERAGE cost ($160,000).
Optimizing Inventory
The next step is to perform repeated simulations with different stocking
levels to find the optimum. In the graph below, the average cost for various
stocking levels appears in magenta.
This shows that average cost is minimized by stocking either 6,000 or 7,000, but this is not the whole story. The dark blue line is perhaps even more important, as it shows the 95th percentile of cost. That is, there is a 5% risk of cost being as greater than this amount. If 7,000 were stocked, there is a 5% chance that costs will exceed $450,000. If 8,000 is stocked, the average cost increases slightly, but the 5% value at risk, as it is called, drops to $300,000. Thus instead of a single optimal stocking level, we see that there is a trade-off between average cost and risk between 7,000 and 8,000 units. However, stocking 5,000 units to meet average demand is way off the mark!