Objective 11. Perform What-If Analysis

If a formula depends on the value in a cell, you can see what effect it will have if you change the value in that cell. Then, you can copy the value computed by the formula and paste it into another part of the worksheet where it can be compared and charted. This can be done for multiple formulas.

Activity 2.20. Performing What-If Analysis and Using Paste Special

Ms. French can see that a growth rate of 26% each decade will result in a population of more than 2.3 million people by 2040. The city planners will likely ask her what the population might be if it grows at varying rates, for example the highest rate of 36%. Because the formulas are constructed to use the growth rate entered into a single cell, Ms. French can answer these questions quickly by entering different percentages into that cell. To keep the results of each set of calculations so they can be compared, you will paste the results of each what-if analysis into another area of the worksheet.

1.

In cell A13 type Comparison of Estimates and then press . Click A6, click Format Painter , and then click cell A13. Select the range A8:B8, click the Fill Color button arrow , and in the last row, click the second colorTan. Click cell A1, hold down and click cells A6 and A13. Apply the Gold fill color to these titles.

 

2.

In cells A15:A18 type the following row titles:

Estimated Growth Rate

26% Growth

31.5% Growth

36% Growth

 

3.

Select the range B10:F10, click Copy , click cell B15, and then click Paste .

 

4.

Select the range B11:F11, click Copy , click cell B16, and then click Paste . Click cell C16, and notice on the Formula Bar that the formula was pasted into the cell.

This is not the desired result, because formulas were copied. The actual calculated values are needed in the range B16:F16.

 

5.

Click the Undo button . Select the range B11:F11 again and click Copy . Click cell B16, and then from the Edit menu, click Paste Special. In the displayed Paste Special dialog box, under Paste, click Values and number formats.

The Paste Special command can paste the values that result from the calculation of formulas into other cellsalong with the formatting applied to the copied cells.

 
   

6.

In the displayed Paste Special dialog box, click OK. Click cell C16. Notice on the Formula Bar that the cell contains a value, not a formula. Press to cancel the moving border, and then compare your screen with Figure 2.48.

 

Figure 2.48.

(This item is displayed on page 747 in the print version)

The estimates based on a 26% growth rate are pasted along with the formatting.

 

 

7.

Click cell B8. Type 31.5 and on the Formula Bar, click the Enter button . Notice that the projected values in the range C11:F11 are recalculated.

The value 31.5% is halfway between 26% and 36%the highest and lowest growth values from row 4. Although the cell may display 32%, you can see that the underlying value is 31.5%.

 

8.

Select the range B11:F11 and then click Copy . Click cell B17. From the Edit menu, click Paste Special. In the Paste Special dialog box, click Values and number formats, and then click OK.

 

9.

In cell B8, type 36 and press . Notice that the projected values in C11:F11 are recalculated.

 

10.

Use the skills you just practiced to copy the values and formats in the range B11:F11 to the range B18:F18.

 
   

11.

Press to cancel the moving border, and then click an empty cell to cancel the selection. In rows 15:18, notice that the data and titles are arranged in simple rows and columns in adjacent cells for convenient charting. Compare your screen with Figure 2.49.

 

Figure 2.49.

(This item is displayed on page 748 in the print version)

Ms. French is now able to answer several what-if questions about the future population of the city and provide a range of population estimates based on the smallest, largest, and medium rates of growth over the past four decades.

 

 

12.

Save your workbook.

 

[Page 748 (continued)]

Objective 12 Compare Data with a Line Chart
Related

Категории