"Blue Waters Cottage" Case Study | | Administrator | |

|

Contents
I Introduction 5
II Analysis of the popularity and profitability of cottages 5
III Results of your research 6
IV Conclusion 7
V Recommendations 7
VI IPO Chart 8
VII One-Page Report 9

I Introduction
“Blue Waters Cottages” is a small complex providing three cottages, Gardenview, Seaspray and Seaview, with different nightly rates. The minimum rental period is three days. If the booking is more than five days, the customers can enjoy a 20% discount rate for the additional days. They can also receive a $30.00 discount per stay if they bring their own linen. The owner, Shelly, has a new business partner who plans to expand their property portfolio.
The purpose of the report is to suggest the ways to increase profitability of current properties as well as provide insight into the type of property they should consider purchasing through analysis.

II Analysis of the popularity and profitability of cottages
According to the output of our spreadsheet, we can analyse the popularity and profitability of each cottage from 2011/1/1 to 2011/12/27.
To find out the popularity, we calculate the times of bookings of each cottage. Seaspray is the most popular one, with 18 times of bookings out of 49 in total during the period. Seaview comes next with 17, and Gardenview 14. From the prospect of total booking days, the ranking is the same as below. Seaspray 145 days, Seaview 103 days and Gardenview 72 days, which makes the result much clearer.
There are several factors that may infect the popularity. First, whether the nightly rate is comparable to other similar properties in the area. Since “Blue Waters Cottages” is located 2.5 hours from Melbourne between Lorne and Apollo Bay, we have searched the internet for some accommodation information of the similar cottages near Melbourne. Gardenvale Cottages features 2 bedrooms, en-suite and separate bathroom which is similar to Seaspray in this case. The nightly rate of Gardenvale Cottages is from $190 to $240, so $185, the rate of Seaspray, seems quite competitive. This may partly explain the high popularity of Seaspray. Emerald Inn, which is similar to Seaview of Blue Waters Cottages, offers a rate from $210 to $250 per night, which is about the same as $220 in our case. The rate of Gardenview, $145, is a little bit higher compared to that of Blue Willow Bed & Breakfast, from $110 to $120, which makes Gardenview less popular among the cottages. Second, the structure of the customers. The location of Blue Waters Cottages is a tourist resort and most tourists travel in group or in family, from which we can infer that most customers will choose a cottage with two beds, i.e. Seaview and Seaspray, instead of one i.e. Gardenview.
The profitability is based on the total revenue of each cottage. Seaspray is the most profitable cottage with the total revenue of $24,568, occupying 43.38% of the total revenue of all three cottages, nearly a half. Although the average nightly revenue of Seaspray is $168.27, only ranking the second because of its competitive nightly rate, Seaspray still makes the most profit due to its high popularity. Seaview, with the highest nightly rate, $205.19, ranks next to Seaspray in total revenue, 43.38% of the total. Gardenview, 18.94%, is the least.
To accurately assess profitability of each cottage, market saturation need to be considered as well. This will be demonstrated later.

III Results of your research
According to our research, first of all, Shelly should increase the cottage’s influence by expanding advertisement; she can use not only e-mail but also other information technology to increase the fame of her cottages. What’s more, other media is also powerful to build up brand. Travel magazine should be taken into account.
Based on our analysis, one of the cottages called Seaview has made the highest average nightly revenue, but not very popular. Shelly should adjust his marketing strategy. For example, give more discounts for Seaview to attract customers, or decrease the price of this cottage in slack season. These can attract more customers to book this kind of cottage, and the target customers are high-income people such as businessmen or politicians so that she can increase the profitability of these properties.
In another aspect, about Shelley and her business partner’s expansion plans, our advices are as followed. According to the research, customers prefer to book rooms which have two beds. Shelly should increase the number of Seaview or Seaspray, but we still can not make accurate decision yet because that we don’t know which kind of cottage is in saturation mode. We need more details about booking, such as the accurate number of days that the cottages are full, how many customers failed to book the room that they want, and then based on these details we can get the answer.

IV Conclusion
Through the analysis of Blue Waters Cottages' booking details in a period of time, we have clearly discovered the popularity and profitability of each cottage and possible reasons.
Based on our analysis, if Shelly wants to run her business effectively, she should adjust her advertising strategy to enhance the influence of her cottages. If she wants to make an expansion plan, she needs more accurate and valuable information.

V Recommendations
To improve the advertising strategy, first, Shelly should set up her own website. If she doesn't have enough knowledge to finish it, she can turn to some professional for help. On the website customers can get information and book rooms online. Second, since Shelly has advertised on local newspaper, she can also invite a journalist to write some article about her cottage and beautiful view of seaside. What's more, she can advertise on some travel magazines, which can be more effectively.

VI IPO Chart INPUT | PROCESSING | OUTPUT | 1) Cottage type; Nightly rate 2) Number of night 3) Is number of nights >5,if yes, they receive a 20% discount for additional days | 1) Nightly rate *number of nights 2) If the number of nights >5, 5*nightly rate+ (the number of nights -5)*nightly rate*(1-20%) 3) Do they have linen, if yes, give 30 dollars discount; if no, no discount | 1) Revenue for each individual rental period in 2011and total revenue for 2011 2) Revenue for 2011 by cottage |

VII One-Page Report
We use the function of DAYS360 to calculate the days that customers have stayed at the cottage. As long as we know the start date and the end date, we can automatically calculate the number of days with this function.
There is a function called WEEKDAY. If we know the exact leaving date, then we use the function and calculate the day of the week. What the number ‘1’stand for is that Sunday and ‘2’ for Monday etc.
To calculate the nights during 2011 each cottage was rented, we should use the function of SUMIF instead of SUM because we should calculate it selectively. How to use it? First, we must know the range, condition and summation range that is the list of cottage, the kind of cottage and the prices. Then we put them sequentially into the relevant blank and press Enter, we can get what we want immediately.
Based on the different combinations of fields and the comprehensive analysis of the data, we can notice and conclude the data from different ways so that they control the changing rule of the data. Shelley can find the concentration of bookings through the table. Then she can allocate a particular cleaner to a job or prepare more or less things like fruits depending on it.
Goal seek is used to solve the problem that if we assume the result of a formula, what the variable reference cells should value. It is convenient for Shelley to calculate how much she should earn to get the goal she wants to achieve or the average turnover.
If she wants to change the prices of each cottage, she can also use it to solve the problem that if she can get the total revenue unchanged, how much she should fix with losing a part of customers.
According to the pivot table, we can get the total revenue of each kind of cottage on every weekday. The day when guests have left is shown in the column. The cottage name is displayed in the row. The values areas show the total revenue. The pivot chart makes it easy and intuitive to see on which day of the week the most profit is made.