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:

• Create your own Excel schedule with three tabs (sample layout is provided)
  • Summary Operating Statement
  • Rooms
  • Food and Beverage

• Complete the budget for the Rooms Department
• Complete the budget for the Food and Beverage Department
• Create a 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!