Paul is a fine arts graduate, who has several years' experience working on games graphics. Recently...

  
 
 
 
 
 

Transcribed Image Text:

Paul is a fine arts graduate, who has several years' experience working on games graphics. Recently he has been made redundant, mainly due to the financial difficulty facing his company in the current pandemic situation. He has now turned back to his main love, which is painting and printing, but is finding freelance work financially unproductive at the moment. While Paul is not someone who needs a huge amount of money to be content, he has realised that he needs a certain amount of regular income to enable him to survive, and also to be able to purchase materials needed for his preferred activities. Recently an opportunity has arisen which looks as though it might enable him to achieve what he wants, which is essentially a base level of income, but with enough time to devote to his main interest. There is a small motel available for sale in a small country town, which has a reputation for being something of an 'arty' town. There is already an art gallery there, together with a choir and a hall which is used for visiting performers. The location is close to a very popular national park, with a good prospect of high occupancy rates. As against this, occupation rates are likely to be low in the non-school holiday times and the national park is close to the bush fire areas. The motel is offered as a 'walk in, walk out' (WIWO) facility, and is fully equipped. The owner decides to sell because of the low number of occupancy due to the travelling restriction imposed by the Australian government recently. To show the average occupancy in the past during the same period, the owner has provided a record of room occupancy in attachment 1. The purchase price for the business is $50,000 which Paul considers to be a bargain. However, the buildings are not owned, but leased. The current lease expires in three years, but there are options for at least two further five-year terms in place. The current lease costs $40,000 per annum in rent. The motel has 15 rooms, 10 of which are double or twin rooms. There are a further three rooms with a third single bed, and the remaining two rooms are family rooms that will take four to five people. There is a two- bedded residence for the manager as well. The motel is graded as a three-star facility, the rates per night for the rooms are listed in the table below. Rate per room per day Double/Twin room Three-bed room (Double+Single) Family room (4-5 people) $80 $100 $120 Morning tea is provided at an extra cost of $10 per room per day as part of the motel service. A secured parking spot is also allocated to each room, which costs an additional $5 per day. The cost of toiletries and towels is $3 on average per day for each room. Although the customers want to enjoy the country lifestyle, the internet service (i.e. Wi-Fi) is still a necessity. The cost for internet service is estimated to be $2 per day per room. Paul wants to keep these services and believes that the costs will remain the same in the future. The statement that is provided by the sales agent includes the following information: 1. The business has been in operation for 10 years. 2. The room occupancy record for July 2020-June 2021 is provided (see attachment 2). 3. Interest and financing costs were $2,000. 4. Depreciation expense for the equipment in the motel was $5,000. On investigation Paul is given more detail relating to the operating expenses for the last year, as follows: Bank charges Carpet cleaning¹ Council registration fees New curtains² General cleaning chemicals Insurance Repairs and maintenance Pool chemicals and cleaning Annual newspaper subscription Accountancy fee Wages for casual staff Motel Room cleaning • . 1,600 800 900 1,500 700 . 4,000 5,000 800 1,500 1,500 20,000 20,000 ¹ Carpet cleaning is only for the common area. 2 The owner changes the curtains every five years. ● Electricity Water Donations to local charities Miscellaneous expenses Software, internet and computers (set-up fees) Advertising and memberships Telephone Car expenses Rent Rates Laundry cleaning - beddings Following detailed discussion with the vendor, Paul discovers that: No attempt has been made to segregate personal and business expenses. For the expenses that may involve personal use, the estimated allocation of the expenses is 80% for business and 20% for personal use. 15,000 3,000 1,200 7,000 3,500 8,000 2,000 3,000 40,000 4,000 5,000 The ingredients for morning tea are purchased from cash receipts. The vendor estimates that he takes out about $300 every week from the business for his living. The car expenses are in cash and paid by the business. Paul feels good about this business opportunity as he believes that he can make a good living from it. If he buys the motel business, he will be his own boss and enjoy doing what he is passionate about at the same time. To finance the purchase, Paul will need to borrow $35,000 from the bank with a fixed simple interest rate of 1.25% per annum for 10 years. and the rest of the money will be from his savings. Paul thinks that he can definitely get the lease renewed for another 10 years as he will be given priority to the lease if there is any competition. He also thinks the rates for the rooms can be increased by $10 per room, which reflects the market price based on his research on the internet. His strategy to reduce the expenses includes doing all the room cleaning, repairs and maintenance by himself. During a business trip in the past, Paul has hurt his back but he thinks that he is more than capable to carry out those tasks. He also wants to discontinue the donations to local charities because he doesn't see much benefit of doing that. Paul thinks of using his skills in graphics and art to make a marketing campaign in social media to promote the motel, which he believes will increase the room occupancy by at least 10% for the double/twin rooms, 8% for the 'Three-bed' rooms and 5% for the family rooms. Paul wants to get some advice from you, as you are a close friend and work in a reputable business consultancy company for almost 5 years. He will pay you $500 for your service (discount rate for a friend). He indicates that he will need $200 cash per week for personal use. Paul plans to start the business on 1/9/2022 when he believes that the Australian government will fully relax the restrictions that were put in place for COVID. Use attachment 2 - Room Occupancy 2020-2021 to complete the following instructions. You need to add the 'room occupancy record for 2020-2021' to a workbook in Excel and label the tab as 'Data-Room occupancy-2020-2021. Copy and paste this data to another tab - called 'Data-Room occupancy - budgeted'. Use this to calculate the expected occupancy for 2021-2022 if the marketing campaign is successful. You also need to prepare a 'Budgeted Contribution' in a new tab to show the estimated weekly contribution from the rooms and the total contribution for 2022. You can create/add additional columns if necessary. Create another tab called 'Data' and place this before the tab for 'Budgeted Contribution'. Please enter relevant variables in the 'Data' tab and name them. Use formulas and relevant techniques to populate the columns and calculate the relevant figures which will be used to calculate the budgeted contribution per week. Please name the relevant variables. You want to give this workbook to Paul so he can play around with key variables and observe the impact on total contribution margin. (Hint: dynamic linking, addressing/naming cells, functions, formulas, etc.) In the same workbook, open a new worksheet (a new tab) and label it 'Cash Flow Budget'. Based on instructions below, you will use this tab to prepare a cash flow budget for 12 months, commencing from 1 September 2022. (Paul wants you to change the data. The first month will be September. Use the figures for July and August 2020 for July and August 2022. Only do this for the cash flow budget. Don't disturb the figures for the contribution margin worksheet.) To make it easier to read and navigate the cash flow budget, you need to aggregate the data from the 'Budgeted Contribution' sheet so that monthly totals are shown, rather than weekly totals. To do so, you can insert a column (in the Budgeted Contribution sheet) which will show the month a given week falls in. You also need to use a text function to convert the date for each week into a month (Hint: Google it). Use the subtotal function to aggregate the relevant figures for all weeks belonging to a given month. Run the subtotal function. Copy and paste the visible cells (only the visible cells) to the 'Cash Flow Budget' sheet. After this, copy and transpose, so that the months are the column headings. Prepare a cash flow budget based on the contribution margin and other relevant material. Required: 1) Complete the instructions relating to the Budgeted Contribution tab. 2) Complete the cash flow budget. 3) Identify limitations and weaknesses of the cash budget. 4) a) Create another tab called revised cash budget and create a better designed model. You must state your assumptions for the revised cash budget. Feel free to adopt a format that suits this data. Ensure your model can easily present relevant information aggregated by month and by weeks. 10 marks 10 marks b) Briefly explain why your model is better than the one original one. 15 marks ● 20 marks 5) Open another tab and prepare a budgeted income statement for the 12 months from September 2022. Ensure it is dynamically linked to the revised cash budget. 15 marks ● 6) Prepare a brief for Paul to cover the following areas. Advise Paul if the purchase price is appropriate. Advise Paul on the future perspective of the business if he purchases. Identify and discuss any risks that Paul must be aware of. Advise Paul on whether or not he should proceed with the purchase. What other factors might be worth considering by Paul in reaching a final decision? ● 10 marks 20 marks Attachment 1 - Room occupancy - February to April 2020 Average Days booked per room Double/Twin Room Weeks 5 6 7 8 9 10 11 12 13 14 15 16 17 Week ending 3/02/2020 10/02/2020 17/02/2020 24/02/2020 3/03/2020 10/03/2020 17/03/2020 24/03/2020 31/03/2020 7/04/2020 14/04/2020 21/04/2020 28/04/2020 4 2 2 2 3 2 2 3 6 5 3 3 2 Average Days booked per room Three-bed Room 2 2 2 2 2 3 3 2 5 5 3 2 3 Average Days booked per room Family Room 1 0 0 0 1 0 0 0 5 5 3 0 0 Attachment 2 - Room occupancy - 2020-2021 Weeks 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Week ending 7/07/2020 14/07/2020 21/07/2020 28/07/2020 4/08/2020 11/08/2020 18/08/2020 25/08/2020 1/09/2020 8/09/2020 15/09/2020 22/09/2020 29/09/2020 6/10/2020 13/10/2020 20/10/2020 27/10/2020 3/11/2020 10/11/2020 17/11/2020 24/11/2020 1/12/2020 8/12/2020 15/12/2020 22/12/2020 29/12/2020 5/01/2021 12/01/2021 19/01/2021 26/01/2021 2/02/2021 9/02/2021 16/02/2021 23/02/2021 1/03/2021 8/03/2021 15/03/2021 Average Days booked per room Double/Twin Room 5 2 3 2 2 1 0 2 3 3 4 3 4 2 2 2 3 3 2 3 3 4 5 5 7 7 7 7 2 4 5 5 7 2 3 2 4 Average Days booked per room Three-bed Room 6 2 2 0 0 1 0 2 2 2 3 5 6 3 3 2 3 2 2 2 2 5 5 6 7 7 6 6 3 2 2 4 2 1 2 3 4 Average Days booked per room Family Room 5 2 1 0 0 0 0 0 1 1 3 4 5 1 0 0 1 0 0 0 1 0 0 0 7 7 5 5 1 2 2 1 1 0 0 1 0 38 39 40 41 42 43 44 45 46 17/05/2021 24/05/2021 7890 47 48 49 22/03/2021 29/03/2021 5/04/2021 50 12/04/2021 19/04/2021 26/04/2021 3/05/2021 10/05/2021 31/05/2021 7/06/2021 14/06/2021 51 21/06/2021 52 28/06/2021 5 3 4 2 1 1 2 3 2 3 5 6 5 4 5 2 2 1 1 2 2 3 4 4 1 5 6 3 4 5 0 2 1 3 5 5 2 1 2 1 3 6 4 3 4 Paul is a fine arts graduate, who has several years' experience working on games graphics. Recently he has been made redundant, mainly due to the financial difficulty facing his company in the current pandemic situation. He has now turned back to his main love, which is painting and printing, but is finding freelance work financially unproductive at the moment. While Paul is not someone who needs a huge amount of money to be content, he has realised that he needs a certain amount of regular income to enable him to survive, and also to be able to purchase materials needed for his preferred activities. Recently an opportunity has arisen which looks as though it might enable him to achieve what he wants, which is essentially a base level of income, but with enough time to devote to his main interest. There is a small motel available for sale in a small country town, which has a reputation for being something of an 'arty' town. There is already an art gallery there, together with a choir and a hall which is used for visiting performers. The location is close to a very popular national park, with a good prospect of high occupancy rates. As against this, occupation rates are likely to be low in the non-school holiday times and the national park is close to the bush fire areas. The motel is offered as a 'walk in, walk out' (WIWO) facility, and is fully equipped. The owner decides to sell because of the low number of occupancy due to the travelling restriction imposed by the Australian government recently. To show the average occupancy in the past during the same period, the owner has provided a record of room occupancy in attachment 1. The purchase price for the business is $50,000 which Paul considers to be a bargain. However, the buildings are not owned, but leased. The current lease expires in three years, but there are options for at least two further five-year terms in place. The current lease costs $40,000 per annum in rent. The motel has 15 rooms, 10 of which are double or twin rooms. There are a further three rooms with a third single bed, and the remaining two rooms are family rooms that will take four to five people. There is a two- bedded residence for the manager as well. The motel is graded as a three-star facility, the rates per night for the rooms are listed in the table below. Rate per room per day Double/Twin room Three-bed room (Double+Single) Family room (4-5 people) $80 $100 $120 Morning tea is provided at an extra cost of $10 per room per day as part of the motel service. A secured parking spot is also allocated to each room, which costs an additional $5 per day. The cost of toiletries and towels is $3 on average per day for each room. Although the customers want to enjoy the country lifestyle, the internet service (i.e. Wi-Fi) is still a necessity. The cost for internet service is estimated to be $2 per day per room. Paul wants to keep these services and believes that the costs will remain the same in the future. The statement that is provided by the sales agent includes the following information: 1. The business has been in operation for 10 years. 2. The room occupancy record for July 2020-June 2021 is provided (see attachment 2). 3. Interest and financing costs were $2,000. 4. Depreciation expense for the equipment in the motel was $5,000. On investigation Paul is given more detail relating to the operating expenses for the last year, as follows: Bank charges Carpet cleaning¹ Council registration fees New curtains² General cleaning chemicals Insurance Repairs and maintenance Pool chemicals and cleaning Annual newspaper subscription Accountancy fee Wages for casual staff Motel Room cleaning • . 1,600 800 900 1,500 700 . 4,000 5,000 800 1,500 1,500 20,000 20,000 ¹ Carpet cleaning is only for the common area. 2 The owner changes the curtains every five years. ● Electricity Water Donations to local charities Miscellaneous expenses Software, internet and computers (set-up fees) Advertising and memberships Telephone Car expenses Rent Rates Laundry cleaning - beddings Following detailed discussion with the vendor, Paul discovers that: No attempt has been made to segregate personal and business expenses. For the expenses that may involve personal use, the estimated allocation of the expenses is 80% for business and 20% for personal use. 15,000 3,000 1,200 7,000 3,500 8,000 2,000 3,000 40,000 4,000 5,000 The ingredients for morning tea are purchased from cash receipts. The vendor estimates that he takes out about $300 every week from the business for his living. The car expenses are in cash and paid by the business. Paul feels good about this business opportunity as he believes that he can make a good living from it. If he buys the motel business, he will be his own boss and enjoy doing what he is passionate about at the same time. To finance the purchase, Paul will need to borrow $35,000 from the bank with a fixed simple interest rate of 1.25% per annum for 10 years. and the rest of the money will be from his savings. Paul thinks that he can definitely get the lease renewed for another 10 years as he will be given priority to the lease if there is any competition. He also thinks the rates for the rooms can be increased by $10 per room, which reflects the market price based on his research on the internet. His strategy to reduce the expenses includes doing all the room cleaning, repairs and maintenance by himself. During a business trip in the past, Paul has hurt his back but he thinks that he is more than capable to carry out those tasks. He also wants to discontinue the donations to local charities because he doesn't see much benefit of doing that. Paul thinks of using his skills in graphics and art to make a marketing campaign in social media to promote the motel, which he believes will increase the room occupancy by at least 10% for the double/twin rooms, 8% for the 'Three-bed' rooms and 5% for the family rooms. Paul wants to get some advice from you, as you are a close friend and work in a reputable business consultancy company for almost 5 years. He will pay you $500 for your service (discount rate for a friend). He indicates that he will need $200 cash per week for personal use. Paul plans to start the business on 1/9/2022 when he believes that the Australian government will fully relax the restrictions that were put in place for COVID. Use attachment 2 - Room Occupancy 2020-2021 to complete the following instructions. You need to add the 'room occupancy record for 2020-2021' to a workbook in Excel and label the tab as 'Data-Room occupancy-2020-2021. Copy and paste this data to another tab - called 'Data-Room occupancy - budgeted'. Use this to calculate the expected occupancy for 2021-2022 if the marketing campaign is successful. You also need to prepare a 'Budgeted Contribution' in a new tab to show the estimated weekly contribution from the rooms and the total contribution for 2022. You can create/add additional columns if necessary. Create another tab called 'Data' and place this before the tab for 'Budgeted Contribution'. Please enter relevant variables in the 'Data' tab and name them. Use formulas and relevant techniques to populate the columns and calculate the relevant figures which will be used to calculate the budgeted contribution per week. Please name the relevant variables. You want to give this workbook to Paul so he can play around with key variables and observe the impact on total contribution margin. (Hint: dynamic linking, addressing/naming cells, functions, formulas, etc.) In the same workbook, open a new worksheet (a new tab) and label it 'Cash Flow Budget'. Based on instructions below, you will use this tab to prepare a cash flow budget for 12 months, commencing from 1 September 2022. (Paul wants you to change the data. The first month will be September. Use the figures for July and August 2020 for July and August 2022. Only do this for the cash flow budget. Don't disturb the figures for the contribution margin worksheet.) To make it easier to read and navigate the cash flow budget, you need to aggregate the data from the 'Budgeted Contribution' sheet so that monthly totals are shown, rather than weekly totals. To do so, you can insert a column (in the Budgeted Contribution sheet) which will show the month a given week falls in. You also need to use a text function to convert the date for each week into a month (Hint: Google it). Use the subtotal function to aggregate the relevant figures for all weeks belonging to a given month. Run the subtotal function. Copy and paste the visible cells (only the visible cells) to the 'Cash Flow Budget' sheet. After this, copy and transpose, so that the months are the column headings. Prepare a cash flow budget based on the contribution margin and other relevant material. Required: 1) Complete the instructions relating to the Budgeted Contribution tab. 2) Complete the cash flow budget. 3) Identify limitations and weaknesses of the cash budget. 4) a) Create another tab called revised cash budget and create a better designed model. You must state your assumptions for the revised cash budget. Feel free to adopt a format that suits this data. Ensure your model can easily present relevant information aggregated by month and by weeks. 10 marks 10 marks b) Briefly explain why your model is better than the one original one. 15 marks ● 20 marks 5) Open another tab and prepare a budgeted income statement for the 12 months from September 2022. Ensure it is dynamically linked to the revised cash budget. 15 marks ● 6) Prepare a brief for Paul to cover the following areas. Advise Paul if the purchase price is appropriate. Advise Paul on the future perspective of the business if he purchases. Identify and discuss any risks that Paul must be aware of. Advise Paul on whether or not he should proceed with the purchase. What other factors might be worth considering by Paul in reaching a final decision? ● 10 marks 20 marks Attachment 1 - Room occupancy - February to April 2020 Average Days booked per room Double/Twin Room Weeks 5 6 7 8 9 10 11 12 13 14 15 16 17 Week ending 3/02/2020 10/02/2020 17/02/2020 24/02/2020 3/03/2020 10/03/2020 17/03/2020 24/03/2020 31/03/2020 7/04/2020 14/04/2020 21/04/2020 28/04/2020 4 2 2 2 3 2 2 3 6 5 3 3 2 Average Days booked per room Three-bed Room 2 2 2 2 2 3 3 2 5 5 3 2 3 Average Days booked per room Family Room 1 0 0 0 1 0 0 0 5 5 3 0 0 Attachment 2 - Room occupancy - 2020-2021 Weeks 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 Week ending 7/07/2020 14/07/2020 21/07/2020 28/07/2020 4/08/2020 11/08/2020 18/08/2020 25/08/2020 1/09/2020 8/09/2020 15/09/2020 22/09/2020 29/09/2020 6/10/2020 13/10/2020 20/10/2020 27/10/2020 3/11/2020 10/11/2020 17/11/2020 24/11/2020 1/12/2020 8/12/2020 15/12/2020 22/12/2020 29/12/2020 5/01/2021 12/01/2021 19/01/2021 26/01/2021 2/02/2021 9/02/2021 16/02/2021 23/02/2021 1/03/2021 8/03/2021 15/03/2021 Average Days booked per room Double/Twin Room 5 2 3 2 2 1 0 2 3 3 4 3 4 2 2 2 3 3 2 3 3 4 5 5 7 7 7 7 2 4 5 5 7 2 3 2 4 Average Days booked per room Three-bed Room 6 2 2 0 0 1 0 2 2 2 3 5 6 3 3 2 3 2 2 2 2 5 5 6 7 7 6 6 3 2 2 4 2 1 2 3 4 Average Days booked per room Family Room 5 2 1 0 0 0 0 0 1 1 3 4 5 1 0 0 1 0 0 0 1 0 0 0 7 7 5 5 1 2 2 1 1 0 0 1 0 38 39 40 41 42 43 44 45 46 17/05/2021 24/05/2021 7890 47 48 49 22/03/2021 29/03/2021 5/04/2021 50 12/04/2021 19/04/2021 26/04/2021 3/05/2021 10/05/2021 31/05/2021 7/06/2021 14/06/2021 51 21/06/2021 52 28/06/2021 5 3 4 2 1 1 2 3 2 3 5 6 5 4 5 2 2 1 1 2 2 3 4 4 1 5 6 3 4 5 0 2 1 3 5 5 2 1 2 1 3 6 4 3 4

Answer rating: 100% (QA)

The Budgeted Contribution tab should now look like this Jan Feb Mar Apr May Jun Sales 2000 2400 2200 ... View the full answer