Question

1. Jace collected the following data on the quarterly room occupancy (in hundreds) for a resort hotel in Iowa.

Year	quarter	Occupancy
2013	1	16
	2	21
	3	9
	4	18
2014	1	15
	2	20
	3	10
	4	18
2015	1	17
	2	24
	3	13
	4	22
2016	1	17
	2	25
	3	11
	4	21
2017	1	18
	2	26
	3	14
	4	25

1. Plot the data and describe any trend and seasonal effects you observe in the data.

2. Compute and interpret the seasonal indices.

3. Jace’s manager, Joseph, wants Jace to forecast the occupancy for four quarters of 2018. Can you help him (Forecast he room occupancy for four quarters of 2018)?

Answer 1

A.

In this graph, we see that there is an increasing trend in this data..and there is seasonality in the data.. The Occupancy rate drops every 3rd quarter and then gradually increases and peaks in 2nd quarter.

B.

		Occupancy
	Year	2013	2014	2015	2016	2017	Average	Seasonal Index
Quarter	Q1-13	16	15	17	17	18	16.6	0.922
	Q2-13	21	20	24	25	26	23.2	1.289
	Q3-13	9	10	13	11	14	11.4	0.633
	Q4-13	18	18	22	21	25	20.8	1.156
	Average	16	15.75	19	18.5	20.75	18

Seasonal Index is calculated by dividing each Quarter Average by the Grand average of the entire data( which is 18 in this case)

So, For Q1, Seasonal Index, SI = 16.6/18 = 0.922

For Q2, Seasonal Index, SI = 23.2/18 = 1.289

For Q3, Seasonal Index, SI = 11.4/18 = 0.633

For Q4, Seasonal Index, SI = 20.8/18 = 1.156

This shows us that Q3 has the lowest Occupancy. It becomes better gradually and maxes in Q2

C.

Average Occupancy in 2013 = 16

Average Occupancy in 2014 = 15.8

Average Occupancy in 2015 = 19

Average Occupancy in 2016 = 18.5

Average Occupancy in 2017 = 20.8

Since it is an increasing trend, we can take CAGR to forecast occupancy in 2018.

CAGR = (Occupancy in 2017 /  Occupancy in 2013)^(1/4) - 1 = (20.8/16)^(1/4) - 1 = 5.8%

Now, Average Occupancy in 2018 will be 5.8% more than 2017, which will be equal to 20.8*(1+5.8%) = 22.0066

Now Forecast for each quarter is given by,

For Q1, Occupancy = Average Occupancy * Seasonal Index = 22.0066*0.922 = 20.295

For Q2, Occupancy = 22.0066*1.289 = 28.364

For Q3, Occupancy = 22.0066*0.633= 13.9375

For Q4, Occupancy = 22.0066*1.156 = 25.429