Question

#1 Attached is an Excel spreadsheet containing the exponential smoothing, moving average, and quarterly regression analyses of the data set in problem #23 in Chapter 8. Use the Excel spreadsheet to answer the following questions. #1.1 which of the nine exponential smoothing models in columns J through R would you use to forecast Value for time-13, and why would you use it? (5 points) #1.2 what is the exponential smoothing forecast of Value for time-13 for (Show your work.) (5 points]) 0.37 #1.3 Below is the Solver set-up window. Fill in the Solver set-up window so as to find the optimal-alpha exponential smoothing model shown in column S. (5 points) Solver Parameters Set Objective: To: O Max . Min By Changing Variable Cells Value Of Subject to the Constraints 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. Close Solve 0 Time ear 3 04869 3.60 4.46 4.02 3.95 4.06 3.98 4.03 4.42 5.45 6.07 6.04 4.00 4.50 3.91 4.56 6.03 6.61 6.24 4.63 6.29 6.79 6.24 4.83 4.49 .75 5.20 5.36 .49 5.12 462 6.38 6.19 6.91 6.98 6.34 6.17 5.78 12 15 MA 4-period MA Value 23 4.00 3.80 2S 4.25 3.83 5.25 5.20 5.60 5.50 12 6.50 6.20 .67 3.4769 32 35 SUMMARY OUTPUT No Trend Model SUMMARY OUTPUT Trend Model 0.39823009 usted R Square 0,17255637 68325082 0.93568268 0.469295318 41 Standard Error Standard Error 44 ANOVA 5 1.76470583 2314247 36.125 00675676 604337E-05 71.541656667 0220238095 47 Residual 8 22.6556667 2.83333333 Residual 11 37.66666667 ts Standard Error 021880.402 878254 Lower 95% 5 2.403547 0000 1.37436854 -0.72 76069 0.48759962 4.1692 995 2.16929954 3.0000 1.37 57956133 00008 33612 7436854-2.1828206 0.06059543 0000 1.37436854 -1.4552138 0.183698 1692995 0.16929954 1875 0.392055911 1.5938| 0.385416667| -4, 1351351 35 00043 75743| -250511559기 -0682384403 1692995

Answer 1

1.1)
Lowest MSE , better forecasting
hence
alpha = 0.5

1.2)
alpha = 0.3
Ft = Ft-1 + 0.3(At-1 - Ft-1)
= 5.85 + 0.3 (8 - 5.85)
= 6.495

1.3)
set objective = S16
to minimize , hence Min
changing variable cells = S2

constraint
S2 >= 0
S2 <= 1