Hotel Occupancy and Expenses

Download Data File: SC2 Data

The hotel management industry presents a wide variety of career opportunities. These range from running a bed and breakfast to a management position at a large hotel. No matter what hotel management career you choose to pursue, understanding hotel occupancy and costs are critical to running a successful operation. This exercise examines the occupancy rate and expenses of a small hotel.

1. Open the file named SC2 Data and then Save As SC2 Hotel.
2. Enter a formula in cell C5 on the Occupancy worksheet to calculate the January capacity for the hotel. The capacity shows how many people the hotel can hold during the month. It is calculated by first multiplying the occupants per room by the number of rooms in the hotel. This result is then multiplied by the number of days in the month (cell B5 for January). Create this formula using absolute references so that the appropriate cells do not change when the formula is pasted throughout column C. Hint: two of the cells in the formula need to be absolute references.
3. Copy the formula in cell C5 and paste it into the range C6:C16. Use a paste method that does not remove the border at the bottom of cell C16.
4. Enter a formula in cell E5 on the Occupancy worksheet to calculate the Percent Occupied of the hotel (this statistic shows what percentage of the hotel is full or occupied). Your formula should divide the Actual Occupancy by the Hotel Capacity. Then copy and paste the formula into the range E6:E16. Use a paste method that does not remove the border at the bottom of cell E16. Format the results in E5:E16 as percentages with two decimal places.
5. Enter a function in cell C17 on the Occupancy worksheet that sums the values in the range C5:C16. Copy the function and paste it into cell D17.
6. Copy the formula in cell E16 and paste it into cell E17. Make sure cell E17 is formatted as a percentage with two decimals and bold.
7. On the Statistics worksheet, enter a function into cell B3 that shows the highest value (Max) in the range D5:D16 in the Actual Occupancy column on the Occupancy worksheet.
8. On the Statistics worksheet, enter a function into cell B4 that shows the lowest value (Min) in the range D5:D16 in the Actual Occupancy column on the Occupancy worksheet.
9. On the Statistics worksheet, enter a function into cell B5 that shows the average value in the range D5:D16 in the Actual Occupancy column on the Occupancy worksheet.
10. Use the Auto Fill handle to copy the formulas in the range B3:B5 to the range C3:C5.
11. Format the range B3:B5 for comma format with zero decimal places. Format cells C3:C5 as percentages with two decimal places.
12. The hotel is considering buying or leasing a car to shuttle customers to and from the airport. You hope to keep the monthly payment under $400 and will determine whether leasing or buying will meet that goal. On the Lease or Buy worksheet, type the terms in Figure 2.48 below for the purchase vs. lease of the car. Make sure that dollar amounts and percentages are formatted to match Figure 2.48:
    

    

13. In cell B8 create a PMT function to calculate the Monthly Payment if you purchase the car. Make sure the arguments in the PMT function are converted into months and that the Monthly Payment is a positive number.
14. In cell C8 create a PMT function to calculate the Monthly Payment if you lease the car. The car will have a residual value of $15,000 when the lease is over. Assume that payments are made at the end of the month.
15. Format the Monthly Payments in B8:C8 for Accounting Number Format with two decimals.
16. Select the range A4:A8 and click the Increase Indent button once to indent the labels in column A.
17. From the Page Layout tab, access the Page Setup dialog box launcher and center the Lease or Buy worksheet horizontally on the page.
18. Insert a footer on the Lease or Buy worksheet. Insert the date (use the Insert Date button) in the left section of the footer. Insert the File Name (use the Insert File Name button) in the right section of the footer.
19. Save the SC2 Hotel workbook.
20. Submit the SC2 Hotel workbook as directed by your instructor.

Attribution

Adapted by Mary Schatz from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.