7.2 Dynamic Chart

К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
17 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.