A Monte Carlo simulation allows an organization to estimate the probability of several different outcomes when faced with a decision. This tool, which has uses beyond finance, will help your company understand the ramifications of different courses of action under uncertainty. Not only can the most likely scenario be derived, but also realistic optimistic and pessimistic scenarios.
Though a Monte Carlo simulation has wide-ranging applications for businesses big and small, simple and complicated – it is an advanced analysis. If you’re smart enough to start and manage a business you’re smart enough to use this worksheet. But, realistically speaking, I know that not everybody is going to want to delve into something this advanced.
If you’re an owner or a manager who’s always pushing the limits of your capabilities and yearns to have a better understanding of the environment you operate in – then this is for you. If you’re just coasting, so to speak, then I wouldn’t even bother getting into this.
A Monte Carlo simulation breaks very complex problems into manageable parts. The results of this simulation will give you and your company better insights into the environment you work in, and your company’s potential.
Looking for more spreadsheet templates?
Monte Carlo simulation example and download
Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.
Monte Carlo simulation example worksheet
Just as with the linear programming worksheet, I have to qualify myself here and clarify that I’m not an expert on Monte Carlo simulations. Monte Carlo simulations can take many different forms and can involve concepts that are beyond my grasp. All I can do, however, is do my best to convey what I know to you.
A Monte Carlo simulation can take many forms. At its foundation, it is a simulation of many different potential outcomes based on the Values you enter and the Probabilities that you assign those Values.
If you saw Avengers: Infinity War, then you might remember Doctor Strange saying that he looked into the future at 14,000,605 outcomes. You can think of a Monte Carlo simulation in the same way. It looks into the future at many different possible outcomes. Though due to Excel’s limited capabilities, you won’t be able to look at 14 million+ outcomes. But, you can certainly look at several thousand or more.
Why look at a bunch of different potential outcomes? Well, basically, so that you can know the likelihood of each of them. You still don’t know which outcome will happen for sure. But just as with any of the SpreadsheetsForBusiness.com planning and analysis workbooks, you’ll be better prepared and able to act proactively rather than reactively.
How this Monte Carlo simulation is constructed
First, the nature of the simulation and the variables and components that make it up must be determined. Then for each variable/component combination, an appropriate amount of potential Values and their Probabilities must be determined. Next, an appropriate amount of simulations can then be run to generate a distribution of potential outcomes.
With this information, your company can then understand what the worst-case, best-case, and most-likely outcomes are. If you don’t like what you see, then your company can refer back to the Inputs. You can decide if the Values and Probabilities need to be changed. Or, maybe, if the entire project is ill-conceived and you need to wipe the slate clean and start from scratch.
Once action has been taken on the simulation, the initial inputs can be referred back to, so as ascertain if the assigned Probabilities were appropriate. This will provide insight when performing a similar exercise in the future.
Now we’ll look at the Inputs and Outputs of the Monte Carlo simulation in a little more detail.
Input
The Monte Carlo simulation worksheet is it similar to most of the other ‘quantitative method’ worksheets offered by SpreadsheetsForBusiness.com. Frankly, the hard work goes into building these models. Once they’re built the effort needed to use them is minimal.
In this case, only three pieces of information are needed (though some of them are needed many times). The Number of simulations to run, the Probabilities, and the Values.
Number of simulations to run
As mentioned earlier, a Monte Carlo simulation basically runs through a large number of scenarios and gives insight into the average and outliers that you could expect to happen based on the Probabilities and Values you entered. So the higher the Number of simulations you run – the Average scenario might change a little bit, but the extreme outliers (Pessimistic and Optimistic scenario) might change quite a bit.
The way this model is built you can run anywhere from 1 to 1,000 simulations. In spreadsheet software (Excel, Google Sheets, OpenOffice Calc) you run the risk of bogging down your system if you try to run too many simulations. Though, as time goes on, improvements in computing power tend to make this less of an issue.
So, again, enter as many simulations as you like. Toy with it a little bit. The more simulations you run, the greater the likelihood of extraordinary results.
Stages
If you were making your own Monte Carlo simulation, you could simulate whatever you wanted. Since I have to provide some context in order to make a reasonable example, I chose to look at the life cycle of a new product. You’ll see that the stages are titled Development, Introduction, Growth, Maturity, and Decline.
Remember, not every Monte Carlo simulation has to be about the life cycle of a new product. It can be about anything. What defines a Monte Carlo simulation is, again, the use of Probability and a high Number of simulations.
Variables
So, the stages run horizontally along the top of the Input table. The variables run vertically along the left side of the table.
In this example, the variables are Length of stage (in years), Demand per year (in units), Sales price (per unit), COGS amount (per unit), and Operating expense (per year).
As with the stages, the variables are particular to a life cycle analysis. Not every Monte Carlo simulation is going to use the same variables. However, every Monte Carlo simulation is going to use some sort of variables. If there are no variables then you already know what’s going to happen and you wouldn’t need to run a simulation.
Probability
For each stage/variable combination on the table, you’re going to enter a Probability for each Value entered. What to enter is completely up to you. Use your gut, use your brain, do a lot of research, or…do no research. The Values and Probabilities are the foundation of your Monte Carlo simulation. So if you approach it haphazardly – you’re probably going to have more unreliable results.
What is critical, is that you make sure all of your Probabilities add up to 100%. If they don’t – you either need to add Values (and Probabilities) or, if you’ve used up all four spaces, then you need to consolidate Values into an average. If your Probabilities do not equal 100% you’ll see an error message, in red, show up below.
Value
Each stage/variable combination will allow for the input of up to four Values. Obviously, if you were creating your own Monte Carlo simulation, you can use a limitless number Values. But, in order to keep things manageable, in this example, the limit is set to four.
Output
As you’ve probably gathered above, there are three main sections to the Output of the Monte Carlo simulation.
Here, you’ll find information regarding the variables for the Average scenario, the Pessimistic scenario, and the Optimistic scenarios.
Average scenario
The Average scenario is self-explanatory. It’s the average Value for each stage of the example product’s life cycle. And, subsequently, the expected Net Profit for each stage and in total.
But, what constitutes Pessimistic or Optimistic?
Pessimistic and Optimistic scenarios
Well, that’s up to you. You have the option of inputting a Percentile for each scenario. For the Pessimistic scenario – the lower the percentage you enter, the more detrimental the results will be. On the flip side – for the Optimistic scenario the higher the Percentile you enter, the more beneficial the results will be.
For instance, a Pessimistic scenario with a Percentile of 33% will deliver better results than a Pessimistic scenario with a Percentile of 10%. So, it’s really up to you to decide what you would consider the worst-case scenario.
Keep in mind that if you enter 5%, 1%, or some other really low Percentile, you’re going to get the absolute worst-case scenario. Depending on how many simulations you run, that scenario might technically be feasible, but extremely unlikely.
The same holds true, of course, on the optimistic side. An Optimistic scenario with a Percentile of 90% or 95% is going to deliver better results than one with a percentile of 66%. Use your judgment. You can toy with this until you come to the conclusion that you feel is practical.
Net Profit
For each scenario, you’ll see all of the variables listed and the appropriate Values for each variable/stage combination. Additionally, a Net Profit is calculated for each scenario. Of course, you can expect that the Net profit under the Pessimistic scenario will be less than that for the Average scenario. Which, in turn, will likely have a lower Net Profit than the Optimistic scenario.
These three scenarios are a culmination of all the information you entered in the Input section. Every time you refresh the Values, the Output will change. Because keep in mind that if you make a change to the Inputs, or refresh, a new set of 1 to 1,000 simulations will run. These will be a different set of simulations – so the Average scenario won’t be the same. Nor will the Pessimistic or Optimistic scenarios. But, if you keep refreshing the simulations and looking at the Output and/or the chart, you should notice that all three scenarios tend to hover around the same amounts for each variable.
Monte Carlo simulation chart
The Net profit distribution chart offers a different perspective on the Average, Pessimistic, and Optimistic scenarios.
Actually, this chart offers a view of the entire spectrum of results. You’ll notice that the distribution of results resembles a bell curve. The bars will be lower at the extreme highs and lows and more results will fall within the Values in-between.
As mentioned before, this particular Monte Carlo simulation might not be directly applicable to you or your business. But, it allows you to see the capabilities of such an analysis. Play around with it and you’ll notice how beneficial it would be to look into the future multiple times. How beneficial it would be to have an idea of which outcomes are likely and which are unlikely, and therefore be able to take appropriate action.