Transcript Statistics for Managers Using Microsoft Excel, 3/e

Statistics for Managers
using Microsoft Excel
3rd Edition
Chapter 13
Time Series Analysis
© 2002 Prentice-Hall, Inc.
Chap 13-1
Chapter Topics

The importance of forecasting

Component factors of the time-series model

Smoothing of annual time series


Moving averages

Exponential smoothing
Least square trend fitting and forecasting

Linear, quadratic and exponential models
© 2002 Prentice-Hall, Inc.
Chap 13-2
Chapter Topics




(continued)
Autoregressive models
Choosing appropriate forecasting models
Time series forecasting of monthly or
quarterly data
Pitfalls concerning time-series analysis
© 2002 Prentice-Hall, Inc.
Chap 13-3
The Importance of Forecasting


Government needs to forecast unemployment,
interest rates, expected revenues from income
taxes to formulate policies
Marketing executives need to forecast
demand, sales, consumer preferences in
strategic planning
© 2002 Prentice-Hall, Inc.
Chap 13-4
The Importance of Forecasting
(continued)


College administrators need to forecast
enrollments to plan for facilities and for faculty
recruitment
Retail stores need to forecast demand to
control inventory levels, hire employees and
provide training
© 2002 Prentice-Hall, Inc.
Chap 13-5
Time-Series



Numerical data obtained at regular time
intervals
The time intervals can be annually, quarterly,
daily, hourly, etc.
Example:
Year:1994 1995 1996 1997 1998
Sales:
75.3 74.2 78.5 79.7 80.2
© 2002 Prentice-Hall, Inc.
Chap 13-6
Time-Series Components
Trend
Cyclical
Time-Series
Seasonal
© 2002 Prentice-Hall, Inc.
Random
Chap 13-7
Trend Component


Overall upward or downward movement
Data taken over a period of years
Sales
© 2002 Prentice-Hall, Inc.
Time
Chap 13-8
Cyclical Component



Upward or downward swings
May vary in length
Usually lasts 2 - 10 years
Sales
© 2002 Prentice-Hall, Inc.
Chap 13-9
Seasonal Component



Upward or downward swings
Regular patterns
Observed within 1 year
Sales
Summer
Winter
Spring
Fall
Time (Monthly or Quarterly)
© 2002 Prentice-Hall, Inc.
Chap 13-10
Random or Irregular Component


Erratic, nonsystematic, random, “residual”
fluctuations
Due to random variations of



Nature
Accidents
Short duration and non-repeating
© 2002 Prentice-Hall, Inc.
Chap 13-11
e.g.: Quarterly Retail Sales with
Seasonal Components
Quarterly with Seasonal Components
25
20
Sales
15
10
5
0
0
© 2002 Prentice-Hall, Inc.
5
10
15
20
Time
25
30
35
Chap 13-12
e.g.: Quarterly Retail Sales with
Seasonal Components Removed
Quarterly without Seasonal Com ponents
25
20
Sales
15
Y(t)
10
5
0
0
© 2002 Prentice-Hall, Inc.
5
10
15
20
Tim e
25
30
35
Chap 13-13
Multiplicative
Time-Series Model




Used primarily for forecasting
Observed value in time series is the product of
components
For annual data:
Ti = Trend
Yi  Ti Ci Ii
Ci = Cyclical
For quarterly or monthly data:
Ii = Irregular
Yi  Ti Si Ci I i
Si = Seasonal
© 2002 Prentice-Hall, Inc.
Chap 13-14
Moving Averages





Used for smoothing
Series of arithmetic means over time
Result dependent upon choice of L (length of
period for computing means)
To smooth out cyclical component, L should
be multiple of the estimated average length of
the cycle
For annual time-series, L should be odd
© 2002 Prentice-Hall, Inc.
Chap 13-15
Moving Averages

(continued)
Example: Three-year moving average

Y1  Y2  Y3
First average: MA(3) 
3

Y2  Y3  Y4
Second average: MA(3) 
3
© 2002 Prentice-Hall, Inc.
Chap 13-16
Moving Average Example
John is a building contractor who has constructed 24
single-family homes over a six-year period. Provide
John with a three-year Moving Average Graph.
© 2002 Prentice-Hall, Inc.
Year
Units
Moving
Ave
1994
2
NA
1995
5
3
1996
2
3
1997
2
3.67
1998
7
5
1999
6
NA
Chap 13-17
Moving Average
Example Solution
Year
Response Moving
Ave
Sales
8
L=3
1994
2
NA
1995
5
3
6
1996
2
3
4
1997
2
3.67
2
1998
7
5
1999
6
NA
0
94 95 96 97 98 99
No MA for the first and last (L-1)/2 years
© 2002 Prentice-Hall, Inc.
Chap 13-18
Moving Average Example
Solution in Excel


Use excel formula “=average (cell range
containing the data for the years to average)”
Excel spreadsheet for the single family home
sales example
© 2002 Prentice-Hall, Inc.
Chap 13-19
e.g.: 5-point Moving Averages of
Quarterly Retail Sales
Quarterly 5-point Moving Averages
25
20
Sales
15
MA(5)
Y(t)
10
5
0
0
© 2002 Prentice-Hall, Inc.
5
10
15
20
Time
25
30
35
Chap 13-20
Exponential Smoothing

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


© 2002 Prentice-Hall, Inc.
Close to 0 for smoothing out unwanted cyclical
and irregular components
Close to 1 for forecasting
Chap 13-21
Exponential Weight: Example
Ei  WYi  (1  W ) Ei 1
Year
Response Smoothing Value
Forecast
(W = .2, (1-W)=.8)
1994
2
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
© 2002 Prentice-Hall, Inc.
2
NA
Chap 13-22
Exponential Weight:
Example Graph
Sales
8
Data
6
4
Smoothed
2
0
94
© 2002 Prentice-Hall, Inc.
95
96
97
98
99
Year
Chap 13-23
Exponential Smoothing in Excel

Use tools | data analysis | exponential
smoothing


The damping factor is (1-W )
Excel spreadsheet for the single family home
sales example
© 2002 Prentice-Hall, Inc.
Chap 13-24
Example: Exponential
Smoothing of Real GNP

The EXCEL spreadsheet with the real GDP
data and the exponentially smoothed series
© 2002 Prentice-Hall, Inc.
Chap 13-25
The Least Squares
Linear Trend Model
Year Coded X Sales (Y)
95
0
2
96
1
5
97
2
2
98
3
2
99
4
7
00
5
6
© 2002 Prentice-Hall, Inc.
ˆ
Yi  b0  b1 X i
Chap 13-26
The Least Squares
Linear Trend Model
(continued)
Yˆi  b0  b1 X i  2.143  .743X i
8
Excel Output
7
Coefficients
6
2.14285714
X V a ri a b l e 1 0 . 7 4 2 8 5 7 1 4
5
Sales
I n te rc e p t
4
Projected to
year 2001
3
2
1
0
0
© 2002 Prentice-Hall, Inc.
1
2
X
3
4
5
6
Chap 13-27
The Quadratic Trend Model
Year Coded X Sales (Y)
95
0
2
96
1
5
97
2
2
98
3
2
99
4
7
00
5
6
© 2002 Prentice-Hall, Inc.
2
ˆ
Yi  b0  b1 X i  b2 X i
Chap 13-28
The Quadratic Trend Model
(continued)
2
2
ˆ
Yi  b0  b1 Xi  b2 Xi  2.857  .33Xi  .214Xi
Excel Output
8
7
In te rce p t
2.85714286
6
X V a ria b le 1
-0.3285714
X V a ria b le 2
0.21428571
Sales
Coefficients
5
Projected to
year 2001
4
3
2
1
0
0
© 2002 Prentice-Hall, Inc.
1
2
X
3
4
5
6
Chap 13-29
The Exponential Trend Model
Xi
ˆ
Yi  b0b1
or
log Yˆi  log b0  X1 log b1
C o e f f ic ie n t s
Year Coded X Sales (Y)
95
0
2
In t e rc e p t
0 .3 3 5 8 3 7 9 5
X V a ria b le 1 0 . 0 8 0 6 8 5 4 4
96
1
5
97
2
2
Excel Output of Values in logs
98
3
2
a n t ilo g (. 3 3 5 8 3 7 9 5 ) =
2.17
99
4
7
a n t ilo g (. 0 8 0 6 8 5 4 4 ) =
1.2
00
5
6
© 2002 Prentice-Hall, Inc.
Xi
ˆ
Yi  (2.17)(1.2)
Chap 13-30
The Least Squares
Trend Models in PHStat


Use PHStat | simple linear regression for
linear trend and exponential trend models and
PHStat | multiple regression for quadratic
trend model
Excel spreadsheet for the single family home
sales example
© 2002 Prentice-Hall, Inc.
Chap 13-31
Model Selection Using Differences

Use a linear trend model if the first differences
are more or less constant
Y2  Y1  Y3  Y2 

 Yn  Yn1
Use a quadratic trend model if the second
differences are more or less constant
Y3  Y2   Y2  Y1   
© 2002 Prentice-Hall, Inc.
 Yn  Yn 1   Yn 1  Yn 2  
Chap 13-32
Model Selection Using Differences
(continued)

Use an exponential trend model if the
percentage differences are more or less
constant
 Y2  Y1 
 Y3  Y2 

100%  
100% 
 Y1 
 Y2 
© 2002 Prentice-Hall, Inc.
 Yn  Yn1 

100%
 Yn1 
Chap 13-33
Autoregressive 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 p- th order:
Yi  A0  AY
1 i 1  A2Yi 2 
 ApYi  p  i
Random
Error
© 2002 Prentice-Hall, Inc.
Chap 13-34
Autoregressive Model:
Example
The Office Concept Corp. has acquired a number of
office units (in thousands of square feet) over the
last eight years. Develop the second order
Autoregressive model.
Year Units
93
94
95
96
97
98
99
00
© 2002 Prentice-Hall, Inc.
4
3
2
3
2
2
4
6
Chap 13-35
Autoregressive Model:
Example Solution
Develop the 2nd order
table
Use Excel to estimate a
regression model
Excel Output
Coefficients
I n te rc e p t
3.5
X V a ri a b l e 1
0.8125
X V a ri a b l e 2
-0 . 9 3 7 5
Year
93
94
95
96
97
98
99
00
Yi
4
3
2
3
2
2
4
6
Yi-1
--4
3
2
3
2
2
4
Yi-2
----4
3
2
3
2
2
Yˆi  3.5  .8125Yi 1  .9375Yi 2
© 2002 Prentice-Hall, Inc.
Chap 13-36
Autoregressive Model Example:
Forecasting
Use the second order model to forecast
number of units for 200x:
Yi  3.5  .8125Yi 1  .9375Yi  2
Y2001  3.5  .8125Y2000  .9375Y1999
 3.5  .8125  6  .9375  4
 4.625
© 2002 Prentice-Hall, Inc.
Chap 13-37
Autoregressive Model in PHStat

PHStat | multiple regression

Excel spreadsheet for the office units example
© 2002 Prentice-Hall, Inc.
Chap 13-38
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
© 2002 Prentice-Hall, Inc.
Chap 13-39
Selecting A Forecasting Model

Perform a residual analysis




Look for pattern or direction
Measure sum of square error - SSE (residual
errors)
Measure residual error using MAD
Use simplest model

Principle of parsimony
© 2002 Prentice-Hall, Inc.
Chap 13-40
Residual Analysis
e
e
0
0
T
Random errors
T
Cyclical effects not accounted for
e
e
0
0
T
Trend not accounted for
© 2002 Prentice-Hall, Inc.
T
Seasonal effects not accounted for
Chap 13-41
Measuring Errors


Choose a model that gives the smallest
measuring errors
Sum square
error
(SSE)
n
2
ˆ
 SSE 
 Yi  Yi
i 1



Sensitive to outliers
© 2002 Prentice-Hall, Inc.
Chap 13-42
Measuring Errors

(continued)
Mean Absolute
Deviation
(MAD)
n
ˆ
Y

Y

i
i

MAD  i 1
n

Not sensitive to extreme observations
© 2002 Prentice-Hall, Inc.
Chap 13-43
Principal of Parsimony


Suppose two 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:


© 2002 Prentice-Hall, Inc.
2nd and 3rd order autoregressive
Least-squares exponential
Chap 13-44
Forecasting With Seasonal Data


Use categorical predictor variables with leastsquare trending fitting
Exponential model with quarterly data:
Xi
Q1
Q2
Q3
ˆ
Y  b0b1 b2 b3 b4



The bi provides the multiplier for the i-th quarter
relative to the 4th quarter.
Qi = 1 if i-th quarter and 0 if not
Xj = the coded variable denoting the time period
© 2002 Prentice-Hall, Inc.
Chap 13-45
Forecasting With Quarterly Data:
Example
Standards and Poor’s Composite Stock Price Index:
Quarter
1995
1996
1997
I
4 4 5 .7 7
5 0 0 .7 1
6 4 5 .5
7 5 7 .1 2
2
4 4 4 .2 7
5 4 4 .7 5
6 7 0 .6 3
8 8 5 .1 4
3
4 6 2 .6 9
5 8 4 .4 1
6 8 7 .3 1
9 4 7 .2 8
4
4 5 9 .2 7
6 1 5 .9 3
7 4 0 .7 4
9 7 0 .4 3
Excel Output
© 2002 Prentice-Hall, Inc.
1994
Regression Statistics
Multiple R
0.989936819
R Square
0.979974906
Adjusted R Square 0.972693054
Standard Error
0.043867069
Observations
16
r2 is .98
Appears to be
an excellent fit.
Chap 13-46
Forecasting With Quarterly Data:
Example
(continued)
Excel Output
Intercept
Coded X
Q1
Q2
Q3
Coefficients Standard Error
6.011187697
0.031115484
0.055372493
0.002452244
0.010421639
0.031879168
0.023885562
0.031404042
0.019342411
0.031115484
Regression Equation for the first quarter:
ln Yˆi  ln b0  X i ln b1  Q1 ln b2
 6.011  .0553 X i  0.0104Q1
© 2002 Prentice-Hall, Inc.
Chap 13-47
Forecasting with Quarterly Data
in PHStat


Use PHStat | multiple regression
Excel spreadsheet for the stock price index
example
© 2002 Prentice-Hall, Inc.
Chap 13-48
Pitfalls Regarding
Time-Series Analysis


Assuming the mechanism that governs the
time series behavior in the past will still hold
in the future
Using mechanical extrapolation of the trend to
forecast the future without considering
personal judgments, business experiences,
changing technologies, and habits, etc.
© 2002 Prentice-Hall, Inc.
Chap 13-49
Chapter Summary



Discussed the importance of forecasting
Addressed component factors of the timeseries model
Performed smoothing of data series



Moving averages
Exponential smoothing
Described least square trend fitting and
forecasting

Linear, quadratic and exponential models
© 2002 Prentice-Hall, Inc.
Chap 13-50
Chapter Summary




(continued)
Addressed autoregressive models
Described procedure for choosing appropriate
models
Addressed time series forecasting of monthly
or quarterly data (use of dummy variables)
Discussed pitfalls concerning time-series
analysis
© 2002 Prentice-Hall, Inc.
Chap 13-51