Using Excel and the information below, prepare next year's operating budget for a 185-room full service hotel. Use 4 separate worksheets to create 3 individual departmental budget schedules (Rooms, F&B and Admin) then a consolidated summary operating budget (like a summary operating statement that provides net operating income). The budget project is to be completed individually. The budget departmental schedules and summary operating budget statement must follow the proper USALI format.

Your workbook must contain 4 properly named worksheets in this order: (1) Summary Operating budget, (2) Rooms budget, (3) F&B budget and the (4) Administration Budget (which has no revenues).

Rooms Department information:

Revenues: Occupancy is expected to be 69.5% next year. ADR for transient rooms sold is expected @ $144.55 and Group rooms are expected to have an ADR of $138.50. The typical room sales mix is 70% transient and 30% Groups

Expenses (Payroll): There are four lines to budget for here.

1. The front office payroll is usually 7.5% of room revenues.

2. Fixed housekeeping wages represent 4.5% of room revenues

3. Room attendant wages (average wage is 17.00/ hour, attendants clean a room in 30 minutes)

4. Rooms staff benefits represent 10% of total rooms dept. payroll.

Other Rooms Department Expenses:

1. Guest Comfort (amenities) $10.25 CPOR

2. Laundry & Linen $10.55 CPOR

3. Training 2.75% of rooms revenue

Food & Beverage Department information:

Food Revenues:

1. The dining room: 75% of forecasted dining room food revenues are based on average food sales of $23.75 per room night. The remainder comes from outside guests, not staying at the hotel. (this amount is intentionally left out; you have enough information to figure out the extra 25%)

2. Rooms Service: Room service food revenues (not including beverage sales) are estimated at $8 per room night.

Beverage Revenues:

1.The minibar: Minibar revenues average out to $2.50 per room night 2. The bar: Bar revenues typically represent 30% of total food revenues

Cost of Sales:

1. Food Cost 33.5%

2. Beverage Costs 39.75%

Payroll:

1. Staff payroll represents 18.5% of F&B revenues

2. Benefits are 10% of staff payroll costs

Other expenses:

1. China & Glassware is 3.75% of F&B revenues

2. Laundry is 2. 5% of F&B revenues

3. Training is 5.65% of F&B revenues

Administration & General Department information: (no revenues just expenses)

Payroll:

1. General Manager's Office 2. 5% of total revenues

2. Department Heads 5.15% of total revenues

3. Benefits 10% of administration's payroll costs

Other Expenses:

1. Management travel and training 4% of total administration payroll 2. Uniforms 0.35% of total revenues

3. Sales & Marketing 8.15% of total revenues

4. Printing & Stationary 0.25% of total revenues

5. Bank Charges 0.65% of total revenues

6. Cleaning Materials 2.75% of total revenues

7. Contract Maintenance 2.15% of total revenues

EXAMPLE

Rooms Department Schedule Budget for 2019 Rooms Available 36,500 Rooms Sold 25,550 Occupancy % 70.0% ADR 5 131.55 Rooms RevPAR $ 92.09 Total RevPAR $ 127.51 REVENUES Transient Revenues $ 2,441,303 Group Revenues $ 919,800 Total Room Revenues $3,361,103 EXPENSES Payroll Front Office S 235,277 Housekeeping $ 117,639 Room Attendant $ 204,400 Rooms Staff Benefits S 55,732 Total Payroll $ 613,047 OTHER ROOMS EXPENSES Guest Comfort 5 191,625 Laundry and Linen $ 178,850 Training 35 63,861 Total Other Expenses $ 434,336 DEPARTMENTAL PROFIT (L055) 5 2,313,719 72. 63% 27. 37% 100%: 7.00% 3. 50% 5.08% 1. 65% 18. 24% 5.70% 5.32% 1.90% 12.92% 68.84% I'OOCI and Beverage Departmental acneome Budget for 2019 Rooms Available 36,500 Rooms Sold 25,550 Occupancy % 70.0% ADR S 131.55 Rooms RevPAR S 92.09 Total RevPAR S 127.51 REVENUES Food Revenues S 930,750 Beverage Revenues S 362,263 Total Departmental Revenue S 1,293,013 COST OF SALES Food Cost S 279,225 Beverage Cost S 101,434 Total Cost of Sales S 380,659 PAYROLL Staff S 258,602.50 Benefits S 25,860.25 Total Payroll Expense S 284,463 OTHER EXPENSES China 8: Glassware S 19,395 Laundry S 25,860 Training S 77,581 Total Other Expenses S 122,836 DEPARTMENTAL PROFIT (L055) 5 505,055 71. 98% 28.02% 100. 00% 30. 00% 28. 00% 29. 44% 20% 2. 00% 22. 00% 1.5% 2.00%: 6.0%: 9.50%, 39.06% Administration Departmental Schedule Budget for 2019 PAYROL EXPENSES General Manager Department Heads Benefits Total Payroll (ninth-tn- OTHER EXPENSES Management Travel and Training Uniforms Sales and Marketing Printing and Stationary Bank Charges Cleaning Materials Contract Maintenance Total Other Expenses DEPARTMENTAL PROFIT (LOSS) mmmmmmmm 130,315 255,976 38,629 424,921 19, 121 23,271 325,788 34,906 13,962 116,353 139,623 673,025 2.80% 5.50% 0.83% 9.13% 0.41% 0.50% 7.0% 0.75% 0.30% 2.50% 3.00% 14.46% Summary Operating Statement Budget for 2019 Rooms Available 36,500 Rooms Sold 25,550 Occupancy % 70.0% ADR 131.55 Rooms RevPAR 92.09 Total RevPAR 127.51 OPERATING REVENUE Rooms $ 3,361, 103 72.22% Food and Beverage $ 1,293,013 27.78% Total Operating Revenue $ 4,654, 115 100% DEPARTMENTAL EXPENSES Rooms $ 1,047,383 22.50% Food and Beverage $ 787,957 16.93% Total Departmental Expenses $ 1,835,341 39.43% TOTAL DEPARTMENTAL PROFIT $ 2,818,774 60.57% UNDISTRIBUTED OPERATING EXPENSES Admin and General 444,042 9.54% Uniforms 23,271 0.50% Sales and Marketing 325,788 7.00% Printing and Stationary 34,906 0.75% Bank Charges 13,962 0.30% Cleaning Materials 116,353 2.50% Contract Maintenance $ 139,623 3.00% Total Undistributed Expenses $ 1,097,945 23.59% GROSS OPERATING PROFIT $ 1,720,829 36.97%