Microsoft Office Tutorials and References
In Depth Information
Excel Chapter 3 What-If Analysis, Charting, and Working with Large Worksheets
In the Lab continued
Table 3–14 RockieView Resort and Spa Worksheet Data
Total Net Revenue
Cost of Sales
Instructions Part 1: Do the following to create the worksheet shown in Figure 3–88.
1. Apply the Solstice theme to the worksheet. Bold the entire worksheet by selecting the entire
worksheet and using the Bold button on the Ribbon.
2. Change the following column widths: A = 28.00; B through I = 13.00; J = 14.00.
3. Enter the worksheet titles in cells A1 and A2 and the system date in cell J2. Format the date to the
4. Enter the column titles, row titles, and the ﬁ rst three rows of numbers in Table 3–14 in rows 3
through 6. Center and italicize the column headings in the range B3:J3. Add a thick bottom border
to the range B3:J3. Sum the individual rows 4, 5, and 6 in the range J4:J6.
5. Enter the Square Footage row in Table 3–14 with the comma format symbol in row 16. Sum row
16 in cell J16. Use the Format Painter button to format cell J16. Change the height of row 16 to
39.00. Vertically center the range A16:J16 through the use of the Format Cells dialog box.
6. Enter the remaining row titles in the range A7:A17 as shown in Figure 3–88. Increase the font size
in cells A7, A14, and A15 to 16-point.
7. Copy the row titles in range A8:A13 to the range A18:A23. Enter the numbers shown in the range
B18:B23 of Figure 3–88 with format symbols.
8. The planned indirect expenses in the range B18:B23 are to be prorated across the proﬁ t center as
follows: Administrative (row 8), Energy (row 10), and Marketing (row 13) on the basis of Total Net
Revenue (row 4); Depreciation (row 9), Insurance (row 11), and Maintenance (row 12) on the basis
of Square Footage (row 16). Use the following formulas to accomplish the prorating:
a. Banquet Room Administrative (cell B8) = Administrative Expenses * Banquet Room Total Net
Revenue / Resort Total Net Revenue or =$B$18 * B4 / $J$4
b. Banquet Room Depreciation (cell B9) = Depreciation Expenses * Banquet Room Square
Footage / Total Square Footage or =$B$19 * B16 / $J$16
c. Banquet Room Energy (cell B10) = Energy Expenses * Banquet Room Total Net Revenue /
Resort Total Net Revenue or =$B$20 * B4 / $J$4
d. Banquet Room Insurance (cell B11) = Insurance Expenses * Banquet Room Square Feet / Total
Square Footage or =$B$21 * B16 / $J$16
e. Banquet Room Maintenance (cell B12) = Maintenance Expenses * Banquet Room Square
Footage / Total Square Footage or =$B$22 * B16 / $J$16
f. Banquet Room Marketing (cell B13) = Marketing Expenses * Banquet Room Total Net
Revenue / Resort Total Net Revenue or =$B$23 * B4 / $J$4
g. Banquet Room Total Indirect Expenses (cell B14) = SUM(B8:B13)
h. Banquet Room Net Income (cell B15) = Total Net Revenue
(Cost of Sales + Direct
Expenses + Total Indirect Expenses) or =B4
(B5 + B6 + B14)
Copy the range B8:B15 to the range C8:I15.
Sum the individual rows 8 through 15 in the range J8:J15.