14.5 Sensitivity
К оглавлению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. Given historical and forecasted financial statements for Cutting Edge B2B Inc., analyze the
sensitivity of the 2001 External Funds Needed to the assumed 2001 Sales Growth Rate.
Solution Strategy. Create a Data Table using Sales Growth Rate as the input variable and External Funds
Needed as the output variable. Then graph the relationship.
FIGURE 14.8 Sensitivity Analysis for Cutting Edge B2B Inc.
How To Build Your Own Spreadsheet Model.
1. Open the Ratios Spreadsheet. Open the spreadsheet that you created for Corporate Financial
Planning - Ratios and immediately save the spreadsheet under a new name using the File | Save
As command.
2. Data Table. Create a list of input values for the 2001 Sales Growth Rate (0.0%, 4.0%, 8.0%, etc.)
in the range C163:I163. Create an output formula that references the 2001 External Funds
Needed by entering the formula =F63 in cell B164. Select the range B163:I164 for the One-
Variable Data Table. This range includes both the input values on the top of the range and the
output formula on the left side of the range. Then choose Data | Table from the main menu and a
Table dialog box pops up. Enter the cell address F5 (2001 Sales Growth Rate) in the Row Input
Cell and click on OK.
3. Graph. Highlight the input values and the results of the data table (excluding the side) in the
range C163:I164 and then choose Insert | Chart from the main menu. Select the XY
(Scatter) chart type and make other selections to complete the Chart Wizard.
The sensitivity analysis indicates that 2001 External Funds Needed is very sensitive to the assumption
about 2001 Sales Growth Rate. Further, there is a linear relationship between 2001 Sales Growth Rate and
2001 External Funds Needed.
Problem. Given historical and forecasted financial statements for Cutting Edge B2B Inc., analyze the
sensitivity of the 2001 External Funds Needed to the assumed 2001 Sales Growth Rate.
Solution Strategy. Create a Data Table using Sales Growth Rate as the input variable and External Funds
Needed as the output variable. Then graph the relationship.
FIGURE 14.8 Sensitivity Analysis for Cutting Edge B2B Inc.
How To Build Your Own Spreadsheet Model.
1. Open the Ratios Spreadsheet. Open the spreadsheet that you created for Corporate Financial
Planning - Ratios and immediately save the spreadsheet under a new name using the File | Save
As command.
2. Data Table. Create a list of input values for the 2001 Sales Growth Rate (0.0%, 4.0%, 8.0%, etc.)
in the range C163:I163. Create an output formula that references the 2001 External Funds
Needed by entering the formula =F63 in cell B164. Select the range B163:I164 for the One-
Variable Data Table. This range includes both the input values on the top of the range and the
output formula on the left side of the range. Then choose Data | Table from the main menu and a
Table dialog box pops up. Enter the cell address F5 (2001 Sales Growth Rate) in the Row Input
Cell and click on OK.
3. Graph. Highlight the input values and the results of the data table (excluding the side) in the
range C163:I164 and then choose Insert | Chart from the main menu. Select the XY
(Scatter) chart type and make other selections to complete the Chart Wizard.
The sensitivity analysis indicates that 2001 External Funds Needed is very sensitive to the assumption
about 2001 Sales Growth Rate. Further, there is a linear relationship between 2001 Sales Growth Rate and
2001 External Funds Needed.