172 Using a Data Table 173 We use the simplest of models to present Data Tables. 174 You earned $100 last year. You expect your earnings to increase by a constant Growth Rate annually. 175 The Net Current Value of your earnings is calculated by the terminal value formula: 176 177 Net Present Value of Earnings = $100 (1 + Growth Rate)/(Discount Rate - Growth Rate) 178 179 Enter a formula in the green box below that expresses this relationship. 180 The 'Growth Rate' is in row 12 and the 'Discount Rate' is in row 13 of the 'DATA TABLES' Sheet. 181 182 If the green box above turns red your formula is incorrect. 183 184 Data Tables require that you set up a template before using it. 185 We have done that in B16:G21 on the 'DATA TABLES' Sheet. 186 We have also set the 'Growth Rate' range C16:G16 from 2% to 10%, also in increments of 2%. 187 We have set the 'Discount Rate' range B17:B21 from 6% to 14% in increments of 2%. 188 Finally, the upper left cell, B16, must be set equal to the result of the model, B181 on this Sheet. 189 190 The above template can now be populated using the Data Table Function, as follows: 191 1 Select the full range (shaded in grey) of the Data Table, B16:21 of the 'DATA TABLE' Sheet. 192 2 Select 'Data' tab 193 3 Select "What-if Analysis' 194 4 Select 'Data Table', which will open a "Data Table' window. 195 5 In the 'Row input cell:' box enter or click on cell 'c12' of the 'DATA TABLES' Sheet. 196 6 In the 'Column input cell:' box enter or click on cell 'C13' of the 'DATA TABLES' Sheet. 197 7 Select "OK" 198 The Data Table function has created a table of results by automatically varying the row and column inputs. 199 This provides a 'sensitivity analysis' of the results as you can easily see how sensitive the results are to changes in input parameters. 200 In the above case, the NPV of your earnings varies from 0 to 0, a huge swing. 201 The negative NPVs result from the fact that when the Growth Rate is less than the Discount Rate the NPV is negative. 202 In the green cell immediately below enter the result for Discount Rate = 8% and Growth Rate = 4%. 203 204 172 Using a Data Table 173 We use the simplest of models to present Data Tables. 174 You earned $100 last year. You expect your earnings to increase by a constant Growth Rate annually. 175 The Net Current Value of your earnings is calculated by the terminal value formula: 176 177 Net Present Value of Earnings = $100 (1 + Growth Rate)/(Discount Rate - Growth Rate) 178 179 Enter a formula in the green box below that expresses this relationship. 180 The 'Growth Rate' is in row 12 and the 'Discount Rate' is in row 13 of the 'DATA TABLES' Sheet. 181 182 If the green box above turns red your formula is incorrect. 183 184 Data Tables require that you set up a template before using it. 185 We have done that in B16:G21 on the 'DATA TABLES' Sheet. 186 We have also set the 'Growth Rate' range C16:G16 from 2% to 10%, also in increments of 2%. 187 We have set the 'Discount Rate' range B17:B21 from 6% to 14% in increments of 2%. 188 Finally, the upper left cell, B16, must be set equal to the result of the model, B181 on this Sheet. 189 190 The above template can now be populated using the Data Table Function, as follows: 191 1 Select the full range (shaded in grey) of the Data Table, B16:21 of the 'DATA TABLE' Sheet. 192 2 Select 'Data' tab 193 3 Select "What-if Analysis' 194 4 Select 'Data Table', which will open a "Data Table' window. 195 5 In the 'Row input cell:' box enter or click on cell 'c12' of the 'DATA TABLES' Sheet. 196 6 In the 'Column input cell:' box enter or click on cell 'C13' of the 'DATA TABLES' Sheet. 197 7 Select "OK" 198 The Data Table function has created a table of results by automatically varying the row and column inputs. 199 This provides a 'sensitivity analysis' of the results as you can easily see how sensitive the results are to changes in input parameters. 200 In the above case, the NPV of your earnings varies from 0 to 0, a huge swing. 201 The negative NPVs result from the fact that when the Growth Rate is less than the Discount Rate the NPV is negative. 202 In the green cell immediately below enter the result for Discount Rate = 8% and Growth Rate = 4%. 203 204