Can anyone help me to solve this question please?

E % Actual 2019/2020 39,055 Budget 2020/2021 39,055 29,930 Hught 39,055 29,930 29,930 13,140 13.140 82,125 30,853 13.140 82.125 82.125 18,557 The Bentley 2 Standard Room Available 3 Superior Rooms Available 4 Deluxe Room Available 5 Total Room Available per annum 6 Standard Rooms Occupied 7 Superior Rooms Occupied 8 Deluxe Rooms Occupied 9 Total Rooms Occupied 10 Standard room ADR 5 11 Superior room ADR 12 Deluxe room ADR 13 Standard room and 14 Superior room revenue 4,073 53,453 65.1% 52 24 15 Deluxe room revend 5340.00 $410.00 $490.00 $10,490,173 57.608.206 51.995.26 $20,094,345 $375.71 $1205.661 $2,000 45 5602310 53.817-926 $23.212.271 6.09 10.09 16.00 100.0% 37.04 12.09 32.00 43.04 88.00 16 Total Rooms Revenue 17 Total Rooms ADR S 18 Telephone Revenue 19 Laundry Raven 20 InHouse Movies 21 Total Other Revenue 22 Total Rooms Division Revenue 23 COSTS 24 Payroll 25 Travel Agent Commissions 26 Telephone 27 Laundry & Dry Cleaning 28 In How Movies 29 Geaning Supplies 30 Rewers 31 Uniforms 32 Guestes 33 Linen & towels 34 VIP Program 35 Stationary & Niwanan 36 Total Costa 37 G.O.P (Gross Operating Profit) 38 Interest (8% of $40 million) 39 Pre-Tex Profit 40 Tax (334) 41 Net Profit 42 Capital Rainvestment 43 Return On Investment (ROI) 44 5.490 58 847 540 52.411 32: $385 811 $864,057 $530421 $1,123 822 $1.291 263 5621,719 5669.544 3956,491 $1.482,561 5813.017 $19.997 591 $3,914 579 2.69 2.89 4.09% 83.64 3.09 $714 5791 5235 81: 3478.768 $10.000.000 2.09 The Bentley Hotel is a 5* hotel in the Beverley Hills district of Los Angeles. It has 225 rooms, made of up of 107 Standard rooms priced at $340, 82 Superior rooms (larger rooms with a sitting area) at $410 and 36 Deluxe rooms at $490 (with balconies & spa baths). The Bentley is renowned for its 10 high customer service and loyal clientele, especially amongst LA's elite movie clientele. 6 7 8 9 11 12 13 14 15 17 18 19 20 21 It's now May 2020 and time to set the next year's budget for Jul 2020-Jun 2021. The Management team have been told to prepare for a tough one. Despite the obvious downturn due to COVID-19, the owners are looking to minimise the losses and set a course for recovery for 2020-2021. 16 However, they are also prepared to accept the following key assumptions: Occupancy for all 3 rooms types will decrease by 7% from last year Average Daily Rates (ADR) will also decrease by $8.00 for Standard rooms, $12.00 for Superior rooms and $15.00 for Deluxe rooms Additional rooms revenue (telephone, laundry and movies) are all projected to decrease by 2% Total Costs are to be decreased by 2%, however Travel Agent Commissions are expected to rise 22 to 18% of Total Revenue, in order to maximise sales 23 24 25 26 27 28 29 30 31 32 33 34 Your task is to: 1. Prepare the next Budget for 2020/2021, based on the key assumptions above. Tab 3 has been pre-loaded with last years figures for you to use as a guide. (10 marks) 2. Write up your Budget Commentary tab 4) in relation to where you see savings being made for certain costs, and briefly describe how these can be achieved. (10 marks) 3. Then complete the Hubbart Method on tab 3 to then suggest what the ADR's for each room type should be for a 3.50% ROI, based on an extra $5 million capital investment added to the original $10 million invested. (10 marks) E % Actual 2019/2020 39,055 Budget 2020/2021 39,055 29,930 Hught 39,055 29,930 29,930 13,140 13.140 82,125 30,853 13.140 82.125 82.125 18,557 The Bentley 2 Standard Room Available 3 Superior Rooms Available 4 Deluxe Room Available 5 Total Room Available per annum 6 Standard Rooms Occupied 7 Superior Rooms Occupied 8 Deluxe Rooms Occupied 9 Total Rooms Occupied 10 Standard room ADR 5 11 Superior room ADR 12 Deluxe room ADR 13 Standard room and 14 Superior room revenue 4,073 53,453 65.1% 52 24 15 Deluxe room revend 5340.00 $410.00 $490.00 $10,490,173 57.608.206 51.995.26 $20,094,345 $375.71 $1205.661 $2,000 45 5602310 53.817-926 $23.212.271 6.09 10.09 16.00 100.0% 37.04 12.09 32.00 43.04 88.00 16 Total Rooms Revenue 17 Total Rooms ADR S 18 Telephone Revenue 19 Laundry Raven 20 InHouse Movies 21 Total Other Revenue 22 Total Rooms Division Revenue 23 COSTS 24 Payroll 25 Travel Agent Commissions 26 Telephone 27 Laundry & Dry Cleaning 28 In How Movies 29 Geaning Supplies 30 Rewers 31 Uniforms 32 Guestes 33 Linen & towels 34 VIP Program 35 Stationary & Niwanan 36 Total Costa 37 G.O.P (Gross Operating Profit) 38 Interest (8% of $40 million) 39 Pre-Tex Profit 40 Tax (334) 41 Net Profit 42 Capital Rainvestment 43 Return On Investment (ROI) 44 5.490 58 847 540 52.411 32: $385 811 $864,057 $530421 $1,123 822 $1.291 263 5621,719 5669.544 3956,491 $1.482,561 5813.017 $19.997 591 $3,914 579 2.69 2.89 4.09% 83.64 3.09 $714 5791 5235 81: 3478.768 $10.000.000 2.09 The Bentley Hotel is a 5* hotel in the Beverley Hills district of Los Angeles. It has 225 rooms, made of up of 107 Standard rooms priced at $340, 82 Superior rooms (larger rooms with a sitting area) at $410 and 36 Deluxe rooms at $490 (with balconies & spa baths). The Bentley is renowned for its 10 high customer service and loyal clientele, especially amongst LA's elite movie clientele. 6 7 8 9 11 12 13 14 15 17 18 19 20 21 It's now May 2020 and time to set the next year's budget for Jul 2020-Jun 2021. The Management team have been told to prepare for a tough one. Despite the obvious downturn due to COVID-19, the owners are looking to minimise the losses and set a course for recovery for 2020-2021. 16 However, they are also prepared to accept the following key assumptions: Occupancy for all 3 rooms types will decrease by 7% from last year Average Daily Rates (ADR) will also decrease by $8.00 for Standard rooms, $12.00 for Superior rooms and $15.00 for Deluxe rooms Additional rooms revenue (telephone, laundry and movies) are all projected to decrease by 2% Total Costs are to be decreased by 2%, however Travel Agent Commissions are expected to rise 22 to 18% of Total Revenue, in order to maximise sales 23 24 25 26 27 28 29 30 31 32 33 34 Your task is to: 1. Prepare the next Budget for 2020/2021, based on the key assumptions above. Tab 3 has been pre-loaded with last years figures for you to use as a guide. (10 marks) 2. Write up your Budget Commentary tab 4) in relation to where you see savings being made for certain costs, and briefly describe how these can be achieved. (10 marks) 3. Then complete the Hubbart Method on tab 3 to then suggest what the ADR's for each room type should be for a 3.50% ROI, based on an extra $5 million capital investment added to the original $10 million invested. (10 marks)