What-If Analysis, Charting, And Working With Large Worksheets Page 27
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
87What-If Analysis, Charting, and Working with Large Worksheets
Excel Chapter 3
EX 163
Table 3 – 7 Formulas for Determining Cost of Goods Sold, Margin, Expenses, Total Expenses,
and Operating Income for July
Cell
Row Title
Formula
Comment
B14
Cost of Goods Sold
=B13 * (1 – B4)
Revenue times (1 minus Margin %)
B15
Gross Margin
= B13 – B14
Revenue minus Cost of Goods Sold
B18
Bonus
=IF(B13 >= B7, B2, 0)
Bonus equals value in B2 or 0
B19
Commission
=B13 * B3
Revenue times Commission %
B20
Marketing
=B13 * B5
Revenue times Marketing %
B21
Research and Development
=B13 * B6
Revenue times Research and
Development %
B22
Support, General, and
=B13 * B8
Revenue times Support, General,
Administrative
and Administrative %
B23
Total Expenses
=SUM(B18:B22)
Sum of July Expenses
B25
Operating Income
=B15 – B23
Gross Margin minus Total Expenses
As the formulas are entered as shown in Table 3 – 7 in column B for July and then copied to columns C
through G (August through December) in the worksheet, Excel will adjust the cell references for each column
automatically. Thus, after the copy, the August Commission expense in cell C19 would be =C13 * C3. While the cell
reference C13 (February Revenue) is correct, the cell reference C3 references an empty cell. The formula for cell
C7 should read =C13 * B3, rather than =C13 * C3, because B3 references the Commission % value in the What-If
Assumptions table. In this instance, a way is needed to keep a cell reference in a formula the same, or constant, when
it is copied.
The following steps enter the cost of goods formula = B13*(1 – $B$4) in cell B14 using Point mode. To enter
an absolute cell reference, you can type the dollar sign ($) as part of the cell reference or enter it by pressing f4 with
the insertion point in or to the right of the cell reference to change it to absolute.
1
•
+
Press
to select cell B13
CTRL
HOME
and then click cell B14 to show cell
B13 and to select the cell in which to
Enter box
formula appears
enter the fi rst formula.
in formula bar
=
•
Type
(equal sign), select cell B13,
*(1–b4
type
to continue entering
the formula, and then press
to
F
4
change the most recently typed cell
reference, in this case cell b4, from a
relative cell reference to an absolute
)
cell reference. Type
to complete
the formula (Figure 3–25).
Is an absolute reference required in
cell $B$4 is absolute
cell B13 is relative
this formula?
No, because a mixed cell reference
could have been used. The formula
Figure 3 – 25
in cell B14 will be copied across
columns, rather than down rows. So, the formula entered in cell B14 in Step 1 could have been
entered as =B13*(1–$B4), rather than =B13*(1–$B$4). That is, the formula could have included
the mixed cell reference $B4, rather than the absolute cell reference $B$4. When you copy a
formula across columns, the row does not change anyway. The key is to ensure that column B
remains constant as you copy the formula across columns. To change the absolute cell
reference to a mixed cell reference, continue to press the
key until you achieve the desired
F
4
cell reference.
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education