2.9 Scored Assessment: SC2 Hotel
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
- Open the file named SC2 Data and then Save As SC2 Hotel.
- Switch to the Occupancy worksheet if needed.
- Replace the [Insert Year] in A1 with the year number for last year.
- 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.
- 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.
- Format the numbers in columns C and D for comma format with zero decimal places.
- 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.
- 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.
- Format the Totals (C17:E17) as bold.
- Apply any number formatting that aids in the readability and professionalism of the worksheet.
Statistics Worksheet
- Replace the [Insert Year] in A1 with the year number for last year.
- Enter a function in cell B3 that finds the highest value in the Actual Occupancy column from th