Trend and Seasonal - Regression趋势和季节性回归.doc

Regression Technique With Dummy VariablesforTrend Models With Seasonal VariationSituation: Model has been observed to have linear trend and 4-period seasonal variation over 20 periods.Required: Forecast for the next 4 periods (21-24) using regression.1. Add n-1 dummy variables for the n-seasons. Since in this model there is 4-period seasonal variation add 3 dummy variables. The model is now y = b0 + B1t + b2S1 + b3S2 + b4S3 + , where S1, S2, and S3 correspond to seasons 1, 2, and 3 respectively.2. Highlight C2:D5 and COPYPut cursor in C6: PASTEPut cursor in C10: PASTEPut cursor in C14: PASTEPut cursor in C18: PASTEPut cursor in C22: PASTEPut cursor in C6: PASTE1. Enter dummy variable values in C2:D5For data from Season 1: Enter S1 = 1 S2 = 0 S3 = 0. For data from Season 2: Enter S1 = 0 S2 = 1 S3 = 0. For data from Season 3: Enter S1 = 0 S2 = 0 S3 = 1. For data from Season 4: Enter S1 = 0 S2 = 0 S3 = 0.2. Move Column B in front of column A (if necessary) so that Period and Seasons columns are next to each other.3. Highlight B1:B25 Select: CUT4. Right Mouse Click on cell A1 Select: INSERT CUT CELLS3. Perform Regression on Values vs. Period, S1, S2 and S3.5. Fill in Regression dialogue boxRESULT4. Do forecast.6. In cell F22 enter:=$H$17+ MMULT(B22:E22,$H$18:$H$21)7. Drag F22 to F23:F25NOTES:1.In this case, MMULT (row, column) is equivalent to SUMPRODUCT. SUMPRODUCT, however, requires both entries be rows or both entries be columns.2.Check to see if all p-values for the period and seasons are low. If not, a modifie