BDM Group Assignment – Trimester 2, 2020
Adopted from Accounting: An introduction by Atrill, McLaney and Harvey
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 | $80 |
| Three-bed room (Double+Single) | $100 |
| Family room (4-5 people) | $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:
- The business has been in operation for 10 years.
- The room occupancy record for July 2019 – June 2020 is provided (see attachment 2).
- Interest and financing costs were $2,000.
- 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 | 1,600 | Electricity | 15,000 | |
| Carpet cleaning1 | 800 | Water | 3,000 | |
| Council registration fees | 900 | Donations to local charities | 1,200 | |
| New curtains2 | 1,500 | Miscellaneous expenses | 7,000 | |
| General cleaning chemicals | 700 | Software, internet and computers (set-up fees) | 3,500 | |
| Insurance | 4,000 | Advertising and memberships | 8,000 | |
| Repairs and maintenance | 5,000 | Telephone | 2,000 | |
| Pool chemicals and cleaning | 800 | Car expenses | 3,000 | |
| Annual newspaper subscription | 1,500 | Rent | 40,000 | |
| Accountancy fee | 1,500 | Rates | 4,000 | |
| Wages for casual staff | 20,000 | Laundry cleaning – beddings | 5,000 | |
| Motel Room cleaning | 20,000 |
1 Carpet cleaning is only for the common area.
2 The owner changes the curtains every five years.
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.
- 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 in 1/9/2020 when he believes that the Australian government will lift the travel bans and open the boarders of states nationally.
Use attachment 2 – Room Occupancy 2019-2020 to complete the following instructions.
You need to add the ‘room occupancy record for 2019-2020’ to a workbook in Excel and label the tab as ‘Data-Room occupancy-2019-2020’. Copy and paste this data to another tab – called ‘Data-Room occupancy – budgeted’. Use this to calculate the expected occupancy for 2020-2021 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 2021. 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 2020. (Paul wants you to change the data. The first month will be September. Use the figures for July and August 2019 for July and August 2021. 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:
- Complete the instructions relating to the Budgeted Contribution tab. 15 marks
- Complete the cash flow budget. 20 marks
- Identify limitations and weaknesses of the cash budget. 10 marks
- 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
b) Briefly explain why your model is better than the one original one. 10 marks
- Open another tab and prepare a budgeted income statement for the 12 months from September 2020. Ensure it is dynamically linked to the revised cash budget. 15 marks
Feel free to use a format similar to Case Exhibit 2.15 in the textbook. Do not include narrative, such as x units sold at $y per unit.
- 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?
20 marks
Submission details.
Please submit a single Excel file containing your responses for questions 1, 2, 4a, and 5.
Please submit your responses to questions 3, 4b, and 6 using the template provided. Please note, you must use this template.
Do not include the letter u in your id number.
Do not change the font or formatting of the template.
Only one member of each team will submit the files.
Marking guide
Marks will be awarded for your ability to develop spreadsheets which comply with your manager’s requests. Marks will be deducted for inappropriate use of hard coding in the various outputs (such as the budgeted contribution and income statement). Some formulae are a bit tricky. You are welcome to break down your formulae in separate cells, as long as the combined effect is correct.
In relation to the text-based responses, marks will be awarded for the relevance and completeness of the responses. A couple of responses require short responses. Good responses to question 6 will demonstrate students have been able to synthesize and apply a range of concepts covered in this unit, and some concepts not covered in this unit.
Penalties will apply for not following the submission requirements.
Attachment 1 – Room occupancy – February to April 2019
| Average Days booked per room | Average Days booked per room | Average Days booked per room | ||
| Weeks | Week ending | Double/Twin Room | Three-bed Room | Family Room |
| 5 | 3/02/2019 | 4 | 2 | 1 |
| 6 | 10/02/2019 | 2 | 2 | 0 |
| 7 | 17/02/2019 | 2 | 2 | 0 |
| 8 | 24/02/2019 | 2 | 2 | 0 |
| 9 | 3/03/2019 | 3 | 2 | 1 |
| 10 | 10/03/2019 | 2 | 3 | 0 |
| 11 | 17/03/2019 | 2 | 3 | 0 |
| 12 | 24/03/2019 | 3 | 2 | 0 |
| 13 | 31/03/2019 | 6 | 5 | 5 |
| 14 | 7/04/2019 | 5 | 5 | 5 |
| 15 | 14/04/2019 | 3 | 3 | 3 |
| 16 | 21/04/2019 | 3 | 2 | 0 |
| 17 | 28/04/2019 | 2 | 3 | 0 |
Attachment 2 – Room occupancy – 2019-2020
| Average Days booked per room | Average Days booked per room | Average Days booked per room | ||
| Weeks | Week ending | Double/Twin Room | Three-bed Room | Family Room |
| 1 | 7/07/2019 | 5 | 6 | 5 |
| 2 | 14/07/2019 | 2 | 2 | 2 |
| 3 | 21/07/2019 | 3 | 2 | 1 |
| 4 | 28/07/2019 | 2 | 0 | 0 |
| 5 | 4/08/2019 | 2 | 0 | 0 |
| 6 | 11/08/2019 | 1 | 1 | 0 |
| 7 | 18/08/2019 | 0 | 0 | 0 |
| 8 | 25/08/2019 | 2 | 2 | 0 |
| 9 | 1/09/2019 | 3 | 2 | 1 |
| 10 | 8/09/2019 | 3 | 2 | 1 |
| 11 | 15/09/2019 | 4 | 3 | 3 |
| 12 | 22/09/2019 | 3 | 5 | 4 |
| 13 | 29/09/2019 | 4 | 6 | 5 |
| 14 | 6/10/2019 | 2 | 3 | 1 |
| 15 | 13/10/2019 | 2 | 3 | 0 |
| 16 | 20/10/2019 | 2 | 2 | 0 |
| 17 | 27/10/2019 | 3 | 3 | 1 |
| 18 | 3/11/2019 | 3 | 2 | 0 |
| 19 | 10/11/2019 | 2 | 2 | 0 |
| 20 | 17/11/2019 | 3 | 2 | 0 |
| 21 | 24/11/2019 | 3 | 2 | 1 |
| 22 | 1/12/2019 | 4 | 5 | 0 |
| 23 | 8/12/2019 | 5 | 5 | 0 |
| 24 | 15/12/2019 | 5 | 6 | 0 |
| 25 | 22/12/2019 | 7 | 7 | 7 |
| 26 | 29/12/2019 | 7 | 7 | 7 |
| 27 | 5/01/2020 | 7 | 6 | 5 |
| 28 | 12/01/2020 | 7 | 6 | 5 |
| 29 | 19/01/2020 | 1 | 0 | 0 |
| 30 | 26/01/2020 | 1 | 0 | 0 |
| 31 | 2/02/2020 | 1 | 0 | 0 |
| 32 | 9/02/2020 | 1 | 0 | 0 |
| 33 | 16/02/2020 | 1 | 0 | 0 |
| 34 | 23/02/2020 | 2 | 1 | 0 |
| 35 | 1/03/2020 | 0 | 0 | 0 |
| 36 | 8/03/2020 | 0 | 0 | 0 |
| 37 | 15/03/2020 | 0 | 0 | 0 |
| 38 | 22/03/2020 | 0 | 0 | 0 |
| 39 | 29/03/2020 | 0 | 0 | 0 |
| 40 | 5/04/2020 | 0 | 0 | 0 |
| 41 | 12/04/2020 | 0 | 0 | 0 |
| 42 | 19/04/2020 | 0 | 0 | 0 |
| 43 | 26/04/2020 | 1 | 0 | 0 |
| 44 | 3/05/2020 | 2 | 0 | 0 |
| 45 | 10/05/2020 | 3 | 0 | 0 |
| 46 | 17/05/2020 | 2 | 0 | 0 |
| 47 | 24/05/2020 | 3 | 0 | 0 |
| 48 | 31/05/2020 | 5 | 5 | 3 |
| 49 | 7/06/2020 | 6 | 6 | 6 |
| 50 | 14/06/2020 | 5 | 3 | 4 |
| 51 | 21/06/2020 | 4 | 4 | 3 |
| 52 | 28/06/2020 | 5 | 5 | 4 |
Type of service-Academic paper writing
Type of assignment-Coursework
Subject-Accounting
Pages / words-4 / 1100
Academic level-Sophomore (College 2nd year)
Paper format-MLA
Line spacing-Double
Language style-AU English
