Texas A & M University Commerce Plot the Time Series Data Excel Task

Description

4 attachmentsSlide 1 of 4attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3attachment_4attachment_4.slider-slide > img { width: 100%; display: block; }
.slider-slide > img:focus { margin: auto; }

Unformatted Attachment Preview

Single Exponential Smoothing
?t+1 = ?Yt + (1- ?) Y
?t, where the smoothing constant is a positive fraction 0 < ? < 1. Weighted average of Y current actual and current forecast is next period’s forecast with exponential smoothing factor proving the weight. Rearranging we get, ? Yt+1 = ? Yt + ? (Yt - ? Yt). In other words, next period’s forecast is current forecast plus the correction factor proportional to the current error in forecasting. Thus, successive forecasts are based on the learning/adjustment process based on the errors made in forecasting. The smoothing factor is arbitrarily selected. If ? is large (near 1), then large weight is given to recent values and very low weight to distant past. The smoothing of the series is relatively small (small damping factor). If ? is near zero, significant weight is given to a series of distant past values and very low weight to recent values. The damping factor is very large and the smoothing of the series is extreme (damping all fluctuations). Several smoothing factors can be tried, and the best ? value selected by error comparison. We do so for two values below using Excel Exponential smoothing function: Data? Data analysis ? Exponential smoothing. Fill the dialogue box for ? = 0.1 with damping factor 1 - ? = 0.9. For ? 0.7 the damping factor is 0.3. The plots the two forecasts follow (select chart in the dialogue box). The second period forecast is simply taken as the first year actual by the Excel program. We can use the average of the series of average of first few observations as the forecast for the first period or use a forecast for this period available from previous research. But the influence of the choice of initial forecast dies down after a few periods. The sum of Squares at the bottom is obtained using the Excel “Sumsq” function. Year-q 2000-q1 2000-q2 2000-q3 2000-q4 2001-q1 2001-q2 2001-q3 2001-q4 2002-q1 2002-q2 2002-q3 2002-q4 2003-q1 2003-q2 2003-q3 2003-q4 2004-q1 2004-q2 2004-q3 2004-q4 Period (t) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Yt 50 35 25 40 45 35 20 30 35 20 15 40 55 35 25 55 55 40 35 60 Yhat(0.1) et(.1) 50.00 48.50 46.15 45.54 45.48 44.43 41.99 40.79 40.21 38.19 35.87 36.28 38.16 37.84 36.56 38.40 40.06 40.05 39.55 -15.00 -23.50 -6.15 -0.53 -10.48 -24.43 -11.99 -5.79 -20.21 -23.19 4.13 18.72 -3.16 -12.84 18.44 16.60 -0.06 -5.05 20.45 ? Y (0.7) 50 39.5 29.35 36.81 42.54 37.26 25.18 28.55 33.07 23.92 17.68 33.30 48.49 39.05 29.21 47.26 52.68 43.80 37.64 et(.7) -15 -14.5 10.65 8.20 -7.54 -17.26 4.82 6.45 -13.07 -8.92 22.32 21.70 -13.49 -14.05 25.79 7.74 -12.68 -8.80 22.36 2005-q1 2005-q2 2005-q3 2005-q4 2006-q1 21 22 23 24 25 75 50 40 65 85 41.59 44.93 45.44 44.90 46.91 33.41 53.29 21.71 5.07 68.49 -18.49 -5.44 55.55 -15.55 20.10 44.66 20.34 38.09 58.90 26.10 7273.85 6246.7 Sumofsq SumofSq MSE= 7273.85/24 = MSE = 6246.7/24 303 260 Exponential Smoothing (0.1) Value 100 50 Actual Forecast 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Data Point error plot (0.1) 50.00 40.00 30.00 20.00 10.00 0.00 -10.00 0 -20.00 -30.00 5 10 15 20 25 30 Exponential Smoothing (0.7) 100 Value 80 60 Actual 40 Forecast 20 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Data Point et(.7) 30 20 10 0 -10 0 5 10 15 20 25 30 -20 -30 By comparing the errors from the two forecasts we find the smoothing factor 0.7 not only captures the fluctuations in the data much better but also has lower MSE. Moreover, the errors for ? = 0.1 have a strange pattern, mostly negative for the first half and mostly positive for the second half. On the other, hand the errors for ? = 0.7 seem to have relatively random fluctuations around zero, although the variance seems increasing towards the end. In Excel we can use Solver program to search for the ? value which produces the smallest overall error (like MSE). I will skip this topic here. Total points 125. Submit through D2L in Word/Excel format, single or multiple files. Must show your work, answer all parts of the question, and write necessary explanations to earn full points. You have to use Excel to solve this numerical problem (project). Part (A)- 65 points The data below is for 20 periods of sales in $millions. t 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Sales(Y) 10 11 14 14 12 15 18 16 16 17 16 20 24 22 20 24 26 28 28 26 Plot the time series data. Forecast for the 21st period using simple exponential smoothing with alpha 0.3 and 0.8 and plot the actual 20 period values with forecasted values. Compare the two forecasts using MSE and RMSE. Also calculate the errors and plot the errors for the two forecasts. Comment on the plots (about stationarity). Do you see systematic underestimation? Now perform the forecast using Holt’s two parameter model with alpha = 0.3 and beta = 0.6. Plot the Holt’s forecast with actual values. Also calculate the MSE for Holt’s forecast. Is it better than the simple exponential smoothing forecasts? Plot the errors for Holt’s model. Does it look nearly stationary. Part (B)- 60 points For the data below (Production = Y in million tons) plot the data and comment whether you see a seasonal pattern. Perform Holt-Winters’ seasonal forecast using alpha = 0.5, beta = 0.4 and gamma = 0.8. Make forecasts for 2007-q1 and 2007-q2 using Holt-Winters’ model. Calculate the errors and MSE and RMSE. Plot the errors and comment on the plot. Year-q 2001-1 2001-2 2001-3 2001-4 2002-1 2002-2 2002-3 2002-4 2003-1 2003-2 2003-3 2003-4 2004-1 2004-2 2004-3 2004-4 2005-1 2005-2 2005-3 2005-4 2006-1 2006-2 2006-3 2006-4 2007-1 2007-2 period=t 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Y 79 105 95 89 95 137 132 116 115 153 142 127 142 201 179 164 200 243 230 216 222 280 265 255 Holt’s Double Exponential Smoothing Holt’s (Two-parameter) Linear Double Exponential Smoothing Method Holt’s (two-parameter) smoothing technique is designed to better handle data with well- defined (significant) trends compared to the single (simple) exponential smoothing. Holt’s method allows for evolving local linear trends in a time series. Holt’s technique smooths the level and slope directly by using different smoothing constant for each. These smoothing constants provide estimates of level (Lt) and slope (Tt) that adapt over time as new observations become available. Holt’s technique provides a great deal of flexibility (compared to other methods like Brown’s double exponential smoothing) in selecting the rates of tracking the level and trend. Three equations are used in this method: (1) Current level estimate Lt = ?Yt + (1- ?)(Lt-1 + Tt-1); (2) Trend estimate Tt = ?(Lt – Lt-1) + (1- ?) Tt-1 ?t+p = Lt + p Tt, where Lt = estimate of current level (3) The forecast for p periods into the future Y The ? is the smoothing constant for level (0 < ? < 1) and ? is the smoothing constant for trend (0 < ? < 1). Tt = the trend estimate, Yt = actual observation at period t, and ? Yt+p = forecast for p periods into the future Let us use the same example we used for single smoothing with ? = 0.4 and ? = 0.3 (I typed these values in cells J4 and J5). A B C Year Quarters (t) Yt 2000 1 50 2 35 3 25 4 40 5 45 6 35 7 20 8 30 9 35 10 20 11 15 12 40 13 55 14 35 15 25 16 55 17 55 18 40 19 35 20 60 21 75 22 50 23 40 24 65 25 85 2001 2002 2003 2004 2005 2006 D E F G J Yt 90 80 70 60 50 40 30 20 10 0 0 5 10 15 20 25 30 Initial values of L and T To begin the algorithm, the initial L1 is generally set as Y1, and the initial trend value T1 is [(Y2 – Y1)/2 + (Y4 -Y3)/2] = 0 (which happens to be 0 for the given data, but can be non-zero in other data). The Excel formula I used is =(C3-C2)/2+(C5-C4)/2 The first row has labels and data starts from second row. Therefore, L2 appears in the third row and L1 and T1 appear in second row. The Algorithm Now the algorithm can start according to the equations 1-3 above. L2 = 0.4(Y2) + (1-0.4)(L2-1 + T2-1) = 0.4(35) + 0.6(50 + 0) = 44?The Excel formula is =$J$4*C3+(1-$J$4)*(D2+E2) T2 = ?(Lt – Lt-1) + (1- ?) Tt-1 = 0.3(L2 – L1) + 0.7T1 = 0.3(44 - 50) + 0.7(0) = -1.8? The Excel formula is =($J$5*(D3-D2)+(1-$J$5)*E2) ?t+p = Lt + p Tt for one period ahead forecast, p = 1 and Y ?t+p = Lt + Tt Y ?2 = L1 + T1 = 50+ 0 = 50 ? the Excel formula is =D2+E2 Y ?2 = 35 – 50 = -15 ? the Excel formula is =C3-F3 e2 = Y2 - Y Successive values are calculated in a similar fashion by copying the formula in successive rows. You can copy all four formulas together by selecting the four cells in the third row and dragging down the corner. The result is the table below: A B C D E F G Year Quarters (t) Yt Lt Tt Yhat(t+p, p= 1) et 2000 1 50 50.00 0.00 50.00 0.00 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 35 25 40 45 35 20 30 35 20 15 40 55 35 25 55 55 40 35 60 75 50 40 65 85 44.00 35.32 34.87 37.22 35.56 28.50 27.24 28.82 24.51 19.38 25.78 37.32 38.37 34.59 43.18 49.75 48.32 44.46 51.01 62.02 60.19 53.86 58.40 69.92 -1.80 -3.86 -2.84 -1.28 -1.40 -3.10 -2.54 -1.31 -2.21 -3.09 -0.24 3.30 2.62 0.70 3.07 4.12 2.45 0.56 2.36 4.95 2.92 0.14 1.46 4.48 50.00 42.20 31.46 32.03 35.94 34.17 25.40 24.70 27.51 22.30 16.29 25.54 40.62 40.99 35.30 46.25 53.86 50.77 45.02 53.37 66.98 63.10 54.01 59.87 74.40 -15.00 -17.20 8.54 12.97 -0.94 -14.17 4.60 10.30 -7.51 -7.30 23.71 29.46 -5.62 -15.99 19.70 8.75 -13.86 -15.77 14.98 21.63 -16.98 -23.10 10.99 25.13 6090.21 SumofSq MSE 243.61 2001 2002 2003 2004 2005 2006 H I J Actual vs Yhat 90 80 70 60 50 40 30 20 10 0 0 5 10 15 Yt 20 25 30 Yhat(t+p) et 40.00 30.00 20.00 10.00 0.00 0 -10.00 -20.00 -30.00 5 10 15 20 25 30 The Holt-Winters’ seasonal exponential Smoothing This procedure allows for both trend and seasonal patterns of the data to be considered and is an extension of the Holt’s model. An additional equation is used for the seasonality observed in the data. The model is helpful in making monthly and quarterly forecasts for inventory management purposes. Yt 90.00 80.00 70.00 60.00 50.00 40.00 30.00 20.00 10.00 0.00 0 5 10 15 20 25 30 The four equations for multiplicative form Yt=St*Tt*Ct*It of Winters’ model are: (1) The level estimate: Lt = ?Yt/St-s + (1- ?)(Lt-1 + Tt-1) (2) The trend estimate: Tt = ?(Lt – Lt-1) + (1- ?) Tt-1 (3) The seasonality estimate: St = ?(Yt/Lt) + (1- ?)St-s (4) The p periods into future forecast ? Y t+p = (Lt + pTt)St-s+p Where the new elements added to Holt’s model are: ? = the smoothing constant for the seasonality estimate; St = the seasonal estimate; and s = the length of seasonality Initial Values As in the Holt’s model, the smoothing parameters ?, ? and ? are arbitrarily selected. Let us use values 0.7, 0.3 and 0.4 for ?, ? and ?, respectively, on the above quarterly data. I found these parameters to be the “almost” the best using Solver optimization to minimize RMSE). A simple approach is to start the algorithm after period 5 (for quarterly data). The length of seasonality for quarterly data is 4. The seasonal indexes for first four quarters can be found by dividing Y1 to Y4 by the average of Y1 to Y4. Then, L5 = Y5/S1 using =C6/F2 and T5 = (Y5/S1) – (Y4/S4) The Algorithm Starting from S5, L6 and T6 Now the algorithm can start for the next Lt, Tt and St values as per the above formulas. Note that I typed the three parameter values on cells J1, J2 and J3. S5 = ?(Y5/L5) + (1- ?)S5-4 = ?(Y5/L5) + (1- ?)S1 = 0.4(Y5/L5) + (1- 0.4)S1 = using Excel formula =$J$3*(C6/D6)+1-$J$3)*F2 equals 1.33. Next, L6 = 0.7(Y6/S2) + 0.3(L5 + T5) using Excel formula =$J$1*(C7/F3)+(1-$J$1)*(D6+E6) equals 35.25. T6 = 0.3(L6 - L5) + 0.7T5 using the Excel formula =$J$2*(D7-D6)+(1-$J$2)*E6 equals -2.18. S6 = 0.4(Y6/L6) + 0.6S2 using the Excel formula =$J$3*(C7/D7)+(1-$J$3)*F3 equals 0.96. ? t+p = (Lt + pTt)St-s+p gives (for p = 1): Y ? 5+1 = Y ?6 = (L5 + T5)S2 using the Excel formula =(D6+E6)*F3 equals 28. Finally, e6 = Y6 - Y ?6 using the Y Excel formula =C7-G7 equals 7. Now we can copy the formulas for the next quarters by dragging down (can drag cell formulas together). 1 There are other (more complicated) methods for finding the initial values such as using Regression and dummy variable Regression, but we will skip those (dealt in later chapters) because, ultimately, the significance of the choice of initial values dies down as the updating progresses in the algorithm. Besides, our concern is about the future forecasted values rather than the initial forecasts. After the forecasting we can calculate the RMSE using =SQRT(SUMSQ(H7:H26)/COUNT(H7:H26)) which gives the RMSE = 6.86. The forecast for beyond sample periods (second and third quarters of 2006) equal 58.93 and 45.33. The plots of Holt-winters’ forecast vs actual values and the plot of errors are given after the Table of calculations and results. COLUMNs A B C D Lt E F Tt St G ?t+p = (Lt+pTt)St+1-p ?? H I J et ? 0.7 Year Period(t) Yt 2000-q1 1 50 1.33 ? 0.3 q2 2 35 0.93 ? 0.4 q3 3 25 0.67 q4 4 40 1.07 2001-q1 5 45 33.75 -3.75 1.33 q2 6 35 35.25 -2.18 0.96 28.00 7.00 q3 7 20 30.92 -2.82 0.66 22.05 -2.05 q4 8 30 28.12 -2.82 1.07 29.98 0.02 2002-q1 9 35 25.97 -2.62 1.34 33.74 1.26 q2 10 20 21.63 -3.13 0.94 22.35 -2.35 q3 11 15 21.49 -2.23 0.67 12.19 2.81 q4 12 40 32.02 1.60 1.14 20.54 19.46 2003-q1 13 55 38.83 3.16 1.37 45.02 9.98 q2 14 35 38.55 2.13 0.93 39.65 -4.65 q3 15 25 38.15 1.37 0.67 27.43 -2.43 q4 16 55 45.64 3.20 1.17 45.04 9.96 2004-q1 17 55 42.75 1.38 1.34 66.91 -11.91 q2 18 40 43.36 1.15 0.93 41.03 -1.03 q3 19 35 50.10 2.82 0.68 29.67 5.33 q4 20 60 51.90 2.52 1.16 61.70 -1.70 2005-q1 21 75 55.60 2.87 1.34 72.73 2.27 q2 22 50 55.31 1.92 0.92 54.20 -4.20 q3 23 40 58.37 2.27 0.68 38.89 1.11 q4 24 65 57.35 1.28 1.15 70.46 -5.46 2006-q1 25 85 61.94 2.27 1.35 78.65 6.35 q2 58.93 Q3 45.33 6.86 RMSE 2 Actual and Winters' Forecast 90.00 80.00 70.00 60.00 50.00 40.00 30.00 20.00 10.00 0.00 0 5 10 15 Yt 20 Yhat(t+p) 25 30 Linear (Yt ) Error Plot of Winters' Forecast 25.00 20.00 15.00 10.00 5.00 0.00 -5.00 0 5 10 15 -10.00 -15.00 3 20 25 Purchase answer to see full attachment Explanation & Answer: Data Excel Task Tags: algorithm data structures forecasted values User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.