Microsoft Office Tutorials and References
In Depth Information
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.
• Use the vertical scroll bar to move
the window so cell A6 is in the
upper-left corner of the screen.
• 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
• 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.
changes to assumptions affect
projected monthly expenses and
operating incomes, which in turn affect
projected semiannual operating income
vertical split bar