Microsoft Office Tutorials and References
In Depth Information
EX 186
Excel Chapter 3 What-If Analysis, Charting, and Working with Large Worksheets
Absolute versus Relative Addressing
The next sections describe the formulas and functions needed to complete the calculations
in the worksheet.
Plan
Ahead
Determine necessary formulas and functions needed.
The next step is to enter the formulas that calculate the following values for January: cost of
goods sold (cell B5), gross margin (cell B6), expenses (range B9:B13), total expenses (cell B14),
and the operating income (cell B16). The formulas are based on the projected monthly sales
in cell B4 and the assumptions in the range B19:B25.
The formulas for each column (month) are the same, except for the reference to the
projected monthly sales in row 4, which varies according to the month (B4 for January, C4 for
February, and so on). Thus, the formulas for January can be entered in column B and then
copied to columns C through G. Table 3–6 shows the formulas for determining the January
costs of goods, gross margin, expenses, total expenses, and operating income in column B.
If the formulas are entered as shown in Table 3–6 in column B for January and then copied
to columns C through G (February through June) in the worksheet, Excel will adjust the cell
references for each column automatically. Thus, after the copy, the February Commission
expense in cell C10 would be =C4 * C20. While the cell reference C4 (February Sales) is
correct, the cell reference C20 references an empty cell. The formula for cell C7 should
read =C4 * B20, rather than =C4 * C20, because B20 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.
Table 3–6 Formulas for Determining Cost of Goods, Margin, Expenses, Total Expenses,
and Operating Income for January
Cell
Row Title
Formula
Comment
B5
Cost of Goods Sold
=B4 * (1 B21)
Sales times (1 minus Margin %)
B6
Gross Margin
= B4 B5
Sales minus Cost of Goods
B9
Bonus
=IF(B4 >= B24, B19, 0)
Bonus equals value in B19 or 0
B10
Commission
=B4 * B20
Sales times Commission %
B11
Marketing
=B4 * B22
Sales times Marketing %
B12
Research and
Development
=B4 * B23
Sales times Research and
Development %
B13
Support, General, and
Administrative
=B4 * B25
Sales times Support, General, and
Administrative %
B14
Total Expenses
=SUM(B9:B13)
Sum of January Expenses
B16
Operating Income
=B6 B14
Gross Margin minus Total Expense
Absolute Referencing
Absolute referencing is
one of the more difﬁ cult
worksheet concepts to
understand. One point to
keep in mind is that the
paste operation is the only
operation affected by an
absolute cell reference.
An absolute cell reference
instructs the paste
operation to keep the
same cell reference as it
copies a formula from one
cell to another.
To keep a cell reference constant when copying a formula or function, Excel uses a
technique called absolute cell referencing. To specify an absolute cell reference in a formula,
enter a dollar sign (\$) before any column letters or row numbers you want to keep constant
in formulas you plan to copy. For example, \$B\$20 is an absolute cell reference, while B20 is a
relative cell reference. Both reference the same cell. The difference becomes apparent when
they are copied to a destination area. A formula using the absolute cell reference \$B\$20
instructs Excel to keep the cell reference B20 constant (absolute) in the formula as it copies
it to the destination area. A formula using the relative cell reference B20 instructs Excel to
adjust the cell reference as it copies it to the destination area. A cell reference with only one
dollar sign before either the column or the row is called a mixed cell reference . Table 3–7
gives some additional examples of absolute, relative, and mixed cell references. Search JabSto ::

Custom Search