7.2 Dynamic Chart
К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1617 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
85 86 87 88 89 90 91 92 93 94 95 96 97
Problem. How sensitive is the Intrinsic Value to changes in: (1) the Inflation Rate, (2) Earnings Retention
Rate, and (3) Real Discount Rate (k)? Said differently, how important is it to be very accurate in
forecasting these three inputs?
Solution Strategy. First, we vary the Real Discount Rate input and use Excel's Data Table feature to
generate the corresponding Intrinsic Value / Share outputs. Second, we construct a Dynamic Chart by
graphing the Data Table inputs and outputs and by adding spinners to the Inflation Rate and Earnings
Retention Rate inputs.
FIGURE 7.2 Spreadsheet for Stock Valuation - Dynamic Chart.
How To Build This Spreadsheet Model.
1. Open the Two Stage Spreadsheet and Move A Few Things. Open the spreadsheet that you
created for Stock Valuation – Two Stage and immediately save the spreadsheet under a new name
using the File | Save As command. Insert ten rows above the Outputs, by selecting the range
A8:A17 and clicking on Insert | Rows. Move The Label "Outputs," by selecting the cell A18,
clicking on Edit | Cut, selecting the cell A14 and clicking on Edit | Paste. Using the same steps,
move the range A5:B5 to the range A15:B15 and move the range A6:B6 to the range A5:B5.
2. Increase Row Height for the Spinners. Select the range A4:A5. Then click on Format | Row
Height from the main menu. Enter a height of 30 and click on OK.
3. Display the Forms Toolbar. Select View | Toolbars | Forms from the main menu.
4. Create the Spinners. Look for the up-arrow / down-arrow button on the Forms toolbar (which
will display the word “Spinner” if you hover the cursor over it) and click on it. Then draw the box
for a spinner from the upper left corner of cell C4 down to the lower right corner of the cell. Then
a spinner appears in the cell C4. Right click on the spinner (press the right mouse button while
the cursor is above the spinner) and a small menu pops up. Click on Copy. Then select the cell
C5 and click on Paste. This creates an identical spinner in cell C5. You now have two spinners
down column C.
5. Create The Cell Links. Right click on the first spinner in the cell C4 and a small menu pops up.
Click on Format Control and a dialog box pops up. Enter the cell link D4 in the Cell link edit
box and click on OK. Repeat this procedure for the other spinner. Link the spinner in cell C5 to
cell D5. Click on the up-arrows and down-arrows of the spinners to see how they change the
values in the linked cells.
6. Create Scaled Inputs. The values in the linked cells are always integers, but they can be scaled
appropriately to the problem at hand. In cell B4, enter =D4/200. In cell B5, enter =D5/10.
7. Enter Real Discount Rate Values. In the range C15:H15, enter the values 7%, 8%, 9%, 10%,
11%, and 12%.
8. Create A Data Table To Calculate Intrinsic Value / Share. Use Excel's Data Table feature to
calculate the Intrinsic Value / Share for each corresponding Real Discount Rate. Specify the
output cell by entering =D32 in cell B16. Select the range B15:H16, click on Data | Table, enter
$B$15 in the Row Input Cell box, and click on OK.
9. Graph the Intrinsic Value / Share. Select the range C15:H16, then click on Insert | Chart
from the main menu. Select an XY(Scatter) chart type and make other selections to complete
the Chart Wizard. Place the graph in the range E2:J13.
The Data Table and the graph indicate that decreasing the Real Discount Rate from 12% to 7% causes the
Intrinsic Value / Share to jump from $125 / Share to $235 / Share. Thus, the Intrinsic Value / Share is
very sensitive to Real Discount Rate. Clicking on the spinner for the Inflation Rate causes no change at all
in the Intrinsic Value / Share. This makes sense because the Inflation Rate contributes equally to the
Nominal Return on Investment and to the Nominal Discount Rate. The two effects cancel each other out,
leaving zero net impact on Intrinsic Value / Share. Clicking on the spinner for the Earnings Retention
Rate causes a huge movement in the Intrinsic Value / Share. Hence, it is important to be as accurate as
possible about both the Real Discount Rate and the Earnings Retention Rates.
Problem. How sensitive is the Intrinsic Value to changes in: (1) the Inflation Rate, (2) Earnings Retention
Rate, and (3) Real Discount Rate (k)? Said differently, how important is it to be very accurate in
forecasting these three inputs?
Solution Strategy. First, we vary the Real Discount Rate input and use Excel's Data Table feature to
generate the corresponding Intrinsic Value / Share outputs. Second, we construct a Dynamic Chart by
graphing the Data Table inputs and outputs and by adding spinners to the Inflation Rate and Earnings
Retention Rate inputs.
FIGURE 7.2 Spreadsheet for Stock Valuation - Dynamic Chart.
How To Build This Spreadsheet Model.
1. Open the Two Stage Spreadsheet and Move A Few Things. Open the spreadsheet that you
created for Stock Valuation – Two Stage and immediately save the spreadsheet under a new name
using the File | Save As command. Insert ten rows above the Outputs, by selecting the range
A8:A17 and clicking on Insert | Rows. Move The Label "Outputs," by selecting the cell A18,
clicking on Edit | Cut, selecting the cell A14 and clicking on Edit | Paste. Using the same steps,
move the range A5:B5 to the range A15:B15 and move the range A6:B6 to the range A5:B5.
2. Increase Row Height for the Spinners. Select the range A4:A5. Then click on Format | Row
Height from the main menu. Enter a height of 30 and click on OK.
3. Display the Forms Toolbar. Select View | Toolbars | Forms from the main menu.
4. Create the Spinners. Look for the up-arrow / down-arrow button on the Forms toolbar (which
will display the word “Spinner” if you hover the cursor over it) and click on it. Then draw the box
for a spinner from the upper left corner of cell C4 down to the lower right corner of the cell. Then
a spinner appears in the cell C4. Right click on the spinner (press the right mouse button while
the cursor is above the spinner) and a small menu pops up. Click on Copy. Then select the cell
C5 and click on Paste. This creates an identical spinner in cell C5. You now have two spinners
down column C.
5. Create The Cell Links. Right click on the first spinner in the cell C4 and a small menu pops up.
Click on Format Control and a dialog box pops up. Enter the cell link D4 in the Cell link edit
box and click on OK. Repeat this procedure for the other spinner. Link the spinner in cell C5 to
cell D5. Click on the up-arrows and down-arrows of the spinners to see how they change the
values in the linked cells.
6. Create Scaled Inputs. The values in the linked cells are always integers, but they can be scaled
appropriately to the problem at hand. In cell B4, enter =D4/200. In cell B5, enter =D5/10.
7. Enter Real Discount Rate Values. In the range C15:H15, enter the values 7%, 8%, 9%, 10%,
11%, and 12%.
8. Create A Data Table To Calculate Intrinsic Value / Share. Use Excel's Data Table feature to
calculate the Intrinsic Value / Share for each corresponding Real Discount Rate. Specify the
output cell by entering =D32 in cell B16. Select the range B15:H16, click on Data | Table, enter
$B$15 in the Row Input Cell box, and click on OK.
9. Graph the Intrinsic Value / Share. Select the range C15:H16, then click on Insert | Chart
from the main menu. Select an XY(Scatter) chart type and make other selections to complete
the Chart Wizard. Place the graph in the range E2:J13.
The Data Table and the graph indicate that decreasing the Real Discount Rate from 12% to 7% causes the
Intrinsic Value / Share to jump from $125 / Share to $235 / Share. Thus, the Intrinsic Value / Share is
very sensitive to Real Discount Rate. Clicking on the spinner for the Inflation Rate causes no change at all
in the Intrinsic Value / Share. This makes sense because the Inflation Rate contributes equally to the
Nominal Return on Investment and to the Nominal Discount Rate. The two effects cancel each other out,
leaving zero net impact on Intrinsic Value / Share. Clicking on the spinner for the Earnings Retention
Rate causes a huge movement in the Intrinsic Value / Share. Hence, it is important to be as accurate as
possible about both the Real Discount Rate and the Earnings Retention Rates.