Handling the inherent uncertainty in forecasting can be a difficult and important task. Each CEO, CFO, or board member will have their approach and experience with financial projections, uncertainty, and different incentives. Comparing actual results to forecasts often highlights the need for explicit recognition of uncertainty.
Monte Carlo simulations can be used to handle risks and probabilities. They are useful for many things, from constructing DCF values, valuing options in M&A, and discussing risks with the lenders to seeking funding and guiding the allocation of VC funds for startups. This article is a tutorial that shows you how to use Monte Carlo simulations.
Forecasting involves a lot of uncertainty. Since 2003, I’ve built and populated hundreds of financial and operating models for LBOs and startup fundraisings. Budgets, M&A, and corporate strategic planning. Every CEO, CFO, board member, investor, or investment committee member brings their own experience and approach to financial projections and uncertainty–influenced by different incentives. Comparing actual results to predictions can help you understand how big the differences between forecasts and real outcomes can be.
I started using scenario and sensitivity analysis to model uncertainty and still find them useful. Since 2010, when I added Monte Carlo simulations into my toolbox, I’ve found that they are an excellent tool to refine and improve how you think about risks and probabilities. I’ve used this approach to do everything from constructing DCF values, valuing options in M&A, and discussing risks with the lenders to seeking funding and guiding the distribution of VC funds for startups. Board members, investors, and senior management have always praised the approach. This article provides a tutorial that explains how to build a DCF model using a Monte Carlo Simulation.
Every decision is a matter of weighing probabilities
Let’s look at a few approaches to dealing with uncertainty before we begin the case study. Finance 101 is the concept of expected value, which is the probability-weighted mean of all cash flows for all scenarios. Finance professionals and decision-makers take different approaches to putting this simple insight into action. It can be anything from not acknowledging or discussing uncertainty, on the one hand, to sophisticated software and models, on the other. Some people spend more time discussing probability than calculating cash flow.
Let’s look at a few other ways to handle uncertainty in long- or medium-term forecasts. It would be best if you recognized many of them.
Create one scenario. The default approach for budgets, startups, and investment decisions. It is not clear about the level of uncertainty or that the actual outcome may be different from the projected one. Others may see it as a stretch goal, with the likelihood of the work being less than what was projected. Some see it as a baseline with more upside potential than downside. Some may view it as a “Base Case” with a 50/50 chance of success or failure. Some approaches, particularly for startups, are very ambitious, and failure or shortfall will be the most likely outcome. However, a higher discount is used to try and account for risk.
In this example, the inputs to the long-term forecast of cash flow are all point estimates. This yields a result of EUR50,000,000 with an implicit probability of 100%.
This can be done in its simplest form using a mix of fixed costs, semi-variable costs, and variable costs to simulate the impact, for example, of a sales growth of 10% above or below the base case.
In more complex forms, you will think about the future differently for each scenario and analyze the impact of other technological developments, competition dynamics, and macro-trends on the performance of the company.
Three different scenarios produce three different outcomes, which are assumed to be equally probable. We do not consider the probabilities of products other than those in the low and high scenarios.
Create base-, downside, and upside cases with probabilities clearly recognized. For example, each bear and bull case contains a 25% chance in the tails, while the fair value estimate is the midpoint. This allows for a more detailed analysis of the tail risk, which is what happens outside of the upside and downside scenarios.
Probability distributions allow you to visualize and model the entire range of outcomes possible in a forecast. It is possible to do this not only on an aggregate basis but also with individual inputs, assumptions, and drivers. The Monte Carlo method is then used to calculate aggregate probability distributions, which allows for an analysis of how different uncertain variables affect the overall uncertainty. The approach is important because it forces all those involved in research and decision-making to recognize the inherent uncertainty in forecasting and think in probabilities.
As with the other approaches, this one has its drawbacks. These include the possibility of false precision, the overconfidence that can result from using a sophisticated model, as well as the extra work needed to estimate the parameters and select the appropriate probability distributions.
What is a Monte Carlo simulation?
Monte Carlo Simulations Model the Probability of Different Outcomes in Financial Forecasts. The Monte Carlo simulations are named after the Monte Carlo area in Monaco, which is famous for its high-end casino. Random outcomes are at the heart of the Monte Carlo simulations, as they are with roulette and slot machines. Monte Carlo simulations can be used in many fields, including engineering, project planning, oil and gas exploration, R&D, and insurance.
Probability Distributions
In simulations, uncertain inputs can be described by probabilities. These are illustrated with parameters like mean and standard deviation. Inputs for financial projections can range from margins and revenue to more specific information such as foreign exchange rates, commodity prices, or capital expenditures.
If one or more inputs are described as probability distributions, then the output will also be a probability distribution. The computer then calculates and stores the results. Iterations are hundreds, thousands, or tens of thousands of repetitions. These iterations, when added together, approximate the probability distribution for the final result.
Types of Inputs
The input distributions are either continuous, where the randomly generated values can take any matter within the distribution (for instance, a normal distribution), or discrete, where probabilities can be attached to more than two distinct scenarios.
A simulation can contain distributions of various types. Consider, for instance, an R&D pharmaceutical project that has several stages with discrete probabilities of success or failure. The continuous distributions can describe the uncertain amounts of investment needed for each step and the potential revenue if the product reaches the market. This chart shows the results of such a simulation. There is a 65% chance of losing all investments between EUR5 and EUR50 million but a 35% chance of making a net profit, most likely between EUR100 and EUR250.
For example, the Monte Carlo simulation for a project with several go/no-go stages and uncertain investments in between, with an undetermined value if the project reaches completion.
Monte Carlo Simulations for Practice
Monte Carlo simulations don’t get used as much because the tools that are commonly used in finance do not support them well. Excel and Google Sheets only allow one number in each cell. Although they can generate random numbers and define probability distributions, creating a financial model that includes Monte Carlo functionality is difficult. While many financial institutions use Monte Carlo simulations to value derivatives, analyze portfolios, and more, these tools are usually developed in-house or proprietary, making them unaffordable for the individual finance professional.
In order to simplify Monte Carlo simulations, I would like to call your attention to Excel plugins such as ModelRisk from Vose and riskAMP. These plugins make it easy to work with Monte Carlo and can be integrated into existing models. I will be using @RISK in the walkthrough.
