Topic: Excel accounting coursework

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:

  1. The business has been in operation for 10 years.
  2. The room occupancy record for July 2019 – June 2020 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 charges1,600 Electricity15,000
Carpet cleaning1800 Water3,000
Council registration fees900 Donations to local charities1,200
New curtains21,500 Miscellaneous expenses7,000
General cleaning chemicals700 Software, internet and computers (set-up fees)3,500
Insurance4,000 Advertising and memberships8,000
Repairs and maintenance5,000 Telephone2,000
Pool chemicals and cleaning800 Car expenses3,000
Annual newspaper subscription1,500 Rent40,000
Accountancy fee1,500 Rates4,000
Wages for casual staff20,000 Laundry cleaning – beddings5,000
Motel Room cleaning20,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:

  1. 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 roomAverage Days booked per roomAverage Days booked per room
WeeksWeek endingDouble/Twin RoomThree-bed RoomFamily Room
53/02/2019421
610/02/2019220
717/02/2019220
824/02/2019220
93/03/2019321
1010/03/2019230
1117/03/2019230
1224/03/2019320
1331/03/2019655
147/04/2019555
1514/04/2019333
1621/04/2019320
1728/04/2019230

Attachment 2 – Room occupancy – 2019-2020

  Average Days booked per roomAverage Days booked per roomAverage Days booked per room
WeeksWeek endingDouble/Twin RoomThree-bed RoomFamily Room
17/07/2019565
214/07/201922
321/07/2019321
428/07/2019200
54/08/2019200
611/08/2019110
718/08/2019000
825/08/2019220
91/09/2019321
108/09/2019321
1115/09/2019433
1222/09/2019354
1329/09/2019465
146/10/2019231
1513/10/2019230
1620/10/2019220
1727/10/2019331
183/11/2019320
1910/11/2019220
2017/11/2019320
2124/11/2019321
221/12/2019450
238/12/2019550
2415/12/2019560
2522/12/2019777
2629/12/2019777
275/01/2020765
2812/01/2020765
2919/01/2020100
3026/01/2020100
312/02/2020100
329/02/2020100
3316/02/2020100
3423/02/2020210
351/03/2020000
368/03/2020000
3715/03/2020000
3822/03/2020000
3929/03/2020000
405/04/2020000
4112/04/2020000
4219/04/2020000
4326/04/2020100
443/05/2020200
4510/05/2020300
4617/05/2020200
4724/05/2020300
4831/05/2020553
497/06/2020666
5014/06/2020534
5121/06/2020443
5228/06/20204

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

get essaywriters

Related Post