Microsoft Office Tutorials and References
In Depth Information
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,
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
CTRL + HOME to select cell B13
and then click cell B14 to show cell
B13 and to select the cell in which to
enter the i rst formula.
Press
Enter box
formula appears
in formula bar
= (equal sign), select cell B13,
type *(1–b4 to continue entering
the formula, and then press F 4 to
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
this formula?
No, because a mixed cell reference
could have been used. The formula
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 F4 key until you achieve the desired
cell reference.
Type
cell \$B\$4 is absolute
cell B13 is relative
Figure 3 –25

Search JabSto ::

Custom Search