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

CHAPTER 5

Using Decision Variables

The first four chapters covered the basics of specifying Crystal Ball assumptions and analyzing Crystal Ball forecasts. This chapter covers the basics of defining and using Crystal Ball decision variables and its decision support tools, Decision Table and OptQuest.

5.1 DEFINING DECISION VARIABLES

Decision variables are spreadsheet cells in which the values are varied systematically rather than sampled randomly, as are assumptions. They can be cells that hold values dictated by actual decisions or cells for which we just want to see their effect on selected forecasts in a form of sensitivity analysis.

As an example of the latter, consider the model depicted in Figure 5.1, TwoCorrelatedAssets.xls, where we have two correlated financial assets. We wish to vary the correlation coefficient to see the effect on the rate of return of a portfolio composed of the two assets. To keep things simple we will simulate a model for which we know the exact solution so that we can compare the simulation results to the truth.

FIGURE 5.1 Spreadsheet model in file TwoCorrelatedAssets.xls, which models rate of return on portfolio as the correlation coefficient, ρ, varies from -1.00 to +1.00 in steps of 0.25. Cell D5 is a decision variable, cells A9 and A10 are assumptions, and cell A13 is a forecast.

Consider a portfolio composed of two assets, A and B. Asset A has ...