ECO 309 Texas A & M University Commerce Exponential Smoothing Worksheet

Description

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

Unformatted Attachment Preview

Eco 309-Sp22-Assignment 3 due by midnight on April 4, 2022(Chapter 5)
Total points 125. Submit through D2L in Word/Excel format, single or multiple files. Must show
all Excel 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).
The following table gives monthly data for four years in million tons of consumption of ice
cream in a country. Plot the series and comment on the visible seasonality and trend. Estimate
the centered moving averages for this monthly series. Plot CMA and comment. Next, estimate
the S,I component which only includes seasonal and irregular movements of the series. Then find
the seasonal indexes for the twelve months removing the irregular component. Find the deseasonalized levels for the series. Plot De-seasonalized Y and comment. Then estimate the trend
values for the four sample years and the 12 months of the year 2019 (beyond the sample years)
using linear regression. Finally, make the forecast for the 12 months of 2015 using the Ratio-toMoving Average method to capture the Trend and Seasonal patterns, using Excel. Plot the
forecasted values for the 60 periods including 12 months of the year 2019. Plot the errors for insample periods and calculate RMSE. Comment on the error plot with respect to the existence of
pattern or lack of visible pattern.
Month/Year
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
2015
800
810
840
900
1000
1100
1160
1100
1050
1010
850
830
2016
860
870
900
1050
1170
1300
1380
1310
1250
1200
1050
1020
2017
1110
1120
1160
1230
1340
1450
1530
1450
1390
1350
1220
1170
2018
1290
1330
1360
1470
1620
1780
1870
1770
1720
1635
1400
1370
(Hint: First, you have to type the data in column form in Excel)
Seasonal and Trend Decomposition using Moving Averages (Ratio to Moving Average Method)
An alternative to Winters’ method consists of first obtaining de-seasonalized or seasonally adjusted data.
Weekly, monthly, or quarterly observations often exhibit seasonality. In multiplicative decomposition
model (Yt = St*Tt*Ct*It), “ratio to moving average” is a popular method for measuring seasonal
variation. In this method, the trend is estimated using centered moving average (CMA) and deseasonalized data.
S
DSY=Y/S
T
?=S*T
Y
0.932
5.149
5.247
4.891
0.838
4.894
5.394
4.519
1.093
5.488
5.541
6.058
1.133
1.143
5.685
5.688
6.503
5.975
0.971
0.932
6.222
5.835
5.440
6.188
0.840
0.838
6.207
5.982
5.012
6.300
6.325
1.075
1.093
6.219
6.130
6.702
7.4
6.350
6.400
1.156
1.143
6.472
6.277
7.176
9
6.0
6.450
6.538
0.918
0.932
6.436
6.424
5.988
10
5.6
6.625
6.675
0.839
0.838
6.684
6.571
5.505
11
7.5
6.725
6.763
1.109
1.093
6.860
6.718
7.345
Year-q
Period t
Yt
MA(4)
CMA
S,I
2010-q1
1
4.8
q2
2
4.1
q3
3
6.0
5.350
5.475
1.096
q4
4
6.5
5.600
5.738
2011-q1
5
5.8
5.875
q2
6
5.2
6.075
q3
7
6.8
q4
8
2012-q1
q2
q3
q4
12
7.8
6.800
6.838
1.141
1.143
6.822
6.865
7.849
2013-q1
13
6.3
6.875
6.938
0.908
0.932
6.758
7.012
6.537
q2
14
5.9
7.000
7.075
0.834
0.838
7.043
7.160
5.998
q3
15
8.0
7.150
1.093
7.317
7.307
7.989
8.4
1.143
7.347
q4
16
7.454
8.522
2014-q1
17
0.932
7.601
7.086
q2
18
0.838
7.748
6.491
q3
19
1.093
7.895
8.632
q4
20
1.143
8.042
9.195
Y/CMA
The above table shows the four years quarterly data of sales (Yt) ($millions) from 2010 to 2013. Our goal
is to predict sales for four quarters of 2014 accounting for the trend and seasonality factors. The plot of
the data below clearly shown a quarterly seasonal pattern with the peak on the fourth quarter every year.
Moreover, there is also a clear upward trend from year to year.
1
Yt
10.00
8.00
6.00
Yt
4.00
Linear (Yt)
2.00
0.00
0
2
4
6
8
10
12
14
16
18
We converted the first column with year and quarters to a coded time t = 1,2…16…20 in the second
column. The data is available only up to t = 16. The rest four quarters are future periods for which we
?t for the four quarters of 2014 (or t = 17 to 20).
want to predict/forecast the Y-values or ??
The first step is to calculate the moving average (with 4 periods of seasonal interval) denoted as MA(4) in
the fourth column. We put the first value in the third period although the position should be 2.5 or
between second and third period. This is because we have the seasonal lag of 4, an even number. Since
there is no position in the spreadsheet for 2.5 row, we put it in the third row. We will correct this issue in
this next step when we calculate Centered moving Average CMA in the fifth column. For monthly data
with lag of 12 we have similar issue because the MA(12) for the first 12 months belongs to the 6.5 potion
but will be put in the 7th month and corrected by calculating CMA. This issue does not arise if we have
odd numbered seasonal lag such as 3, 5, 7, 9, etc.
The formula we applied to get the first entry for MA(4) is =AVERAGE(C2:C5) which averaged the first
four values of Yt. We copied this formula down (by dragging the corner) all the way up to t = 15 (we do
not have four values to average after that). The formula for the last entry in column MA(4) becomes
=AVERAGE(C14:C17) giving the value 7.15. The next step is to calculate CMA. This is done simply by
taking the average of two MA values. The first CMA entry is obtained by averaging 5.35 and 5.60
(MA(4) entry for t = 3 and 4) and reporting it in t = 3. The formula applied is =AVERAGE(D4:D5). We
copy it down up to t = 14, because there are not two values of MA available after that. The CMA is like
smoothed data which has ironed out the fluctuations and mainly includes the Trend, as shown below.
CMA vs Yt
10.00
8.00
6.00
4.00
2.00
0.00
0
2
4
6
8
10
Yt
CMA
2
12
14
16
18
Since the CMA reflects mainly the trend, we can use it to extract the Seasonal and Irregular component
from the actual data by diving the Yt by CMA because Yt = T*S*I in the multiplicative model (ignoring
the cyclical component for data with only few years as the present data). So, we call Yt/CMA as S,I and
report it in the sixth column. The formula for the first entry is =C4/E4 which is copied down from t = 3 to
t = 14. The first entry of S,I is for q3 of the first year. It shows that q3 value is about 10% higher than the
first-year average. Similarly, the second entry shows that the fourth quarter value is about 13% higher
than the first-year average. But these percentages change from year to year due to “Irregularities”. To
remove the annual irregularities, we take the average by quarter for the various years (the textbook
suggests using Median instead of Mean, but we follow the more popular method). There are three values
for q1 in column S,I: 0.97, 0.92 and 0.97. The average rounded to decimal is 0.93 reported in the seventh
column S, which has purged the irregularities from S,I. You can use the excel function for average such
as =AVERAGE(F6,F10,F14) for quarter 1 and =AVERAGE(F7,F11,F15) for quarter 2,
=AVERAGE(F4,F8,F12) for quarter 3, and =AVERAGE(F5,F9,F13) for quarter 4. But the problem is
that it is very likely to mess up in selecting the appropriate cells by quarters. In case of monthly data, it
will be even more difficult to keep track. You can use the “If” function of Excel if you know how.
Otherwise, you can first create another table to facilitate this process. It does not matter whether you show
the quarters in columns or rows. The values in the following table come from the column S,I.
YearQuarter
Q1
Q2
Q3
Q4
2010


1.096
1.133
2011
0.971
0.840
1.075
1.156
2012
0.918
0.839
1.109
1.141
2013
0.908
0.834


Average
0.932
0.838
1.093
1.143
Now we can type these four average numbers in column S (seasonal indexes) for the four quarters (same
value for a quarter each year) from t= 1 to t = 20 (we extend for four quarters beyond the sample period
for prediction/forecasting). For example, the value 0.932 is reported for the first quarter repeatedly for the
five years.
Next, we create the “de-seasonalized” or seasonally adjusted data denoted here as DSYt = Yt/S for t =1 to
16 in column 8. The following plot shows that it follows the linear trendline very closely. It is largely
devoid of the seasonal fluctuations. It has one advantage over the CMA that it covers all the sample
period while CMA is missing for two observations in the beginning and two at the end. Besides, the
Seasonal Indexes calculated in the process of deriving DSY will be useful for future forecasting periods.
3
Yt vs DSYt
10.0
8.0
6.0
4.0
2.0
0.0
0
2
4
6
8
Yt
10
DSY=Y/S
12
14
16
18
Linear (Yt)
The next step is to use the de-seasonalized data to estimate the trend line. We will use the Regression
function of Excel. Click on data, then Data Analysis, and select regression from the menu. In the dialogue
box select the column for DSY for the dependent (Y) variable and the column for t as the explanatory (X)
variable. Check, labels and as many other options as you want. The resulting computer output is given
below.
The summary output shows a very successful regression with R-square 0.9208 implying that the linear
model captures over 92% of variations in DSY across time (also confirmed by the high F-value and high
t-values of the coefficients. The numbers of particular interest to us are the value of Intercept = 5.0996
and slope with respect to t = 0.1471. We will use these numbers to derive the Trend values for Y from t =
1 to t = 20 reported in column T. The first entry was obtained by using the Excel formula
=$Q$31+$Q$32*B2 which means Intercept plus slope times the t-value (=1). The intercept and slope
coefficients were reported in the Q column at rows 31 and 32.We put $ signs in the cell reference for
intercept and slope to lock the cells when we copy the formula for changing value of t. Copying this
formula down the T column gives all the 20 values for T.
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.9596
R Square
0.9208
Adj R Square
0.9151
Standard
Error
0.2127
Observations
16
ANOVA
df
Regression
Residual
Total
1
14
15
SS
7.3609
0.6332
7.9941
MS
7.3609
0.0452
4
F
162.7479
Signif. F
0.0000
Intercept
t
Coefficients
5.0996
0.1471
Std Error
0.1115
0.0115
t Stat
45.7259
12.7573
P-value
0.0000
0.0000
Lower
95%
4.8604
0.1224
Upper
95%
5.3388
0.1719
RESIDUAL OUTPUT
Observation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Predicted DSY=Y/S
5.2467
5.3939
5.5410
5.6882
5.8353
5.9824
6.1296
6.2767
6.4239
6.5710
6.7181
6.8653
7.0124
7.1596
7.3067
7.4538
Residuals
-0.0976
-0.4999
-0.0533
-0.0029
0.3865
0.2246
0.0898
0.1957
0.0125
0.1135
0.1415
-0.0429
-0.2542
-0.1170
0.0103
-0.1067
The residuals and their plot show very low errors in the regression estimation and errors following a
nonsystematic or random feature around zero.
the Residual Plot
Residuals
0.5000
0.0000
-0.5000 0
-1.0000
2
4
6
8
10
12
14
16
18
t
Finally, we derive the Predicted/forecasted values of Y for all 20 periods returning the seasonality into the
?=S*T.
forecasted by multiplying the trend values with seasonal corresponding quarter seasonal indexes Y
The forecasted values for the four quarters of 2014 are the results for which we performed all the above
exercises. Let us see how our forecast incorporation trend and seasonality looks compared to the actual
data. The plot shows very good fit and the future quarter forecasts exhibit a trend and seasonality similar
to the actual data.
5
Plot of Ratio-to-Moving Average Forecast
10.0
9.0
8.0
7.0
6.0
5.0
4.0
3.0
2.0
1.0
0.0
1
2
3
4
5
6
7
Yt
8
9
10
11
Y-hat=S*T
6
12
13
14
Linear (Yt)
15
16
17
18
19
20
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. Purchase answer to see full attachment Explanation & Answer: 2 pages Tags: excel exponential smoothing User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.