Reference no: EM132180753
Task 1
You are required to complete a draft budget based on information and factors that were determined during an executive meeting at Hotel Futura.
A. Access the excel spreadsheet named "A1-Task 1-Hotel Futura 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 Futura 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 food 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 total 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%.
Task 2
You have provided the chief financial controller with the draft budget for 2017. Following the recent executive meeting where the draft budget was discussed, you are now required to establish the final budget reflecting the changes based on the latest actuals and variances as well as major road works which will affect Futura Restaurant and Bar during 2017.
A. Access the excel spread sheet named "A1-Task 2-Budget Futura_Restaurant_Bar". The first tab on this spreadsheet is labelled "Futura Restaurant and Bar" and shows the Draft budget figures for the 2017 financial year.
B. Use the template "Revised Budget" on the second tab of the spreadsheet and perform the calculations below using basic formulas based on the following changes:
|
Month
|
Customer numbers
|
Average Spend (Food)
|
Average Spend (Beverage)
|
|
January
|
1850
|
$ 45.00
|
$ 9.70
|
|
February
|
2000
|
$ 37.00
|
$ 9.70
|
|
March
|
700
|
$ 42.00
|
$ 9.70
|
|
April
|
1200
|
$ 48.00
|
$ 9.70
|
|
May
|
1200
|
$ 36.50
|
$ 9.70
|
|
June
|
600
|
$ 35.00
|
$ 9.70
|
|
July
|
950
|
$ 34.00
|
$ 9.70
|
|
August
|
800
|
$ 38.00
|
$ 9.70
|
|
September
|
900
|
$ 29.00
|
$ 9.70
|
|
October
|
650
|
$ 29.50
|
$ 9.70
|
|
November
|
980
|
$ 35.50
|
$ 9.70
|
|
December
|
2200
|
$ 48.00
|
$ 9.70
|
1. Calculate the anticipated Food revenue for each month and the yearly total.
2. Calculate the anticipated Beverage revenue per month and the yearly total.
3. Calculate the Total Revenue for each month and the yearly total.
4. Calculate the overheads total for each month (at 90% of turnover for each for each month with 1000 or more customers and at 96% for each month with less than 1000 customers) and the yearly total.
5. Calculate the profit for each month and the yearly total.
6. Calculate the Cost of Goods Sold for food and beverages, given a combined percentage of 32%.
7. Calculate the staff costs for each month at 31% for each month with 1000 or more customers and at 35% for each month with less than 1000 customers.
8. Calculate the ‘Other overheads" for the operation.
9. Print a copy of the revised budget.
10. Print the revised budget showing all formulae used.
Task 3
Actual performance of Futura Restaurant and Bar for the first Quarter 2017 is available as below:
Actual revenue for Quarter 1 2017
|
Month
|
Customer numbers
|
Average Spend
(Food)
|
Food Revenue
|
Average Spend
(Beverage)
|
Beverage Revenue
|
Total
|
|
January
|
1573
|
$ 41.00
|
$ 64,493.00
|
$9.70
|
$15,258.10
|
$ 79,751.10
|
|
February
|
1700
|
$ 38.00
|
$ 64,600.00
|
$11.00
|
$ 18,700.00
|
$ 83,300.00
|
|
March
|
595
|
$ 42.00
|
$ 24,990.00
|
$9.80
|
$ 5,831.00
|
$ 30,821.00
|
|
Crosscheck
|
$193,872.10
|
|
Total
|
|
|
|
|
|
|
Actual Expense for Quarter 1 2017
|
Profit
|
Overheads
|
COGS - Food & Beverage
|
Staff Costs
|
Other Overheads
|
|
$ 3,987.56
|
$ 75,763.55
|
$ 29,507.91
|
$ 24,722.84
|
$ 21,532.80
|
|
$ 8,330.00
|
$ 74,970.00
|
$ 26,656.00
|
$ 25,823.00
|
$ 22,491.00
|
|
$ 1,232.84
|
$ 29,588.16
|
$ 9,862.72
|
$ 10,787.35
|
$ 8,938.09
|
|
$ 13,550.40
|
$180,321.71
|
$ 66,026.63
|
$ 61,333.19
|
$ 52,961.89
|
|
$193,872.10
|
Crosscheck
|
You are required to prepare:
1. a quarterly variance report including:
• Variance in customer number
• Variance in revenue from food and beverage department
• Variance in COGS- food and beverage
• Variance in Staff cost
• Variance in other overhead
2. Investigate the variance and report internal and external factors contributing to this variance. Collect feedback from relevant staff (your assessor) to determine the cause of variance.
3. Discuss the matter with relevant managers and present a report outlining suggested options to address and rectify these issues.
Guideline for excel
Task 1: Draft budget:
- Access the excel spreadsheet named "A1-Task 1-Hotel Futura Budget Forecast". The firsttab on this spreadsheet is labeled "Departments Small" and shows the existing budget figures for the 2016 financial year.
- 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 eachdepartment.
- Check the points provided in the assignment for each department and notice the changes and do the draft budget according to the changes provided.
- Put the changes accordingly and do your draft budget.
Task 2:
Revised budget:
- Access the excel spread sheet named "A1-Task 2-Budget Futura_Restaurant_Bar". The first tabon this spreadsheet is labeled"Futura Restaurant and Bar" and shows the Draft budget figuresfor the 2017 financial year.
- Use the template "Revised Budget" on the second tab of the spreadsheet and perform the calculations using basic formulas.
- Check the table provided in your assignment and put the changes accordingly in your revised budget.
- Do your revised budget with all calculation showing in excel file.
Task 3:
Variance report:
- Check the excel file named A1 - Task 3 - Actual performance sheet and actual revenue and actual expense are provided in the sheet.
- Compare this with your revised budget and determine the variances.
- Write variance report including these 5 points.
- Write some internal and external factors.
- Write some suggestions.
- These three components must be done.
Required
1 Prepare a quarterly variance report including:
Variance in number of customer
Variance in revenue for food and beverage department
Variance in COGS- food and beverage
Variance in Staff cost
Variance in other overhead
2 Investigate the variance and report internal and external factors contributing to these variance
3 Discuss the matter with relevant managers and suggest options to address and rectify these issues
Attachment:- Assessment.rar