Task 1
Requires you to prepare a draft budget following a meeting with all heads of department where you have finalised the details and requirements to be included for next year’s budget.
Task 2
Requires you to prepare a final budget, based on the changes made to your draft budget following a management meeting.
Task 3
Requires you to assess actual performance against estimated performance and prepare accurate financial reports. You will be preparing a summary report for the budget and performance and making recommendations for next budget based on review.
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
Answer 1 – Anticipated food revenue
|
Month |
Customer number |
Average food spend |
Total food spend |
|
January |
1850 |
$ 45.00 |
$ 83,250.00 |
|
February |
2000 |
$ 37.00 |
$ 74,000.00 |
|
March |
700 |
$ 42.00 |
$ 29,400.00 |
|
April |
1200 |
$ 48.00 |
$ 57,600.00 |
|
May |
1200 |
$ 36.50 |
$ 43,800.00 |
|
June |
600 |
$ 35.00 |
$ 21,000.00 |
|
July |
950 |
$ 34.00 |
$ 32,300.00 |
|
August |
800 |
$ 38.00 |
$ 30,400.00 |
|
September |
900 |
$ 29.00 |
$ 26,100.00 |
|
October |
650 |
$ 29.50 |
$ 19,175.00 |
|
November |
980 |
$ 35.50 |
$ 34,790.00 |
|
December |
2200 |
$ 48.00 |
$ 105,600.00 |
|
Total |
$ 557,415.00 |
||
Answer 2 - Anticipated beverage revenue
|
Month |
Customer number |
Average beverage spend |
Total beverage spend |
|
January |
1850 |
$ 9.70 |
$ 17,945.00 |
|
February |
2000 |
$ 9.70 |
$ 19,400.00 |
|
March |
700 |
$ 9.70 |
$ 6,790.00 |
|
April |
1200 |
$ 9.70 |
$ 11,640.00 |
|
May |
1200 |
$ 9.70 |
$ 11,640.00 |
|
June |
600 |
$ 9.70 |
$ 5,820.00 |
|
July |
950 |
$ 9.70 |
$ 9,215.00 |
|
August |
800 |
$ 9.70 |
$ 7,760.00 |
|
September |
900 |
$ 9.70 |
$ 8,730.00 |
|
October |
650 |
$ 9.70 |
$ 6,305.00 |
|
November |
980 |
$ 9.70 |
$ 9,506.00 |
|
December |
2200 |
$ 9.70 |
$ 21,340.00 |
|
Total |
$ 136,091.00 |
||
Answer 3 – Total revenue
|
Month |
Total revenue |
|
January |
$ 101,195.00 |
|
February |
$ 93,400.00 |
|
March |
$ 36,190.00 |
|
April |
$ 69,240.00 |
|
May |
$ 55,440.00 |
|
June |
$ 26,820.00 |
|
July |
$ 41,515.00 |
|
August |
$ 38,160.00 |
|
September |
$ 34,830.00 |
|
October |
$ 25,480.00 |
|
November |
$ 44,296.00 |
|
December |
$ 126,940.00 |
|
Total |
$ 693,506.00 |
Answer 4 – Overheads
|
Month |
Customer number |
Average food spend |
Total food spend |
Overheads |
|
January |
1850 |
$ 45.00 |
$ 83,250.00 |
$ 74,925.00 |
|
February |
2000 |
$ 37.00 |
$ 74,000.00 |
$ 66,600.00 |
|
March |
700 |
$ 42.00 |
$ 29,400.00 |
$ 28,224.00 |
|
April |
1200 |
$ 48.00 |
$ 57,600.00 |
$ 51,840.00 |
|
May |
1200 |
$ 36.50 |
$ 43,800.00 |
$ 39,420.00 |
|
June |
600 |
$ 35.00 |
$ 21,000.00 |
$ 20,160.00 |
|
July |
950 |
$ 34.00 |
$ 32,300.00 |
$ 31,008.00 |
|
August |
800 |
$ 38.00 |
$ 30,400.00 |
$ 29,184.00 |
|
September |
900 |
$ 29.00 |
$ 26,100.00 |
$ 25,056.00 |
|
October |
650 |
$ 29.50 |
$ 19,175.00 |
$ 18,408.00 |
|
November |
980 |
$ 35.50 |
$ 34,790.00 |
$ 33,398.40 |
|
December |
2200 |
$ 48.00 |
$ 105,600.00 |
$ 95,040.00 |
|
Total |
$ 557,415.00 |
$ 513,263.40 |
||
|
Month |
Customer number |
Average beverage spend |
Total beverage spend |
Overheads |
|
January |
1850 |
$ 9.70 |
$ 17,945.00 |
$ 16,150.50 |
|
February |
2000 |
$ 9.70 |
$ 19,400.00 |
$ 17,460.00 |
|
March |
700 |
$ 9.70 |
$ 6,790.00 |
$ 6,518.40 |
|
April |
1200 |
$ 9.70 |
$ 11,640.00 |
$ 10,476.00 |
|
May |
1200 |
$ 9.70 |
$ 11,640.00 |
$ 10,476.00 |
|
June |
600 |
$ 9.70 |
$ 5,820.00 |
$ 5,587.20 |
|
July |
950 |
$ 9.70 |
$ 9,215.00 |
$ 8,846.40 |
|
August |
800 |
$ 9.70 |
$ 7,760.00 |
$ 7,449.60 |
|
September |
900 |
$ 9.70 |
$ 8,730.00 |
$ 8,380.80 |
|
October |
650 |
$ 9.70 |
$ 6,305.00 |
$ 6,052.80 |
|
November |
980 |
$ 9.70 |
$ 9,506.00 |
$ 9,125.76 |
|
December |
2200 |
$ 9.70 |
$ 21,340.00 |
$ 19,206.00 |
|
Total |
$ 136,091.00 |
$ 125,729.46 |
||
Answer 5 – Profit
|
Month |
Customer number |
Total revenue |
Overheads |
Profit |
|
January |
1850 |
$ 101,195.00 |
$ 91,075.50 |
$ 10,119.50 |
|
February |
2000 |
$ 93,400.00 |
$ 84,060.00 |
$ 9,340.00 |
|
March |
700 |
$ 36,190.00 |
$ 34,742.40 |
$ 1,447.60 |
|
April |
1200 |
$ 69,240.00 |
$ 62,316.00 |
$ 6,924.00 |
|
May |
1200 |
$ 55,440.00 |
$ 49,896.00 |
$ 5,544.00 |
|
June |
600 |
$ 26,820.00 |
$ 25,747.20 |
$ 1,072.80 |
|
July |
950 |
$ 41,515.00 |
$ 39,854.40 |
$ 1,660.60 |
|
August |
800 |
$ 38,160.00 |
$ 36,633.60 |
$ 1,526.40 |
|
September |
900 |
$ 34,830.00 |
$ 33,436.80 |
$ 1,393.20 |
|
October |
650 |
$ 25,480.00 |
$ 24,460.80 |
$ 1,019.20 |
|
November |
980 |
$ 44,296.00 |
$ 42,524.16 |
$ 1,771.84 |
|
December |
2200 |
$ 126,940.00 |
$ 114,246.00 |
$ 12,694.00 |
|
Total |
14030 |
$ 693,506.00 |
$ 638,992.86 |
$ 54,513.14 |
Answer 6 – Cost of goods sold
|
Month |
Customer number |
COGS |
|
January |
1850 |
$ 32,382.40 |
|
February |
2000 |
$ 29,888.00 |
|
March |
700 |
$ 11,580.80 |
|
April |
1200 |
$ 22,156.80 |
|
May |
1200 |
$ 17,740.80 |
|
June |
600 |
$ 8,582.40 |
|
July |
950 |
$ 13,284.80 |
|
August |
800 |
$ 12,211.20 |
|
September |
900 |
$ 11,145.60 |
|
October |
650 |
$ 8,153.60 |
|
November |
980 |
$ 14,174.72 |
|
December |
2200 |
$ 40,620.80 |
|
Total |
14030 |
$ 221,921.92 |
Answer 7 – Staff costs
|
Month |
Customer number |
Staff costs |
|
January |
1850 |
$ 31,370.45 |
|
February |
2000 |
$ 28,954.00 |
|
March |
700 |
$ 12,666.50 |
|
April |
1200 |
$ 21,464.40 |
|
May |
1200 |
$ 17,186.40 |
|
June |
600 |
$ 9,387.00 |
|
July |
950 |
$ 14,530.25 |
|
August |
800 |
$ 13,356.00 |
|
September |
900 |
$ 12,190.50 |
|
October |
650 |
$ 8,918.00 |
|
November |
980 |
$ 15,503.60 |
|
December |
2200 |
$ 39,351.40 |
|
Total |
14030 |
$ 224,878.50 |
Answer 8 – Other Overheads
|
Month |
Customer number |
Overheads |
Other overheads |
|
January |
1850 |
$ 91,075.50 |
$ 27,322.65 |
|
February |
2000 |
$ 84,060.00 |
$ 25,218.00 |
|
March |
700 |
$ 34,742.40 |
$ 10,495.10 |
|
April |
1200 |
$ 62,316.00 |
$ 18,694.80 |
|
May |
1200 |
$ 49,896.00 |
$ 14,968.80 |
|
June |
600 |
$ 25,747.20 |
$ 7,777.80 |
|
July |
950 |
$ 39,854.40 |
$ 12,039.35 |
|
August |
800 |
$ 36,633.60 |
$ 11,066.40 |
|
September |
900 |
$ 33,436.80 |
$ 10,100.70 |
|
October |
650 |
$ 24,460.80 |
$ 7,389.20 |
|
November |
980 |
$ 42,524.16 |
$ 12,845.84 |
|
December |
2200 |
$ 114,246.00 |
$ 34,273.80 |
|
Total |
14030 |
$ 638,992.86 |
$ 192,192.44 |
Task 3
Answer 1 – Variance
|
Variance in customer number |
|||
|
Month |
Budget |
Actual |
Variance |
|
January |
1850 |
1573 |
-277 |
|
February |
2000 |
1700 |
-300 |
|
March |
700 |
595 |
-105 |
|
Variance in revenue from food department |
|||
|
Month |
Budget |
Actual |
Variance |
|
January |
$ 83,250.00 |
$ 64,493.00 |
$ (18,757.00) |
|
February |
$ 74,000.00 |
$ 64,600.00 |
$ (9,400.00) |
|
March |
$ 29,400.00 |
$ 24,990.00 |
$ (4,410.00) |
|
Variance in revenue from beverage department |
|||
|
Month |
Budget |
Actual |
Variance |
|
January |
$ 17,945.00 |
$ 15,258.10 |
$ (2,686.90) |
|
February |
$ 19,400.00 |
$ 18,700.00 |
$ (700.00) |
|
March |
$ 6,790.00 |
$ 5,831.00 |
$ (959.00) |
|
Variance in COGS - Foods and beverages |
|||
|
Month |
Budget |
Actual |
Variance |
|
January |
$ 32,382.40 |
$ 29,507.91 |
$ 2,874.49 |
|
February |
$ 29,888.00 |
$ 26,656.00 |
$ 3,232.00 |
|
March |
$ 11,580.80 |
$ 9,862.72 |
$ 1,718.08 |
|
Variance in staff cost |
|||
|
Month |
Budget |
Actual |
Variance |
|
January |
$ 31,370.45 |
$ 24,722.82 |
$ 6,647.63 |
|
February |
$ 28,954.00 |
$ 25,823.00 |
$ 3,131.00 |
|
March |
$ 12,666.50 |
$ 10,787.35 |
$ 1,879.15 |
|
Variance in other overhead |
|||
|
Month |
Budget |
Actual |
Variance |
|
January |
$ 27,322.65 |
$ 21,532.80 |
$ 5,789.85 |
|
February |
$ 25,218.00 |
$ 22,491.00 |
$ 2,727.00 |
|
March |
$ 10,495.10 |
$ 8,938.09 |
$ 1,557.01 |
Main objective of preparing the budget is to analyse the variance with the actual revenue and expenses and finding out the reason for the deviation. Therefore, the operational areas shall be investigated and periodically compared with the budget to assess the significant deviation (Glantz, Slinker and Neilands 2016). Variances for the following aspects may caused for the reasons mentioned below –
- Customer number – the main reason of unfavourable variance in customer number was reduction in availability of rooms owing to renovation. Further, the customer may have found same level of service in another hotel at lower cost or the unfavourable variance may be due to season change. In addition to these the marketing campaign of the company may have been ineffective (Schick 2015).
- Revenue from food and beverage – the reason of unfavourable variance was that the company was too optimistic regarding the budget. Further, there may have been bad review in media regarding the food quality of the Futura hotel. Another reason may be the customers
- COGS – the favourable variance of COGS may cause due to healthier economy and availability of resources at lower cost. Further, the lower COGS was due to less competition in the market.
- Staff cost – the favourable variance of staff cost may cause due to worse economy and availability of labours at lower cost. Further, the reason may be lower level of competition which in turn, leaves less job options for the staff in the hotel industry (Pilleboue et al. 2015).
- Other overheads – the favourable variance other overhead may due to efficient management of overheads. Further, the application of new technology may have resulted into lowering of overheads. In addition to these, using higher quality of resources may have contributed to lower overheads as compared to budgets (Barr and McClellan 2018).
To rectify the above mentioned issues the management are suggested the following –
- Periodic review for actual performance shall be compared with the budgeted performance and the managers shall assess the performance accordingly.
- The controllable and uncontrollable variances shall be identified to take steps for the controllable variances.
- To improve the sales revenue the managers shall conduct the advertising campaign in different way. For instance, the travel agent may paid a percentage of commission when they send customers to the hotels.
- While preparing the budget the previous period’s trend shall be taken into consideration to minimize the gap between budget and actual amount.
- The price of the foods and beverages can be reduced to attract more customers
- The hotel can also introduce new menus from all over the world that will definitely attract some food lovers.
Reference
Barr, M.J. and McClellan, G.S., 2018. Budgets and financial management in higher education. John Wiley & Sons.Glantz, S.A., Slinker, B.K. and Neilands, T.B., 2016. Primer of applied regression & analysis of variance. McGraw-Hill Medical Publishing Division.Pilleboue, A., Singh, G., Coeurjolly, D., Kazhdan, M. and Ostromoukhov, V., 2015. Variance analysis for Monte Carlo integration. ACM Transactions on Graphics (TOG), 34(4), p.124.Schick, A., 2015. The road to PPB: The stages of budget reform. In Public Budgeting (pp. 39-56). Routledge.To export a reference to this article please select a referencing stye below:
My Assignment Help. (2020). Budget Preparation And Assessment For Hotel Futura. Retrieved from https://myassignmenthelp.com/free-samples/sitxfin004-prepare-and-monitor-budgets/actual-amount.html.
"Budget Preparation And Assessment For Hotel Futura." My Assignment Help, 2020, https://myassignmenthelp.com/free-samples/sitxfin004-prepare-and-monitor-budgets/actual-amount.html.
My Assignment Help (2020) Budget Preparation And Assessment For Hotel Futura [Online]. Available from: https://myassignmenthelp.com/free-samples/sitxfin004-prepare-and-monitor-budgets/actual-amount.html
[Accessed 25 April 2024].
My Assignment Help. 'Budget Preparation And Assessment For Hotel Futura' (My Assignment Help, 2020) <https://myassignmenthelp.com/free-samples/sitxfin004-prepare-and-monitor-budgets/actual-amount.html> accessed 25 April 2024.
My Assignment Help. Budget Preparation And Assessment For Hotel Futura [Internet]. My Assignment Help. 2020 [cited 25 April 2024]. Available from: https://myassignmenthelp.com/free-samples/sitxfin004-prepare-and-monitor-budgets/actual-amount.html.
WhatsApp
