Please help, I know this is a lot but please help me. Please

PROJECT 3 | VARIANCE ANALYSIS in SERVICE INDUSTRIES FLINT HILLS LODGE CASE Hattie Hughes is reecting on her hotel's performance over the last year. This is her second year as the General Manager of the Flint Hills Lodge, Next week, she will travel to a regional meeting in Omaha to report to the Vice President of Hotel Operations. She has gathered all of the necessary data but requires your team's help to run a variance analysis, interpret the results and prepare her report. The Flint Hills Lodge is a recently renovated, 126-room economy-lodging hotel in El Dorado, Kansas. Built in 1984, the Lodge is part of a privately-owned hotel chain headquartered in Des Molnes, Iowa. The chain owns and operates hotels across the Midwest. Hughes has already prepared a report comparing the original budget to the actual revenue and expense data (see the Project 3 data le, Table 1 Budget and Actual). A description of each account is provided as a tab within the data le. Human Resources provided Hughes with the hours worked by hotel staff (see Table 2 Hours) and Hughes shares the following with your team: 0 The general manager and maintenance worker are normally full-time workers, meaning they work 40 hours per week, 50 weeks a year. c All other workers are considered full time, though many of them work less than 40 hours per week (see Table 2 Hours). a In preparing the budget, Hughes planned on housekeeping staff using a standard time of 30 minutes per room with a wage rate of $7.00 per hour. - The head housekeeper's wage was budgeted at $9.50 per hour for 40 hours a week for 50 weeks a year. With a small staff, the head housekeeper also cleans rooms and Hughes prepared the budget assuming she would spend 2,000 hours cleaning rooms. Hughes tells you that her productivity may actually be lower (meaning she cleans fewer rooms) because of her supervisory duties (inspections and employee training). Accordingly, during slow periods, the head housekeeper may work fewer hours. - Hughes anticipated having one person stafng the front desk at all hours. a With the exception of the general manager, employees do not get paid vacation time. - Further, with high turnover, most employees do not receive benets (healthcare and retirement contributions). Outside of payroll costs, most of the other costs are fixed, with a few exceptions: supplies, credit card commissions, guest vouchers and refunded rentals. REQUIREMENTS: 1. Prepare a flexible budget based on the actual number of rooms rented. Your team should arrive at a total flexible budget of $587,476. Calculate a flexible budget variance for each line item. This portion of the project should be prepared and submitted via Excel.Budget Actual Rooms Available 45,990 45,990 Rooms Rented 27,376 25,435 Occupancy 59.5% 55.3% ADR $43.79 $44.65 Revenue per Available Room $26.07 $24.69 Revenues Room Rentals $1,200, 138 $1, 136,619 Revenue Allowance -$1,280 -$1,060 Total Room Revenue $1, 198,858 $1, 135,559 Internet Access $4,900 $5,534 Vouchers $5,000 $1,262 Laundry $1,310 $1,240 Vending $3,900 $3,708 Total Other Operating Revenue $15,110 $11,744 Total Revenue $1,213,968 $1,147,303 Expenses General Manage $28,800 $27,434 Front Desk Staff $72,000 $82,089 Total Front Office Payroll $100,800 $109,523 Head Housekeeper $19,000 $22,226 Housekeepers & Laundry $81,816 $82,556 Total Housekeeping Payroll $100,816 $104,782 Maintenance Wages $20,700 $23,577 Regional Maintenance Support Wages $3, 180 $6,452 Incentive Pay $0 $0 Bonus Expense $2,520 $1,346 Payroll Taxes $19,381 $21,339 Total Other Personnel Payroll $45.781 $52,714 Gross Payroll $247,397 $267,019 Vacation Pay $5,700 $3,494 Holiday Pay $1,620 $1,690 Staff Motivation $640 $639 Relocation Expense $0 $1.506 Training $2,260 $576 Workers Compensation $18,00 $17,588 Health Insurance $28,800 $20,005 401(K) Expense (Retirement) $720 6841 Life Insurance $672 $613 Total Benefits Expense $58,412 $46,952 Total Personnel Costs $305,809 $313,971Budget Actual Total Personnel Costs $305,809 $313,971 Supplies, Cleaning Chemicals $6.083 Supplies, Linens $13,916 Supplies, Others $26.007 $18.064 Total Supplies $38,063 Memberships & Subscriptions $30 SO Travel $3,360 $4.141 Meals Expense $840 $921 al Travel & Meals $4,50 $5,062 Bad Debt $350 Cash Over and Shor so O/S Bank Deposit so $113 $27 Credit Card Charge Back sol $619 Credit Card Fees 14,568 $14,376 Guest Vouchers $1,20 $540 Refunded Rentals $1,000 $1,887 Total Other Expense 16,768 $17,912 Other Operating Exp. & External Services $600 $122 Total Misc Operating Expenses $21,86 23,696 Property Marketing $6,000 $4,80 Building Repairs $1,800 $1,913 Landscaping $9,90 $10,524 Repair Contracts $8,10 $8,460 Laundry Repairs $6.000 $2,713 Room repairs (room, doors & windows) $2,400 $5,610 HVAC Repairs $4,800 $3,219 Pool Repairs $1,60 $2,447 Electrical & Fire Repairs $4,320 $5,852 Plumbing Repairs $3,300 $5,243 Maintenance Supplies $3,600 $4,160 Unclassified maintenace (petty cash) $900 $2.411 Total Repairs & Maintenance $46,720 $52,552 Electricity $42,000 Gas $16,800 $36,144 $13,428 Water $33,000 $21,919 Total Energy $91,800 $71.491 Trash $3.720 $3.494 Sewe 18,900 16,160 Salt $2,100 $1,740 Telephone $12,00 $15.00 $10,524 Satellite Television Fees $13.413 Utility Fees $900 $802 Total Other Utilities $46.133 TOTAL EXPENSES $550,824 $550,713 PROFIT $663,144 $596,590Hours Hourly Employee ID Worked Wage Rate General Manager 44215 2,040.00 $13.45 Front Desk Employees 251256 2,077.10 $8.98 488442 (front desk supervisor 2,087.50 $9.72 743796 1,610. 10 58.45 766468 1,520.00 $8.55 371219 ,425. 10 $8.60 980511 517 .1 $8.30 Head Housekeeper 134076 2,291.30 $9.70 Housekeepers 172931 1,462.20 $8.15 215403 1,627.80 $8.10 219100 1,249.20 $8.10 264365 1, 117.20 $7.60 306002 1, 188.00 $7.65 322777 1, 151.50 $7.55 352605 423.6 $7.40 586120 43.7 $7.30 693453 245.7 $7.35 696487 155.5 $7.25 724731 73.4 $7.25 729577 511.1 $7.60 748933 111.6 $7.25 772040 121.4 $7.30 805364 282.4 $7.30 828589 125.4 $7.25 363038 63.6 $7.25 882112 21.9 $7.25 85651 235.4 $7.30 901 105 124.2 $7.25 911934 320 $7.35 Maintenance Staff 288133 1,966.30 $11.99 166365 (regional support) 367.4 $17.56