Case Study Assignment You have recently been recruited by Middlesex Hotel, part of a small chain of boutique hotels based in the London area, as an assistant management accountant reporting to the Management Accountant, Marc Polonius. Your role involves helping in the preparation of future budgets and forecasts. Up until now they have only produced annual Financial Statements and a simple budget, however, given the need for better forward planning you have been asked to produce a five year profit forecast (Income Statement) for the years 2023 to 2027 and a detailed monthly cash flow for next year 2023. Marc, has determined the future cost estimates without discussing these with other managers. The increase in sales in future periods is based on estimates produced by the group sales manager. More relevant information will be extracted from their estimated Income Statement 2022 summarised in Appendix 1. Section 1: Management Accountant's cost estimates and Sales manager's forecast information for guest rooms. Middlesex Hotel has twenty-five guest rooms, a restaurant, bar and guest lounge; there are fifteen standard and ten deluxe guest rooms. The estimated average occupancy rate for all guest rooms is between 60% and 75% of capacity, it is expected for 2022 the occupancy rate will be 60%. The hotel plans to open a new wing on the 15t July 2023, containing five small guest apartments. The revenue expected from opening the new apartments in 2023 is 69,420. Demand for guest rooms is seasonal and classified as low, medium or high season. Different room rates apply according to the seasonal demand, estimated occupancy rates in each season and the applicable room rate is shown in appendix 1 . There is strong competition in the area for standard rooms, for this reason the hotel manager does not plan to increase the room rate for these rooms; the room rates for deluxe rooms is expected to rise by 3% per annum from 2023 and the room rate for the new apartments is expected to rise by 3% from 2024. The hotel uses a laundry service for linen, laundry costs are expected to increase 20% in 2023 due to increases in energy prices, then by 5% per annum from 2024. Room cleaning wages are expected to increase by 6% in 2023 , then by 4% per annum from 2024. The cost of a pack of toiletries provided for guests is expected to increase by 3% per annum. The fixed lease for the premises of 120,000 expires on 31st May 2023. A new 5year lease has been agreed at a price of 138,000 p.a. The rent is paid quarterly in arrears. Rates are estimated as 30% of the rental value. Utility costs to inflate by 200% in 2023 , due to uncertainty, the forecast will base future costs on the 2023 cost and increase by 4% per annum from 2024. Salaries and remuneration to increase by 6% per annum. All other overheads are expected to increase in-line with UK inflation of 7% per annum. Remaining costs for construction, fixtures and fittings for the planned new apartments will require a payment of 30,000 in February followed by another payment of 25,000 in June. Depreciation costs are expected to increase by 20% in 2023 , then by 5% per annum from 2024. At present the company has an arranged overdraft facility charged at 10%. Information relevant to the preparation of the 12 month detailed 2023 Cash Flow Forecast for guest rooms: The opening cash balance is estimated to be 30,000 at the beginning of 2023 . Sales are collected 10% in the current month and 70% after one month and 19.5% after two months, the remaining 0.5% relate to uncollectable bad debts. The laundry service and toiletries pack are paid one month later. All other costs are paid in the month they are incurred in. Each room takes 0.45 hours to clean and it is estimated idle time is running at 10%. Direct labour is currently paid 10.50 per hour in 2022 . Ignore sales tax. Workings should to be done in 'work sheet 2' and driven to a front page summary 'work sheet 1' showing yearly profit forecasts and monthly forecast cash flow summaries produced under the assumptions described above. The idea of any model is that if say the key assumptions are altered all relevant figures will automatically change for all future years; similarly if the percentage of sales is changed then again any linked data also needs to reflect this. As far as possible ALL figures should be driven initially from last years results or the previous years forecast. It is important to work out which are fixed and which are the variable costs in putting together your forecasts and consider the cost driver. Section 2: Traditional costing versus Activity Based Costing To date Middlesex Hotel has used a traditional method to allocate costs for services provided, applying valume hased measures. The table below shows how costs have been allncated for rooms booked during 2022: Marc Polonius is considering introducing Activity Based Costing to allocate costs in 2023. The hotel manager has provided some information regarding the costs related to the set-up costs for providing a room on the day of arrival and subsequent days of stay in the hotel. A full pack of clean laundry is provided on day of check-in, on subsequent days during stay in hotel, usually only half of the linen pack is sent to laundry as guests often re-use towels several days. The laundry service cost for a full pack of clean linen in 2022 is 13. The rooms are fully cleaned on day of arrival, this takes 35 minutes per room, on subsequent days during stay in hotel, rooms only take 20 minutes to clean. A full pack of toiletries is provided on day of arrival, on subsequent days during stay in hotel, the average cost per room to top-up toiletries is 1 per room. Reception are responsible for the check-in process on average this takes 15 minutes per new room booking. On subsequent days during stay in hotel, reception spend on average 5 minutes per room booking dealing with enquiries. On the day of check-out the average time taken for reception is 10 minutes per room. The receptionist has stated that check-out time can vary as some customers use the fast key drop service which only takes 5 minutes to process whereas some customers have many queries and the process can take up to 20 minutes. The total hours worked by reception per year is 2,520 hours. Concierge take bags to rooms on check-in this takes on average 10 minutes, on subsequent days during stay in hotel, the average time spent per room booking is 5 minutes. The concierge works 1,920 hours per year. Requirnd: a) Produce a DETAILED 5 year Forecast Income Statement for the hotel's guest rooms showing 2022 for reference and covering 2023 - 2027 inclusive. b) Produce a DETAILED monthly Cash Flow for JUST 2023 based on 2022's Financial Statement and all of the information given above for the hotel's guest rooms. You will need to use the 12 monthly information given above. You will need to expand the overhead section by making reasonable assumptions as to what other costs you would expect to be reflected in a hotel and ensure you have accurately included all of the information given to you in Appendix 1. Please make sure your individual category totals for Revenue, Cost of Salns, Ovarhaads etc. do actually reconcile to those given in the forecast Financial Statement. c) Clearly identify, on your excel spreadsheet, all assumptions used in your model as to how expenses or income have been inflated and translated and which easts you have treated as being fixed as opposed to variable costs. d) Using the information provided in Section 1, produce a brief report for your manager. The report should include the following: - Recommendations that the business needs to consider going forward given your findings on the five year profit forecast and monthly cash flow forecast. Identify the differences between a cash flow statement and a profit forecast as to what items are included in one but not in the other. Explain the timing differences between receipts from sales and payments for purchases and the impact on the cash flow balances in 2023. Discuss the advantages and disadvantages of the budget method applied by Marc Polonius, the management accountant, for determining the predictions of future costs. Suggest an alternative method to determine predictions of future costs. The report should be a_brief summary of the points required, no more than 900 words maximum on Word document, submitted through Turnitin dropbox. Continued.on next page e) Using the information provided in Section 2, apply Activity Based Costing principles to determine the costs in 2023 for a cost pool for 'Guest room set-up costs' and compare to total costs for a cost pool 'Guest room on subsequent day of stay'. Prepare a brief report advising management of the hotel group of your findings and suggest any recommendations. This second report should include the following: A cost estimate using Activity Based Costing principles. Give detailed notes explaining how each cost has been calculated. - Identify any further information required to calculate the full cost per room provided. Give suggestions for how to treat reception check-out costs. Appendix 1 Extract from Estimated Financial Statement and Notes to the Accounts for the year to 31st December 2022 Estimated Income Statement - 31 st December 2022 Middlesex Hotel Guest rooms LaundryServiceRoomCleaningWagesToiletrypacksCostofsalesOverheadsf(49,275)(28,750)(9,965)2022f(87.990)377.265) Notes to Accounts: Staff Costs SalariesReceptionNightdutyConciergeManagerandadministration%TimespentonGuestroomsAveragenumberEmployees1.51150%Averagesalaryin202224,00019,00018,50060,000 Case Study Assignment You have recently been recruited by Middlesex Hotel, part of a small chain of boutique hotels based in the London area, as an assistant management accountant reporting to the Management Accountant, Marc Polonius. Your role involves helping in the preparation of future budgets and forecasts. Up until now they have only produced annual Financial Statements and a simple budget, however, given the need for better forward planning you have been asked to produce a five year profit forecast (Income Statement) for the years 2023 to 2027 and a detailed monthly cash flow for next year 2023. Marc, has determined the future cost estimates without discussing these with other managers. The increase in sales in future periods is based on estimates produced by the group sales manager. More relevant information will be extracted from their estimated Income Statement 2022 summarised in Appendix 1. Section 1: Management Accountant's cost estimates and Sales manager's forecast information for guest rooms. Middlesex Hotel has twenty-five guest rooms, a restaurant, bar and guest lounge; there are fifteen standard and ten deluxe guest rooms. The estimated average occupancy rate for all guest rooms is between 60% and 75% of capacity, it is expected for 2022 the occupancy rate will be 60%. The hotel plans to open a new wing on the 15t July 2023, containing five small guest apartments. The revenue expected from opening the new apartments in 2023 is 69,420. Demand for guest rooms is seasonal and classified as low, medium or high season. Different room rates apply according to the seasonal demand, estimated occupancy rates in each season and the applicable room rate is shown in appendix 1 . There is strong competition in the area for standard rooms, for this reason the hotel manager does not plan to increase the room rate for these rooms; the room rates for deluxe rooms is expected to rise by 3% per annum from 2023 and the room rate for the new apartments is expected to rise by 3% from 2024. The hotel uses a laundry service for linen, laundry costs are expected to increase 20% in 2023 due to increases in energy prices, then by 5% per annum from 2024. Room cleaning wages are expected to increase by 6% in 2023 , then by 4% per annum from 2024. The cost of a pack of toiletries provided for guests is expected to increase by 3% per annum. The fixed lease for the premises of 120,000 expires on 31st May 2023. A new 5year lease has been agreed at a price of 138,000 p.a. The rent is paid quarterly in arrears. Rates are estimated as 30% of the rental value. Utility costs to inflate by 200% in 2023 , due to uncertainty, the forecast will base future costs on the 2023 cost and increase by 4% per annum from 2024. Salaries and remuneration to increase by 6% per annum. All other overheads are expected to increase in-line with UK inflation of 7% per annum. Remaining costs for construction, fixtures and fittings for the planned new apartments will require a payment of 30,000 in February followed by another payment of 25,000 in June. Depreciation costs are expected to increase by 20% in 2023 , then by 5% per annum from 2024. At present the company has an arranged overdraft facility charged at 10%. Information relevant to the preparation of the 12 month detailed 2023 Cash Flow Forecast for guest rooms: The opening cash balance is estimated to be 30,000 at the beginning of 2023 . Sales are collected 10% in the current month and 70% after one month and 19.5% after two months, the remaining 0.5% relate to uncollectable bad debts. The laundry service and toiletries pack are paid one month later. All other costs are paid in the month they are incurred in. Each room takes 0.45 hours to clean and it is estimated idle time is running at 10%. Direct labour is currently paid 10.50 per hour in 2022 . Ignore sales tax. Workings should to be done in 'work sheet 2' and driven to a front page summary 'work sheet 1' showing yearly profit forecasts and monthly forecast cash flow summaries produced under the assumptions described above. The idea of any model is that if say the key assumptions are altered all relevant figures will automatically change for all future years; similarly if the percentage of sales is changed then again any linked data also needs to reflect this. As far as possible ALL figures should be driven initially from last years results or the previous years forecast. It is important to work out which are fixed and which are the variable costs in putting together your forecasts and consider the cost driver. Section 2: Traditional costing versus Activity Based Costing To date Middlesex Hotel has used a traditional method to allocate costs for services provided, applying valume hased measures. The table below shows how costs have been allncated for rooms booked during 2022: Marc Polonius is considering introducing Activity Based Costing to allocate costs in 2023. The hotel manager has provided some information regarding the costs related to the set-up costs for providing a room on the day of arrival and subsequent days of stay in the hotel. A full pack of clean laundry is provided on day of check-in, on subsequent days during stay in hotel, usually only half of the linen pack is sent to laundry as guests often re-use towels several days. The laundry service cost for a full pack of clean linen in 2022 is 13. The rooms are fully cleaned on day of arrival, this takes 35 minutes per room, on subsequent days during stay in hotel, rooms only take 20 minutes to clean. A full pack of toiletries is provided on day of arrival, on subsequent days during stay in hotel, the average cost per room to top-up toiletries is 1 per room. Reception are responsible for the check-in process on average this takes 15 minutes per new room booking. On subsequent days during stay in hotel, reception spend on average 5 minutes per room booking dealing with enquiries. On the day of check-out the average time taken for reception is 10 minutes per room. The receptionist has stated that check-out time can vary as some customers use the fast key drop service which only takes 5 minutes to process whereas some customers have many queries and the process can take up to 20 minutes. The total hours worked by reception per year is 2,520 hours. Concierge take bags to rooms on check-in this takes on average 10 minutes, on subsequent days during stay in hotel, the average time spent per room booking is 5 minutes. The concierge works 1,920 hours per year. Requirnd: a) Produce a DETAILED 5 year Forecast Income Statement for the hotel's guest rooms showing 2022 for reference and covering 2023 - 2027 inclusive. b) Produce a DETAILED monthly Cash Flow for JUST 2023 based on 2022's Financial Statement and all of the information given above for the hotel's guest rooms. You will need to use the 12 monthly information given above. You will need to expand the overhead section by making reasonable assumptions as to what other costs you would expect to be reflected in a hotel and ensure you have accurately included all of the information given to you in Appendix 1. Please make sure your individual category totals for Revenue, Cost of Salns, Ovarhaads etc. do actually reconcile to those given in the forecast Financial Statement. c) Clearly identify, on your excel spreadsheet, all assumptions used in your model as to how expenses or income have been inflated and translated and which easts you have treated as being fixed as opposed to variable costs. d) Using the information provided in Section 1, produce a brief report for your manager. The report should include the following: - Recommendations that the business needs to consider going forward given your findings on the five year profit forecast and monthly cash flow forecast. Identify the differences between a cash flow statement and a profit forecast as to what items are included in one but not in the other. Explain the timing differences between receipts from sales and payments for purchases and the impact on the cash flow balances in 2023. Discuss the advantages and disadvantages of the budget method applied by Marc Polonius, the management accountant, for determining the predictions of future costs. Suggest an alternative method to determine predictions of future costs. The report should be a_brief summary of the points required, no more than 900 words maximum on Word document, submitted through Turnitin dropbox. Continued.on next page e) Using the information provided in Section 2, apply Activity Based Costing principles to determine the costs in 2023 for a cost pool for 'Guest room set-up costs' and compare to total costs for a cost pool 'Guest room on subsequent day of stay'. Prepare a brief report advising management of the hotel group of your findings and suggest any recommendations. This second report should include the following: A cost estimate using Activity Based Costing principles. Give detailed notes explaining how each cost has been calculated. - Identify any further information required to calculate the full cost per room provided. Give suggestions for how to treat reception check-out costs. Appendix 1 Extract from Estimated Financial Statement and Notes to the Accounts for the year to 31st December 2022 Estimated Income Statement - 31 st December 2022 Middlesex Hotel Guest rooms LaundryServiceRoomCleaningWagesToiletrypacksCostofsalesOverheadsf(49,275)(28,750)(9,965)2022f(87.990)377.265) Notes to Accounts: Staff Costs SalariesReceptionNightdutyConciergeManagerandadministration%TimespentonGuestroomsAveragenumberEmployees1.51150%Averagesalaryin202224,00019,00018,50060,000