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 periods forecast with exponential smoothing factor proving the
weight.
Rearranging we get, ?
Yt+1 = ?
Yt + ? (Yt - ?
Yt). In other words, next periods 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.