No products in the cart.

# COMM 2502 Dalhousie University Week 11 Time Series via Excel Minitab Practice: Statistics Answers 2021

COMM 2502 Dalhousie University Week 11 Time Series via Excel Minitab Practice: Statistics Answers 2021

## COMM 2502 Dalhousie University Week 11 Time Series via Excel Minitab Practice: Statistics Answers 2021

**Question Title:**

COMM 2502 Dalhousie University Week 11 Time Series via Excel Minitab Practice

**Full Question:**

Unformatted Attachment Preview

Time series analysis, Part 2

Data can be found in the worksheet “Week 11 – Data.xlsx”.

1. Automobile unit sales at B.J. Scott Motors, Inc., provided the following 10-year time

series.

Year

Sales

Year

Sales

1

400

6

260

2

390

7

300

3

320

8

320

4

340

9

340

5

270

10

370

a. Construct a time series plot. Comment on the appropriateness of a linear trend.

b. Using Minitab’s procedure Stat > Time Series > Trend analysis, develop a

quadratic trend equation that can be used to forecast sales and forecast sales in

year 11.

c. Set up the same analysis as a proper regression model. (Prepare proper variables

to estimate a quadratic model and make sure to request computation of the

Durbin-Watson statistic — under “Results…” and obtain residual plots.)

d. Is the model statistically significant? (Use = 0.05)

e. Obtain a point forecast and a 95% forecast interval for sales in year 11.

f. Assess the assumptions about the residuals in the model of part c).

2. Consider the following time series data. Complete questions using Minitab.

Quarter

Year 1

Year 2

Year 3

1

4

6

7

2

2

3

6

3

3

5

6

4

5

7

8

a. Construct a time series plot. Does the data set exhibit a trend? (What shape is

it?) Is there a seasonal component?

b. Use indicator variables for Quarter 1, Quarter 2 and Quarter 3 to develop an

estimated regression equation to account for any seasonal and linear trend

effects in the data. Make sure to request sequential SS under “Options…” and to

put the trend variable into the model first.

c. Test the significance of the trend component at the 0.05 level.

d. Test the significance of the seasonal component using a partial F-test. ( = 0.05.)

e. Use Minitab to compute quarterly forecasts for the next four quarters (that is,

for year 4).

3. A random sample of house sales data contains the following columns: Period, Year, QTR,

and Sales. Use Minitab to complete following questions:

a. Construct a time series plot. Does the series exhibit a trend? (What shape?) Is

the error component likely to be additive or multiplicative?

b. Use indicator variables for Quarter 1, Quarter 2 and Quarter 3 to develop an

estimated regression equation to account for any seasonal and linear trend

effects in the data.

c. Assess the model assumptions for the model in part (b).

d. Test for positive autocorrelation at the 0.05 level of significance.

Year

1

2

3

4

5

6

7

8

9

10

Sales

400

390

320

340

270

260

300

320

340

370

Year

1

1

1

1

2

2

2

2

3

3

3

3

Quarter

1

2

3

4

1

2

3

4

1

2

3

4

Value

4

2

3

5

6

3

5

7

7

6

6

8

Period

YEAR

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

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

QTR

2007

2007

2007

2007

2008

2008

2008

2008

2009

2009

2009

2009

2010

2010

2010

2010

2011

2011

2011

2011

2012

2012

2012

2012

2013

2013

2013

2013

2014

2014

2014

2014

2015

2015

2015

2015

2016

2016

2016

2016

2017

2017

1

2

3

4

1

2

3

4

1

2

3

4

1

2

3

4

1

2

3

4

1

2

3

4

1

2

3

4

1

2

3

4

1

2

3

4

1

2

3

4

1

2

Sales(1m)

1.833

6.195

34.016

34.74545

23.72

40.00715

82.9883

168.8318

177.659903

173.561126

164.481455

191.724755

158.876197

228.717085

219.005638

263.52436

219.787788

244.275418

215.627478

245.755091

222.0488

220.249563

267.505866

316.743315

233.214037

277.179933

327.105733

335.18943

364.632771

389.721118

401.197965

541.215865

472.575739

516.80666

568.780706

725.854593

532.617761

609.335895

558.895127

781.455415

677.783175

735.093484

43

44

45

46

47

48

2017

2017

2018

2018

2018

2018

3

4

1

2

3

4

716.77901

921.353915

679.916669

756.091631

568.309423

544.6669

Comm 2502

Predictive Analytics

Week 11 – Time Series, part 2

Inferences and regression models

© 2021 H.I. Gassmann

A quick overview

• Components of a time series and

– Trend, seasonality, cyclical, and residual effects

• Forecasts

– Always outside the range of observed values

• Smoothing methods

– Moving averages

– Exponential smoothing

• Inferences

– Forecast intervals

• Regression models

© 2021 H.I. Gassmann

Smoothing methods and forecasting

• Averaging different periods

– Backward in time or centred; constant length or varying length; equal weights or unequal weights

• Simple moving averages (computing vs. forecasting)

– Average of k adjacent periods relative to reference period t

• Backward MA: t, t–1, t–2, …, t–k+1: MAt(k) =

+ −1 + −2 +⋯+ − +1

– Observations lost at start

• Centred MA: t, k/2 periods backward, k/2 periods forward. If k is even, average Yt-k/2 and Yt+k/2

– Forecast for next period +1 =

+ −1 + −2 +⋯+ − +1

• Use same forecast for all periods beyond range of data

–

= MA ( )

Assumes no trend, no seasonality

• Simple (single) exponential smoothing

– Implicitly combine all past data with diminishing weights

– = + 1 − −1 with 1 = 1 and a constant between 0 and 1

– Assigns weight a to Yt, a2 to Yt–1, a3 to Yt–2, …, (1 – a – a2 – …) to Y1

• Larger a gives more weight to present, smaller a gives more weight to past

– Forecast for next period, +1 =

• Use same forecast for all periods beyond range of data

© 2021 H.I. Gassmann

Forecast errors and forecast intervals

• Forecast methods may depend on which components are present (T, S, C, I)

• Forecast errors are computed ex post

– Difference between the forecast and actual value

• Assessing forecast accuracy using one-period-ahead forecasting)

– For each period t, find forecast from information available up to t – 1

• E.g., moving averages, exponential smoothing, etc. ( +1 = MAt (or ESt) )

– Mean absolute error (Average of the absolute deviation | – |)

– Mean squared error (Average of the squared deviation ( – )2 )

– Mean absolute percentage error (Average of the relative deviation | – | / )

• Forecasts beyond range of observed values

– Smoothing methods: +1 = + 2 = … = MAT (or EST)

– The interval +1 +/- 1.96* MSE is an approximate 95% forecast interval

• (This assumes normality)

© 2021 H.I. Gassmann

Example (Wells Fargo data, PE 10)

• 30-day backward moving average

• Margin of error (95% confidence): 1.96* MSE = 4.7848897

• Lower bound = 46.1667 – 4.7849, upper bound 46.1667 + 4.7849

© 2021 H.I. Gassmann

Modelling a trend

• Example: Canadian military spending

– Predictor variable: Time

15.00

Canadian Military Spending

($B)

10.00

5.00

• Can use Year or Period (1, 2, 3, …)

• Period gives easier interpretation to constant:

0.00

1975

1980

1985

1990

– Estimated spending when period = 0 (i.e., 1979)

– Make sure to request Durbin-Watson statistic (under “Results…”)

(for Period = 15 – i.e., 1994)

© 2021 H.I. Gassmann

1995

The Durbin-Watson test

• Autocorrelation

– Residuals et-1, et in periods t – 1 , t correlated

– Compare variance of et – et-1 to variance of et

• If uncorrelated, then

σ − −1 2

σ 2

should be about 2,

because Var(et – et-1) = Var(et) + Var(et-1)

• Durbin-Watson test

– H0: consecutive residuals uncorrelated

(i.e., no autocorrelation)

– Ha: there is autocorrelation (usually positive)

• Test statistic:

σ − −1 2

σ 2

• Reject H0 based on table values

© 2021 H.I. Gassmann

Tables for Durbin-Watson test

Critical Values for the Durbin-Watson test

Significance levels for a one-sided test, a = 0.05

Ho: No autocorrelation

Ha: Positive autocorrelation

Structure of the table

Rows: number of observations

Columns: Number of predictors

Decision rule

If test statistic < dL, reject Ho. There is positive AC
If test statistic > dU, do not reject Ho

If between dL and dU, test is inconclusive

Example: Military spending

n = 14; p = 1; test statistic (Minitab): 0.491121

Less than 0.97, so reject Ho. Autocorrelation exists

For a two-sided test, significance level is 0.10

Rejection region: DW < dL OR DW > 4 – dL

Non-rejection region: DW > dU or < 4 – dU
Region of ambiguity: between dL and dU
AND between 4 – dU and 4 - dL
Example: Semi-annual Walmart revenues
Fitting a cubic trend model has n = 25, p = 3
DW reported as 3.06, which exceeds 4 – 1.12 = 2.88
Conclusion for Ho: Autocorrelation (positive or neg.)
Reject Ho at 0.10 level
1
dL
dU
6
0.61
1.40
7
0.70
1.36
8
0.76
1.33
9
0.82
1.32
0.88
1.32
10
12
0.97
1.33
15
1.08
1.36
20
1.20
1.41
25
1.29
1.45
30
1.35
1.49
35
1.40
1.52
40
1.44
1.54
45
1.48
1.57
50
1.50
1.58
1.55
1.62
60
70
1.58
1.64
80
1.61
1.66
90
1.63
1.68
100
1.65
1.69
150
1.72
1.75
200
1.76
1.78
250
1.78
1.80
300
1.80
1.82
400
1.83
1.84
500
1.85
1.86
600
1.86
1.87
800
1.88
1.89
© 2021
1.89
1.90
1000H.I. Gassmann
1500
1.91
1.92
2000
1.93
1.93
n
2
3
4
5
dL
dU
dL
dU
dL
dU
dL
dU
0.47
0.56
0.63
0.70
0.81
0.95
1.10
1.21
1.28
1.34
1.39
1.43
1.46
1.51
1.55
1.59
1.61
1.63
1.71
1.75
1.78
1.80
1.83
1.85
1.86
1.88
1.89
1.91
1.92
1.90
1.78
1.70
1.64
1.58
1.54
1.54
1.55
1.57
1.58
1.60
1.61
1.63
1.65
1.67
1.69
1.70
1.72
1.76
1.79
1.81
1.82
1.85
1.86
1.87
1.89
1.90
1.92
1.93
0.37
0.45
0.53
0.66
0.81
1.00
1.12
1.21
1.28
1.34
1.38
1.42
1.48
1.52
1.56
1.59
1.61
1.69
1.74
1.77
1.79
1.82
1.84
1.86
1.88
1.89
1.91
1.92
2.29
2.13
2.02
1.86
1.75
1.68
1.65
1.65
1.65
1.66
1.67
1.67
1.69
1.70
1.72
1.73
1.74
1.77
1.80
1.82
1.83
1.85
1.87
1.88
1.89
1.90
1.92
1.93
0.30
0.38
0.51
0.69
0.89
1.04
1.14
1.22
1.28
1.34
1.38
1.44
1.49
1.53
1.57
1.59
1.68
1.73
1.76
1.78
1.82
1.84
1.85
1.87
1.89
1.91
1.92
2.59
2.41
2.18
1.98
1.83
1.77
1.74
1.73
1.72
1.72
1.72
1.73
1.74
1.74
1.75
1.76
1.79
1.81
1.83
1.84
1.86
1.87
1.88
1.89
1.90
1.92
1.93
0.24
0.38
0.56
0.79
0.95
1.07
1.16
1.23
1.29
1.33
1.41
1.46
1.51
1.54
1.57
1.66
1.72
1.75
1.78
1.81
1.83
1.85
1.87
1.89
1.91
1.92
2.82
2.51
2.22
1.99
1.89
1.83
1.80
1.79
1.78
1.77
1.77
1.77
1.77
1.78
1.78
1.80
1.82
1.83
1.84
1.86
1.87
1.88
1.90
1.91
1.92
1.93
What does Durbin-Watson test “prove”?
Mis-specified trend
Cyclical effect
Durbin-Watson test cannot
distinguish the two situations
© 2021 H.I. Gassmann
Youth unemployment, a closer look
•
•
•
•
•
•
•
No trend, no seasonality
Strong cyclical effect
Sample mean 14.515
Sample standard deviation 2.133
Residuals et: Observation – Mean
Correlation between et and et -1: 0.6676
Use et -1 as a predictor!
© 2021 H.I. Gassmann
Year
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
Data
12.2
13.7
14.0
12.6
12.8
12.7
18.2
19.2
17.3
16.2
14.8
13.2
11.5
11.0
12.4
15.8
17.1
17.1
15.8
14.7
15.3
16.2
15.1
14.0
12.6
12.8
13.6
Resid Lagged
-2.3148
-0.8148 -2.3148
-0.5148 -0.8148
-1.9148 -0.5148
-1.7148 -1.9148
-1.8148 -1.7148
3.6852 -1.8148
4.6852 3.6852
2.7852 4.6852
1.6852 2.7852
0.2852 1.6852
-1.3148 0.2852
-3.0148 -1.3148
-3.5148 -3.0148
-2.1148 -3.5148
1.2852 -2.1148
2.5852 1.2852
2.5852 2.5852
1.2852 2.5852
0.1852 1.2852
0.7852 0.1852
1.6852 0.7852
0.5852 1.6852
-0.5148 0.5852
-1.9148 -0.5148
-1.7148 -1.9148
-0.9148 -1.7148
Canadian military spending, revisited
• Linear trend
15.00
– Predictor variable: Time Period (1, 2, 3, …, 14)
Canadian Military Spending
($B)
10.00
5.00
0.00
1975
• Quadratic trend
– Predictors: Period, Period^2
(for 1994 – i.e., Period = 15, Period2 = 225)
© 2021 H.I. Gassmann
1980
1985
1990
1995
Walmart: Exponential trend for annual data
Walmart Annual Sales ($B)
• First try: Fit a linear trend
400
300
200
100
• Second try: quadratic trend
• Third try: log transform
• Fourth try: Log with quadratic
• Final model: Add previous period’s residuals as predictor
– Justification: Residuals correlated with previous period’s residuals
– Results see next slide
© 2021 H.I. Gassmann
0
1970
1980
1990
2000
2010
The final model for annual Walmart data
• Response: Ln(Revenue)
• Predictors
– Period, Period^2, Lagged residuals
• Results
• Large VIF: Multicollinearity
– Period and Period^2 (expected)
• Anderson-Darling: p-value = 0.887
• Forecasting next period
– Period = 31, Period^2 = 961
– Last know residual: 2.0494
• Revenue = 399.33 – (355.62. 430.63)
© 2021 H.I. Gassmann
Quarterly data with indicator variables (Walmart)
• First model
– Predictors: Period, 3 indicators for quarters
Quarter Sales ($B) Period Ind_Q3
2008, Q3
98.34
0.25
1
2008, Q4
108.63
0.5
0
2009, Q1
94.24
0.75
0
2009, Q2
100.88
1
0
…
…
…
…
Ind_Q4
0
1
0
0
…
Ind_Q1
0
0
1
0
…
– Note data values: Periods increment by 0.25; base case is quarter 2
• Coefficient of periods: Estimated annual increase
• Intercept: All predictors are zero: 2008, Q2
– Anderson-Darling Normality test: p < 0.005
© 2021 H.I. Gassmann
Quarterly data with indicator variables (cont’d)
• Second model
– Period, indicators for Q3, Q4, Q1, lagged residuals
© 2021 H.I. Gassmann
Purchase answer to see full
attachment
[/vc_column_text][vc_message message_box_color="success" icon_fontawesome="fas fa-check-circle"]This question was handled by a Studyhelp247 Statistics tutor and the student left a positive review. 19+ custom answers have been given to students for this question in [month] [year] alone. To avoid plagiarism and maintain student privacy, we do not resell answers. Get your custom solution now. It is affordable.[/vc_message][vc_single_image image="125612" img_size="full" add_caption="yes" alignment="center"][/vc_column_inner][vc_column_inner width="5/12" css=".vc_custom_1624029708881"][vc_custom_heading text="Consider this Statistics Assignment Done
Beat its deadline and stringent requirements." font_container="tag:h4|text_align:center|color:%230a0a0a" google_fonts="font_family:Alegreya%20Sans%3A100%2C100italic%2C300%2C300italic%2Cregular%2Citalic%2C500%2C500italic%2C700%2C700italic%2C800%2C800italic%2C900%2C900italic|font_style:700%20bold%20regular%3A700%3Anormal" css_animation="slideInUp" css=".vc_custom_1624081323381"][vc_column_text][advanced_iframe securitykey="your key" use_shortcode_attributes_only="true" src="https://core.studyhelp247.com/main-site-question-post/" id="example4" height="350" width="450" show_part_of_iframe="true" show_part_of_iframe_x="-100" show_part_of_iframe_y="75" show_part_of_iframe_width="450" show_part_of_iframe_height="350" show_part_of_iframe_new_window="_top" show_part_of_iframe_new_url="https://core.studyhelp247.com/new-question-posting/"][/vc_column_text][vc_message icon_fontawesome="far fa-smile"]**>**Approximate cost: **$8 per page.**<br>**>**Choose your own tutor.<br>**>**Get free revision, plagiarism reports, and a list of sources.<br>**>**Release the money only after delivery[/vc_message]

## How it Works

## Why you should post this Statistics question now.

**Flawless Deliveries**

Our tutors deliver fault free assignments after careful and detailed research. All instructions are followed to the letter.

**100% Originality Guarantee**

Every task delivered to students on our platform has 100% originality guarantee. Find plagiarism, get 100% money back.

**Timely Deliveries**

When you post a question here, we assure on-time delivery, or 100% money back. You cannot miss deadlines with us.

**24/7 Customer Support**

Our support agents are always online, ready to help you when using our platform. It only gets better.

**Student-Friendly Prices**

Getting quality help should not break your pocket. Find instant quality help for whatever budget you have.

**Free Revisions**

You can request your tutor for as many revisions as needed to ensure you are 100% satisfied with our services.

**100% Privacy**

Every action you take on our platform is anonymous. Once you receive your help, it is almost as if you weren’t here.

**100% Refunds**

If a tutor breaks any of our guarantees, you get 100% money back to ask questions in the future. No questions.

Here are other questions on different subjects that our tutors have handled with excellent results.

The first ones are MT 101 GIKIEST Number Roots Factorization Odd Powers & Parabolas Worksheet and ISM 645 UAGC Strategizing for Corporate Leading Advantage Essay which required in-depth research and computations. The third and fourth ones are COMM 2502 Dalhousie University Week 11 Time Series via Excel Minitab Practice and Pittsburg University Computer Java Selenium Katalon Testing Tool Lab Report, which called for experts to help with online quizzes. All these were answered for students who were looking for study assistants for homework help 2021

## Reviews from Students; Verified by Trustalyze

### Studyhelp247 Exceeded my expectations.

2 years ago

I believe in studyhelp247 now. Here, I found the best tutor to assist me with a complex math task. It was seamless using the site.

**Response from Studyhelp247**

Thank you for using our platform to find academic help. We are happy that you found what you were looking for.

### By far, the best source of affordable custom writing solutions.

2 years ago

I came to studyhelp247 after seeing an ad and they have been great. It is quite affordable owing to the fact that there are no service fee charges.

**Response from Studyhelp247**

Thank you for the great review. We are committed to providing the best platform to foster student excellennce. 🙂

### Very responsive support

2 years ago

I have enjoyed using studyhelp247. The support was very responsive and I received quality work

**Response from Studyhelp247**

Thank you.

Our support agents are always available to assist you with any issue.

### Great work done for me by Anastacia.

2 years ago

Thank you for the great work that you delivered. I will be back for more. 🙂

**Response from Studyhelp247**

Thank you for using our site.