Chap 15

```Forecasting
Chapter 15
15-1
Chapter Topics
■ Forecasting Components
■ Time Series Methods
■ Forecast Accuracy
■ Time Series Forecasting Using Excel
■ Time Series Forecasting Using QM for Windows
■ Regression Methods
15-2
Forecasting Components
■ A variety of forecasting methods are available for use depending on
the time frame of the forecast and the existence of patterns.
■ Time Frames:



Short-range (one to two months)
Medium-range (two months to one or two years)
Long-range (more than one or two years)
■ Patterns:




Trend
Random variations
Cycles
Seasonal pattern
15-3
Forecasting Components
Patterns (1 of 2)

Trend - A long-term movement of the item being forecast.

Random variations - movements that are not predictable and follow
no pattern.

Cycle - A movement, up or down, that repeats itself over a lengthy
time span.

Seasonal pattern - Oscillating movement in demand that occurs
periodically in the short run.
15-4
Forecasting Components
Patterns (2 of 2)
Figure 15.1 (a) Trend; (b) Cycle; (c) Seasonal; (d) Trend with Season
15-5
Forecasting Components
Forecasting Methods
1. Times Series - Statistical techniques that use historical
data to predict future behavior.
2. Regression Methods - Regression (or causal ) methods
that attempt to develop a mathematical relationship
between the item being forecast and factors that cause it
to behave the way it does.
3. Qualitative Methods - Methods using judgment, expertise
and opinion to make forecasts.
15-6
Forecasting Components
Qualitative Methods


“Jury of executive opinion,” a qualitative technique, is the most
common type of forecast for long-term strategic planning.

Performed by individuals or groups within an organization,
sometimes assisted by consultants and other experts, whose
judgments and opinions are considered valid for the
forecasting issue.

Usually includes specialty functions such as marketing,
engineering, purchasing, etc., in which individuals have
experience and knowledge of the forecasted item.
Supporting techniques include the Delphi Method, market
research, surveys, and technological forecasting.
15-7
Time Series Methods
Overview

Statistical techniques that make use of historical data collected
over a long period of time.

Methods assume that what has occurred in the past will
continue to occur in the future.

Forecasts based on only one factor - time.
15-8
Time Series Methods
Moving Average (1 of 6)

Moving average uses values from the recent past to develop
forecasts.

This dampens random increases and decreases.

Useful for forecasting relatively stable items that do not display
any trend or seasonal pattern.

Formula for moving average (MA):
n
 Di
MAn  i1n
where:
n  number of periods in the moving average
D  data in period i
i
15-9
Time Series Methods
Moving Average (2 of 6)
Example: Instant Paper Clip Supply Company wants to forecast
orders for the month of November. Develop three-month and
five-month moving averages using the data.
Table 15.1 Orders for 10-month period
15-10
Time Series Methods
Moving Average (3 of 6)
Example: Instant Paper Clip Supply Company wants to forecast
orders for the month of November.

Three-month moving average:
3
 Di
MA  i1  90 110 130 110 orders
3
3
3

Five-month moving average:
5
 Di
MA  i1  90 110 130  75  50  91 orders
5
5
5
15-11
Time Series Methods
Moving Average (4 of 6)
Table 15.1 Three- and 5-month moving averages
15-12
Time Series Methods
Moving Average (5 of 6)
Figure 15.2 Three- and 5-month moving averages
15-13
Time Series Methods
Moving Average (6 of 6)

Longer-period moving averages react more slowly to changes in
demand than do shorter-period moving averages.

The appropriate number of periods to use often requires trial-anderror experimentation.

A moving average does not react well to changes (trends,
seasonal effects, etc.) but is easy to use and inexpensive.

Good for short-term forecasting.
15-14
Time Series Methods
Weighted Moving Average

In a weighted moving average, weights are assigned to the most
recent data.
n
WMA   W D
n i1 i i
where W  the weight for period i, between 0% and 100%
i
Wi  1.00
Example: Paper clip company weights 50% for October, 33%
for September, 17% for August:
3
WMA   W D  (.50)(90)  (.33)(110)  (.17)(130)  103.4 orders
i i
3 i1

Determining precise weights and the number of periods requires
trial-and-error experimentation.
15-15
Time Series Methods
Exponential Smoothing (1 of 11)

Exponential smoothing weights recent past data more strongly
than more distant data.

Two forms: simple exponential smoothing and adjusted
exponential smoothing.

Simple exponential smoothing:
Ft + 1 = Dt + (1 - )Ft
where:
Ft + 1 = the forecast for the next period
Dt = actual demand in the present period
Ft = the previously determined forecast for the present period
 = a weighting factor (smoothing constant).
15-16
Time Series Methods
Exponential Smoothing (2 of 11)

The most commonly used values of  are between 0.10 and
0.50.

Determination of  is usually judgmental and subjective
and often based on trial-and -error experimentation.
15-17
Time Series Methods
Exponential Smoothing (3 of 11)
Example: PM Computer Services (see Table 15.4).

Exponential smoothing forecasts using smoothing constant of .30.

Forecast for period 2 (February):
F2 =  D1 + (1- )F1 = (.30)(.37) + (1-.30)(.37) = 37 units

Forecast for period 3 (March):
F3 =  D2 + (1- )F2 = (.30)(.40) + (1-.30)(37) = 37.9 units
15-18
Time Series Methods
Exponential Smoothing (4 of 11)
Table 15.4 Exponential smoothing forecasts,  = .30 and  = .50
15-19
Time Series Methods
Exponential Smoothing (5 of 11)

The forecast that uses the higher smoothing constant (.50) reacts
more strongly to changes in demand than does the forecast with the
lower constant (.30).

Both forecasts lag behind actual demand.

Both forecasts tend to be consistently lower than actual demand.

Low smoothing constants are appropriate for stable data
without trend; higher constants appropriate for data with trends.
15-20
Time Series Methods
Exponential Smoothing (6 of 11)
Figure 15.3 Exponential smoothing forecasts
15-21
Time Series Methods
Exponential Smoothing (7 of 11)
■
Adjusted exponential smoothing: exponential smoothing with a
Formula
where:
AFt + 1 = Ft + 1 + Tt+1
T = an exponentially smoothed trend factor
Tt + 1 + (Ft + 1 - Ft) + (1 - )Tt
Tt = the last period trend factor
 = smoothing constant for trend ( a value between zero and one).
■
Reflects the weight given to the most recent trend data.
■
Determined subjectively.
15-22
Time Series Methods
Exponential Smoothing (8 of 11)
Example: PM Computer Services exponentially smoothed
forecasts with  = .50 and  = .30 (see Table 15.5 next slide).
T3 = (F3 - F2) + (1 - )T2
= (.30)(38.5 - 37.0) + (.70)(0) = 0.45
AF3 = F3 + T3 = 38.5 + 0.45 = 38.95
15-23
Time Series Methods
Exponential Smoothing (9 of 11)
Table 15.5 Adjusted exponentially smoothed forecast values
15-24
Time Series Methods
Exponential Smoothing (10 of 11)
■
The adjusted forecast is consistently higher than the simple
exponentially smoothed forecast.
■
It is more reflective of the generally increasing trend of the
data.
15-25
Time Series Methods
Exponential Smoothing (11 of 11)
Figure 15.4 Adjusted exponentially smoothed forecast
15-26
Time Series Methods
Linear Trend Line (1 of 5)
■
When demand displays an obvious trend over time, a least squares
regression line , or linear trend line, can be used to forecast.
■
Formula:
y  a  bx
where:
a  intercept (at period 0)
b  slope of the line
x  the time period
y  forecast for demand
for period x
b   xy  nxy
 x2  nx
a  y bx
where:
n  number of periods
x  nx
y  ny
15-27
Time Series Methods
Linear Trend Line (2 of 5)
Example: PM Computer Services (see Table 15.6)
x  78  6.5
12
y  557  46.42
12
1.72
b   xy2  nxy2  3,867  (12)(6.5)(46.42)
2
650 126.5
 x  nx
a  y  bx  46.42  (1.72)(6.5)  35.2
y  35.2 1.72x linear trend line
for period 13, x  13, y  35.2 1.72(13)  57.56
15-28
Time Series Methods
Linear Trend Line (3 of 5)
Table 15.6 Least squares calculations
15-29
Time Series Methods
Linear Trend Line (4 of 5)
■
A trend line does not adjust to a change in the trend as does the
exponential smoothing method.
■
This limits its use to shorter time frames in which the trend will
not change.
15-30
Time Series Methods
Linear Trend (5 of 5)
Figure 15.5 Linear trend line
15-31
Time Series Methods
■
A seasonal pattern is a repetitive up-and-down movement in
demand.
■
Seasonal patterns can occur on a quarterly, monthly, weekly, or daily
basis.
■
A seasonally adjusted forecast can be developed by multiplying
the normal forecast by a seasonal factor.
■
A seasonal factor can be determined by dividing the actual
demand for each seasonal period by total annual demand:
Si =Di/D
15-32
Time Series Methods
■
Seasonal factors lie between zero and one and represent the portion
of total annual demand assigned to each season.
■
Seasonal factors are multiplied by annual demand to provide
15-33
Time Series Methods
Example: Wishbone Farms
Table 15.7 Demand for turkeys at Wishbone Farms
S1 = D1/ D = 42.0/148.7 = 0.28
S2 = D2/ D = 29.5/148.7 = 0.20
S3 = D3/ D = 21.9/148.7 = 0.15
S4 = D4/ D = 55.3/148.7 = 0.37
15-34
Time Series Methods

Multiply forecasted demand for an entire year by seasonal factors to
determine the quarterly demand.

Forecast for entire year (trend line for data in Table 15.7):
y = 40.97 + 4.30x = 40.97 + 4.30(4) = 58.17

SF1 = (S1)(F5) = (.28)(58.17) = 16.28
SF2 = (S2)(F5) = (.20)(58.17) = 11.63
SF3 = (S3)(F5) = (.15)(58.17) = 8.73
SF4 = (S4)(F5) = (.37)(58.17) = 21.53
15-35
Forecast Accuracy
Overview

Forecasts will always deviate from actual values.

Difference between forecasts and actual values are referred to as
forecast error.

We would like forecast error to be as small as possible.

If forecast error is large, either the technique being used is the
wrong one, or the parameters need adjusting.

Measures of forecast errors:




Mean absolute percentage deviation (MAPD)
Cumulative error (E bar)
Average error, or bias (E)
15-36
Forecast Accuracy
Mean Absolute Deviation (1 of 7)

MAD is the average absolute difference between the forecast
and actual demand.

The most popular and simplest-to-use measures of forecast error.

Formula:
 Dt  Ft
n
where:
t  the period number
D  demand in period t
t
F  the forecast for period t
t
n  the total number of periods
15-37
Forecast Accuracy
Mean Absolute Deviation (2 of 7)
Example: PM Computer Services (see Table 15.8).
Compare accuracies of different forecasts using MAD:
 Dt  Ft 53.41

 4.85
n
11
15-38
Forecast Accuracy
Mean Absolute Deviation (3 of 7)
Table 15.8 Computational values for MAD and error
15-39
Forecast Accuracy
Mean Absolute Deviation (4 of 7)

The lower the value of MAD relative to the magnitude of the
data, the more accurate the forecast.

When viewed alone, MAD is difficult to assess.

MAD must be considered in light of magnitude of the data.
15-40
Forecast Accuracy
Mean Absolute Deviation (5 of 7)

Can be used to compare the accuracy of different forecasting
techniques working on the same set of demand data (PM Computer
Services):
Exponential smoothing ( = .50): MAD = 4.04
Adjusted exponential smoothing ( = .50,  = .30): MAD = 3.81
Linear trend line: MAD = 2.29

The linear trend line has the lowest MAD; increasing  from .30 to
.50 improved the smoothed forecast.
15-41
Forecast Accuracy
Mean Absolute Deviation (6 of 7)

A variation on MAD is the mean absolute percent deviation
(MAPD).

Measures the absolute error as a percentage of demand rather
than per period.

Eliminates the problem of interpreting the measure of accuracy
relative to the magnitude of the demand and forecast values.

Formula:
 Dt  Ft 53.41
MAPD 

.103 or 10.3%
520
 Dt
15-42
Forecast Accuracy
Mean Absolute Deviation (7 of 7)
MAPD for other three forecasts:
Exponential smoothing ( = .50): MAPD = 8.5%
Adjusted exponential smoothing ( = .50,  = .30):
MAPD = 8.1%
Linear trend: MAPD = 4.9%
15-43
Forecast Accuracy
Cumulative Error (1 of 2)


Cumulative error is the sum of the forecast errors (E =et).
A relatively large positive value indicates the forecast is biased
low, a large negative value indicates the forecast is biased high.

If the preponderance of errors are positive, the forecast is
consistently low; and vice versa.

The cumulative error for a trend line is always almost zero, and

is therefore not a good measure for this method.
The cumulative error for PM Computer Services can be read
directly from Table 15.8.

E =  et = 49.31, indicating the forecasts are frequently below
actual demand.
15-44
Forecast Accuracy
Cumulative Error (2 of 2)

Cumulative error for other forecasts:
Exponential smoothing ( = .50): E = 33.21
Adjusted exponential smoothing ( = .50,  =.30):
E = 21.14

Average error (bias) is the per-period average of cumulative error.

Average error for the exponential smoothing forecast:
et 49.31

E n 
 4.48
11
A large positive value of average error indicates a forecast is biased
low; a large negative error indicates it is biased high.

15-45
Forecast Accuracy
Example Forecasts by Different Measures
Table 15.9 Comparison of forecasts for PM Computer Services
Results consistent for all forecasts:

Larger value of alpha is preferable.

Adjusted forecast is more accurate than exponential smoothing.

Linear trend is more accurate than all the others.
15-46
Time Series Forecasting Using Excel (1 of 4)
=B3*B8+(1-B3)*C8
=C9+D9
=B9-E9
=ABS(B9-E9)
=G21/11
=SUM(F9:F20)
Exhibit 15.1
15-47
Time Series Forecasting Using Excel (2 of 4)
To access this window, click
on “Data” on the toolbar
ribbon and then the “Data
Exhibit 15.2
15-48
Time Series Forecasting Using Excel (3 of 4)
Demand values
 = 0.5
Cells in which the
forecasted values
will be placed
Exhibit 15.3
15-49
Time Series Forecasting Using Excel (4 of 4)
Exhibit 15.4
15-50
Exponential Smoothing Forecast with Excel QM
forecasting macros
Input problem data in
cells B7 and B10:B21
Exhibit 15.5
15-51
Time Series Forecasting
Solution with QM for Windows (1 of 2)
Exhibit 15.6
15-52
Time Series Forecasting
Solution with QM for Windows (2 of 2)
Exhibit 15.7
15-53
Regression Methods
Overview

Time series techniques relate a single variable being forecast to
time.

Regression is a forecasting technique that measures the
relationship of one variable to one or more other variables.

The simplest form of regression is linear regression.
15-54
Regression Methods
Linear Regression
Linear regression relates demand (dependent variable ) to an
independent variable.
y  a  bx
a  y bx
b   xy  nxy
2
2
x

nx

where:
x  nx  mean of the x data
y  ny  mean of the y data
15-55
Regression Methods
Linear Regression Example (1 of 3)
State University Athletic Department.
Wins
4
6
6
8
6
7
5
7
Attendance
36,300
40,100
41,200
53,000
44,000
45,600
39,000
47,500
x
(wins)
4
6
6
8
6
7
5
7
49
y
(attendance, 1,000s)
36.3
40.1
41.2
53.0
44.0
45.6
39.0
47.5
346.7
xy
145.2
240.6
247.2
424.0
264.0
319.2
195.0
332.5
2,167.7
x2
16
36
36
64
36
49
25
49
311
15-56
Regression Methods
Linear Regression Example (2 of 3)
x  49  6.125
8
y  346.9  43.34
8
b   xy  nxy  (2,167.70  (8)(6.125)(43.34)  4.06
2
(311)  (8)(6.125)2
 x2  nx
a  y bx  43.34  (.406)(6.125) 18.46
Therefore, y 18.46  4.06x
Attendance forecast for x  7 wins is
y 18.46  4.06(7) 46.88 or 46,880
15-57
Regression Methods
Linear Regression Example (3 of 3)
Figure 15.6
Linear regression line
15-58
Regression Methods
Correlation (1 of 2)

Correlation is a measure of the strength of the relationship
between independent and dependent variables.
Formula:
r
n xy   x y
2
2

2
2
 n x    x    n y    y  






Value lies between +1 and -1.
Value of zero indicates little or no relationship between
variables.
Values near 1.00 and -1.00 indicate a strong linear relationship.
15-59
Regression Methods
Correlation (2 of 2)
Value for State University example:
r
(8)(2,167.7)  (49)(346.7)
.948




2
2
 (8)(311)  (49)   (8)(15,224.7)  (346.7) 



Since the value is close to one, we have evidence of a
strong linear relationship.
15-60
Regression Methods
Coefficient of Determination


The coefficient of determination is the percentage of the
variation in the dependent variable that results from the
independent variable.
Computed by squaring the correlation coefficient, r.
For the State University example:
r = .948, r2 = .899
 This value indicates that 89.9% of the amount of variation in
attendance can be attributed to the number of wins by the team,
with the remaining 10.1% due to other, unexplained, factors.
15-61
Regression Analysis with Excel (1 of 6)
=INTERCEPT(B5:B12,A5:A12)
=CORREL(B5:B12,A5:A12)
Exhibit 15.8
15-62
Regression Analysis with Excel (2 of 6)
Click on “Insert” to access “Charts”
Click on “Scatter”
Exhibit 15.9
15-63
Regression Analysis with Excel (3 of 6)
Exhibit 15.10
15-64
Regression Analysis with Excel (4 of 6)
Exhibit 15.11
15-65
Regression Analysis with Excel (5 of 6)
Exhibit 15.12
15-66
Regression Analysis with QM for Windows (6 of 6)
Exhibit 15.13
15-67
Multiple Regression with Excel (1 of 4)
Multiple regression relates demand to two or more independent
variables.
General form:
y = 0 +  1x1 +  2x2 + . . . +  kxk
where  0 = the intercept
 1 . . .  k = parameters representing
contributions of the independent
variables
x1 . . . xk = independent variables
15-68
Multiple Regression with Excel (2 of 4)
State University example revisited; does the addition of
promotional and advertising expenditures to wins improve the
prediction of attendance?
Wins Promotion (\$) Attendance
4
29,500
36,300
6
55,700
40,100
6
71,300
41,200
8
87,000
53,000
6
75,000
44,000
7
72,000
45.600
5
55,300
39,000
7
81,600
47,500
15-69
Multiple Regression with Excel (3 of 4)
r2, the coefficient of determination
Regression equation
coefficients for x1 and x2
Exhibit 15.14
15-70
Multiple Regression with Excel (4 of 4)
Includes x1 and x2
columns
Exhibit 15.15
15-71
Example Problem Solution
Computer Software Firm (1 of 4)
Problem Statement:
 For the data below, develop an exponential smoothing forecast
using  = .40, and an adjusted exponential smoothing forecast
using  = .40 and  = .20.

Compare the accuracy of the forecasts using MAD and cumulative error.
Period
1
2
3
4
5
6
7
8
Units
56
61
55
70
66
65
72
75
15-72
Example Problem Solution
Computer Software Firm (2 of 4)
Step 1: Compute the Exponential Smoothing Forecast.
Ft+1 =  Dt + (1 - )Ft
Step 2: Compute the Adjusted Exponential Smoothing
Forecast
AFt+1 = Ft +1 + Tt+1
Tt+1 = (Ft +1 - Ft) + (1 - )Tt
15-73
Example Problem Solution
Computer Software Firm (3 of 4)
Period
1
2
3
4
5
6
7
8
9
Dt
56
61
55
70
66
65
72
75

Ft

56.00
58.00
56.80
62.08
63.65
64.18
67.31
70.39
AFt

56.00
58.40
56.88
63.20
64.86
65.26
68.80
72.19
Dt - Ft
Dt - AFt


5.00
5.00
-3.00
-3.40
13.20
13.12
3.92
2.80
1.35
0.14
7.81
6.73
7.68
6.20


35.97
30.60
15-74
Example Problem Solution
Computer Software Firm (4 of 4)
Step 3: Compute the MAD Values
 Dt  Ft 41.97

 5.99
n
7
 Dt  AFt 37.39

 5.34
n
7
Step 4: Compute the Cumulative Error.
E(Ft) = 35.97
E(AFt) = 30.60
15-75
Example Problem Solution
Building Products Store (1 of 5)
For the following data:

Develop a linear regression model

Determine the strength of the linear relationship using
correlation.

Determine a forecast for lumber given 10 building permits
in the next quarter.
15-76
Example Problem Solution
Building Products Store (2 of 5)
Quarter
Building
Permits, x
Lumber Sales
(1,000s of board ft), y
1
2
3
4
5
6
7
8
9
10
8
12
7
9
15
6
5
8
10
12
12.6
16.3
9.3
11.5
18.1
7.6
6.2
14.2
15.0
17.8
15-77
Example Problem Solution
Building Products Store (3 of 5)
Step 1: Compute the Components of the Linear Regression Equation.
x  92  9.2
10
y 128.6 12.86
10
b   xy  nxy  (1,290.3)  (10)(9.2)(12.86)
2
2
2
(932)

(10)(9.2)
x

nx

1.25
a  y bx 12.86  (1.25)(9.2)
1.36
15-78
Example Problem Solution
Building Products Store (4 of 5)
Step 2: Develop the Linear regression equation.
y = a + bx, y = 1.36 + 1.25x
Step 3: Compute the Correlation Coefficient.
r
n xy   x y
2
2

2
2
 n x    x    n y    y  

r


(10)(1,290.3)  (92)(128.6)
2

2
 (10)(932)  (92)   (10)(1,810.48)(128.6) 



.925
15-79
Example Problem Solution
Building Products Store (5 of 5)
Step 4: Calculate the forecast for x = 10 permits.
Y = a + bx = 1.36 + 1.25(10) = 13.86 or 1,386 board ft
2013 Pearson Education, Inc. Publishing as Prentice Hall
15-80
system, or transmitted, in any form or by any means, electronic, mechanical, photocopying,
recording, or otherwise, without the prior written permission of the publisher.
Printed in the United States of America.
15-81
```