Microsoft Office Tutorials and References

In Depth Information

EX 224

Excel Chapter 3
What-If Analysis, Charting, and Working with Large Worksheets

In the workbook created in this chapter, many of the formulas are dependent on

the assumptions in the range B19:B25. Thus, if you change any of the assumption

values, Excel immediately recalculates all formulas. Excel redraws the 3-D Pie chart as well,

because it is based on these numbers.

To Analyze Data in a Worksheet by Changing Values

A what-if question for the worksheet in Chapter 3 might be
what
would happen to the semiannual operating

income in cell H16
if the Bonus, Commission, Support, General, and Administrative assumptions in the What-If

Assumptions table are changed as follows: Bonus $100,000.00 to $75,000.00; Commission 3.25% to 2.25%; Support,

General, and Administrative 17.00% to 14.50%? To answer a question like this, you need to change only the ﬁ rst,

second, and seventh values in the What-If Assumptions table as shown in the following steps. The steps also divide

the window into two vertical panes. Excel instantaneously recalculates the formulas in the worksheet and redraws

the 3-D Pie chart to answer the question.

1

•
Use the vertical scroll bar to move

the window so cell A6 is in the

upper-left corner of the screen.

horizontal

split box

•
Drag the vertical split box from

the lower-right corner of the

screen to the left so that the

vertical split bar is positioned as

shown in Figure 3-78.

•
Use the right scroll arrow to view

the totals in column H in the

right pane.

•
Enter
75000
in cell B19,
2.25
in

cell B20, and
14.50
in cell B25

(Figure 3–78), which causes the

semiannual operating income

in cell H16 to increase from

$9,459,176.31 to $10,886,373.12.

new projected

semiannual

operating

income

current location

of vertical

split box

changes to assumptions affect

projected monthly expenses and

operating incomes, which in turn affect

projected semiannual operating income

vertical split bar

original location

of vertical

split box

Figure 3–78