What-If Analysis, Charting, And Working With Large Worksheets Page 64
ADVERTISEMENT
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87EX 200
Excel Chapter 3
What-If Analysis, Charting, and Working with Large Worksheets
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 six-month operating
income in cell H25 if the Bonus, Commission, Support, General, and Administrative assumptions in the What-If
Assumptions table were changed as follows: Bonus $200,000.00 to $150,000.00; Commission 5.75% to 4.00%;
Support, General, and Administrative 18.75% to 15.75%? To answer a question like this, you need to change only
the fi 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
+
•
Press
to
CTRL
HOME
select cell A1.
•
Drag the vertical split
box from the lower-
original location of
right corner of the
horizontal split box
screen to the left so
that the vertical split
bar is positioned as
changes to assumptions
affect projected monthly
shown in Figure 3– 80
expenses and operating
incomes, which in turn
to split the screen
affect projected six-month
current location of
vertically.
operating income
vertical split box
current location of
•
Drag the horizontal
horizontal split box
split box from the
upper-right corner
of the screen
vertical split bar
down so that the
horizontal split bar
new projected six-month
operating income
is positioned as
shown in Figure 3– 80
to split the screen
horizontally.
original location
•
Use the scroll arrows
of vertical split box
in the lower-right
Figure 3 – 80
pane to view the total
operating income in column H in the lower-right pane.
150000
4%
15.75%
•
Enter
in cell B2,
in cell B3, and
in cell B8 (Figure 3 –80), which
causes the six-month operating income in cell H25 to increase from $2,567,592.65 to
$4,150,551.23.
To Goal Seek
If you know the result you want a formula to produce, you can use goal seeking to determine the value of
a cell on which the formula depends. The following steps close and reopen the Modern Music Shops Six-Month
Financial Projection workbook. They then use the Goal Seek command (Data tab | Data Tools group) to determine
the Support, General, and Administrative percentage in cell B8 that will yield a six-month operating income of
$3,000,000.00 in cell H25, rather than the original $2,567,592.65.
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education