You have recently been recruited by Middlesex Hotel, part of a small chain of boutique hotels...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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 1¹ 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 5 year 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 volume based measures. The table below shows how costs have been allocated for rooms booked during 2022: Laundry Service Room Cleaning Wages Toiletries & tea/coffee Cost allocation bases Per room Per direct labour hour Per room Salaries and Remuneration Rent & Rates Other Overheads Per room Floor space Per room 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. Required: 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 Sales, Overheads 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 costs 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 - 31st December 2022 Middlesex Hotel Guest rooms Type of room Standard Deluxe Rate per room 2023 Apartment Rate per room by season Low £70.00 £90.00 £100.00 Medium High £84.00 £98.00 £108.00 £126.00 £120.00 £140.00 No. Rooms booked per Low 5 3 1 season Medium 9 6 High 14 8 3 5 Laundry Service Room Cleaning Wages Toiletry packs Cost of sales Overheads Notes to Accounts: Staff Costs Salaries Reception Night duty Concierge Manager and administration % Time spent on Guest rooms Average number Employees 1.5 1 1 50% (49,275) (28,750) (9,965) Average salary in 2022 £24,000 £19,000 £ £18,500 £60,000 2022 £ (87,990) (377,265) 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 1¹ 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 5 year 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 volume based measures. The table below shows how costs have been allocated for rooms booked during 2022: Laundry Service Room Cleaning Wages Toiletries & tea/coffee Cost allocation bases Per room Per direct labour hour Per room Salaries and Remuneration Rent & Rates Other Overheads Per room Floor space Per room 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. Required: 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 Sales, Overheads 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 costs 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 - 31st December 2022 Middlesex Hotel Guest rooms Type of room Standard Deluxe Rate per room 2023 Apartment Rate per room by season Low £70.00 £90.00 £100.00 Medium High £84.00 £98.00 £108.00 £126.00 £120.00 £140.00 No. Rooms booked per Low 5 3 1 season Medium 9 6 High 14 8 3 5 Laundry Service Room Cleaning Wages Toiletry packs Cost of sales Overheads Notes to Accounts: Staff Costs Salaries Reception Night duty Concierge Manager and administration % Time spent on Guest rooms Average number Employees 1.5 1 1 50% (49,275) (28,750) (9,965) Average salary in 2022 £24,000 £19,000 £ £18,500 £60,000 2022 £ (87,990) (377,265)
Expert Answer:
Answer rating: 100% (QA)
To prepare a fiveyear profit forecast for the years 2023 to 2027 and a detailed monthly cash flow for 2023 several assumptions and information provided by Middlesex Hotels Management Accountant and Sa... View the full answer
Related Book For
Accounting concepts and applications
ISBN: 978-0538745482
11th Edition
Authors: Albrecht Stice, Stice Swain
Posted Date:
Students also viewed these management leadership questions
-
The president of a small chain of women's clothing stores was concerned about a four-year trend of decreasing profits. The stores have been characterized as being rather conservative over the years...
-
You have recently been appointed chief investment officer of a major charitable foundation. Its large endowment fund is currently invested in a broadly diversified portfolio of stocks (60 percent)...
-
You have recently been hired as a staff assistant in the office of the chairman of the board of directors of Clefton, Inc. Because you have some background in accounting, the chairman has asked you...
-
The lower-of-cost-or-net realizable value rule for inventory is an example of the application of: (a) the conservatism convention. (b) the historical cost principle. (c) the materiality concept. (d)...
-
Ten years ago, Hailey invested $2,000 and locked in a 9 percent annual interest rate for 30 years (end 20 years from now). Aidan can make a 20-year investment today and lock in a 10 percent interest...
-
Elite Video has the following unadjusted trial balance as of October 31, 2012. The debit and credit totals are not equal as a result of the following errors: a. The balance of cash was overstated by...
-
Andrew Reitz established a trust in 2000, naming his sons, James and John, as sole beneficiaries and himself as trustee. Upon Andrews death, Hal Rachal Jr., the attorney who drafted the trust, became...
-
Allocation of corporate costs to divisions. Dusty Rhodes, controller of Richfield Oil Company, is preparing a presentation to senior executives about the performance of its four divisions. Summary...
-
Imagine that you lend $5,000 to a friend at 7%, and say, "Pay me back when you get a job." Five years later, your friend gets a job and pays you back. Your friend assumed that you meant simple...
-
Janice Morgan, age 24, is single and has no dependents. She is a freelance writer. In January 2021, Janice opened her own office located at 2751 Waldham Road, Pleasant Hill, NM 88135. She called her...
-
Suppose the price is currently at $15 in this competitive industry. We would expect that Price Supply A Supply B SupplyC THE 250 300 400 $15 $10 $5 Entire Market Qty Demand S per unit Ony Single...
-
When an agent enters into a contract on behalf of a principal, the principal must ratify the contract to be bound. (True/False)
-
Zuri is employed by American Grocers to buy and install a computer system for Americans distribution network. When the system is set up and running, the agency a. terminates automatically. b....
-
A shareholders appraisal right is available in all states under all circumstances. (True/False)
-
Disparate-treatment discrimination occurs when an employer intentionally discriminates against an employee. (True/False)
-
When is a shareholders appraisal right available?
-
You are a U.S exporter who sells goods at $10 USD each to a buyer in Canada. The exchange rate is $1USD = $1.25 CAD. What is the cost to the Canadian buyer in Canadian dollars?
-
6. (Potential Energy and Conservation of Energy) What should be the spring constant k of a spring designed to bring a 1200-kg car to rest from a speed of 95 km/h so that the occupants undergo a...
-
Why do the LIFO and average cost inventory cost flow assumptions result in different inventory numbers for the perpetual and periodic inventory methods?
-
Ecotec Inc. purchased 70% of the outstanding common stock of Beatrix Co. on January 1, 2010, paying $875,000. On that day, the balance sheets of the two companies immediately after the purchase are...
-
Wal-Marts stockholders equity statements provide details of equity transactions of the company during the 2009 fiscal year. Locate the statements in the 2009 Form 10-K for Wal-Mart (Appendix A) and...
-
Cinnamon, Inc. recorded a total deferred tax asset in Year 3 of \($12\),301, off set by a \($12\),301 valuation allowance. Cinnamon most likely: A . fully utilized the deferred tax asset in Year 3. B...
-
Midland Brands issues three-year bonds dated January 1, 2015 with a face value of $5,000,000. Th e market interest rate on bonds of comparable risk and term is 3%. If the bonds pay 2.5% annually on...
-
A company issues $1,000,000 face value of 10-year bonds on January 1, 2015 when the market interest rate on bonds of comparable risk and terms is 5%. Th e bonds pay 6% interest annually on December...
Study smarter with the SolutionInn App