Scheduling / Inventory Project

Scheduling / Inventory Project

You are the Executive Housekeeper at a Resort Hotel. Your General Manager has asked you to prepare two reports for the Budget Committee. Using Excel spreadsheets, include the following information to prepare the reports:
Report #1 – Annual Staffing / Payroll
Number of Hotel Rooms 3500
Projected Occupancy % (assume same for all 52 weeks)
Average Occupancy Sunday – Thursday 80%
Average Occupancy on Fri, Saturday 90%
Average Room Rate
Sun – Thursday $ 99
Fri, Saturday $ 129
Housekeeping Staff Considerations
1 Exec. Housekeeper @ $75,000 annual Salary
2 Assistant Housekeepers @ $50,000 annual salary
2 Warehouse Managers @ $40,000 annual salary
2 Linen Managers @ $35,000 annual salary
4 Status Board Operators / Pay rate is $11.00 p/hour
Each GRA completes 15 rooms daily / Pay rate is $12.00 per hour
Each House Porter is responsible for assisting 6 GRAs / Pay rate is $15.00 per hour
Each Utility Porter is responsible for 100 occupied rooms / Pay rate is $17.00 p/hour
Each Floor Inspector supervises 12 GRAs / Pay rate is $18.00 p/hour
Employee benefits for Full-time employees = 40% of base pay rate
Hotel / Housekeeping operates 24/7/ 365
Part-time employees receive no benefits
All Full-time employees work 8 hours shifts, 5 days p/week
Part time / Relief employees work 8 hour shifts, but not necessarily 5 days p/week
Round all Employees, Rooms Sold to whole numbers. Calculate Payroll costs in actual numbers.
Report 2: Annual Linen Inventory cost
Room Types:
70% of rooms have 2 queen size beds, 30% have 1 king-size bed.
Rollaway beds are 10% of total rooms
Linen Requirement p/Bed Type
King Beds – 1 king bedpad, 2 king sheets, 1 king blanket, 1 king bedspread, 4 regular pillows with cases
Queen Beds -2 queen sheets 1 queen blanket, 1 queen bedspread, 2 regular pillows with cases
Rollaway Beds – 2 double sheets, 1 double blanket, 1 regular pillow with case
Terry Requirement p/room
Each room requires 4 Bath Towels, 4 Face Towels, 2 Washcloths, 1 Bathmat, 1 Shower curtain
Par Stock Requirements
King: Sheets – 3 Par, blankets 2 Par, bedspreads 1.5 Par, Pillows 1.5 Par, Bedpads 1.5 Par
Queen: Sheets – 3 par, blankets 1.5 Par, bedspreads 1.5 Par, Pillows 1.5 Par, Bedpads 1.5 par
Roll away beds – Double sheets 1.5 Par, Double blankets 1.5 Par, Pillows 1.5 Par
Pillow cases – 3 par Bath towels – 3 Par Face towels – 3 Par
Washcloths – 2 Par Bathmats – 1.5 Par Shower curtains – 1.5 Par
Linen Purchase Cost
King: sheets $ 8.00 each, blankets $12.00 each, bedpads $ 5.00 each, bedspreads $20.00 each
Queen: sheets $ 6.00 each, blankets $10.00 each, bedpads $ 4.00 each, Beadspreads $18.00 each
Pillows $5.00 each, Pillow cases $ 2.00 each
Double sheets $5.00 each Double Blankets $7.00 each Double Pads $3.00 each
Terry Purchase Cost
Bath Towels $ 8.00 each Face towels $ 5.00 each
Washcloths $ 1.00 each Bathmats $ 3.00 each
Shower curtains $ 6.00 each

Round all Linen goods to whole numbers. Calculate Purchase costs in actual numbers.