Week 2 Assignment: Labor Budget

This assignment, which focuses on the Housekeeping Department budget for the Xavier Hotel, has two parts. In the first part, you will use the occupancy forecast to create a schedule for the Housekeeping Department. In the second part, you will use the schedule you created to calculate payroll costs.

Part 1 Schedule

In the first part of this assignment, you will use the occupancy forecast to create a schedule for the Housekeeping Department at the Xavier Hotel. In the attached Excel file, open the tab called “Schedule.” You will find a table that lists the occupancy forecast for each day in the first week of November.

1. Your first step is to determine the number of occupied rooms for each night. The Xavier Hotel has 450 guestrooms.

Follow these steps to determine the number of occupied rooms for each night:

· Total number of guestrooms × Forecast occupancy percentage ÷ 100 = Number of occupied rooms

For example, on Sunday, November 1, the forecast occupancy is 43%:

· 450 guestrooms × 43 ÷ 100 = 194 occupied rooms

2. Your next step is to determine how many employees to schedule for each night, based on the number of occupied rooms. The table includes information about several types of employee in the Housekeeping Department:

· Room attendants

· Housepersons (A.M.)

· Lobby Attendants

· Floor Supervisor

· Housepersons (P.M.)

For each type of employee, the table lists the productivity standard and the number of labor hours per shift. You need to determine:

a. the number of labor hours you need from each type of employee and

b. how many of each type of employee you need to schedule.

a. Follow these steps to determine the number of labor hours you need from each type of employee:

· Number of occupied rooms × Productivity standard = Labor hours

For example, on Sunday, November 1, 194 rooms are expected to be occupied. The productivity standard for room attendants is 0.5:

· 194 occupied rooms × 0.5 = 97 labor hours

b. Follow these steps to determine how many of each type of employee you need to schedule:

· Number of labor hours ÷ Labor hours per shift = Number of employees scheduled

For example, on Sunday, November 1, you need 97 labor hours from room attendants, who have 7 labor hours in their shifts:

· 97 labor hours ÷ 7 labor hours per shift = 14 room attendants scheduled

Note: When determining how many employees to schedule, you will need to round up or down based on the following rule of thumb:

· If you calculate that you need to schedule 2.1 to 2.4 employees, round down to 2.

· If you calculate that you need to schedule 2.5 to 2.9 employees, round up to 3.

3. Your final step is to determine the total number of each type of employee that needs to be scheduled for the week. For each type of employee, you will need to add together the number of employees scheduled for all 7 nights.

As you work through these calculations, enter the quantities in the table on the “Schedule” tab in Excel.

Part 2 Payroll

In the second part of the assignment, you will use the schedule you created to calculate payroll costs for the week of November 1-November 7. In the attached Excel file, open the tab called “Payroll.” You will find a table that lists the hours of pay per shift and the hourly pay rate for each type of employee in the Housekeeping Department.

1. Your first step is to determine the total number of each type of employee that needs to be scheduled for the week. You should be able to copy this information over from your final calculations on the “Schedule” tab.

2. Your next step is to determine the total hours of pay for the week for each type of employee.

Follow these steps to determine the total hours of pay for the week for each type of employee:

· Total employees scheduled for the week × Hours of pay per shift = Total hours of pay for the week

3. Your next step is to determine the total payroll for the week for each type of employee.

Follow these steps to determine the total payroll for the week for each type of employee:

· Total hours of pay for the week × Hourly pay rate = Total payroll for the week

4. Your final step is to determine the total payroll for the week for all of the employees in the Housekeeping Department. You will need to add together the total payroll for the week for all types of employees.

As you work through these calculations, enter the quantities in the table on the “Schedule” tab in Excel.

Submit your completed Excel file.

HOSP3012

College of Online Education

Johnson & Wales University