You are required to complete a draft budget based on information and factors that were determined during an executive meeting at Hotel Ideal.
A.Access the excel spreadsheet named "Task 1-Hotel Ideal Budget Forecast". The first tab on this spreadsheet is labelled "Departments Small" and shows the existing budget figures for the 2016 financial year.

B.Use the template "Draft Budget" on the second tab of the spreadsheet and perform the calculations below using basic formulas.
Your forecast needs to include the Dollar Figures and the % values for these affected by changes outlined below.
The % values must be listed for each expense item shown in the Expenses Analysis for each department.
You have met with the department heads of Hotel Ideal and the following details have been discussed to prepare your draft budget for 2017:
1.Rooms Division:

a.Due to renovations the rooms available have been reduced to 96%.
b.The forecasted occupancy rate has been adjusted to 80%.
c.The revenue per available room needs to be increased to $150.00
d.The COGS will increase to 15% of total room revenue
e.Staff costs need to be increased to 20% of total room revenue to allow for increases in superannuation and awards.
f.Other Expenses need to increase to 8% of total room revenue to cover electricity price rises.

2.Catering:

a.The food revenue will be increased by 15% due to a new marketing campaign and specialty menus
b.The beverage revenue will increase by 8%.
c.Staff costs need to be adjusted to 44% of the total budget.
d.Other Expenses will need to be increased to 7%.

 

3.Banquet:

The Banquet Division will be directly affected by the new marketing campaign which has been directed at daytime seminars and corporate functions. For this purpose the kitchen has received specialised equipment including multiple combi steamers, hold-o-mats and sous-vide equipment.

a.The new food revenue budget was set at $ 2,000,000.00 ($2 Million) and the beverage revenue was increased by 75%.
b.The COGS will increase to 26%.
c.The staff costs have been reduced to 19%.
d.Other Expenses will need to increase to 14%.

4.Room Service:

a.The room service revenue from food needs to be increased by 15%.
b.Due to a different system to clear floors and organise delivery, the staff costs will be reduced to 34%.

5.Mini Bar:

The mini bar budget remains unchanged and increased staff costs are absorbed through different processes.

6.Bar Budget:

a.The food revenue budget has been increased by 25% with the implementation of a Tapas Menu.
b.The Beverage Revenue budget has been increased 20% with the introduction of a new cocktail bar and happy hour specials.
c.Staff costs will need to be adjusted to 36%.
d.Other Expenses need to be increased to 18%.  

 

Hotel Ideal Budget Forecast Total Revenue 2016 Room Budget Rooms Available Rooms Occupied Total Occupancy % Revenue Per Available Room Total Room Revenue Total Catering Revenue Food Budget (Restaurants) Total Revenue Food Revenue Beverage Revenue Banquet Budget Total Revenue Food Revenue Beverage Revenue Room Service Budget Total Revenue Food Revenue Beverage Revenue Other Revenue Mini Bar Budget Total Revenue $ 11,213,138.73 Bar Budget Total Revenue Food Revenue Beverage Revenue 63875 51548 80.70 134.80 $ $ 6,948,897.21 $ 4,264,241.52 $ 2,019,370.53 $ 1,740,112.81 $ 279,257.72 $ 1,313,940.16 $ 1,130,203.33 $ 183,736.83 $ Beverage Revenue / Minibar $ Other Revenue $ $ 417,442.15 $ 335,796.64 $ 34,668.69 $ 46,976.82 146,471.02 146,471.02 $ $ $ 299,825.78 367,017.66 67,191.88 100% 80.70% 86.17% 13.83% 86.02% 13.98% 80.44% 8.31% 11.25% 100% 18.31% 81.69% $ 7,358,400.00 Forecasted 2017 Forecast $ $ $ 61320 49056 0.8 150.00 7,358,400.00 Forecast % Expenses Analysis $ Staff costs $ 80% Other Expenses $ Total Expenses $ 96% COGS Expenses Analysis COGS Staff costs Other Expenses Total Expenses Expenses Analysis COGS Staff costs Other Expenses 1,042,334.58 1,393,779.44 555,511.78 2,991,625.80 Expenses Analysis COGS Staff costs Other Expenses Expenses Analysis COGS Staff costs Other Expenses Expenses Analysis COGS Staff costs Other Expenses 15% 20% 8%