Type of Submission

AHFME Symposium Abstract


The object of the paper is to show how Crystal Ball, an add-inn to a spreadsheet, can be used to provide better answers to capital-budgeting questions. While capital budgeting is not a new tool to analyze the uncertainty inherent in long-range investment decisions, recent technological developments now allow complex Monte Carlo simulations to be run on a personal computer within a "familiar" spreadsheet in a fraction of the time that was required just a few years ago. This paper shows how it is now possible to explicitly model uncertainty within the "point-and-click environment that is within the grasp of most of today's spreadsheet users.

The difficulty in the capital-budgeting process stems from the uncertainties surrounding the estimation of the amount and timing of future cash flows. Unlike most traditional approaches that ignore uncertainty and rely instead on single, best-guess point estimates, we suggest that we need to explore ways to grapple with the probabilistic nature of capital-budgeting calculations.

The paper first presents a deterministic model using single point estimates to analyze a typical capital-budgeting problem - which machine should the company buy? The deterministic model assumes that all of the inputs are known with certainty and each input is represented by a single point estimate. Next, simulation software is used to replace the single point estimate in each cell of the model with the appropriate probability density function, or a distribution of most likely values. The probability density function defined the range of values that the cell may take on during the simulation. The results of the simulation provide much more robust information than would a single most likely outcome showing the range of possible answers and the probability of their occurrence.

Hopefully this paper demonstrates how we can provide our students with another tool before they enter the work force so they can better serve their companies as they look into the future.