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|
|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:
On investigation Paul is given more detail relating to the operating expenses for the last year, as follows:
|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|
|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:
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.
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.
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;
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
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
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
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
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