Question

La Villa is a ski resort in the Italian Alps. Many guests visit the town, however, most of them come in the winter season because of the phenomenal skiing. A group of hotels has analyzed their demand for the last three years. Fill in the table below. Using a smoothing parameter of 0.20 and an initial forecast of 17000.00, forecast demand for the first four quarters in 2015. Re-seasonalized Forecast (Round Seasonal Index to 3 decimal places and all the other answers to 2 decimal places.) Year Qtr Demand Quarter Season Seasonal Deaseasonalized Smoothed Forecast Average Index Demand Demand 2012 1 21,900 1 22,800.00 1.280 201501 2 14,100 214,767.00 0.830|| 2015Q2 3 14,500 314,667.00 0.820 2015Q3 | 4 | 18,100 418,933.00 1.060 201504 2013 1 21,600|| Overall 17,792.00 2 16,8001 3 16,900 4 18,700 2014 1 24,200 | 2 12,400 3 13,900 4 | 19,900

Answer 1

Important Formula:

Formula of De- seasonalized Demand = Demand of each period /Seasonal Index

Smoothed Demand with alpha = 0.20 forecast = (0.20*previous period De- seasonalized Demand)+(0.80*previous period smoothed demand forecast)

Re-Seasonalized Forecast for Year 2015 as per Excel formula

Here, Seasonal average for each quarter = average of Demand of Year 1, Year 2, Year 3 for the same quarter

Global or Overall average = average of all the seasonal average

Seasonal Index = Seasonal average for each quarter/ Global or Overall average

1	B	C	D	E	F	G	H	I	J	K	L
2	year	Quarter	Demand	Quarter	Seasonal Average	Seasonal Index	De- seasonalized Demand	Smoothed Demand with alpha = 0.20	year	Quarter	Re-Seasonalized Forecast
3	2012	1	21900	1	22566.67	1.27	17225.63	17000.00	2015.00	1.00	22097.15
4		2	14100	2	14433.33	0.81	17340.07	17045.13		2.00	14193.10
5		3	14500	3	15100.00	0.85	17044.70	17104.11		3.00	14898.93
6		4	18100	4	18900.00	1.06	16998.68	17092.23		4.00	18698.67
7	2013	1	21600	Overall	17750.00		16989.66	17073.52
8		2	16800				20660.51	17056.75
9		3	16900				19865.89	17777.50
10		4	18700				17562.17	18195.18
11	2014	1	24200				19034.71	18068.58
12		2	12400				15249.42	18261.80
13		3	13900				16339.40	17659.33
14		4	19900				18689.15	17395.34
15	2015	1

Formula:

1	B	C	D	E	F	G	H	I	J	K	L
2	year	Quarter	Demand	Quarter	Seasonal Average	Seasonal Index	De- seasonalized Demand	Smoothed Demand with alpha = 0.20	year	Quarter	Re-Seasonalized Forecast
3	2012	1	21900	1	=AVERAGE(D3,D7,D11)	=F3/$F$7	=D3/G3	17000	2015	1	=(AVERAGE(I3,I7,I11))*G3
4		2	14100	2	=AVERAGE(D4,D8,D12)	=F4/$F$7	=D4/G4	=(0.2*H3)+(0.8*I3)		2	=(AVERAGE(I4,I8,I12))*G4
5		3	14500	3	=AVERAGE(D5,D9,D13)	=F5/$F$7	=D5/G5	=(0.2*H4)+(0.8*I4)		3	=(AVERAGE(I5,I9,I13))*G5
6		4	18100	4	=AVERAGE(D6,D10,D14)	=F6/$F$7	=D6/G6	=(0.2*H5)+(0.8*I5)		4	=(AVERAGE(I6,I10,I14))*G6
7	2013	1	21600	Overall	=AVERAGE(F3:F6)		=D7/G3	=(0.2*H6)+(0.8*I6)
8		2	16800				=D8/G4	=(0.2*H7)+(0.8*I7)
9		3	16900				=D9/G5	=(0.2*H8)+(0.8*I8)
10		4	18700				=D10/G6	=(0.2*H9)+(0.8*I9)
11	2014	1	24200				=D11/G3	=(0.2*H10)+(0.8*I10)
12		2	12400				=D12/G4	=(0.2*H11)+(0.8*I11)
13		3	13900				=D13/G5	=(0.2*H12)+(0.8*I12)
14		4	19900				=D14/G6	=(0.2*H13)+(0.8*I13)
15	2015	1