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

Search JabSto ::

Custom Search