Nội dung

CHAPTER 5 Using Decision Variables 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. T 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 5.1, TwoCorrelatedAssets.xls, 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, µA = 10 percent, and standard deviation, σA = 20 percent. Asset B has a normally distributed rate of return with mean, µA = 15 percent, and standard deviation, σB = 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, ρ, between the rates of return on Assets A and B. In Two Correlated Assets.xls, cells A9 and A10 are assumptions, and cell A13 is a forecast. The assumptions in cells A9 and A10 of the file TwoCorrelatedAssets.xls 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 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. on the left at −100 percent. This is accomplished by entering this value in the field directly above the Mean field as shown in Figure 5.2 for the rate of return assumption for Asset B. With a mean of 15 percent and a standard deviation of 30 percent, the normal distribution will rarely produce any values below −100% (which is more than 3.8 standard deviations below the mean), but truncating the distribution ensures that on those rare occasions when a value less than −100% is generated from a nontruncated normal distribution for Asset B, Crystal Ball will discard it and generate another random value in its place. Asset A’s rate of return is also bounded from below by −100%. Note that the title bar of the dialog in Figure 5.2 for cell A10 indicates that it is correlated with another assumption, in this case the assumption in cell A9. During any simulation run, the correlation coefficient is the value in cell D5, which is what we will vary with the Decision Table tool. To make Cell D5 into a decision variable, first click on it. Then select Define→Define Decision. . . from the top menu or click on the Define Decision icon on the Crystal Ball toolbar. Fill in the fields as shown in Figure 5.3. This tells Crystal Ball that you wish to vary the value in cell D5 from −1.00 to +1.00 in discrete steps of 0.25. Click OK and cell D5’s background will turn yellow to indicate that a decision variable is defined in that cell. You are now ready to use the Decision Table tool or OptQuest with the model. 73 Using Decision Variables FIGURE 5.2 Define assumption dialog for cell A10 in file TwoCorrelatedAssets.xls. FIGURE 5.3 Define Decision Variable dialog window. DECISION TABLE WITH ONE DECISION VARIABLE Select Run→Tools→Decision Table. . . from the top menu. You will get a dialog window for step 1 like that shown in Figure 5.4. For models with more than one forecast defined, you would first highlight the forecast for which you wish to analyze the sensitivity to the decision variable. As there is only one forecast in this model, there is no choice to be made here, so click Next > to select the forecast Portfolio ROR. 74 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE 5.4 Dialog window for step 1 of using Decision Table. The dialog for step 2 is where you choose the decision variables to evaluate, as shown in Figure 5.5. Again, because there is only one decision variable defined, click >> to move the decision variable Correlation to the Chosen Decision Variables field, then click Next>. Step 3 is where you specify the Decision Table options. Take the default options depicted in Figure 5.6, then click Start. Crystal Ball will begin running nine sets of 10,000 simulation trials, one set for each of the following values of the correlation coefficient: {−1.00, −0.75, −0.50, −0.25, 0.00, 0.25, 0.50, 0.75, 1.00} After it has finished running 90,000 simulation trials, Crystal Ball will create a separate workbook like that in Figure 5.7 holding nine forecasts and three buttons:Trend Chart, Overlay Chart, and Forecast Charts. Trend Chart To see the trend chart, select the forecasts in cells B2:J2, then click Trend Chart to get a graphical display similar that shown in Figure 5.8. A trend chart displays the certainty bands for several forecasts on one plot. The chart in Figure 5.8 clearly Using Decision Variables FIGURE 5.5 Dialog window for step 2 of using Decision Table. FIGURE 5.6 Dialog window for step 3 of using Decision Table. 75 76 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE 5.7 Results of using Decision Table. FIGURE 5.8 Trend chart displaying results of using Decision Table. shows how the variability of the portfolio rate of return increases as the correlation coefficient goes from −1.00 to +1.00. At the left end of the trend chart, the 90 percent certainty band extends from 4.3 percent to 20.7 percent for the forecast Portfolio ROR (1), which is the forecast generated when the correlation coefficient is −1.00. At the right end, the 90 percent certainty band extends from −28.6 percent to 53.6 percent for the forecast Portfolio ROR (9), which is the forecast generated when the correlation coefficient is +1.00. These correspond to the same certainty intervals indicated in the forecast charts for Portfolio ROR (1) and Portfolio ROR (9) displayed in Figure 5.9. Note how the certainty bands diverge from left to right, but remain centered on 12.5 percent. They correspond very well to the theoretical bounds calculated in Two Correlated Assets.xls, and displayed in Figure 5.10. Using Decision Variables 77 FIGURE 5.9 Frequency charts for Portfolio ROR (1) and (9) from Decision Table results. These charts conform well to theory, which holds that the rate of return on the portfolio will be normally distributed with mean µP = wµA + (1 − w)σB , and standard deviation
(5.1) σP = w2 σA2 + (1 − w)2 σB2 + 2w(1 − w)ρσA σB , where w is the weight (0 ≤ w ≤ 1) of Asset A in the portfolio. For this example, w = 0.5. 78 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE 5.10 Theoretical 90 percent certainty bounds for trend chart displaying results of using Decision Table. Bounds are calculated as µP + −1 (y)σP , where −1 (y) is the inverse CDF for the standard normal distribution, y = 0.05 for the lower bound, and y = 0.95 for the upper bound. It is worth noting that our model differed slightly in at least two ways from the theoretical model underlying Expression (5.1). First, Crystal Ball uses Spearman correlation, ρ S , for sampling instead of the Pearson correlation, ρ, used in Expression (5.1); however, for normally distributed rates of return this makes little difference. Second, we truncated the rate of return assumptions on the left, which has the effect of changing the standard deviations slightly, but this also made little difference. Overlay Chart The forecast charts in Figure 5.9 look similar, but the scales of their horizontal axes are much different. To make it easier to compare forecasts, use an overlay chart. Click on cell B2, then hold down the Ctrl key and click on cell J2 to select the forecasts Using Decision Variables 79 FIGURE 5.11 Overlay chart displaying results of using Decision Table. for Portfolio ROR (1) and Portfolio ROR (9). Then click the Overlay Chart button to get a chart like that displayed in Figure 5.11, which clearly shows the difference in dispersion between the forecasts for Portfolio ROR (1) and Portfolio ROR (9). You can create an overlay chart for more than two forecasts at a time, but do so with care as they become hard to read when too many forecasts are included. DECISION TABLE WITH TWO DECISION VARIABLES You can also use the Decision Table tool with two decision variables. The output is similar to the output with one decision variable, except that Crystal Ball will produce an array containing a forecast for every possible combination of the values specified for each decision. This section contains an example of using Decision Table with a model built as an illustration of how to estimate a value for managerial flexibility. Model For an example of a situation with two decision variables, suppose that a firm can invest in a project having a three-year life and a terminal value that depends on the cash flow in the final quarter of the third year. Suppose further that there are only two sources of uncertainty: (1) the average quarterly growth rate of revenue, and (2) variable cost as a percentage of revenue. We assume that average quarterly revenue growth is random and follows a normal distribution with mean = 5 percent, and standard deviation = 5 percent. Variable cost as a percentage of revenue is also 80 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE 5.12 Spreadsheet segment from Project.xls. Note that columns E through K are hidden. normally distributed with a mean of 50 percent, and a standard deviation of 5 percent. The discount rate is assumed to be 12.5 percent. Figure 5.12 shows a spreadsheet segment from Project.xls that is used to value this project with net present value (NPV). For the purposes of this example, we will use the Mean of the NPV forecast, ENPV, as the value of the project. By looking at this project from different perspectives, we can estimate the value of the project manager’s flexibility over time to affect the course of the project. To estimate the value of this flexibility, we consider two scenarios to find: ENPV 1 = The value of the project without managerial flexibility; and ENPV 2 = The value of the project with managerial flexibility. The value of the managerial flexibility is then determined as the difference between ENPV 1 and ENPV 2. With a standard NPV analysis, we assume that the project manager has no flexibility to make decisions during the life of the project. That is, once the project