I need some explain and sample, excel

Assessment 3 - Prepare a Budget report.~ After the budget has been prepared from assessment 2, you are required to prepare a budget report on the financial activity and make recommendations from the financial information calculated. The report must follow the professional format with a table of contents, introduction, body, conclusion, recommendations and appropriate referencing.~ 1. How do the figures compare overall?- 2. What areas would need to be investigated? 3. Given the suggested changes in the operation for the 2022/2023, discuss which departments you will need to liaise with in terms of financial and HR impacts. Which details will you need to discuss for a successful implementation of the budget with the managers and supervisors?- 4. What factors need to be considered when the hotel reaches full accommodation capacity based on the future forecast for 2023/2024?~ 5. Given the modernisation of the operation, the Director in conjunction with the F&B Director have approached you to prepare for the purchase and implementation of a new stock control system linked to a POS to enhance security provisions and prevent theft. ~ Research a suitable system by comparing two different systems.~ Recommend one of them providing details for the features and a cost estimate including ongoing costs for maintenance or subscriptions. ~ T . Attach all information you have researched to this assessment.912 19 ~ 11 - 7 1 71 ~ % B2 VIXfx notel Futura budget Forecast CUZ 1/ ZUL2 G H 4 5 10,959,895.35 Forecasted 2019/2020 6 7 Forecast $ Expenses Analysis Staff Cost Food Cost Beverage Cost Other Expense 8 Room Budget Forecast % 9 Rooms Available 63875 100% COGS 972,845.61 - 14% 10 Rooms Oopupied 51548 Staff costs 1.181,312.53 17% 1,181,312.53 80.70 Other Expenses 416,933.83 6% S 416,933.83 11 Total Occupancy % 80.79 12 Revenue Per 134.80 Total Expenses 2,571,091.97 18 Total Room Revenue 6,948,897.21 14 15 Total Catering Revenue 4,010,798.14 16 17 Food Budget Expenses Analysis 18 Total Revenue 2,019.370.53 COGS 805,811.16 30% 520,997.60 84,813.56 Staff costs 1,130,847.50 58% 1,130,847.50 19 Total Food Revenue 1,740.112.81 279,257.72 14% Other Expenses 121,162.23 20 Total Beverage Revenu 121, 162.23 21 Total Expenses 1.857,820.89 22 Expenses Analysis 211,250.96 43,268.27 28 Banquet Budge 24 Total Revenue 1,080,496.78 COGS 254,519.23 24% 381,778.84 28% 53,024.84 25 Total Food Revenue 876,759.95 381,778.84 83% Staff costs 26 Total Beverage Revenue 183,738.83 17% Other Expenses 53,024.84 689.322.91 27 28 Room Service Budget Expenses Analysis 83,488.43 8,348.84 29 Total Revenue 417,442.15 COGS 104,380.54 25% 229,593.18 229.593.18 56% S 25,046.53 30 Total Food Revenue 335,796.64 80% Staff costs 25.048.53 8% 31 Total Beverage Revenue 34,688.69 8% Other Expenses 32 Total Other Revenue 46,976.82 11% 359,000.25 55,658.99 33 - Expenses Analysis 35, 153.04 34 Mini Bar Budget 35 Total Revenue 146,471.02 COGS 35, 153.04 24% Staff costs 55.658.99 38% 19,041.23 36 Beverage Revenue / Minibar 146.471.02 100% Other Expenses 19.041.23 13% 37 Other Revenue 109,853.27 38 39 B Expenses Analysis 20,479.59 93,295.89 Total Revenue 387,017.86 COGS 113,775.47 31% 41% 22,021.06 41 Total Food Revenue 87. 191.88 18% Staff costs 150,477.24 299,825.78 82%% Other Expenses 22,021.08 150,477.24 42 Total Beverage Revenue $ 43 286,273.17 44 3,129,668.27 5 836,216.57 $ 657,229.72 45 10,959.695.35 $ 10.959.695.35 Total 264,879.61 5 45 47 43 49 Departments Draft Budget Comparison +v ~ % 9 00 00 B2 I X Vfx Hotel Futura Budget Forecast 2022/2023 10.959.6195.35 $ 13.650,727.17 Forecasted 20192920 Forecast % Percentage Staff Cost Food Cost Beverage Cost Other Expense Percentage Forecast $ 63875 100% 57437 5 1037.83 39 10 51548 S 1 756 944 19 22% 1,755,944.19 718,340.81 17 T 80.70 718 340 81 12 Revenue Per Avalatie Rori 13480 3.51 1 888 38 6901 837 21 7 981.564 50 14 15 Total Catering 4010.798.14 5679.163.27 16 17 Food Budget Expenses Anaryals 2 302.728.07 103,622.76 18 Toa Reverse 2019.370.53 5% 87% COGS S 690.818.42 30% 601,012.03 19 Tow Food Reverun 1740.1 12 81 2001.129 73 15% Sixfont 782 927 54 34%% 782.927.54 161,190.96 20 Toad Beverage Reverse 14%% S 301.592.34 La co Co 279.257 72 161.190.98 21 Tall Experts 1.634 936.93 22 23 B 4 Toil Revenue 1.060,496.78 2.284 792 09 COGS S 862,589.71 563,201.25 99,388.46 25 Tow Food Reverse 876.759.95 83% S 2,090.090.00 55%% S 434.110.50 19% 434,110.50 26 Tow Binwrigg Re 183:736.83 17%% S 284 792.09 15% Other Expense 319.870.69 319,870.89 27 S 1.418.571.09 8 Room Service Budge 29 T 417 642.15 S 501.391.31 COGS 125.347.83 104,038.70 8,774.35 30 Toul Food Roman 335.796.64 $19.745.80 15% 170.473.05 170,473.05 81 Tout average Revrun 34 668.69 8% 4.658.69 7%% Other Expense 30.083 48 30,083.48 82 Tout Ofer Re 48.976 82 6.976 82 325.904.35 want Bar Budget 45471.02 146 47102 COGS 35.153.04 24%% 35,153.04 36 Beverages Revenue/ Minta 56 471.02 100% 146 471 02 100% Stall costs 55 654.98 55,658.99 37 Other Revme Offer Expertses 19 041.29 1386 19,041.23 109 853.27 89 Bar Budget Expenses Analysis 26,138.69 111,433.36 967 017.68 $43.790.79 COGS 137.57204 31%% 142,009.85 41 Tail Food Revrun 67.191.89 18% S 83 989 85 19% Gall ousis 142 004.85 32% 48,815.89 42 Toul Beverage Revenue 299/825.78 359.790.94 209% 81% Ofer Experies 1145 43 128 347.78 $ 10,550,695.35 13,660 727.37 Total 5 3,341,124.11 5 1.294.390.66 5 358.371.97 5 Departments Draft Budget Comparison +20147 L JIL v v F12 V : XVfx A B C D E F G H W N - 2018/2019 2019/2020 $ % $ % Turnover $10,959,695.35 100% $13,227,148.11 100% UT A 7 Staff Cost $3, 129,668.27 29% $3,253,707.03 25% 9 Food Cost $836,216.57 8% $1, 188,822.88 9% 10 11 Beverage Cost $264,879.61 2% $340,384.32 3% 12 13 Other Expenses $657,229.72 6% $1,239,328.48 9% 14 15 16 17 18 19 20 N 22 23 Comparison + Departments Draft BudgetN31 V XV fc =M31+L31+K31+J31+131+H31+G31+F31+E31+D31+C31+B31 A B O Statement of cashflows as at 28 February 2023 and forecast to June 2023 FY 2017/18 FY 2018/19 FY 2019/20 FY 2020/21 YTD 2021/22| Actual Feb 2023 Forecast March April May June 2022/23 ($ '000) ($ '000) ($ '000) ($ '000) ($ '000) ($ '000) ($ '000) ($ '000) ($ 1000) (5 080) ($ 000) Receipts 111,831 117,716 132,915 134,349 102,354 11,296 8,476 7 270 8,476 9,698 145,390 7 Total receipts 111,831 117,716 132,913 134,349 102,354 1,296 8,476 7270 8,476 9,698 145,390 8 Payments to suppliers 59,230 51,250 65,875 68.952 60,000 4,839 10,881 6,396 4.477 3,839 76,787 9 Payment to employees 35,052 37,995 38,595 41,016 23,581 5,295 2,562 2,197 2,562 2,931 43,937 10 Capital purchases 15.898 18,591 13.891 16,249 187 989 96 45 121 19,921 11 Total payments 109,247 115,143 123,059 123,859 99,830 10,321 14,431 8.689 7,083 6,891 140,645 12 Cash inc/(dec) 2,584 2573 9,854 10,49 2,524 975 -5,955 -1,420 1,393 2,807 4,745 13 Cash at the beginning 28,014 30,598 33,171 43,025 53,515 56,039 57,014 51,059 49,639 51,032 53,515 14 Cash held 30,598 33.171 43,025 53,515 56,059 57,014 51,059 49 639 51,032 53,839 58,260 15 16 Assumptions 17 July August September October November December January | February March April May June 18 Monthly receipt forecasts 7.00% 6.00% 7.50% 7.00% 14.00% 17.00% 15.00% 8.33% 5.83%% 5.00% 5.83% 6.67% Monthly supplier payment 6.50% 7.00% 8.00% 4.17% 7.50% 8.38% 13.33%% 16.67% 14.17% 8.33% 5.83% 5.00% 19 forecasts Monthly employee costs 5.00% 6.00% 6.00% 7.00% 9.00% 12.00% 10.00% 8.33% 5.83% 5.009 5.83% 6.67% 20 forecasts 21 Capital Purchase Provisions 2.00% 2.00% 2.00% 2.00% 2.00% 2.00% 2.00% 2.00% 200% 2.00% 2.00% 2.00% 22 23 24 July August September October November December |January | February March April May June 2023/24 25 ($ '000) ($ '000) ($ '000) ($ '000 ($ '000) ($ '000) ($ '000) ($ 1000) ($ 000) ($ '000) ($ 000) ($ 1000) ($ '000) 26 Monthly receipt forecasts $10,177 $8,723 $10,904 $10,177 $20,355 $24,716 $21,809| $12,111 $8 476 $7,270 $8,476 $9,698 152,892 27 Total receipts $10,177 $8,723 $10,904 $10,177 $20,355 $24,716 $21,809 $12,111 $8,476 $7,270 $8,476 $9,698 152.892 Monthly supplier payment $4 99 $5,375 $6,143 $3,202 $5,759 $6,396 $10 236 $12 800 $10 88 $6 396 $4,477 $3,839 $80 495 28 forecasts Monthly employee costs $2,197 $2,636 $2,636 $3,076 $3,954 $439 $4 394 $3,647 $2.562 $2,197 $2,562 $2931 33,231 29 forecasts 30 Capital purchases $398 $398 $398 398 398 $398 $398 $398 1398 $398 1398 1398 4,776 31 Total payments $7,586 $8,410 $9, 178 $6,676 $10,112 $7,234 $15,028 $16,846 $13 841 $8.992 $7,437 $7, 168 118,508 32 Cash inc/(dec] $2 591 $314 $1,727 3,501 $10,243 $17,482 $6,781 -$4,735 -$5,364 -$1,722 $1.040 $2.529 34 387 33 Cash at the beginning $53,515 $56,106 $56,420 $58,146 $61,647 $71,890 $89,372 $96,153 $91,419 $86054 $84.332 $85,372 87,901 34 Cash held $56,106 $56,420 $58,146 $61,647 $71,890 $89,372 $96,153 $91,419 $86,054 $84,332 $85,372 $87,901 122,288 35 36 37 Stater