You have been asked to help prepare the operating budget for a proposed new hotel that has the following 125 rooms keys A 65 seat coffee shop A75 seat dining room A 90 seat cocktail lounge The operating budget for the first year (365 days) will be based on the following information Rooms Department Occupancy is 60 percent The average room rate of $263 Fixed payroll for bell staff, front office employees, and other personnel attached to the rooms department are estimated at $526,900 For every 15 rooms occupied each day, one housekeeper will be required for an eight hour shift at a rate of $12 50 an hour Staff benefits expense will be 18 of total payroll Linen and laundry costs will be 6 of total rooms' revenue Supplies and other items will be 3 of total rooms' revenue Food and Beverage Department Food The dining room is open 6 days a week, 52 weeks a year for lunch and dinner only Lunch seat turnover is 1 5, with an average food check of $28 00 Dinner seat turnover is 1 0, with an average food check of $44 00 The coffee shop is open 7 days a week for all meal periods Breakfast seat turnover is 1 0, with an average food check of $15 00 Lunch seat turnover is 1 5 with an average food check of $25 00 Dinner seat turnover is 0 75, with a $30 00 average food check The cocktail lounge serves an estimated 20 food orders per day, with an $18 50 average check The lounge is closed on Sundays and certain holidays and only operates for 310 days during the year Beverage Operates 310 days a year Each seat in the cocktail lounge is expected to generate $5,250 per year In addition, the lounge revenue will reflect alcoholic beverages served in the coffee shop and dining room Coffee shop Beverage revenue is estimated to be 25 percent of combined lunch and dinner food revenue Dining room Beverage revenue is estimated to be 30 of combined lunch and dinner food sales revenue Department Expenses Total fixed payroll costs equal $787,315 Total variable payroll costs equal 10 of total food and beverage revenue Fringe benefits equal 18 of total food and beverage revenue Other costs are based on a percentage of total food revenue Food cost is 25 of food revenue Beverage cost is 20 of total beverage revenue Laundry and linen 6 of total food and beverage revenue Supplies Food 8 of total food revenue Supplies Beverage 5 of total beverage revenue Miscellaneous Operating Expenses 3 of total food and beverage revenue Undistributed Expenses Administrative and general $856,800 Telecommunications and Information Systems 152,400 Marketing 547,600 Utilities costs 452,400 Property operation maintenance 358,900 Required Summary Operating Statement Rooms Food and Beverage Complete the budget for the Rooms Department Complete the budget for the Food and Beverage Department Create consolidated schedule that combines the Rooms and Food and Beverage Department and reflects the Undistributed Expenses For the purposes of this budget round all figures (no decimal points) Include a vertical analysis in all the schedules All schedules must use formulas, if no formulas are used points will be deducted Must be filled out like the template below Summary Operating Statement Budget Revenue Room Food and Beverage Total Operating Revenue Departmental Expenses Room Food and Beverage Total Departmental Expenses Departmental Profit Undistributed Expenses Assumptions Administrative and General Telecommunications and Information Systems Sales and Marketing Property Operation and Maintenance Utilities Total Undistributed Expesnes Gross Operating ProfitRooms Department Budget Assumption Budget Keys Total Available Rooms Occupancy Rooms Sold Average Daily Rate (ADR) Budget Rooms Revenue Payroll Expenses Management Payroll Non Management Payroll Total Payroll Benefits Expense Total Payroll Expenses Other Operating Expenses Linen and Laundry Supplies Total Other Expenses Rooms Departmental Profit (Loss)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 23, 2024

You have been asked to help prepare the operating budget for a proposed new hotel that has the following: 125-rooms/keys A 65-seat coffee shop A75-seat

You have been asked to help prepare the operating budget for a proposed new hotel that has the following:

125-rooms/keys
A 65-seat coffee shop
A75-seat dining room
A 90-seat cocktail lounge

The operating budget for the first year (365 days) will be based on the following information:

Rooms Department Occupancy is 60 percent
The average room rate of $263.
Fixed payroll for bell staff, front-office employees, and other personnel attached to the rooms department are estimated at $526,900.
For every 15 rooms occupied each day, one housekeeper will be required for an eight-hour shift at a rate of $12.50 an hour.
Staff benefits expense will be 18% of total payroll.
Linen and laundry costs will be 6% of total rooms' revenue.
Supplies and other items will be 3% of total rooms' revenue.
Food and Beverage Department
Food
The dining room is open 6 days a week, 52 weeks a year for lunch and dinner only.
Lunch seat turnover is 1.5, with an average food check of $28.00.
Dinner seat turnover is 1.0, with an average food check of $44.00.
The coffee shop is open 7 days a week for all meal periods.
Breakfast seat turnover is 1.0, with an average food check of $15.00
Lunch seat turnover is 1.5 with an average food check of $25.00
Dinner seat turnover is 0.75, with a $30.00 average food check.
The cocktail lounge serves an estimated 20 food orders per day, with an $18.50 average check.
The lounge is closed on Sundays and certain holidays and only operates for 310 days during the year.
Beverage
Operates 310 days a year
Each seat in the cocktail lounge is expected to generate $5,250 per year
In addition, the lounge revenue will reflect alcoholic beverages served in the coffee shop and dining room.
Coffee shop
Beverage revenue is estimated to be 25% percent of combined lunch and dinner food revenue
Dining room
Beverage revenue is estimated to be 30% of combined lunch and dinner food sales revenue.
Department Expenses
Total fixed payroll costs equal $787,315
Total variable payroll costs equal 10% of total food and beverage revenue
Fringe benefits equal 18% of total food and beverage revenue
Other costs are based on a percentage of total food revenue:
Food cost is 25% of food revenue
Beverage cost is 20% of total beverage revenue.
Laundry and linen 6% of total food and beverage revenue
Supplies?Food 8% of total food revenue
Supplies?Beverage 5% of total beverage revenue
Miscellaneous Operating Expenses 3% of total food and beverage revenue
Undistributed Expenses
Administrative and general $856,800
Telecommunications and Information Systems 152,400
Marketing 547,600
Utilities costs 452,400
Property operation & maintenance 358,900

Required:

Summary Operating Statement
Rooms
Food and Beverage
Complete the budget for the Rooms Department
Complete the budget for the Food and Beverage Department
Create consolidated schedule that combines the Rooms and Food and Beverage Department and reflects the Undistributed Expenses
For the purposes of this budget round all figures (no decimal points)
Include a vertical analysis in all the schedules
All schedules must use formulas, if no formulas are used points will be deducted!

Must be filled out like the template below:

Summary Operating Statement Budget Revenue Room Food and Beverage Total Operating Revenue Departmental Expenses Room Food and Beverage Total Departmental Expenses Departmental Profit Undistributed Expenses Assumptions Administrative and General Telecommunications and Information Systems Sales and Marketing Property Operation and Maintenance Utilities Total Undistributed Expesnes Gross Operating ProfitRooms Department Budget Assumption Budget Keys Total Available Rooms Occupancy % Rooms Sold Average Daily Rate (ADR) Budget % Rooms Revenue Payroll Expenses Management Payroll Non-Management Payroll Total Payroll Benefits Expense Total Payroll Expenses Other Operating Expenses Linen and Laundry Supplies Total Other Expenses Rooms Departmental Profit/(Loss)