Question

ts) Can-Do Canoe sells lightweight portable canoes. Quarterly demand for its most 2. (70 point ar product family over the past 3 years has been as follows. popul 2015 2 2016 2 2017 Year Demand 25 120 40 60 30 140 608035 150 55 90 4 Quarter a) (10 points) Use an exponential smoothing model with smoothing constant a 0.2 to develop a forecast for these data. How does it it? What is the resulting MSD? (b) (10 points) Use an exponential smoothing with a linear trend model with smoothing c) (10 points) Use the Winters method with smoothing constants α-β γ 0.2 to (d) (20 points) Find smoothing constants that minimizes MSD over the second two years constants α-p-02 todevelop a forecast for these data How does it fit? What is the resulting MSD? develop a forecast for these data. How does it fit? What is the resulting MSD? of data. How does the resulting forecast fit thedata in the third year? (소수점 셋째 자리까지) (e) (20 points) Find smoothing constants that minimizes MSD over the third year of data. How much better does the model fits the data in the third year than that of part (d)? Which model, (d) or (e), do you think is likely to better predict demand in year 4? 수점 셋째 자리까지)

I wonder only both (d) and (e).

If you use excel, please show what you do exactly.

Please help me. Thanks.

Answer 1

(d)

ABC D 5 6 Year 2015 Qtr Time 1 1 2 2 120 -TREND SDS6:SDS17,SCS6SCS17,C7)AVERAGEFBS6:SBS17,2,S6SFS1)D/TS -1o I6 33 40 TREND SDS :SD 17 SCS6: CS17.CS DSES AVER ELF SBS6 SB 17.3 SFS SF功|-BS1D8 SI 6 1 SESI)*(H7 17 4 4 60 TRENDSDS : D 17 SCS6: CS17.C9D9E9 AVERAGE F SBS6: BS17.4. FS SF I #81DSJMH1 SBSI)" 318) 5 Data 25 Detrended -TREND(SDS&SDS17,SCS&SCS17.C6) =D6E6 -AVERAGEIF(SBS&SBS17.1,SFS6:SFS17) -INTERCEPT(D6D17,C6:C17) 3 10 2016 1 30 TREND(SDS6:SDS17,SCS6:SC$17,C10) D10/E10 2 6 140 TREND SDS6:SDS17,SCS6:SCS17,C11) DIIE11 3 7 60 TREND SDS6:SDS17,SCS6 SC$17,C12)D12 E12 4 S 80TREND SDS6:SDS17,SCS6 SC$17,C13)D13/E13 14 2017 1 9 35 TREND(SDS6:SDS17,SCS6:SC$17,C14) D14/E14 2 10 150 -TREND(SDS6:SDS17,SCS6:SCS17,C15) =D15/E15 3 11 55 TREND SDS6:SDS17,SCS6 SC$17,C16D16/E16 4 12 90TREND SDS6:SDS17,SCS6 SC$17,C17D17E17 -AVERAGE(G6 SBS1 D106(1-SBS1)*(H9+I9) -SBS 1 *(DI 1汀7 Hl-SBSI)"(H10-110) SBS1 D12/J8)(1-SBS1) *(H11+111) SBS1 D139)(1-SBS1) *(H12+112) SBS1 D14/J10) (1-SBS1)*(H13+113) -SBS 1 *(D15汀11)+(1-SESI)"(H14-114) SBS1 D16/T12) (1-SBS1)*(H15+115) -SBS 1"(D17汀13H1-SESI)"(H16-116) 13 17 18 2018 1 21

Trend 2.2 2-2 2-2 2-2 Seasonality -ru-ru-ru-ru-ru-ru-ru-ru Forecast 0123 -G6 SGS10 0-112345 6-1 17 17 17 17 =(K10-D10) 0123 11 SBS2*(H11-H10) (1-SBS2)*I10 SBS3*D11 H11) (1-SBS3)*7H10 I10) *J7 13 SB2*(H13-H12)1-SBS2) B3*D13/H13 (1-SBS3) *H12+112) *9 15|-SBS2°(H15-H14)+(1-SBS2)*114-SBS3 *(DI5/H15)-(1-SBS3)朾11 H14-114) 11 0123456 7. (K12-D12) ' (K13-D13)*2 (K14-D14)^2 (K15-D15)'2 16 17 18 リ10 ost FAVERAGEL10L17) 00 y XX XX XX XX XX XX XX MSD 鉚遛遛遛遛遛遛遛遛遛遛遛

Set Objective SL$22 OMin Value Of Max By Changing Variable Cells: Subject to the Constraints: $B$1:$B$3<1 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

After solving, we get the following result:

α =	0.007
γ =	0.000
δ =	0.000
Year	Qtr	Time (t)	Data (Yt)	Trend	Detrended	Seasonals	Level (Lt)	Trend (Tt)	Seasonality (St)	Forecast (Ŷt)	Error2
2015	1	1	25	57.5	0.435	0.43	54.55	2.95	0.43
	2	2	120	60.5	1.985	1.90	59.01	2.95	1.90
	3	3	40	63.4	0.631	0.69	62.17	2.95	0.69
	4	4	60	66.4	0.904	0.98	65.63	2.95	0.98
2016	1	5	30	69.3	0.433	1.00	68.58	2.95	0.43	29.71	0.08
	2	6	140	72.3	1.937		71.55	2.95	1.90	136.15	14.82
	3	7	60	75.2	0.798		74.60	2.95	0.69	51.20	77.43
	4	8	80	78.2	1.023		77.58	2.95	0.98	75.72	18.32
2017	1	9	35	81.1	0.431		80.54	2.95	0.43	34.89	0.01
	2	10	150	84.1	1.784		83.46	2.95	1.90	158.90	79.13
	3	11	55	87.0	0.632		86.37	2.95	0.69	59.38	19.20
	4	12	90	90.0	1.000		89.34	2.95	0.98	87.22	7.75
2018	1									39.99
	2									181.28
	3									67.49
	4									98.77
											27.09
											MSD

180 160 140 110 100 Data (yt) Forecast (Yt) 80 40 20 10 12 14

(e)

ABC D 5 6 Year 2015 Qtr Time 1 1 2 2 120 -TREND SDS6:SDS17,SCS6SCS17,C7)AVERAGEFBS6:SBS17,2,S6SFS1)D/TS -1o I6 33 40 TREND SDS :SD 17 SCS6: CS17.CS DSES AVER ELF SBS6 SB 17.3 SFS SF功|-BS1D8 SI 6 1 SESI)*(H7 17 4 4 60 TRENDSDS : D 17 SCS6: CS17.C9D9E9 AVERAGE F SBS6: BS17.4. FS SF I #81DSJMH1 SBSI)" 318) 5 Data 25 Detrended -TREND(SDS&SDS17,SCS&SCS17.C6) =D6E6 -AVERAGEIF(SBS&SBS17.1,SFS6:SFS17) -INTERCEPT(D6D17,C6:C17) 3 10 2016 1 30 TREND(SDS6:SDS17,SCS6:SC$17,C10) D10/E10 2 6 140 TREND SDS6:SDS17,SCS6:SCS17,C11) DIIE11 3 7 60 TREND SDS6:SDS17,SCS6 SC$17,C12)D12 E12 4 S 80TREND SDS6:SDS17,SCS6 SC$17,C13)D13/E13 14 2017 1 9 35 TREND(SDS6:SDS17,SCS6:SC$17,C14) D14/E14 2 10 150 -TREND(SDS6:SDS17,SCS6:SCS17,C15) =D15/E15 3 11 55 TREND SDS6:SDS17,SCS6 SC$17,C16D16/E16 4 12 90TREND SDS6:SDS17,SCS6 SC$17,C17D17E17 -AVERAGE(G6 SBS1 D106(1-SBS1)*(H9+I9) -SBS 1 *(DI 1汀7 Hl-SBSI)"(H10-110) SBS1 D12/J8)(1-SBS1) *(H11+111) SBS1 D139)(1-SBS1) *(H12+112) SBS1 D14/J10) (1-SBS1)*(H13+113) -SBS 1 *(D15汀11)+(1-SESI)"(H14-114) SBS1 D16/T12) (1-SBS1)*(H15+115) -SBS 1"(D17汀13H1-SESI)"(H16-116) 13 17 18 2018 1 21

Seasonality 5-6 7 :-G6SGS10 1234 13B2*H13-H12)(1-SBS2)*I12 BS3*(D13/H13 (1-SBS3)*9 H12+112)* 9 14 SBS2*(H14-H13) (1-SBS2)*I13 SBS3*D14/H14)(1-SBS3)*1 H13+113)*J10(K14-D14)2 -eaea 312-(K16-D16 0123456 7. =SBS2*(H17-H16)-(l-SBS2)*116 siiii (D (D (D (D (D (D (D (D =SBS3 *(D17/H17)-(1-SBS3)"J1 -di ...-di-20 // SB SB SB SB SB SB SB 17 01234 H16-116)朾13-17-D17)"2 (SHS17+SIS17) J14 (SHS 17+2* SIS 17) 6 15 鉚遛遛遛遛遛遛遛遛遛遛遛 AVERAGEL14:L17) MSD 123456789 10 1 2 3 4 5 6 7 8 9 20 21 22

Set Objective SL$22 OMin Value Of Max By Changing Variable Cells: Subject to the Constraints: $B$1:$B$3<1 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method GRG Nonlinear Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

α =	0.000
γ =	0.395
δ =	0.000
Year	Qtr	Time (t)	Data (Yt)	Trend	Detrended	Seasonals	Level (Lt)	Trend (Tt)	Seasonality (St)	Forecast (Ŷt)	Error2
2015	1	1	25	57.5	0.435	0.43	54.55	2.95	0.43
	2	2	120	60.5	1.985	1.90	57.50	2.9545	1.90
	3	3	40	63.4	0.631	0.69	60.45	2.9545	0.69
	4	4	60	66.4	0.904	0.98	63.41	2.9545	0.98
2016	1	5	30	69.3	0.433	1.00	66.36	2.9545	0.43	28.75
	2	6	140	72.3	1.937		69.32	2.9545	1.90	131.92
	3	7	60	75.2	0.798		72.27	2.9545	0.69	49.66
	4	8	80	78.2	1.023		75.23	2.9545	0.98	73.45
2017	1	9	35	81.1	0.431		78.18	2.9545	0.43	33.87	1.27
	2	10	150	84.1	1.784		81.14	2.9545	1.90	154.41	19.49
	3	11	55	87.0	0.632		84.09	2.9545	0.69	57.79	7.76
	4	12	90	90.0	1.000		87.05	2.9545	0.98	84.99	25.08
2018	1									38.99
	2									176.91
	3									65.91
	4									96.53
											13.40
											MSD

160 120 Data (Yt) -Forecast (Yt) TiHi 80 60 20 0 14 10 1 4 0