tailieunhanh - Financial Modeling with Crystal Ball and Excel Chapter 5

CHAPTER 5 Using Decision Variables T he 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. 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 the effect of one or two variables on selected forecasts in a form of sensitivity analysis. As an example. | 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. 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 the effect of one or two variables on selected forecasts in a form of sensitivity analysis. As an example of the latter consider the model depicted in Figure where we have two correlated assets and 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 true answer so that we can compare the simulation results to the truth. Consider a portfolio composed of two assets A and B. Asset A has a normally distributed rate of return with mean 10 percent and standard deviation va 20 percent. Asset B has a normally distributed rate of return with mean 15 percent and standard deviation vb 30 percent. We will invest half of our available funds in Asset A and half in Asset B and we are interested in seeing how the distribution of the rate of return of our portfolio varies as a function of the correlation coefficient p between the rates of return on Assets A and B. In cells A9 and A10 are assumptions and cell A13 is a forecast. The assumptions in cells A9 and A10 of the file are defined as normal distributions as described in Chapter 4. However to reflect the limited liability of stock ownership the rates of return on both assets are truncated 71 72 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE .