simple excel and report

Case Study general plan/guidelines

1. Define the objective of the case study. Discuss the problem and what the questions you

are going to answer.

2. Discuss the information you are given to answer your question and what information

you will actually use.

3. Conduct the analysis, talk about the data you used and what you did with the data, and

why you did what you did.

4. Discuss your conclusions and implications of your results. Discuss limitations of your

results (if there are any) and what can be done to address this better (answer the

question even better)

5. See Example of the case study below (This is an example of Case Study 2 – this is just for

your reference and nothing more). This is just a suggested outline and example. I do

want you to use your creativity and know-how to come up with what you think is right.

Remember, this is supposed to mirror a project that you manager would give you if you

were a statistical analyst. In the end, it is your decision how best to do this.

The Hawaiian Inn

Quarterly Occupancy Rate Analysis

2000-2007

March 31, 2008

This analysis covers the quarterly occupancy rates for The Hawaiian Inn for the eight

year period 2000-2007.

The years studied show a total 8-year occupancy mean of 75.26%, and a standard

deviation of 5.89. The 8-year occupancy mean is the comparison rate used throughout

this analysis.

2

Of the 32 quarters reported, the minimum occupancy rate was 57.2% and the maximum

was 84.77%. Both these rates are outside the range of 2 standard deviations of the 8-

year occupancy mean, 60.52% - 83.77%, and can be counted as unusual values.

However they are the only two occupancy rates out of the total 32 that are outside this

range. When considering 3 standard deviations of the 8-year occupancy mean, which is

a range of 54.70% - 89.58% all occupancy rates fall within this range.

The recession of the early 2000’s had a direct impact on the occupancy rates for the

first 16 quarters analyzed, (Years 2000-2003). Of the first 16 quarters only four were

above the 8-year occupancy mean of 75.26%, with year 2002 having no occupancy rate

above the 8-year occupancy year mean. This correlates directly with the recession

years and the two main populations effected; The European Union felt the effects of this

recession mainly between 2000-2001 and the United States was effected from 2002-

2003. The occupancy rates were at the lowest in 2002 when the United States was still

feeling the effects of the recession and the European Union was starting to go through

recovery.

79.20 80.73 71.67 74.40 80.23 83.83 83.60 77.30

75.23 70.67 67.87 67.08

75.66 78.40 78.20

71.93

78.53 70.27

72.53 77.39

81.52 84.77 82.50

79.00

73.07

57.20 67.10

71.34

73.37

77.24 74.07

72.47

2000 2001 2002 2003 2004 2005 2006 2007

Occupancy Rates by Quarter 2000-2007

Q1 Q2 Q3 Q4

8-Year Occupancy Mean: 75.26%

3

Even with the recession it should be noted that the best occupancy rates are

consistently in the 1st and 3rd quarters.

Following the recession the next 12 quarters (Years 2004-2006) were boon years for

The Hawaiian Inn. Only one of the quarters was below the 8-year occupancy mean. Six

of the remaining 11 quarters were over an 80% occupancy rate. Note that all six >80%

rates were in the 1st and 3rd quarters.

The final year in the analysis is 2007. An area of immediate concern would be the drop

in occupancy rates. Only two rates are above the 8-year occupancy mean, again these

rates fall in the 1st and 3rd quarters. The yearly mean of 2007 is 75.18% which is lower

than the 8-year occupancy mean (75.26%) and the yearly mean for 2000 (76.51%). The

year 2000 signifies the start of the early 2000’s recession.

With the fluctuation of 2007 and no further data to analyze, it is recommended that the

years prior to the year 2000 be analyzed for a pattern of increasing/decreasing

occupancy rates. At least six prior years should be included, 1994-1999.

It is also recommended that occupancy rates should be re-analyzed using monthly rates

for the period 1994-2007, looking for a pattern that 2007 may or may not be the start of

a period of decreasing occupancy rates that mirrors the years 2000-2003.

76.51

69.72 69.79

72.55

77.70

81.06

79.59

75.18

64.00

66.00

68.00

70.00

72.00

74.00

76.00

78.00

80.00

82.00

2000 2001 2002 2003 2004 2005 2006 2007

Occupancy Rate Means by Individual Year

2000-2007

8-Year Occupancy Mean: 75.26%

4

Consistently the data indicates that the 1st and 3rd quarters are the peak seasons for

The Hawaiian Inn. The 2nd and 4th quarters are the off-season periods.

An aggressive marketing campaign that pushes the features, amenities and customer

service of The Hawaiian Inn during the peak season will capitalize on the higher

occupancy rates and generate maximum revenue at peak room prices.

Likewise aggressive marketing with discounts, coupons, and lower prices will serve as

attractive incentives to travelers seeking a bargain during the off-season.

While The Hawaiian Inn is looking for ways to increase occupancy rates

overall, but especially during the off-season, careful forecasting of net

revenue after discounts needs to be completed to ensure that:

Revenue from higher occupancy rates with discounts > Revenue from lower occupancy

rates with no discounts

78.87

73.13

78.31

70.73

66.00

68.00

70.00

72.00

74.00

76.00

78.00

80.00

1

8-Year Quarterly Means-2000-2007

Quarter 1 Quarter 2 Quarter 3 Quarter 4

8-Year Occupancy Mean: 75.26%