Financial Modeling with Crystal Ball and Excel, + Website, 2nd Edition

CHAPTER 3

Building A Crystal Ball Model

Monte Carlo simulation is a tool for modeling uncertainty. Typically, we begin with a deterministic spreadsheet model of the situation we are analyzing, then use Crystal Ball to add stochastic assumptions to represent the most important sources of uncertainty. In the past, stochastic modeling was an endeavor best undertaken only by highly trained scientists and engineers working on mainframe computers. Crystal Ball has been developed over the years to make Monte Carlo simulation accessible to financial analysts and others using Excel on personal computer workstations rather than coding in a programming language such as C++ or FORTRAN. In order to illustrate the basics of building Crystal Ball models, this chapter goes through the process of starting with a simple financial model and adding stochastic assumptions to a deterministic model.

3.1 SIMULATION MODELING PROCESS

Most analysts facing a business problem follow the typical process shown here for stochastic modeling:

1. Develop a model that “behaves like” the real problem, with a special consideration of the assumptions–the random or probabilistic input variables.

2. Run a set of trials to learn about the behavior of the simulation model.

3. Continually modify the model until it has credibility with the decision makers.

4. Analyze the forecast (output) statistics and graphics to help make decisions about the real problem.

The analysis and decisions in step 4 often will lead you to think ...