Using Monte Carlo simulation for a capital budgeting project
Virgina Clark, Margaret Reed, Jens A. Stephan · Management accounting quarterly · 2010
Monte Carlo simulation is a powerful spreadsheet-based tool that allows managers to better understand and visualize risk and uncertainty in discounted cash flow (DCF) analysis. The primary output, a histogram of net present values (NPV), maps the entire distribution of possible outcomes as a bell-shaped curve and therefore estimates the probability of success for the project (e.g., NPV > zero). Although we use fictional names, we illustrate a real capital budgeting problem using Monte Carlo simulation to demonstrate how employing this tool can result in more-informed decision making. Finance theory states that expected (mean) cash flows should be discounted at the opportunity cost of capital using a decision rule to accept or reject all positive or negative NPV projects. A central issue for managers, however, is how to deal with uncertainty--i.e., the fact that expected cash flows are only a point estimate of a large number of possible realizations. Traditional finance textbooks suggest two tools for this--sensitivity analysis and scenario analysis. Sensitivity analysis tweaks one variable at a time and evaluates the effect on the project's net present value, and scenario analysis examines a limited number of combinations of variables: worst-case (WC), most-likely-case (MLC), and best-case (BC) estimates of financial variables that determine future cash flows (e.g., sales, costs, growth rates, investment in working capital, etc.). The output is three project NPVs where all variables simultaneously take on one of the three hypothetical realizations. Neither tool produces probabilities of success or failure for the project. Monte Carlo simulation, however, overcomes the limitations of sensitivity and scenario analyses by examining the effects of all possible combinations of variables and their realizations. Although the inputs are no different from scenario analysis, Monte Carlo simulation treats the estimates as a triangular distribution with the probability of WC and BC realizations being close to zero and increasing linearly up to the MLC. The simulation package then draws randomly from this distribution (100,000 times in our examples) for all variables that are specified in the DCF model and calculates an NPV for each draw. The result is a bell-shaped distribution function of NPVs (i.e., the area under the curve is equal to one) that provides estimates of the probability of success and failure. Sensitivity analysis applied to the Monte Carlo results allows us to address issues such as: 1. Which variables are the most important--i.e., have a large impact on the NPV calculation and/or have a high level of uncertainty? This helps focus management attention on relevant issues and actions that might reduce uncertainty. 2. Suppose the project-evaluation team sees that there is an optimistic bias in the forecast of unit sales. It is a simple matter to change the WC and/or the BC estimate and determine the resulting decrease in the probability of a successful project. 3. Suppose management believes that prices and volume are negatively correlated--i.e., on average, higher prices result in lower sales volume. Alternatively, many costs move up or down together, implying a positive correlation. Simulation software permits the financial modeler to specify such correlations and quantify their effects on the probability of success or failure. An interesting byproduct of simulation is that it can clearly highlight implausible assumptions. Consider a project submitted to the capital expenditure committee with a 99% probability of success and an internal rate of return (IRR) greater than the cost of capital. On the surface, it appears to be the perfect project--high returns with little chance of failure. Projects with high returns, however, by definition must be risky projects because, as we know, there is no free lunch. Therefore, the probability of a positive NPV project cannot be close to 100%--even projects that earn their cost of capital are risky. …