Question

The past sales history for Store 7 is provided in the table below. Adjust this data using the seasonality index determined using the initial 2 years.  Report the MAD value for the re-seasonalized forecast.

Month	Year	Period	Store 7
January	1	1	54483
February	1	2	66981
March	1	3	87332
April	1	4	90292
May	1	5	82586
June	1	6	78925
July	1	7	68756
August	1	8	58782
September	1	9	32654
October	1	10	33480
November	1	11	40975
December	1	12	53249
January	2	13	66118
February	2	14	77069
March	2	15	99512
April	2	16	105271
May	2	17	96267
June	2	18	88441
July	2	19	70121
August	2	20	60222
September	2	21	41374
October	2	22	40001
November	2	23	51680
December	2	24	67137

You have now moved through the next year and have the sales data available for this year:

Month	Year	Period	Store 7
January	3	25	68748
February	3	26	72754
March	3	27	81986
April	3	28	118321
May	3	29	97910
June	3	30	89358
July	3	31	71854
August	3	32	69065
September	3	33	54770
October	3	34	48398
November	3	35	62645
December	3	36	61248

Use only Year 1 and Year 2 to create the forecast, then compare that forecast to the sales for Year 3.

Answer 1

Month	Year	Period	Actual	Seasonal averages	Seasonal indices	Deseasonalized data	Trend	Re-seasonalized forecast	|Error|
Jan	1	1	54483	60300.5	0.898	60675.8	59894	53781	702.2
Feb	1	2	66981	72025.0	1.073	62451.6	60525	64915	2066.2
Mar	1	3	87332	93422.0	1.391	62776.8	61156	85078	2254.2
Apr	1	4	90292	97781.5	1.456	62010.9	61788	89967	324.7
May	1	5	82586	89426.5	1.332	62017.7	62419	83121	534.7
Jun	1	6	78925	83683.0	1.246	63336.3	63051	78569	356.0
Jul	1	7	68756	69438.5	1.034	66494.4	63682	65848	2908.2
Aug	1	8	58782	59502.0	0.886	66341.9	64313	56985	1797.4
Sep	1	9	32654	37014.0	0.551	59244.2	64945	35796	3142.0
Oct	1	10	33480	36740.5	0.547	61194.9	65576	35877	2396.9
Nov	1	11	40975	46327.5	0.690	59395.7	66207	45674	4699.2
Dec	1	12	53249	60193.0	0.896	59407.4	66839	59910	6661.0
Jan	2	13	66118		0.898	73633.2	67470	60584	5534.0
Feb	2	14	77069	67154.5	1.073	71857.4	68102	73041	4028.3
Mar	2	15	99512		1.391	71532.2	68733	95618	3894.2
Apr	2	16	105271		1.456	72298.1	69364	100999	4271.9
May	2	17	96267		1.332	72291.3	69996	93210	3057.0
Jun	2	18	88441		1.246	70972.7	70627	88010	430.8
Jul	2	19	70121		1.034	67814.6	71258	73682	3561.0
Aug	2	20	60222		0.886	67967.1	71890	63698	3475.7
Sep	2	21	41374		0.551	75064.8	72521	39972	1402.0
Oct	2	22	40001		0.547	73114.1	73153	40022	21.0
Nov	2	23	51680		0.690	74913.3	73784	50901	779.1
Dec	2	24	67137		0.896	74901.6	74415	66701	435.9
								MAD	2447.2

C A D B G H Seasonal Re-seasonalized Deseasonalized Month Year Period Actual Seasonal averages Error Trend forecast 2 indices data =TREND($H$3:$H$26, $D$3:$D$26, D3) =TREND($H$3: $H$26,$D$3:$D$26, D4) =TREND(SH$3:$H$26, $D$3:$D$26, D5) TREND(SH$3:$H$26,$D$3:$D$26, D6) =TREND($H$3:$H$26,$D$3:$D$26, D7) =TREND($H$3: $H$26,$D$3:$D$26, D8) =TREND($H$3: $H$26,$D$3:$D$26,D9) -TREND(SH$ 3:$H$26, $D$3:$D$26, D10) =TREND($H$3:$H$26,$D$3:$D$26,D11) =TREND($H$3:$H$26,$D$3:$D$26, D12) =TREND($H$3:$H$26, $D$3:$D$26, D13) TREND(SH$3:$H$26,$D$3:$D$26, D14) TREND($H$3:$H$26,$D$3:$D$26, D15) TREND($H$3: $H$26,$D$3:$D$26,D16) =TREND($H$3:$H$26,$D$3:$D$26,D17) =TREND($H$3:$H$26,$D$3:$D$26,D18) =TREND($H$3:$H$26,$D$3:$D$26,D19) =TREND($H$3:$H$26,$D$3:$D$26, D20) =TREND($H$3:$H$26, $D$3:$D$26, D21) -TREND(SH$3:$H$26, $D$3:$D$26, D22) =TREND($H$3:$H$26,$D$3:$D$26, D23) =TREND($H$3: $H$26,$D$3:$D$26,D24) TREND(SH$3:$H$26,$D$3:$D$26, D25) =TREND($H$3: $H$26,$D$3:$D$26,D26) 54483 (E3+E15)/2 66981 (E4+E16)/2 87332 (E5+E17)/2 90292 (E6+E18)/2 82586 (E7+E19)/2 78925 E8+E20)/2 68756 (E9+E21)/2 58782 (E10+E22)/2 32654 (E11+E23)/2 33480 (E12+E24)/2 40975 (E13+E25)/2 53249 (E14+E26)/2 F3/$F$16 F4/$F$16 F5/$F$16 -Е3/G3 E4/G4 E5/G5 =13"G3 ABS(E3-J3) =ABS(E4-J4) ABS(E5-J5) 3 1 1 Jan Feb 1 =l4*G4 4 2 1 15*G5 5 Mar =F6/$F$16 - E6/G6 1 =16*G6 =ABS(E6-J6) 6 Apr May F7/$F$16 -E7/G7 1 17*G7 =ABS(E7-17) -ABS(E8-J8) 7 5 =F8/$F$16 -E8/G8 = 18 * G8 8 Jun 1 = 19*G9 =ABS(E9-J9) 1 F9/$F$16 E9/G9 9 Jul 7 -F10/$F$16 E10/G10 EF11/$F$16 E11/G 11 F12/$F$16 -E12 /G12 -ABS(E10-J10) 10 Aug 110*G10 1 - ABS(E11-J11) -ABS(E12-J12) 11 Sep 1 111*G11 9 = 1 12 *G12 12 Oct 1 10 1 F13/$F$16=E13/G13 =13 *G13 -ABS(E13-J13) 13 Nov 11 F14/$F$16 =E14/G14 - E15/G15 -ABS(E14-J14) =ABS(E15-J15) 14*G14 14 Dec 1 12 15*G15 15 Jan 2 13 66118 G3 77069AVERAGE(F3:F14) G4 -E16/G16 -ABS(E16-J16) Feb =16*G16 16 2 14 -E17/G17 =ABS(E17-J17) 17 G5 117*G17 Mar 2 15 99512 -E18/G18 G6 118*G18 ABS(E18-J18) 18 Apr 16 105271 = 1 19 *G19 =120 *G20 121 *G21 = 122*G22 -E19/G19 -ABS(E19-J19) -ABS(E20-J20) -ABS(E21-J21) -ABS(E22-J22) =ABS(E23-J23) May 96267 G7 19 2 17 -E20/G20 20 18 88441 G8 Jun 2 E21/G21 -E22/G22 E23/G23 E24/G24 E25/G25 Jul 21 2 19 70121 G9 22 Aug 2 20 60222 G10 =123"G23 23 Sep 2 21 41374 G11 =124*G24 ABS(E24-J24) 24 Oct 2 22 40001 EG12 25 125*G25 =ABS(E25-J25) =ABS(E26-J26) =AVERAGE( K3 :K26) Nov 23 51680 G13 -E26/G26 126*G26 26 Dec 2 24 67137 G14 MAD 27

Comparison with year-3 data

Month	Year	Period	Actual	Seasonal averages	Seasonal indices	Deseasonalized data	Trend	Re-seasonalized forecast
Jan	3	25	68748		0.898		75047	67387
Feb	3	26	72754		1.073		75678	81167
Mar	3	27	81986		1.391		76309	106158
Apr	3	28	118321		1.456		76941	112031
May	3	29	97910		1.332		77572	103299
Jun	3	30	89358		1.246		78204	97451
Jul	3	31	71854		1.034		78835	81516
Aug	3	32	69065		0.886		79466	70411
Sep	3	33	54770		0.551		80098	44148
Oct	3	34	48398		0.547		80729	44167
Nov	3	35	62645		0.690		81360	56128
Dec	3	36	61248		0.896		81992	73492

140000 120000 A 100000 80000 Actual 60000 Re-seasonalized forecast 40000 Third year 20000 0 40 0 5 10 15 20 25 30 35 Period