Content area
Full Text
A critical aspect of any capital budgeting decision is evaluating the risk surrounding key variables in the decision. Whether it involves assumptions about revenue growth, expense growth, the costs of capital, or other input variables, any forecasting model invites an element of uncertainty. Performing sensitivity analysis helps reduce that uncertainty by examining the impact that different values for the assumptions might have on a project's net present value (NPV) or internal rate of return (IRR).
There are several different approaches to performing sensitivity analysis. Single variable sensitivity analysis involves varying the base input variables one at a time to see the effect on various model outputs. Scenario analysis involves constructing worst case, best case, and most likely case scenarios. And Monte Carlo simulation uses a probability distribution to represent the uncertainty or risk associated with key variables. This is usually the preferred method to use when there is enough information or data available to identify potential value ranges for the input variables.
Commercial software is available that will enable you to perform Monte Carlo simulation, but it isn't always feasible to purchase software. For one-time projects or in situations with limited budgets, it's possible to use Excel to create a Monte Carlo simulation using three of the most common probability distributions.
Probability Distributions
The first step in creating a Monte Carlo simulation is to decide which probability distribution to apply. There are a number of ways to determine the appropriate choice, including:
* Judgment based on experience and individual knowledge,
* Computed distribution based on historical data,
* A survey of key managers about their beliefs concerning a specific variable, or
* The Delphi technique, where knowledgeable individuals (experts) are repeatedly polled on their beliefs until a consensus belief emerges.
Out of the dozens of possible probability distributions, the three used most often in capital budgeting simulations are rectangular (or continuous uniform) distribution, normal distribution, and triangle distribution. Figure 1 shows what each of these distributions looks like and describes their key variables. Each is possible to create within an Excel worksheet.
Rectangular Distribution
In a rectangular distribution, all the values within a range-from the lowest to highest-are equally probable. In Excel, this can be created using the RANDBETWEEN function, which generates a random number...