For Project 2 part 1, complete and submit the second Careers in Practice (Skil

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”).