statistics for managers using microsoft excel/spss · pdf file15/02/2016 ·...

© 1999 Prentice-Hall, Inc. Chap. 15- 1

Statistics for Managers

Using Microsoft Excel/SPSS

Chapter 15

Time-Series Analysis


Chapter Topics

• Component Factors of the Time-Series Model

• Smoothing of Data Series

Moving Averages

Exponential Smoothing

• Least Square Trend Fitting and Forecasting

Linear, Quadratic and Exponential Models

• Autoregressive Models

• Choosing Appropriate Models

• Monthly or Quarterly Data


What Is Time-Series

• A Quantitative Forecasting Method to

Predict Future Values

• Numerical Data Obtained at Regular

Time Intervals

• Projections Based on Past and Present

Observations

• Example:

Year: 1994 1995 1996 1997 1998

Sales: 75.3 74.2 78.5 79.7 80.2


Time-Series Components

Time-Series

Cyclical

Random

Trend

Seasonal


Trend Component

• Overall Upward or Downward Movement

• Data Taken Over a Period of Years

Sales

Time


Cyclical Component

• Upward or Downward Swings

• May Vary in Length

• Usually Lasts 2 - 10 Years

Sales

Time


Seasonal Component

• Upward or Downward Swings

• Regular Patterns

• Observed Within 1 Year

Sales

Time (Monthly or Quarterly)


Random or Irregular Component

• Erratic, Nonsystematic, Random,

‘Residual’ Fluctuations

• Due to Random Variations of

Nature

Accidents

• Short Duration and Non-repeating


Multiplicative Time-Series Model

•Used Primarily for Forecasting

•Observed Value in Time Series is the product

of Components

•For Annual Data:

•For Quarterly or Monthly Data: iiii ICTY

iiiii ICSTY

Ti = Trend

Ci = Cyclical

Ii = Irregular

Si = Seasonal


Moving Averages

• Used for Smoothing

• Series of Arithmetic Means Over Time

• Result Dependent Upon Choice of L, Length

of Period for Computing Means

• For Annual Time-Series, L Should be Odd

• Example: 3-year Moving Average

First Average:

Second Average:

33 321 YYY

)(MA

33 432 YYY

)(MA


Moving Average Example

Year Units Moving

Ave

1994 2 NA

1995 5 3

1996 2 3

1997 2 3.67

1998 7 5

1999 6 NA

John is a building contractor with a record of a total of 24

single family homes constructed over a 6 year period.

Provide John with a Moving Average Graph.


Moving Average Example Solution

Year Response Moving

Ave

1994 2 NA

1995 5 3

1996 2 3

1997 2 3.67

1998 7 5

1999 6 NA 94 95 96 97 98 99

8

6

4

2

0

Sales



• Weighted Moving Average

Weights Decline Exponentially

Most Recent Observation Weighted Most

• Used for Smoothing and Short Term Forecasting

• Weights Are:

Subjectively Chosen

Ranges from 0 to 1

Close to 0 for Smoothing

Close to 1 for Forecasting


Exponential Weight: Example

Year Response Smoothing Value Forecast

(W = .2)

1994 2 2 NA

1995 5 (.2)(5) + (.8)(2) = 2.6 2

1996 2 (.2)(2) + (.8)(2.6) = 2.48 2.6

1997 2 (.2)(2) + (.8)(2.48) = 2.384 2.48

1998 7 (.2)(7) + (.8)(2.384) = 3.307 2.384

1999 6 (.2)(6) + (.8)(3.307) = 3.846 3.307

11 iii E)W(WYE


Exponential Weight: Example Graph

94 95 96 97 98 99

8

6

4

2

0

Sales

Year

Data

Smoothed


The Linear Trend Model

iii X..XbbY 743143210 Year Coded Sales

94 0 2

95 1 5

96 2 2

97 3 2

98 4 7

99 5 6

0

1

2

3

4

5

6

7

8

1993 1994 1995 1996 1997 1998 1999 2000

Projected to

year 2000

Coefficients

In te rc e p t 2 .1 4 2 8 5 7 1 4

X V a ria b le 1 0 .7 4 2 8 5 7 1 4

Excel Output


The Quadratic Trend Model

2210 iii XbXbbY

22143308572 iii X.X..Y

Excel Output

Year Coded Sales

94 0 2

95 1 5

96 2 2

97 3 2

98 4 7

99 5 6

Coefficients

In te rce p t 2.85714286

X V a ria b le 1 -0.3285714

X V a ria b le 2 0.21428571


C o e f f ic ie n t s

In t e rc e p t 0 . 3 3 5 8 3 7 9 5

X V a ria b le 10 . 0 8 0 6 8 5 4 4

The Exponential Trend Model

iX

i bbY 10 or 110 blogXblogYlog i

Excel Output of Values in logs

iXi ).)(.(Y 21172

Year Coded Sales

94 0 2

95 1 5

96 2 2

97 3 2

98 4 7

99 5 6

a n t ilo g (.3 3 5 8 3 7 9 5 ) = 2 .1 7

a n t ilo g (.0 8 0 6 8 5 4 4 ) = 1 .2


Autogregressive Modeling

• Used for Forecasting

• Takes Advantage of Autocorrelation

1st order - correlation between consecutive

values

2nd order - correlation between values 2

periods apart

• Autoregressive Model for pth order:

ipipiii YAYAYAAY 22110

Random

Error


Autoregressive Model: Example

The Office Concept Corp. has acquired a number of office

units (in thousands of square feet) over the last 8 years.

Develop the 2nd order Autoregressive models.

Year Units

92 4

93 3

94 2

95 3

96 2

97 2

98 4

99 6


Autoregressive Model: Example Solution

Year Yi Yi-1 Yi-2

92 4 --- ---

93 3 4 ---

94 2 3 4

95 3 2 3

96 2 3 2

97 2 2 3

98 4 2 2

99 6 4 2

Coefficients

In te rce p t 3.5

X V a ria b le 1 0.8125

X V a ria b le 2 -0 .9375

Excel Output

21 9375812553 iii Y.Y..Y

•Develop the 2nd order

table

•Use Excel to run a

regression model


Autoregressive Model Example: Forecasting

21 9375812553 iii Y.Y..Y

Use the 2nd order model to forecast number of

units for 2000:

6254

493756812553

9375812553 199819992000

.

...

Y.Y..Y


Autoregressive Modeling Steps

1. Choose p: Note that df = n - 2p - 1

2. Form a series of “lag predictor” variables

Yi-1 , Yi-2 , … Yi-p

3. Use Excel to run regression model using all p variables

4. Test significance of Ap

If null hypothesis rejected, this model is selected

If null hypothesis not rejected, decrease p by 1 and repeat


Selecting A Forecasting Model

• Perform A Residual Analysis

Look for pattern or direction

• Measure Sum Square Errors - SSE

(residual errors)

• Measure Residual Errors Using MAD

• Use Simplest Model

Principle of Parsimony


Residual Analysis

Random errors

Trend not accounted for

Cyclical effects not accounted for

Seasonal effects not accounted for

T T

T T

e e

e e

0 0

0 0


Measuring Errors

• Sum Square Error (SSE)

• Mean Absolute Deviation (MAD)

n

iii )YY(SSE

1

2

n

YYMAD

n

iii

1


Principal of Parsimony

• Suppose 2 or more models provide good fit for data

• Select the Simplest Model

Simplest model types:

least-squares linear

least-square quadratic

1st order autoregressive

More complex types:

2nd and 3rd order autoregressive

least-squares exponential


Forecasting With Seasonal Data

• Use Categorical Predictor Variables with Least-Square Trending Fitting

• Exponential Model with Quarterly Data:

The bi provides the multiplier for the ith quarter relative to the 4th quarter.

Qi = 1 if ith quarter and 0 if not

Xj = the coded variable denoting the time period

321

43210

QQQXbbbbbY i


Forecasting With Quarterly Data: Example

4 4 5 . 7 7

4 4 4 . 2 7

4 6 2 . 6 9

4 5 9 . 2 7

5 0 0 . 7 1

5 4 4 . 7 5

5 8 4 . 4 1

6 1 5 . 9 3

6 4 5 . 5

6 7 0 . 6 3

6 8 7 . 3 1

7 4 0 . 7 4

7 5 7 . 1 2

8 8 5 . 1 4

9 4 7 . 2 8

9 7 0 . 4 3

I

2

3

4

Quarter 1994 1995 1996 1997

Standards and Poor’s Composite Stock Price Index:

Regression Statistics

M ultip le R 0.99005245

R S qua re 0.980203854

Adjuste d R S qua re 0.973005256

S ta nda rd Error 0.04361558

O bse rva tions 16

Excel Output

Appears to be

an excellent fit.

r2 is .98


Quarterly Data: Example

Coefficients

In te rc e p t 6 .0 2 9 4 0 3 3 8 6

X V a r ia b le (T re n d ) 0 .0 5 5 2 2 2 2 6 1

X V a r ia b le (Q 1 ) -0 .0 0 6 8 9 2 6 5 6

X V a r ia b le (Q 2 ) 0 .0 1 1 5 6 6 5 0 5

X V a r ia b le (Q 3 ) -0 .0 1 9 3 8 0 0 2 2

Excel Output

2110 blnQblnXblnYln ii

Regression Equation for the first quarter:

100690550296 Q.X.. i


Chapter Summary

• Discussed Component Factors of the Time-Series Model

• Performed Smoothing of Data Series Moving Averages


• Described Least Square Trend Fitting and Forecasting - Linear, Quadratic and Exponential Models

• Addressed Autoregressive Models

• Described Procedure for Choosing Appropriate Models

• Discussed Seasonal Data (use of dummy variables)

statistics for managers using microsoft excel/spss · pdf file15/02/2016 ·...

Documents