Hotel Occupancy and Expenses

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.

There are three worksheets in the workbook for this assignment.

• Occupancy – calculates and displays the maximum hotel capacity for each month (based on the number of rooms, the capacity of each room, and the number of days in the specific month), the actual occupancy (how many actually stayed in the hotel that month), and the occupancy percentage (what percentage of capacity was the hotel each month).
• Statistics – calculates and displays the highest, lowest, and average actual occupancy and occupancy percentages form the Occupancy worksheet.
• Shuttle Purchase – calculates three different down payment options for a loan to purchase a shuttle for the hotel.

Occupancy Worksheet

1. Open the file named SC2 Data and then Save As SC2 Hotel.
2. Switch to the Occupancy worksheet if needed.
3. Replace the [Insert Year] in A1 with the year number for last year.
4. You need to calculate the January capacity for the hotel in C5. 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.
5. 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.
6. Format the numbers in columns C and D for comma format with zero decimal places.
7. In cell C17, enter a function that finds the sum of the monthly hotel capacity values.  Do the same in cell D17 to find the sum of the monthly actual occupancy values.
8. Enter a formula in cell E5 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:E17. Use a paste method that does not remove the borders at the bottom of cell E16 and E17. Format the results in E5:E17 as percentages with two decimal places.
9. Format the Totals (C17:E17) as bold.
10. Apply any number formatting that aids in the readability and professionalism of the worksheet.

Statistics Worksheet

1. Replace the [Insert Year] in A1 with the year number for last year.
2. Enter a function in cell B3 that finds the highest value in the Actual Occupancy column from the Occupancy worksheet.
3. Enter a function in cell B4 that finds the lowest value in the Actual Occupancy column from the Occupancy worksheet.
4. Enter a function in cell B5 that shows the average value of the Actual Occupancy column on the Occupancy worksheet.
5. Use the Auto Fill handle to copy the formulas in the range B3:B5 to the range C3:C5.
6. Apply any number formatting that aids in the readability and professionalism of the worksheet. The numbers should be formatted similarly to the Occupancy worksheet.

Shuttle Purchase Worksheet

The hotel is considering buying a car to shuttle customers to and from the airport.  You need to decide how much of a down payment to make, so you are going to calculate the monthly payment based on three different down payment percentages.  The number of years to pay off the loan will vary for each of the down payment percentage options. Remember, the down payment amount is found by multiplying the price of the car by the down payment percentage. This amount is then subtracted from the price of the car to find the amount of the loan.

1. In cell B5 write a formula that will calculate the amount of the down payment. Be sure to use cell references as much as possible, even for the 0% down payment option. Copy the formula to the other down payment options.
2. In cell B6 write a formula to calculate the amount of the loan. Be sure to use cell references as much as possible. Copy the formula to the other down payment options.
3. In cell B9 create a PMT function to calculate the monthly payment. Make sure the arguments in the PMT function are converted into months and that the monthly payment is a positive number. Be sure to use cell references as much as possible. Copy the function to the other down payment options.
4. In cell B10 create a formula that calculates how much will be paid in total for the vehicle, including the down payment and the total amount paid on the loan in the given number of years.  Copy the formula to the other down payment options.
5. In cell A12, write an explanation of which down payment option is the best and why.
6. Apply any number formatting that aids in the readability and professionalism of the worksheet.

FInalizing the Worksheets

1. Make the following page setup changes to all of the worksheets:
   • Center horizontally and vertically on the page
   • Create a footer with the date on the left and the file name on the right.  Make sure that both the date and the file name will update automatically.
2. Check the spelling on all of the worksheets and make any necessary changes.
3. Save the SC2 Hotel workbook and submit the workbook as directed by your instructor.

Attribution

Adapted from Beginning Excel 2019 and licensed under CC BY.