Integrated Production Systems I

Lecture 2: Forecasting

1
Outline
• Strategic Role of Forecasting
• Components of Forecasting Demand
• Time Series Methods
• Forecast Accuracy
• Forecasting Using Excel
Learning Objectives
• Discuss the strategic role of forecasting in supply chain management
• Describe the forecasting process and identify the components of
forecasting demand
• Forecast demand using various time series models, including
exponential smoothing, and trend and seasonal adjustments
• Discuss and calculate various methods for evaluating forecast
accuracy
• Use Excel to create various forecast models

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Strategic Role of Forecasting
• Strategic Planning: Successful strategic planning requires accurate
forecasts of future products and markets.
• Forecasting demand determines:
• Inventory levels needed
• Quantity of product to produce
• Quantity of raw material to purchase
• What kind of transportation method is required, and on a strategic level,
where factories, warehouses should be located
• Quality Management: Accurately forecasting customer demand is a key to
providing good timely quality service.

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

The Effect of Inaccurate Forecasting
Forecasting: Time Frame
• Indicates how far into the future is forecast
• Short- to mid-range forecast
• typically encompasses the immediate future
• Used for tactical and operational decisions
• daily up to two years
• Long-range forecast
• usually encompasses a period of time longer than two years
• Less accurate
• Used for strategic decisions

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Steps of the Forecasting Process

Copyright ©2019 John Wiley & Sons, Inc. 7

Forecasting

• Predicting the future

• Qualitative forecast methods
o subjective
• Quantitative forecast methods
o based on mathematical formulas

Copyright ©2019 John Wiley & Sons, Inc. 8

Forecasting Methods
• Time series
• statistical techniques that use historical demand data to
predict future demand
• Regression methods
• attempt to develop a mathematical relationship between
demand and factors that cause its behavior
• Qualitative
• use management judgment, expertise, and opinion to
predict future demand

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Data Mining

• Process to analyze large amounts of data

• A set of IT tools
• Identify patterns, trends and relationships among and between
groups of customers, markets and products

Copyright ©2019 John Wiley & Sons, Inc. 10

Time Series: Demand Behavior
• Random variations
• movements in demand that do not follow a pattern
• Trend
• a gradual, long-term up or down movement of demand
• Cycle
• an up-and-down repetitive movement in demand
• Seasonal pattern
• an up-and-down repetitive movement in demand occurring
periodically

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Time Series: Demand Behavior

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Time Series Forecasting Methods

• Assume that what has occurred in the past will continue to

occur in the future.
• Relate the forecast to only one factor - time
• Include:
• moving average
• exponential smoothing
• adjusted exponential smoothing
• linear trend line
• seasonal adjustments

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Moving Average
• Naive forecast
• demand in current period is used as next period’s forecast

• Simple moving average

• uses average demand for a fixed sequence of periods
• good for stable demand with no pronounced behavioral patterns.

• Weighted moving average

• weights are assigned to most recent data

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Moving Average: 1. Naïve Approach
ORDERS
Example 12.1 MONTH PER MONTH FORECAST
Jan 120
Feb 90
Mar 100
Apr 75
May 110
June 50
July 75
Aug 130
Sept 110
Oct 90
Nov -

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Moving Average: 1. Naïve Approach
ORDERS
MONTH PER MONTH FORECAST
Jan 120 -
Feb 90 120
Mar 100 90
Apr 75 100
May 110 75
June 50 110
July 75 50
Aug 130 75
Sept 110 130
Oct 90 110
Nov - 90

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

2. Simple Moving Average
Month Orders Month Orders
January 120 June 50
February 90 July 75
n
March
April
100 August
75 September
130
110
D i

May 110 October 90 MAn = i =1

n
where
n = number of periods in the moving
average
Di = demand in period i

Copyright ©2019 John Wiley & Sons, Inc. 17

 3-month Simple Moving Average

ORDERS MOVING
MONTH PER MONTH AVERAGE 3

Jan 120 
i=1
Di
Feb 90 MA3 =
Mar 100 3
Apr 75
May 110
June 50
July 75
Aug 130
Sept 110
Oct 90
Nov -

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 3-month Simple Moving Average
ORDERS MOVING
MONTH PER MONTH AVERAGE 3

Jan 120 – 
i=1
Di
Feb 90 – MA3 =
Mar 100 – 3
Apr 75 103.3
May 110 88.3 90 + 110 + 130
June 50 95.0
= 3
July 75 78.3
Aug 130 78.3
= 110 orders for Nov
Sept 110 85.0
Oct 90 105.0
Nov - 110.0

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 5-month Simple Moving Average

ORDERS MOVING
MONTH PER MONTH AVERAGE 5

Jan 120 
i=1
Di
Feb 90 MA5 =
Mar 100 5
Apr 75
May 110
June 50
July 75
Aug 130
Sept 110
Oct 90
Nov -

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 5-month Simple Moving Average
ORDERS MOVING
MONTH PER MONTH AVERAGE 5

Jan 120 – 
i=1
Di
Feb 90 – MA5 =
Mar 100 – 5
Apr 75 –
May 110 – 90 + 110 + 130+75+50
= 5
June 50 99.0
July 75 85.0
Aug 130 82.0 = 91 orders for Nov
Sept 110 88.0
Oct 90 95.0
Nov - 91.0

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Smoothing Effects

Copyright ©2019 John Wiley & Sons, Inc. 22

 3. Weighted Moving Average
• Adjusts moving average method to more closely reflect data fluctuations
n
WMAn =  Wi Di
i=1
where
Wi = the weight for period i, between 0
and 100 percent
 Wi = 1.00

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Weighted Moving Average Example

MONTH WEIGHT DATA

August 17% 130
Example 12.2
September 33% 110
October 50% 90
3
November Forecast WMA3 = 
i=1
Wi Di

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Weighted Moving Average Example
MONTH WEIGHT DATA
August 17% 130
September 33% 110
October 50% 90
3
November Forecast WMA3 = 
i=1
Wi Di

= (0.50)(90) + (0.33)(110) + (0.17)(130)

= 103.4 orders

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 4. Exponential Smoothing

• Averaging method
• Weighs most recent data point more strongly
• Reacts more to recent changes
• Widely used, accurate method
• Smoothing constant, α
• applied to most recent data point

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Exponential Smoothing

Ft +1 =  Dt + (1 - )Ft
where:
Ft +1 = forecast for next period
Dt = actual demand for present period
Ft = previously determined forecast for present period
= weighting factor, smoothing constant

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Effect of Smoothing Constant
0.0    1.0

If  = 0.20, then Ft +1 = 0.20 Dt + 0.80 Ft

If  = 0, then Ft +1 = 0 Dt + 1 Ft = Ft
Forecast does not reflect recent data

If  = 1, then Ft +1 = 1 Dt + 0 Ft = Dt
Forecast based only on most recent data

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Exponential Smoothing (α=0.30)
PERIOD MONTH DEMAND F2 = D1 + (1 - )F1
1 Jan 37
2 Feb 40 Assume F1 = D1
3 Mar 41
Example 12.3 4 Apr 37
F3 = D2 + (1 - )F2
5 May 45
6 Jun 50
7 Jul 43
8 Aug 47
9 Sep 56 F13 = D12 + (1 - )F12
10 Oct 52
11 Nov 55
12 Dec 54

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Exponential Smoothing (α=0.30)
PERIOD MONTH DEMAND F2 = D1 + (1 - )F1
1 Jan 37
= (0.30)(37) + (0.70)(37)
2 Feb 40
3 Mar 41 = 37
4 Apr 37
F3 = D2 + (1 - )F2
5 May 45
6 Jun 50 = (0.30)(40) + (0.70)(37)
7 Jul 43 = 37.9
8 Aug 47
9 Sep 56 F13 = D12 + (1 - )F12
10 Oct 52 = (0.30)(54) + (0.70)(50.84)
11 Nov 55
= 51.79
12 Dec 54

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Exponential Smoothing
FORECAST, Ft + 1
PERIOD MONTH DEMAND ( = 0.3) ( = 0.5)
1 Jan 37 – –
2 Feb 40
3 Mar 41
4 Apr 37
5 May 45
6 Jun 50
7 Jul 43
8 Aug 47
9 Sep 56
10 Oct 52
11 Nov 55
12 Dec 54
13 Jan –

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Exponential Smoothing
FORECAST, Ft + 1
PERIOD MONTH DEMAND ( = 0.3) ( = 0.5)
1 Jan 37 – –
2 Feb 40 37.00 37.00
3 Mar 41 37.90 38.50
4 Apr 37 38.83 39.75
5 May 45 38.28 38.37
6 Jun 50 40.29 41.68
7 Jul 43 43.20 45.84
8 Aug 47 43.14 44.42
9 Sep 56 44.30 45.71
10 Oct 52 47.81 50.85
11 Nov 55 49.06 51.42
12 Dec 54 50.84 53.21
13 Jan – 51.79 53.61

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Exponential Smoothing

Copyright ©2019 John Wiley & Sons, Inc. 33

5. Adjusted Exponential Smoothing

AFt +1 = Ft +1 + Tt +1
where
T = an exponentially smoothed trend factor

Tt +1 =  (Ft +1 - Ft) + (1 - ) Tt
where
Tt = the last period trend factor
 = a smoothing constant for trend
0≤≤1

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Adjusted Exponential Smoothing (β=0.30)
FORECAST, Ft + 1
PERIOD MONTH DEMAND ( = 0.3) ( = 0.5)
1 Jan 37 – – T3 = (F3 - F2) + (1 - ) T2
2 Feb 40 37.00 37.00
Example 3 Mar 41 37.90 38.50 AF3 = F3 + T3
12.4 4 Apr 37 38.83 39.75
5 May 45 38.28 38.37
6 Jun 50 40.29 41.68
7 Jul 43 43.20 45.84
8 Aug 47 43.14 44.42
9 Sep 56 44.30 45.71 T13 = (F13 - F12) + (1 - ) T12
10 Oct 52 47.81 50.85
11 Nov 55 49.06 51.42 AF13 = F13 + T13 =
12 Dec 54 50.84 53.21
13 Jan – 51.79 53.61

Assume T2 = 0
Adjusted Exponential Smoothing (α=0.5, β=03)
PERIOD MONTH DEMAND T3 = (F3 - F2) + (1 - ) T2
1 Jan 37 = (0.30)(38.5 - 37.0) + (0.70)(0)
2 Feb 40 = 0.45
3 Mar 41
4 Apr 37 AF3 = F3 + T3 = 38.5 + 0.45
5 May 45 = 38.95
6 Jun 50
7 Jul 43 T13 = (F13 - F12) + (1 - ) T12
8 Aug 47 = (0.30)(53.61 - 53.21) + (0.70)(1.77)
9 Sep 56
= 1.36
10 Oct 52
11 Nov 55
12 Dec 54 AF13 = F13 + T13 = 53.61 + 1.36 = 54.97

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Adjusted Exponential Smoothing (α=0.5, β=03)
FORECAST TREND ADJUSTED
PERIOD MONTH DEMAND Ft +1 Tt +1 FORECAST AFt +1

1 Jan 37 37.00 – –
2 Feb 40 37.00 0.00 37.00
3 Mar 41 38.50 0.45 38.95
4 Apr 37 39.75 0.69 40.44
5 May 45 38.37 0.07 38.44
6 Jun 50 38.37 0.07 38.44
7 Jul 43 45.84 1.97 47.82
8 Aug 47 44.42 0.95 45.37
9 Sep 56 45.71 1.05 46.76
10 Oct 52 50.85 2.28 58.13
11 Nov 55 51.42 1.76 53.19
12 Dec 54 53.21 1.77 54.98
13 Jan – 53.61 1.36 54.96

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Adjusted Exponential Smoothing Forecasts

Copyright ©2019 John Wiley & Sons, Inc. 38

 6. Linear Trend Line
 xy - nxy
y = a + bx b =
 x2 - nx2
where a = y-bx
a = intercept
where
b = slope of the line n = number of periods
x = time period
y = forecast for x
x = = mean of the x values
demand for period x n
y
y = n = mean of the y values

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Least Squares Example
x(PERIOD) y(DEMAND) xy x2
1 37
2 40
3 41
Example 12.5 4 37
5 45
6 50
7 43
8 47
9 56
10 52
11 55
12 54

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Least Squares Example
x(PERIOD) y(DEMAND) xy x2
1 37 37 1
2 40 80 4
3 41 123 9
4 37 148 16
5 45 225 25
6 50 300 36
7 43 301 49
8 47 376 64
9 56 504 81
10 52 520 100
11 55 605 121
12 54 648 144
78 557 3867 650

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Least Squares Example
x =

y =

b = xy - nxy=
x2 - nx2

a = y - bx
 Least Squares Example
x = 78 = 6.5
12
y = 557= 46.42
12
b = xy - nxy= = 3867 - (12)(6.5)(46.42) =1.72
x2 - nx2 650 - 12(6.5)2

a = y - bx
= 46.42 - (1.72)(6.5) = 35.2

y = 35.2 + 1.72x

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Linear trend line y = 35.2 + 1.72x
Forecast for period 13 y = 35.2 + 1.72(13) = 57.56 units

Copyright ©2019 John Wiley & Sons, Inc. 44

7. Seasonal Adjustments
• Repetitive increase/ decrease in demand
• Use seasonal factor to adjust forecasts

Di
Seasonal factor = Si =
D

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Seasonal Adjustment
DEMAND (1000’S PER QUARTER)
YEAR 1 2 3 4 Total
2018 12.6 8.6 6.3 17.5 45.0
2019 14.1 10.3 7.5 18.2 50.1
Example 12.6 2020 15.3 10.6 8.1 19.6 53.6
2021 ? ? ? ?
-----------------------------------------------------------------------------------
Total 42.0 29.5 21.9 55.3 148.7

Example:
Find the demand for each quarter for next year

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Seasonal Adjustment
DEMAND (1000’S PER QUARTER)
YEAR 1 2 3 4 Total
2018 12.6 8.6 6.3 17.5 45.0
2019 14.1 10.3 7.5 18.2 50.1
Example 12.6 2020 15.3 10.6 8.1 19.6 53.6
2021 ? ? ? ?
-----------------------------------------------------------------------------------
Total 42.0 29.5 21.9 55.3 148.7

First, we can calculate the seasonal factors:

𝐷1 42.0 𝐷3 21.9
𝑆1 = = = 0.28 𝑆3 = = = 0.15
σ 𝐷 148.7 σ 𝐷 148.7

𝐷2 29.5 𝐷4 55.3
𝑆2 = = = 0.20 𝑆4 = = = 0.37
σ 𝐷 148.7 σ 𝐷 148.7

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Seasonal Adjustment
DEMAND (1000’S PER QUARTER)
YEAR 1 2 3 4 Total
2018 12.6 8.6 6.3 17.5 45.0
2019 14.1 10.3 7.5 18.2 50.1
Example 12.6 2020 15.3 10.6 8.1 19.6 53.6
2021 ? ? ? ?
-----------------------------------------------------------------------------------
Total 42.0 29.5 21.9 55.3 148.7

Then, we need the forecast of the next year:

𝑦 = 40.97 + 4.30𝑥
𝑦 = 40.97 + 4.30(4)
𝑦 = 58.17

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Seasonal Adjustment
DEMAND (1000’S PER QUARTER)
YEAR 1 2 3 4 Total
2018 12.6 8.6 6.3 17.5 45.0
2019 14.1 10.3 7.5 18.2 50.1
Example 12.6 2020 15.3 10.6 8.1 19.6 53.6
2021 ? ? ? ?
-----------------------------------------------------------------------------------
Total 42.0 29.5 21.9 55.3 148.7

Finally, we adjust the forecast using the seasonal factors:

𝑆𝐹1 = (𝑆1 ) 𝐹 = (0.28) 58.17 = 16.28

𝑆𝐹2 = (𝑆2 ) 𝐹 = (0.20) 58.17 = 11.63
𝑆𝐹3 = (𝑆3 ) 𝐹 = (0.15) 58.17 = 8.73
𝑆𝐹4 = (𝑆4 ) 𝐹 = (0.37) 58.17 = 21.53
 Seasonal Adjustment
DEMAND (1000’S PER QUARTER)
YEAR 1 2 3 4 Total
2018 12.6 8.6 6.3 17.5 45.0
2019 14.1 10.3 7.5 18.2 50.1
Example 12.6 2020 15.3 10.6 8.1 19.6 53.6
2021 16.28 11.63 8.73 21.53 58.17
-----------------------------------------------------------------------------------
Total 42.0 29.5 21.9 55.3 148.7

SF stands for ‘seasonally adjusted forecast’

𝑆𝐹1 = 16.28
𝑆𝐹2 = 11.63 𝐹 = 58.17
𝑆𝐹3 = 8.73
𝑆𝐹4 = 21.53
Forecast Accuracy

• Forecast error: difference between actual demand

and forecast
et = Dt - Ft
• Measures:
• MAD: mean absolute deviation (error)
• MAPD: mean absolute percent deviation
• Cumulative error E
• Average error or bias E

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Mean Absolute Deviation (MAD)
 Dt - Ft 
MAD = n
where
t = period number
Dt = demand in period t
Ft = forecast for period t
n = total number of periods
  = absolute value

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 MAD Example
PERIOD DEMAND, Dt Ft ( =0.3) (Dt - Ft) |Dt - Ft|
1 37 37.00 – –
2 40 37.00
3 41 37.90
Example 12.7
4 37 38.83
5 45 38.28
6 50 40.29
7 43 43.20
8 47 43.14
9 56 44.30
10 52 47.81
11 55 49.06
12 54 50.84

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 MAD Example
PERIOD DEMAND, Dt Ft ( =0.3) (Dt - Ft) |Dt - Ft|
1 37 37.00 – –
2 40 37.00 3.00 3.00
3 41 37.90 3.10 3.10
4 37 38.83 -1.83 1.83
5 45 38.28 6.72 6.72
6 50 40.29 9.69 9.69
7 43 43.20 -0.20 0.20
8 47 43.14 3.86 3.86
9 56 44.30 11.70 11.70
10 52 47.81 4.19 4.19
11 55 49.06 5.94 5.94
12 54 50.84 3.15 3.15
557 49.31 53.39

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

MAD Calculation

 Dt - Ft 
MAD = n

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

MAD Calculation

 Dt - Ft 
MAD = n
53.39
=
11
= 4.85

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Other Accuracy Measures
Mean absolute percent deviation (MAPD):
|Dt - Ft|
MAPD =
 Dt

Cumulative error: E = et

et
Average error: Ē=
n

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

 Comparison of Forecasts
FORECAST MAD MAPD E Ē
Exponential smoothing ( = 0.30) 4.85 9.6% 49.31 4.48
Exponential smoothing ( = 0.50) 4.04 8.5% 33.21 3.02
Adjusted exponential smoothing 3.81 7.5% 21.14 1.92
( = 0.50,  = 0.30)
Linear trend line 2.29 4.9% – –

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Time Series Forecasting Using Excel

• Excel can be used to develop forecasts:

• Moving average
• Exponential smoothing
• Adjusted exponential smoothing
• Linear trend line

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Exponentially Smoothed and Adjusted
Exponentially Smoothed Forecasts
=B5*(C11-C10)+ (1-B5)*D10

=C10+D10

=ABS(B10-E10)

=SUM(F10:F20)

=G22/11
© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e
Demand and Exponentially Smoothed Forecast

Click on “Insert” then “Line”

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Data Analysis and Exponential Smoothing Windows

Copyright ©2019 John Wiley & Sons, Inc. 62

Forecasting With Seasonal Adjustment

© 2014 John Wiley & Sons, Inc. - Russell and Taylor 8e

Thank you!