Monte Carlo simulation for business purposes is a great tool to have in your toolbox. If you’re a business owner or a product line owner in a larger enterprise, chances are you’ve created a spreadsheet to model a business case or something similar. Anytime you have a spreadsheet that contains estimates that are inputs to whatever you’re calculating there is a good chance that Monte Carlo simulation can be used.
For example, a pro forma business case might calculate cash flow for the next five years. Or, maybe you’re considering an investment in equipment and want to know the net present value of the investment. As a result, these types of spreadsheet models can be simulated by replacing the cells containing estimates with a random variable.
A random variable is something whose possible values follow an underlying probability distribution. Before you get scared away by probability distributions, don’t worry, we’re going to keep it simple. You’ll see that for most business purposes, the probability distributions that are most applicable are simple to apply and understand.
Probability Distributions Based on Estimates
Monte Carlo simulation can use any probability distribution as an input. Sometimes these distributions are determined by software that fits the distribution to historical data. When using Monte Carlo simulation for business, there may be no data available. Therefore, we have to rely on intuition to pick a distribution and estimates to define it. There are four common distributions that are used with estimates.
- Trapezoidal
- Triangular
- Uniform
- PERT
Let’s look at each in more detail so we understand what they mean.
The Trapezoidal Distribution
The trapezoidal distribution’s probability density function is shown below. Again, don’t be scared away by the mathematical terms. A probability density function (PDF) is one way of showing how probability is distributed for each possible value of a random variable.
Probability density is plotted on the vertical axis. The possible values of the random variable (our input in a spreadsheet cell) are on the horizontal axis. In addition, the area under the curve sums to 1. This should make sense since the probability of each possible outcome must all add up to 1 for all possibilities.
The trapezoidal distribution is shaped like; you guessed it, a trapezoid. Pretty simple isn’t it? The trapezoidal distribution requires four parameters to define it. These parameters are really estimates that we determine.
- Minimum value (1): The minimum possible value.
- Most likely 1 (1.5): The lowest value that is most likely. Note that between 1.5 and 2.5, probability is highest. Therefore, any value between these two points has the highest probability.
- Most likely 2 (2.5): The highest value that is most likely.
- Maximum value (3): The maximum possible value.
The trapezoidal distribution is useful when you estimate that a variable has an equal likelihood over a range of values. In addition, it allows for outliers with a smaller likelihood. For example, we might estimate that revenue is equally likely to be 10 to 15 million. We also estimate the worst case scenario to be 7 million and the best case scenario to be 20 million. In this example, the parameters are:
- Minimum value = 7
- Most likely 1 = 10
- Most likely 2 = 15
- Maximum = 20
The Triangular Distribution
The triangular distribution is a special case of the trapezoidal distribution. In this case, most likely 1 and most likely 2 are the same value. As a result, we get a triangular shape for the PDF.
The triangular distribution has three parameters:
- Minimum value (1): The minimum possible value.
- Most likely value (2): The value with the highest probability of occurrence.
- Maximum value (3): The maximum possible value.
The Uniform Distribution
The uniform distribution is also a special case of the trapezoidal distribution. In this case, minimum = most likely 1 and maximum = most likely 2. Therefore, every outcome has an equal probability of occurrence.
The uniform distribution has two parameters:
- Minimum value (1): The minimum possible value.
- Maximum value (2): The maximum possible value.
The PERT Distribution
The PERT distribution finds most use in simulating project critical paths where each task’s time to completion is modeled as a random variable that follows the PERT distribution. It can also be used for other purposes where the random variable is modeled from estimates.
With PERT, the probabilities are smoothed around the most likely value. As a result, more of the probability is centered on the most likely value.
The PERT distribution requires three parameters:
- Minimum value (1): The minimum possible value.
- Most likely value (2): The value with the highest probability of occurrence.
- Maximum value (3): The maximum possible value.
An Example of Monte Carlo Simulation for Business
You run a service business and you’re considering expansion of your business. Most of the additional expense involves hiring labor to perform the service. It would be nice to know the possibilities and probabilities of the financial outcomes of such an expansion. Since it's an expansion, we will only concern ourselves with incremental revenue and expenses.
Single Point Estimate Model
First we'll consider a very simple model that uses single point estimates for revenue. That is, we are only using one value for each year's revenue.
The single point model shows an additional five year cash flow of 132,615. What we don't know is the probability of negative cash flow or the probability of other cash flows.
Stochastic Model
Now we're going to replace revenue with random variables. We'll assume a triangular distribution and use our single point estimates as the most likely values. Also, let's assume the best and worst-case values are +/- 30% of the most likely values.
We'll use Simulation Master to do the simulation. We can replace the revenue cells (B7 to F7) with random variable functions. These functions will sample a value from the probability distribution each time the worksheet is recalculated.
Simulation Results
We'll simulate the model for 25,000 iterations. The software will recalculate the sheet which generates new values for each revenue. Then the total cash flow (cell B12) is calculated and recorded. Finally, this is repeated 25,000 times.
The output function value is the five year total cash flow. First, let's find the probability of negative total cash flow. Looking at the probability analysis below, we have a 3.4% chance of negative cash flow. That's pretty low so we have a good chance of making money with the expansion. However, expansion comes with new problems and headaches.
Let's say we want at least 150,000 of additional cash flow over the next five years to make the expansion worthwhile. We can do another probability analysis to find the probabilities above and below 150,000. In this case, we have a 40.4% chance of making at least 150,000. Therefore, our expansion isn't such a slam dunk.
Comparison of Methods
The single point model shows a cash flow of 132,615. Therefore, we could reject the expansion since it's less than 150,000. Otherwise, we could say it's close and move ahead. With the stochastic model we have probabilities of outcomes to make more informed decisions.
A Final Point
You might be asking why do a simulation when we can just plug in worst case estimates and calculate the model to see how bad things could turn out? Similarly, we could plug in best case estimates and find out how great it will be. When we do this, we don’t know the probability for the worst or best case outcome. Chances are that every estimate in the model will not be good or bad to the extreme at the same time. Monte Carlo simulation for business cases gives us a picture of the probabilities involved and allows for more reasoned decisions.
If you are new to Monte Carlo simulation, check out the Basics of Monte Carlo Simulation.
To go more in-depth on Monte Carlo simulation topics, click the Monte Carlo Simulation post tag for a lot more information.
Excel is a registered trademark of Microsoft Corporation. Used with permission from Microsoft.