For Project 2 part 1, complete and submit the second Careers in Practice (Skills Review) Hotel Management Cost Analysis starting on page 180. Please note on page 181, the number of days is in the month is in cell B5 (not C5) and on page 182, for items 9 and 10 you are to enter the function to calculate the highest and lowest Cleaning Expense (not the Actual Occupancy again). Submit your completed Excel file here.
E6:E16. Use a paste method that does not remove the border at the bottom of cell E16.
Saylor URL: http://www.saylor.org/books Saylor.org
182
4. Enter a function in cell C17 on the Historical Costs worksheet that sums the values in the range
C5:C16. Copy the function and paste it into cells D17 and F17. Use a paste method that does not
change the border on the right side of cell F17.
5. Copy the formula in cell E16 and paste it into cell E17. Use a paste method that does not change
the border at the bottom of cell E17.
6. Sort the data in the Historical Costs worksheet based on the values in the Actual Occupancy
column in descending order (largest to smallest). For any duplicate values in the Actual
Occupancy column, sort using the values in the Cleaning Expenses column in descending order.
7. On the Cost Analysis worksheet, enter a function into cell B3 that shows the highest value in the
range D5:D16 in the Actual Occupancy column on the Historical Costs worksheet.
8. On the Cost Analysis worksheet, enter a function into cell B4 that shows the lowest value in the
range D5:D16 in the Actual Occupancy column on the Historical Costs worksheet.
9. On the Cost Analysis worksheet, enter a function into cell C3 that shows the highest value in the
range F5:F16 in the Actual Occupancy column on the Historical Costs worksheet.
10. On the Cost Analysis worksheet, enter a function into cell C4 that shows the lowest value in the
range F5:F16 in the Actual Occupancy column on the Historical Costs worksheet.
11. On the Cost Analysis worksheet, format cells B3 and B4 with a comma and zero decimal places.
Format cells C3 and C4 with US dollars with zero decimal places.
12. On the Cost Analysis worksheet, enter a formula in cell B5 that subtracts the lowest actual
occupancy value from the highest actual occupancy value. Copy this formula and paste it into cell
C5.
13. Enter a formula in cell C6 on the Cost Analysis worksheet that calculates that variable cost
portion for the cleaning expenses per month. As mentioned in the introduction to this exercise,
the cleaning expense contains costs that increase with each room that is cleaned. This is known as
a variable expense and can be estimated by dividing the Actual Occupancy High Low Difference
(cell B5) into the Cleaning Expenses High Low Difference (cell C5). Format the output of this
formula to US dollars with two decimal places.
14. Enter a formula in cell C7 on the Cost Analysis worksheet that calculates the fixed cost portion
for the cleaning expenses per month. This is the amount of money that will be spent on cleaning
Saylor URL: http://www.saylor.org/books Saylor.org
183
expenses no matter how many rooms are cleaned. Since we have calculated the variable cost
portion of the cleaning expense, we can now use it to calculate the fixed expense. To do this,
subtract from the High Cleaning Expense (cell C3) the result of multiplying the variable expense
(cell C6) by the High Actual Occupancy (cell B3). Format the result of the formula to US dollars
with zero decimal places.
15. Enter the number 3500 in cell C2 on the Cleaning Cost Estimatesworksheet. Format the
number with commas and zero decimal places.
16. Apply a yellow fill color to cell C2 on the Cleaning Cost Estimatesworksheet. This is being
formatted to indicate to the user of this worksheet that a number is to be entered into the cell.
17. On the Cleaning Cost Estimates worksheet, enter a formula in cell C3 that calculates the
estimated cleaning expenses given the number that was entered into cell C2. Now that we have
calculated the variable and fixed expenses on the Cost Analysis worksheet, we can use the
results to estimate the cleaning expenses. The formula is a + bX, wherea is the fixed cost, b is the
variable cost, and X is the activity level that is typed into cell C2. The fixed cost is added to the
result of multiplying the variable cost by the activity level in cell C2. Format the output of the
formula to US dollars with zero decimal places.
18. Save the workbook by adding your name in front of the current workbook name (i.e., “your
name Chapter 2 CiP Exercise 2”).
