If you work in finance, operations, or investing, chances are that you’ve had to build a model that shows your business under some future operating conditions. Maybe you are extrapolating profitability, capacity, or growth for the purposes of preparing your operating position, or to make financing and investing decisions.

Every now and then your model will be subject to some type of random variable, such as the price of a commodity, growth volatility, failure rates, etc... It can be sufficient to estimate a future value of your random variable and document your assumptions in your model. However, sometimes the random variable can have significant implications on your business which are time sensitive. In this case, knowing the “expected” future value of your random variable is not enough. For example, if your business is growing at a rate of 10% per year and you are at 80% of your business’ capacity, there is a probability that within the next year you will hit your capacity limit. If you know the likely outcomes you can formulate strategic options to meet future challenges.

Monte Carlo simulations allow you to insert a random variable in your model and simulate many iterations of that variable. At the end you can get not just your expected value, but a distribution of outcomes. This is precisely the type of scenario I am modeling in this blog post. Below I will go through the question that needs modeling, the historical data, calculating descriptive statistics, estimating future data, and finally running and interpreting the Monte Carlo simulation.

(Note that there are simpler examples of Monte Carlo simulations online, but I wanted to show you an example that closely resembles a real business problem.)

The Big Question

For the purposes of this blog post I am asking the following question:

“With 4 years of strong growth, and my current demand reaching 80% of capacity during peak summer months, what are the chances that I will run out of capacity within the next 12 or 24 months?”

The Data and Random Variable

The random variable for this model is the demand growth for widgets, per month, year over year. The demand is stated in average unit widgets sold per day, during a given month. The last 4 years of a theoretical demand history looks as follows:

In percentage terms, the growth curve for the 48 months looks like:

The assumed maximum capacity of my theoretical business is 8870 widgets per day on average. As you can see from the data, in August 2016, I needed to produce roughly 7,000 widgets per day, which is 79% of the current capacity limit.

Calculating Your Descriptive Statistics

Descriptive statistics allow you to see how your data compares to an assumed standard distribution. For the purposes of the above example, I have assumed that the data is normally distributed (feedback welcome). The descriptive statistics for the data are as follows:

The statistics that matter for our estimations are the mean and standard deviation statistics, which are 0.119 and 0.072, respectively.

Estimating Future Data

Using Excel, you can now estimate future data using the formula NormalValue, which takes your mean and standard deviation as input variables and calculates a corresponding random variable. Using this function, I calculated monthly estimates for 2017 and 2018 to extend the data set I presented above. For each year, I highlight the maximum daily demand, which is the result that will test whether I reach my capacity limit or not. My expected maxima for 2017 and 2018 are 7834 widgets and 9128 widgets, respectively. That equates to 88% capacity in 2017 and 103% capacity in 2018. This means that I expect to surpass my capacity limit in 2018, given current growth trends. But how likely are these outcomes?

Simulating Outcomes

As I mentioned earlier, calculating an outcome or a statistical mean does not completely answer my initial question. For that, I will need to simulate the random variable over many iterations so see what other outcomes are likely.

To do this, I can use a tool such as RiskAmp Monte Carlo Simulator for Excel. Once your data is setup like I did in this post, you can select your data point of interest (the maximum daily demand for a given year) and run your simulation.

I chose to run the simulation over 1000 iterations. In my example, the maximum demands now switch slightly to 8491 in 2017 and 9434 in 2018. While this is itself useful, what I’m more interested is the distribution of outcomes. Your Monte Carlo simulation can give you a histogram of outcomes, so that you can see your results over different quartiles or percentiles. A histogram with the simulation outcomes is shows below.

The above data suggests that demand between 7400 widgets per day and 8800 widgets per day make up the 95th percentile of outcomes. That is, there is roughly a 5% chance that I will reach or exceed maximum capacity in 2017. When I re-run the simulation for 2018, I find that demand under my capacity of 8870 widgets per day only make up the 10th percentile of outcomes. This means that I have roughly a 90% chance of exceeding my capacity limit in 2018.

Decision Time

Having answered my original question, I now have the tools to insert forecasts into my model. If I expect the market conditions to remain roughly the same as they have been over the past 4 years, I can safely assume that I will only have excess capacity to take me through 2017, and even then I have a small but real probability that I will run into a capacity limit condition. In 2018, I will very likely be out of capacity. This now begs the question of what are my strategic options to deal with demand that is higher than my capacity?