Excel

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 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
  1. 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

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

We value our customers and so we ensure that what we do is 100% original..
At Custom Writing, we believe in exemplary services that are fully geared toward customer satisfaction. That is why we don’t shy away from giving you the following guarantees;

Money-back guarantee

Trusting us with your work is the best decision you have made, our pleasure lies in seeing you satisfied at 100%. If in the rear chance it happens that you are not satisfied, then know that we will equally not be satisfied. But worry not, our 30 days- Money back guarantee is all you need and that is what we promise you..

Read more

Zero-plagiarism guarantee

We utilize profoundly equipped and gifted writers who produce unique papers liberated from any form of plagiarism. To guarantee this, we run all papers finished by our scholars through a Plagiarism checker to ensure uniqueness and originality. In any case, on the off chance that you have vulnerabilities about the originality or falsification of any paper we have finished and conveyed to you, please get in touch with us straight away. We will quickly investigate, and if the paper is seen as counterfeited, we will take suitable actions including but not limited to, revising the paper for free and in extreme cases we will activate the money back guarantee.

Read more

Free-revision policy

We have an obligation deliver great and specially composed assignments. Our revision strategy endeavors to ensure total client satisfaction, comfort, and a genuine feelings of serenity. We make minor updates and corrections to the underlying request as part of our continuous assistance. However, revisions should just incorporate changes and alterations that were not effectively met, in the underlying request and that are inside the rules as per the current request structure..

Read more

Privacy policy

Our client's Data is an Integral part of our business but clearly, we are not in the business of offering our clients' very own data to others. We realize that you care how your online data is utilized and shared, we equally value your trust that we will do so cautiously and sensibly. We Promise to ensure the security of your own data during transmission by utilizing encryption conventions and programming. Likewise assist us with securing your information by not sharing your passwords and usernames.

Read more

Fair-cooperation guarantee

In submitting a request with us, you consent to the services we give. We will strive to take the necessary steps to convey a far reaching paper according to your prerequisites. Equally we depend on your cooperation to guarantee that we convey on this order.

Read more

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency