Transcript Spreadsheet Modeling & Decision Analysis:

Time Series Analysis
“Forecasting is very
dangerous, especially about
the future.”
--- Samuel Goldwyn
Introduction to Time Series Analysis
• A time-series is a set of observations on a
quantitative variable collected over time.
• Examples
 Dow Jones Industrial Averages
 Historical data on sales, inventory, customer
counts, interest rates, costs, etc
• Businesses are often very interested in forecasting
time series variables.
• Often, independent variables are not available to
build a regression model of a time series variable.
• In time series analysis, we analyze the past behavior
of a variable in order to predict its future behavior.
• Good forecasts can lead to
– Reduced inventory costs.
– Lower overall personnel costs.
– Increased customer satisfaction.
• The Forecasting process can be based on
– Educated guess.
– Expert opinions.
– Past history of data values, known as a time
series.
Forecasting is fundamental to decisionmaking. There are three main methods:
• Subjective forecasting is based on experience,
intuition, guesswork and a good supply of
envelope-backs.
• Extrapolation is forecasting with a rule where
past trends are simply projected into the future.
• Causal modeling (cause and effect) uses
established relationships to predict, for example,
sales on the basis of advertising or prices.
Some Time Series Terms
• Stationary Data - a time series variable exhibiting
no significant upward or downward trend over
time.
• Nonstationary Data - a time series variable
exhibiting a significant upward or downward
trend over time.
• Seasonal Data - a time series variable exhibiting
a repeating patterns at regular intervals over
time.
Stationarity
Non-stationarity (upward trend)
7
6
5
4
3
2
1
0
•
Components of a Time Series
– Long Term Trend
• A time series may be stationary or exhibit trend over time.
• Long term trend is typically modeled as a linear, quadratic or
exponential function.
– Seasonal Variation
• When a repetitive pattern is observed over some time
horizon, the series is said to have seasonal behavior.
• Seasonal effects are usually associated with calendar or
climatic changes.
• Seasonal variation is frequently tied to yearly cycles.
– Cyclical Variation
• An upturn or downturn not tied to seasonal variation.
• Usually results from changes in economic conditions.
– Random effects
The Trend Component
The long-term tendency is usually one of three: growth,
decline, or constant.
Reasons for trends include:
Population growth -- greater demand for products and services
-- greater supply of products and services
Technology -- impacts on efficiency, supply, and demand
Innovation -- impacts efficiency as well as supply and demand
The Seasonal Component
Upward and downward movements which repeat at the same
time each year.
Reasons for seasonal influences include:
Weather -- both outdoor and indoor activities can impact
demand because of the number of people involved
-- supplies of products and services may depend on the
weather
Events, Holidays -- often impact supply and demand
The Cyclical Component
Similar to seasonal variations except that there is likely not a
relationship to the time of the year.
Examples of cyclical influences include:
Inflation/deflation -- energy costs, wages and salaries, and
government spending
Stock market prices -- bull markets, bear markets
Consequences of unique events -- severe weather, law suits
The Irregular Component
Unexplained variations which we usually treat as randomness.
This is the equivalent of the error term in the analysis of
variance model and the regression model.
These are short-term effects, usually. We treat them as
independent from one time period to the next. The length of
the duration of these effects would then be shorter than one
time period, that is, one month for monthly data, one year for
annual data.
Time-Series Model
• The four components of time series come together to
form a time series model.
• There are two popular time series models:
– Additive Model:
Yt  Tt  St  Ct  I t
– Multiplicative Model:
Yt  (Tt )(St )(Ct )(I t )
Where Tt is the trend, St is the seasonal, Ct is the
cyclical and It is the irregular component.
Typical Time Series patterns
Non
Linear
linear
trend
trend
time
time
series
series
Linear Trend and Seasonality time series
A Stationary Time Series
Approaching Time Series Analysis
• There are many, many different time series
techniques.
• It is usually impossible to know which technique
will be best for a particular data set.
• It is customary to try out several different
techniques and select the one that seems to
work best.
• To be an effective time series modeler, you need
to keep several time series techniques in your
“tool box.”
Measuring Accuracy
• We need a way to compare different time series
techniques for a given data set.
• Four common techniques are the:


Yi  Y
i
n
n
Yi  Yˆ i
n
– mean absolute deviation,
MAD =
i 1
100
– mean absolute percent error, MAPE =

n i 1
Yi
2

Yi  Yi 

MSE = 
n
i 1
n
– the mean square error,
RMSE  MSE
We will focus on the MSE.
– root mean square error.
Extrapolation Models
• Extrapolation models try to account for the past
behavior of a time series variable in an effort to
predict the future behavior of the variable.
  f Y , Y , Y ,
Y
t 1
t
t 1
t 2
• We’ll first talk about several extrapolation
techniques that are appropriate for stationary data.
An Example
• Electra-City is a retail store that sells audio and
video equipment for the home and car.
• Each month the manager of the store must order
merchandise from a distant warehouse.
• Currently, the manager is trying to estimate how
many VCRs the store is likely to sell in the next
month.
• He has collected 24 months of data.
Moving Averages
Yt  Yt-1  Yt- k +1

Yt 1 
k
• No general method exists for determining k.
• We must try out several k values to see what works
best.
A Comment on Comparing MSE Values
• Care should be taken when comparing MSE
values of two different forecasting techniques.
• The lowest MSE may result from a technique that
fits older values very well but fits recent values
poorly.
• It is sometimes wise to compute the MSE using
only the most recent values.
Forecasting With The Moving Average Model
Forecasts for time periods 25 and 26 at time period 24:
Y24  Y23 36 + 35

Y25 

 355
.
2
2
 Y
Y
35.5 + 36
25
24

Y26 

 35.75
2
2
Weighted Moving Average
• The moving average technique assigns equal
weight to all previous observations
1
1
1

Yt 1  Yt  Yt-1  Yt- k -1
k
k
k
• The weighted moving average technique allows
for different weights to be assigned to previous
observations.
  w Y  w Y  w Y
Y
t 1
1 t
2 t-1
k t- k -1
where 0  wi  1 and  wi  1
• We must determine values for k and the wi
Forecasting With The Weighted
Moving Average Model
Forecasts for time periods 25 and 26 at time period 24:
Yˆ 25  w1Y24  w2 Y23  0.291 36  0.709 35  35.29
Yˆ 26  w1Yˆ 25  w2 Y24  0.291 35.29  0.709 36  35.79
Exponential Smoothing
 Y
  (Y  Y
 )
Y
t 1
t
t
t
where 0    1
• It can be shown that the above equation is equivalent to:
  Y  (1  )Y  (1  ) 2 Y (1  ) n Y 
Y
t 1
t
t 1
t 2
t n
Examples of Two
Exponential Smoothing Functions
42
40
Units Sold
38
36
34
32
Number of VCRs Sold
Exp. Smoothing alpha=0.1
30
Exp. Smoothing alpha=0.9
28
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
Time Period
Forecasting With The
Exponential Smoothing Model
Forecasts for time periods 25 and 26 at time period 24:
ˆ Y
ˆ   (Y  Y
ˆ )  35.74  0.268(36  35.74)  35.81
Y
25
24
24
24
ˆ Y
ˆ   (Y  Y
ˆ )Y
ˆ   (Y
ˆ Y
ˆ )Y
ˆ  35.81
Y
26
25
25
25
25
25
25
25
Note that,
  3581
Y
. , for t = 25, 26, 27, 
t
Seasonality
• Seasonality is a regular, repeating pattern
in time series data.
• May be additive or multiplicative in
nature...
Stationary Seasonal Effects
A d d itiv e S e a s o n a l E ffe c ts
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
18
19
20
21
22
23
24
25
T im e P e r io d
M u ltip lic a tiv e S e a s o n a l E ffe c ts
1
2
3
4
5
6
7
8
9
10
11
12
13
14
T im e P e r io d
15
16
17
Seasonal Variation
Monthly Sales of Sun Tan Oil
Units sold (in thousands)
250
200
150
100
50
0
0
5
10
15
20
25
Months (January 1993-August 1995)
30
35
Stationary Data With
Additive Seasonal Effects
where
ˆ
Y
t  n  E t  St  n  p
Et   (Yt - St-p )  (1-  )Et 1
St   (Yt - Et )  (1-  )St  p
0  1
0   1
p represents the number of seasonal periods
• Et is the expected level at time period t.
• St is the seasonal factor for time period t.
Forecasting With The Additive
Seasonal Effects Model
Forecasts for time periods 25 to 28 at time period 24:
ˆ
Y
24 n  E 24  S24 n 4
ˆ  E  S  354.44  8.45  363.00
Y
25
24
21
ˆ  E  S  354.44  17.82  336.73
Y
26
24
22
ˆ  E  S  354.44  46.58  401.13
Y
27
24
23
ˆ  E  S  354.44  31.73  322.81
Y
28
24
24
Stationary Data With
Multiplicative Seasonal Effects
where
ˆ
Y
t  n  E t  St  n  p
E t   (Yt /St-p )  (1-  )Et 1
St   (Yt /Et )  (1-  )St  p
0  1
0   1
p represents the number of seasonal periods
• Et is the expected level at time period t.
• St is the seasonal factor for time period t.
Forecasting With The Multiplicative
Seasonal Effects Model
Forecasts for time periods 25 to 28 at time period 24:
ˆ
Y
24 n  E 24  S24 n 4
ˆ  E  S  353.951.015  359.13
Y
25
24
21
ˆ  E  S  354.44  0.946  334.94
Y
26
24
22
ˆ  E  S  354.44 1.133  400.99
Y
27
24
23
ˆ  E  S  354.44  0.912  322.95
Y
28
24
24
Trend Models
• Trend is the long-term sweep or general
direction of movement in a time series.
• We’ll now consider some nonstationary time
series techniques that are appropriate for
data exhibiting upward or downward trends.
An Example
• WaterCraft Inc. is a manufacturer of personal
water crafts (also known as jet skis).
• The company has enjoyed a fairly steady
growth in sales of its products.
• The officers of the company are preparing sales
and manufacturing plans for the coming year.
• Forecasts are needed of the level of sales that
the company expects to achieve each quarter.
• See file Fig11-19.xls
Double Moving Average
ˆ
Y
t  n  E t  nTt
where
E t  2M t  Dt
Tt  2(Mt  Dt ) /(k  1)
Mt  (Yt  Yt 1    Yt k 1) / k
Dt  (Mt  Mt 1    Mt k 1) / k
• Et is the expected base level at time period t.
• Tt is the expected trend at time period t.
Forecasting With The
Double Moving Average Model
Forecasts for time periods 21 to 24 at time period 20:
ˆ
Y
20 n  E 20  nT20
ˆ  E  1T  2385.33  1139.9  2525.23
Y
21
20
20
ˆ  E  2T  2385.33  2 139.9  2665.13
Y
22
20
20
ˆ  E  3T  2385.33  3 139.9  2805.03
Y
23
20
20
ˆ  E  4T  2385.33  4 139.9  2944.94
Y
24
20
20
Double Exponential Smoothing
(Holt’s Method)
ˆ
Y
t  n  E t  nTt
where
Et = Yt + (1-)(Et-1+ Tt-1)
Tt = (Et Et-1) + (1-) Tt-1
0    1 and 0    1
• Et is the expected base level at time period t.
• Tt is the expected trend at time period t.
Forecasting With Holt’s Model
Forecasts for time periods 21 to 24 at time period 20:
ˆ
Y
20 n  E 20  nT20
  E  1T  2336.8  1  152.1  2488.9
Y
21
20
20
  E  2T  2336.8  2  152.1  26410
Y
.
22
20
20
  E  3T  2336.8  3  152.1  27931
Y
.
23
20
20
  E  4T  2336.8  4  152.1  2945.2
Y
24
20
20
Holt-Winter’s Method For
Additive Seasonal Effects
Yˆ  E  nT  S
t n
where
t

t

t n p
Et   Yt  St  p  (1-  )(Et 1  Tt 1 )
Tt   Et  Et 1   (1 -  )Tt 1
St   Yt  Et   (1-  )St  p
0  1
0   1
0   1
Forecasting With Holt-Winter’s
Additive Seasonal Effects Method
Forecasts for time periods 21 to 24 at time period 20:
Yˆ 20n  E 20  nT20  S20n4
ˆ  E  1 T  S  2253.3  1154.3  262.66  2670.3
Y
21
20
20
17
ˆ  E  2  T  S  2253.3  2 154.3  312.59  2249.3
Y
22
20
20
18
ˆ  E  3  T  S  2253.3  3 154.3  205.40  2921.6
Y
23
20
20
19
ˆ  E  4  T  S  2253.3  4 154.3  386.12  3256.6
Y
24
20
20
20
Holt-Winter’s Method For
Multiplicative Seasonal Effects
Yˆ  E  nT S
t n
where

t
t

t n p
Et   Yt / St  p  (1-  )(Et 1  Tt 1 )
Tt   Et  Et 1   (1 -  )Tt 1
St   Yt / Et   (1-  )St  p
0  1
0   1
0   1
Forecasting With Holt-Winter’s
Multiplicative Seasonal Effects Method
Forecasts for time periods 21 to 24 at time period 20:
Yˆ 20n  E 20  nT20  S20n4
ˆ  (E  1T )S  (2217.6  1137.3)1.152  2713.7
Y
21
20
20 17
ˆ  (E  2T )S  (2217.6  2 137.3)0.849  2114.9
Y
22
20
20 18
ˆ  (E  3T )S  (2217.6  3 137.3)1.103  2900.5
Y
23
20
20 19
ˆ  (E  4T )S  (2217.6  4 137.3)1.190  3293.9
Y
24
20
20 20
The Linear Trend Model
Y t  b0  b1X1t
where X1t  t
For example:
X11  1, X12  2, X13  3, 
Forecasting With The Linear Trend Model
Forecasts for time periods 21 to 24 at time period 20:
  b  b X  3751
Y
.  92.6255  21  2320.3
21
0
1
121
  b  b X  3751
Y
.  92.6255  22  2412.9
22
0
1
122
  b  b X  3751
Y
.  92.6255  23  2505.6
23
0
1
123
  b  b X  3751
Y
.  92.6255  24  2598.2
24
0
1
124
The TREND() Function
TREND(Y-range, X-range, X-value for prediction)
where:
Y-range is the spreadsheet range containing the
dependent Y variable,
X-range is the spreadsheet range containing the
independent X variable(s),
X-value for prediction is a cell (or cells) containing the
values for the independent X variable(s) for which we want
an estimated value of Y.
Note: The TREND( ) function is dynamically updated whenever
any inputs to the function change. However, it does not provide
the statistical information provided by the regression tool. It is
best two use these two different approaches to doing regression
in conjunction with one another.
The Quadratic Trend Model
  b b X b X
Y
t
0
1 1t
2 2t
where X1t  t and X2t  t 2
Forecasting With The Quadratic Trend Model
Forecasts for time periods 21 to 24 at time period 20:
2
ˆ  b b X b X
Y
21
0
1 121
2 2 21  653 .67  16 .671  21  3.617  21  2598 .9
2
ˆ  b b X b X
Y

653
.
67

16
.
671

22

3
.
617

22
 2771 .1
22
0
1 122
2 2 22
2
ˆ  b b X b X
Y

653
.
67

16
.
671

23

3
.
617

23
 2950 .4
23
0
1 123
2 2 23
2
ˆ  b b X b X
Y

653
.
67

16
.
671

24

3
.
617

24
 3137 .1
24
0
1 124
2 2 24
Computing Multiplicative
Seasonal Indices
• We can compute multiplicative seasonal
adjustment indices for period p as follows:
Yi
i Y
i
Sp 
, for all i occuring in season p
np
• The final forecast for period i is then
 adjusted = Y
  S , for any i occuring in season p
Y
i
i
p
Forecasting With Seasonal Adjustments
Applied To Our Quadratic Trend Model
Forecasts for time periods 21 to 24 at time period 20:
ˆ  (b  b X  b X ) S  2598 .9 105 .7%  2747 .8
Y
21
0
1 121
2 2 21 1
ˆ  (b  b X  b X ) S  2771 .1 80 .1%  2219 .6
Y
22
0
1 122
2 2 22
2
ˆ  (b  b X  b X ) S  2950 .5 103 .1%  3041 .4
Y
23
0
1 123
2 2 23
3
ˆ  (b  b X  b X ) S  3137 .2 111 .1%  3486 .1
Y
24
0
1 124
2 2 24
4
Summary of the Calculation and Use of
Seasonal Indices
1. Create a trend model and calculate the estimated
value ( Yˆ t ) for each observation in the sample.
2. For each observation, calculate the ratio of the actual
ˆ .
value to the predicted trend value: Yt / Y
t
ˆ ).
(For additive effects, compute the difference: Y  Y
t
t
3. For each season, compute the average of the ratios
calculated in step 2. These are the seasonal indices.
4. Multiply any forecast produced by the trend model by
the appropriate seasonal index calculated in step 3.
(For additive seasonal effects, add the appropriate factor
to the forecast.)
Summary of the Calculation and Use of
Seasonal Indices
1. Create a trend model and calculate the estimated
value ( Yˆ t ) for each observation in the sample.
2. For each observation, calculate the ratio of the actual
ˆ .
value to the predicted trend value: Yt / Y
t
ˆ ).
(For additive effects, compute the difference: Y  Y
t
t
3. For each season, compute the average of the ratios
calculated in step 2. These are the seasonal indices.
4. Multiply any forecast produced by the trend model by
the appropriate seasonal index calculated in step 3.
(For additive seasonal effects, add the appropriate factor
to the forecast.)
11-54
Refining the Seasonal Indices
• Note that Solver can be used to
simultaneously determine the optimal
values of the seasonal indices and the
parameters of the trend model being used.
• There is no guarantee that this will
produce a better forecast, but it should
produce a model that fits the data better
in terms of the MSE.
Seasonal Regression Models
• Indicator variables may also be used in regression
models to represent seasonal effects.
• If there are p seasons, we need p -1 indicator
variables.
• Our example problem involves quarterly data, so p=4
and we define the following 3 indicator variables:
1, if Yt is an observation from quarter 1
X 3t  
0, otherwise
1, if Yt is an observation from quarter 2
X4t  
0, otherwise
1, if Yt is an observation from quarter 3
X5t  
0, otherwise
Implementing the Model
• The regression function is:
  b b X b X b X b X b X
Y
t
0
1 1t
2 2t
3 3t
4 4t
5 5t
where X1t  t and X2t  t 2
Forecasting With The
Seasonal Regression Model
Forecasts for time periods 21 to 24 at time period 20:
Yˆ 21  824.471 17.319(21)  3.485(21) 2  86.805(1)  424.736(0)  123.453(0)  2638.5
ˆ  824.471 17.319(22)  3.485(22) 2  86.805(0)  424.736(1) 123.453(0)  2467.7
Y
22
ˆ  824.471 17.319(23)  3.485(23) 2  86.805(0)  424.736(0)  123.453(1)  2943.2
Y
23
ˆ  824.471 17.319(24)  3.485(24) 2  86.805(0)  424.736(0) 123.453(0)  3247.8
Y
24
Crystal Ball (CB) Predictor
• CB Predictor is an add-in that simplifies
the process of performing time series
analysis in Excel.
• A trial version of CB Predictor is available
on the CD-ROM accompanying this book.
• For more information on CB Predictor see:
http://www.decisioneering.com
Combining Forecasts
• It is also possible to combine forecasts to create a
composite forecast.
• Suppose we used three different forecasting methods
on a given data set.
• Denote the predicted value of time period t using
each method as follows:
F1t , F2t , and F3t
• We could create a composite forecast as follows:
  b b F b F b F
Y
t
0
1 1t
2 2t
3 3t