Period x Year Quarter Actual Demand y Seasonal Factor Deseasonalized Demand y_d
ID: 3247910 • Letter: P
Question
Period x Year Quarter Actual Demand y Seasonal Factor Deseasonalized Demand y_d Seasonally-Adjusted Forecast 1 2011 I 172 2 II 594 3 III 275 4 IV 912 5 2012 I 207 6 II 618 7 III 330 8 IV 886 9 2013 I 227 10 II 673 11 III 356 12 IV 1104 13 2014 I 233 14 II 761 15 III 358 16 IV 1063 17 2015 I 264 18 II 733 19 III 358 20 IV 1118 21 2016 I 275 22 II 745 23 III 411 24 IV 1164 25 2017 I 263 26 II 798 27 III 28 IV a. Use time-series decomposition and the regression program included in the Excel Data Analysis ToolPak to find the seasonally-adjusted forecasts for Quarters III and IV, 2017. Place the regression output data in cell range $H$170:$P$187. Period x Year Quarter Actual Demand y Seasonal Factor Deseasonalized Demand y_d Seasonally-Adjusted Forecast 1 2011 I 172 2 II 594 3 III 275 4 IV 912 5 2012 I 207 6 II 618 7 III 330 8 IV 886 9 2013 I 227 10 II 673 11 III 356 12 IV 1104 13 2014 I 233 14 II 761 15 III 358 16 IV 1063 17 2015 I 264 18 II 733 19 III 358 20 IV 1118 21 2016 I 275 22 II 745 23 III 411 24 IV 1164 25 2017 I 263 26 II 798 27 III 28 IV a. Use time-series decomposition and the regression program included in the Excel Data Analysis ToolPak to find the seasonally-adjusted forecasts for Quarters III and IV, 2017. Place the regression output data in cell range $H$170:$P$187.Explanation / Answer
a)
SUMMARY OUTPUT Regression Statistics Multiple R 0.914850457 R Square 0.836951359 Adjusted R Square 0.830157666 Standard Error 28.66683993 Observations 26 ANOVA df SS MS F Significance F Regression 1 101240.4157 101240.4157 123.195339 6.17E-11 Residual 24 19722.90507 821.7877113 Total 25 120963.3207 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 466.0627042 11.576468 40.25949056 1.5113E-23 442.17 489.9554 442.17 489.9554 t 8.320109125 0.74960398 11.09933957 6.17402E-11 6.773003 9.867216 6.773003 9.867216