Methods of Analysis

emmgmo

unitviii_chapter5presentation.pdf

Home >Business & Finance homework help >Management homework help >Methods of Analysis

Forecasting

After completing this chapter, students will be able to:

LEARNING OBJECTIVES

1. Understand and know when to use various families of forecasting models.

2. Compare moving averages, exponential smoothing, and other time-series models.

3. Seasonally adjust data.

4. Understand Delphi and other qualitative decision- making approaches.

5. Compute a variety of error measures.

5.1 Introduction

5.2 Types of Forecasting Models

5.3 Components of a Time Series

5.4 Measures of Forecast Accuracy

5.5 Forecasting Models – Random Variations Only

5.6 Forecasting Models – Trend and Random Variations

5.7 Adjusting for Seasonal Variations

5.8 Forecasting Models – Trend, Seasonal, and Random Variations

5.9 Monitoring and Controlling Forecasts

CHAPTER OUTLINE

Introduction

• Main purpose of forecasting – Reduce uncertainty and make better

estimates of what will happen in the future

• Subjective methods – Seat-of-the pants methods, intuition,

experience

• More formal quantitative and qualitative techniques

Regression Analysis

Multiple Regression

Moving Average

Exponential Smoothing

Trend Projections

Decomposition

Delphi Methods

Jury of Executive Opinion

Sales Force Composite

Consumer Market Survey

Time-Series Methods

Qualitative Models

Causal Methods

Forecasting Models

Forecasting Techniques

FIGURE 5.1

Qualitative Models

• Incorporate judgmental or subjective factors – Useful when subjective factors are

important or accurate quantitative data is difficult to obtain

• Common qualitative techniques 1. Delphi method

2. Jury of executive opinion

3. Sales force composite

4. Consumer market surveys

Qualitative Models

• Delphi Method – Iterative group process

– Respondents provide input to decision makers

– Repeated until consensus is reached

• Jury of Executive Opinion – Collects opinions of a small group of high-

level managers

– May use statistical models for analysis

Qualitative Models

• Sales Force Composite – Allows individual salespersons estimates

– Reviewed for reasonableness

– Data is compiled at a district or national level

• Consumer Market Survey – Information on purchasing plans solicited

from customers or potential customers

– Used in forecasting, product design, new product planning

Time-Series Models

• Predict the future based on the past

• Uses only historical data on one variable

• Extrapolations of past values of a series

• Ignores factors such as – Economy

– Competition

– Selling price

Components of a Time Series

• Sequence of values recorded at successive intervals of time

• Four possible components – Trend (T)

– Seasonal (S)

– Cyclical (C)

– Random (R)

Components of a Time Series

Series 4: Trend, Seasonal and Random Variations

Series 3: Trend and Random Variations

Series 2: Seasonal Variations Only

Series 1: Random Variations Only

S a le

| | | | | | | | | | | | | | | |

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Time Period (Quarters)

FIGURE 5.2 –

Scatter Diagram for

Four Time Series

of Quarterly Data

Components of a Time Series

S a le

| | | | | | | | | |

0 2 4 6 8 10 12 14 16 18

Time Period (Years)

FIGURE 5.3 – Scatter Diagram of Times Series with Cyclical and Random Components

Time-Series Models

• Two basic forms – Multiplicative

Demand = T x S x C x R

– Additive

Demand = T + S + C + R

– Combinations are possible

Measures of Forecast Accuracy

• Compare forecasted values with actual values – See how well one model works

– To compare models

Forecast error = Actual value – Forecast value

MAD = forecast errorå

• Measure of accuracy – Mean absolute deviation (MAD):

Measures of Forecast Accuracy

YEAR

ACTUAL SALES OF WIRELESS SPEAKERS

FORECAST SALES

ABSOLUTE VALUE OF ERRORS (DEVIATION), (ACTUAL – FORECAST)

1 110 —

2 100 110

3 120 100

4 140 120

5 170 140

6 150 170

7 160 150

8 190 160

9 200 190

10 190 200

11 — 190

TABLE 5.1 – Computing the Mean Absolute Deviation (MAD)

Measures of Forecast Accuracy

YEAR

ACTUAL SALES OF WIRELESS SPEAKERS

FORECAST SALES

ABSOLUTE VALUE OF ERRORS (DEVIATION), (ACTUAL – FORECAST)

1 110 —

2 100 110

3 120 100

4 140 120

5 170 140

6 150 170

7 160 150

8 190 160

9 200 190

10 190 200

11 — 190

TABLE 5.1 – Computing the Mean Absolute Deviation (MAD)

• Forecast based on naïve model

• No attempt to adjust for time series components

YEAR

ACTUAL SALES OF WIRELESS SPEAKERS

FORECAST SALES

ABSOLUTE VALUE OF ERRORS (DEVIATION), (ACTUAL – FORECAST)

1 110 —

2 100 110

3 120 100

4 140 120

5 170 140

6 150 170

7 160 150

8 190 160

9 200 190

10 190 200

11 — 190

—

|100 – 110| = 10

|120 – 110| = 20

|140 – 120| = 20

|170 – 140| = 30

|150 – 170| = 20

|160 – 150| = 10

|190 – 160| = 30

|200 – 190| = 10

|190 – 200| = 10

—

Sum of |errors| = 160

MAD = 160/9 = 17.8

Measures of Forecast Accuracy

TABLE 5.1 – Computing the Mean Absolute Deviation (MAD)

MAD = forecast errorå

n =

160

9 = 17.8

Measures of Forecast Accuracy

• Other common measures – Mean squared error (MSE)

– Mean absolute percent error (MAPE)

– Bias is the average error

MAPE =

error

actual å

n 100%

MSE = (error)

2 å

Forecasting Random Variations

• No other components are present

• Averaging techniques smooth out forecasts – Moving averages

– Weighted moving averages

– Exponential smoothing

Moving Averages

• Used when demand is relatively steady over time – The next forecast is the average of the

most recent n data values from the time series

– Smooths out short-term irregularities in the data series

Moving average forecast =

Sum of demands in previous n periods

Moving Averages

• Mathematically

F t+1

= Y t +Y

t-1 +... +Y

t-n+1

where

Ft+1 = forecast for time period t + 1

Yt = actual value in time period t

n = number of periods to average

Wallace Garden Supply

• Wallace Garden Supply wants to forecast demand for its Storage Shed – Collected data for the past year

– Use a three-month moving average (n = 3)

Wallace Garden Supply TABLE 5.2

MONTH ACTUAL SHED SALES 3-MONTH MOVING AVERAGE

January 10

February 12

March 13

April 16

May 19

June 23

July 26

August 30

September 28

October 18

November 16

December 14

January —

(12 + 13 + 16)/3 = 13.67

(13 + 16 + 19)/3 = 16.00

(16 + 19 + 23)/3 = 19.33

(19 + 23 + 26)/3 = 22.67

(23 + 26 + 30)/3 = 26.33

(26 + 30 + 28)/3 = 28.00

(30 + 28 + 18)/3 = 25.33

(28 + 18 + 16)/3 = 20.67

(18 + 16 + 14)/3 = 16.00

(10 + 12 + 13)/3 = 11.67

Weighted Moving Averages

• Weighted moving averages use weights to put more emphasis on previous periods – Often used when a trend or other pattern is

emerging

– Mathematically

F t+1

= (Weight in period i)(Actual value in period)å

(Weights)å

F t+1

= w

1 Y t +w

2 Y t-1

+... +w n Y t-n+1

w 1 +w

2 +... +w

where

wi = weight for the i th observation

Wallace Garden Supply

• Use a 3-month weighted moving average model to forecast demand – Weighting scheme

5 – 25

WEIGHTS APPLIED PERIOD

3 Last month

2 Two months ago

1 Three months ago

3 x Sales last month + 2 x Sales two months ago + 1 X Sales three months ago

Sum of the weights

Wallace Garden Supply TABLE 5.3

MONTH ACTUAL SHED SALES 3-MONTH WEIGHTED

MOVING AVERAGE

January 10

February 12

March 13

April 16

May 19

June 23

July 26

August 30

September 28

October 18

November 16

December 14

January —

[(3 X 13) + (2 X 12) + (10)]/6 = 12.17

[(3 X 16) + (2 X 13) + (12)]/6 = 14.33

[(3 X 19) + (2 X 16) + (13)]/6 = 17.00

[(3 X 23) + (2 X 19) + (16)]/6 = 20.50

[(3 X 26) + (2 X 23) + (19)]/6 = 23.83

[(3 X 30) + (2 X 26) + (23)]/6 = 27.50

[(3 X 28) + (2 X 30) + (26)]/6 = 28.33

[(3 X 18) + (2 X 28) + (30)]/6 = 23.33

[(3 X 16) + (2 X 18) + (28)]/6 = 18.67

[(3 X 14) + (2 X 16) + (18)]/6 = 15.33

Exponential Smoothing

• Exponential smoothing – A type of moving average

– Easy to use

– Requires little record keeping of data

New forecast = Last period’s forecast

+ (Last period’s actual demand

– Last period’s forecast)

 is a weight (or smoothing constant) with a

value 0 ≤  ≤ 1

Exponential Smoothing

• Mathematically

F t+1

= F t +a(Y

t -F

t )

where

Ft+1 = new forecast (for time period t + 1)

Yt = pervious forecast (for time period t)

 = smoothing constant (0 ≤  ≤ 1)

Yt = pervious period’s actual demand

The idea is simple – the new estimate is the old estimate plus some fraction of the error in the last period

Exponential Smoothing Example

• In January, February’s demand for a certain car model was predicted to be 142

• Actual February demand was 153 autos

• Using a smoothing constant of  = 0.20, what is the forecast for March?

New forecast (for March demand) = 142 + 0.2(153 – 142)

= 144.2 or 144 autos

New forecast (for April demand) = 144.2 + 0.2(136 – 144.2)

= 142.6 or 143 autos

• If actual March demand = 136

Selecting the Smoothing

Constant

• Selecting the appropriate value for  is key to obtaining a good forecast

• The objective is always to generate an accurate forecast

• The general approach is to develop trial forecasts with different values of  and select the  that results in the lowest MAD

Port of Baltimore Example

QUARTER

ACTUAL TONNAGE

UNLOADED FORECAST

USING  = 0.10

FORECAST USING  = 0.50

1 180 175 175

2 168 175.5 = 175.00 + 0.10(180 – 175) 177.5

3 159 174.75 = 175.50 + 0.10(168 – 175.50) 172.75

4 175 173.18 = 174.75 + 0.10(159 – 174.75) 165.88

5 190 173.36 = 173.18 + 0.10(175 – 173.18) 170.44

6 205 175.02 = 173.36 + 0.10(190 – 173.36) 180.22

7 180 178.02 = 175.02 + 0.10(205 – 175.02) 192.61

8 182 178.22 = 178.02 + 0.10(180 – 178.02) 186.30

9 ? 178.60 = 178.22 + 0.10(182 – 178.22) 184.15

TABLE 5.4 – Exponential Smoothing Forecast for  = 0.1 and  = 0.5

Port of Baltimore Example TABLE 5.5 – Absolute Deviations and MADs

QUARTER

ACTUAL TONNAGE

UNLOADED FORECAST

WITH  = 0.10

ABSOLUTE DEVIATIONS FOR  = 0.10

FORECAST WITH  = 0.50

ABSOLUTE DEVIATIONS FOR  = 0.50

1 180 175 5 175 5….

2 168 175.5 7.5.. 177.5 9.5..

3 159 174.75 15.75 172.75 13.75

4 175 173.18 1.82 165.88 9.12

5 190 173.36 16.64 170.44 19.56

6 205 175.02 29.98 180.22 24.78

7 180 178.02 1.98 192.61 12.61

8 182 178.22 3.78 186.30 4.3..

Sum of absolute deviations 82.45 98.63

MAD = Σ|deviations|

= 10.31 MAD = 12.33 n

Best choice

Using Software

PROGRAM 5.1A – Selecting the Forecasting Model

Using Software

PROGRAM 5.1B – Initializing Excel QM

Using Software

PROGRAM 5.1C – Excel QM Output

Using Software

PROGRAM 5.2A – Selecting Time-Series Analysis in QM for Windows

Using Software

PROGRAM 5.2B – Entering Data

Using Software

PROGRAM 5.2C – Selecting the Model and Entering Data

Using Software

PROGRAM 5.2D – Output for Port of Baltimore Example

Forecasting – Trend and Random

• Exponential smoothing does not respond to trends

• A more complex model can be used

• The basic approach – Develop an exponential smoothing forecast

– Adjust it for the trend

Forecast including

trend (FITt+1)

Smoothed forecast (Ft+1)

+ Smoothed Trend (Tt+1) =

Exponential Smoothing

with Trend

• The equation for the trend correction uses a new smoothing constant 

• Ft and Tt must be given or estimated

• Three steps in developing FITt

Step 1: Compute smoothed forecast Ft+1

Smoothed

forecast =

Previous forecast

including trend + (Last error)

F t+1

= FIT t +a(Y

t -FIT

t )

Exponential Smoothing

with Trend

Step 2: Update the trend (Tt +1) using

T t+1

=T t + b(F

t+1 -FIT

t )

Smoothed

forecast =

Previous forecast

including trend

(Error or

excess in trend) +

Step 3: Calculate the trend-adjusted exponential smoothing forecast (FITt +1) using

Forecast including

trend (FITt+1) = +

Smoothed

forecast (Ft+1)

Smoothed

trend (Tt+1)

FIT t+1

= F t+1

+T t+1

Selecting a Smoothing Constant

• A high value of  makes the forecast more responsive to changes in trend

• A low value of  gives less weight to the recent trend and tends to smooth out the trend

• Values are often selected using a trial-and- error approach based on the value of the MAD for different values of 

Midwestern Manufacturing

• Demand for electrical generators from 2007 – 2013

– Midwest assumes F1 is perfect, T1 = 0,  = 0.3,  = 0.4

YEAR ELECTRICAL GENERATORS SOLD

2007 74

2008 79

2009 80

2010 90

2011 105

2012 142

2013 122

TABLE 5.6 –

Demand

FIT 1 = F

1 +T

1 = 74 + 0 = 74

Midwestern Manufacturing

For 2008 (time period 2)

Step 1: Compute Ft+1

F2 = FIT1 + (Y1 – FIT1)

= 74 + 0.3(74 – 74) = 74

Step 2: Update the trend

T2 = T1 + (F2 – FIT1)

= 0 + .4(74 – 74) = 0

Midwestern Manufacturing

Step 3: Calculate the trend-adjusted

exponential smoothing forecast (Ft+1) using

FIT2 = F2 + T2 = 74 + 0 = 74

Midwestern Manufacturing

For 2009 (time period 3)

Step 1: F3 = FIT2 + (Y2 – FIT2)

= 74 + 0.3(79 – 74) = 75.5

Step 2: T3 = T2 + .4(F3 – FIT2)

= 0 + .4(75.5 – 74) = 0.6

Step 3: FIT3 = F3 + T3

= 75.5 + 0.6 = 76.1

Midwestern Manufacturing

TIME

(t)

DEMAND

(Yt) Ft+1 = FITt + 0.3(Yt – FITt) Tt+1 = Tt + 0.4(Ft+1 – FITt) FITt+1 = Ft+1 + Tt+1

1 74 74 0 74

2 79 74

= 74 + 0.3(74 – 74)

= 0 + 0.4(74 – 74)

= 74 + 0

3 80 75.5

= 74 + 0.3(79 – 74)

0.6

= 0 + 0.4(75.5 – 74)

76.1

= 75.5 + 0.6

4 90 77.270

= 76.1 + 0.3(80 – 76.1)

1.068

= 0.6 + 0.4(77.27 – 76.1)

78.338

= 77.270 + 1.068

5 105 81.837

= 78.338 + 0.3(90 – 78.338)

2.468

= 1.068 + 0.4(81.837 – 78.338)

84.305

= 81.837 + 2.468

6 142 90.514

= 84.305 + 0.3(105 – 84.305)

4.952

= 2.468 + 0.4(90.514 – 84.305)

95.466

= 90.514 + 4.952

7 122 109.426

= 95.446 + 0.3(142 – 95.466)

10.536

= 4.952 + 0.4(109.426 – 95.466)

119.962

= 109.426 + 10.536

8 120.573

= 119.962 + 0.3(122 – 119.962)

10.780

= 10.536 + 0.4(120.573 – 119.962)

131.353

= 120.573 + 10.780

TABLE 5.7 – Exponential Smoothing with Trend Forecasts

Midwestern Manufacturing PROGRAM 5.3 – Output from Excel QM Trend-Adjusted Exponential Smoothing

Trend Projections

• Fits a trend line to a series of historical data points

• Projected into the future for medium- to long-range forecasts

• Trend equations can be developed based on exponential or quadratic models

• Linear model developed using regression analysis is simplest

Trend Projections

• Mathematical formula

Ŷ = b 0 +b

1 X

where

= predicted value

b0 = intercept

b1 = slope of the line

X = time period (i.e., X = 1, 2, 3, …, n)

Ŷ

Midwestern Manufacturing

• Based on least squares regression, the forecast equation is

Ŷ = 56.71+10.54X

(sales in 2014) = 56.71 + 10.54(8)

= 141.03, or 141 generators

(sales in 2015) = 56.71 + 10.54(9)

= 151.57, or 152 generators

• Year 2014 is coded as X = 8

• For X = 9

Midwestern Manufacturing

PROGRAM 5.4 – Output from Excel QM for Trend Line

Midwestern Manufacturing

PROGRAM 5.5 – Output from QM for Trend Line

Midwestern Manufacturing

FIGURE 5.4 – Generator Demand Based on Trend Line

x x

| | | | | | | | | | | |

0 1 2 3 4 5 6 7 8 9 10 11

180 –

160 –

140 –

120 –

100 –

80 –

60 –

40 –

20 –

0 –

G e n e ra

to r

D e m

a n d

Time Period

Projected demand for

next 3 years is shown on

the trend line

Trend Line

Ŷ = 56.71+10.54X

Seasonal Variations

• Recurring variations over time may indicate the need for seasonal adjustments in the trend line

• A seasonal index indicates how a particular season compares with an average season – An index of 1 indicates an average season

– An index > 1 indicates the season is higher than average

– An index < 1 indicates a season lower than average

Seasonal Indices

• Deseasonalized data is created by dividing each observation by the appropriate seasonal index

• Once deseasonalized forecasts have been developed, values are multiplied by the seasonal indices

• Computed in two ways – Overall average

– Centered-moving-average approach

Seasonal Indices with No Trend

• Divide average value for each season by the average of all data

– Telephone answering machines at Eichler Supplies

– Sales data for the past two years for one model

– Create a forecast that includes seasonality

Seasonal Indices with No Trend

MONTH SALES DEMAND

AVERAGE 2- YEAR DEMAND

MONTHLY DEMAND

AVERAGE SEASONAL

INDEXYEAR 1 YEAR 2

January 80 100 90 94 0.957

February 85 75 80 94 0.851

March 80 90 85 94 0.904

April 110 90 100 94 1.064

May 115 131 123 94 1.309

June 120 110 115 94 1.223

July 100 110 105 94 1.117

August 110 90 100 94 1.064

September 85 95 90 94 0.957

October 75 85 80 94 0.851

November 85 75 80 94 0.851

December 80 80 80 94 0.851

Total average demand = 1,128

Seasonal index = Average 2-year demand

Average monthly demandAverage monthly demand = = 94 1,128

12 months

TABLE 5.8 – Answering Machine Sales and Seasonal Indices

Seasonal Indices with No Trend • Calculations for the seasonal indices

Jan. July 1,200

12 ´ 0.957 = 96

1,200

12 ´ 1.117 = 112

Feb. Aug. 1,200

12 ´ 0.851 = 85

1,200

12 ´ 1.064 = 106

Mar. Sept. 1,200

12 ´ 0.904 = 90

1,200

12 ´ 0.957 = 96

Apr. Oct. 1,200

12 ´ 1.064 = 106

1,200

12 ´ 0.851 = 85

May Nov. 1,200

12 ´ 1.309 = 131

1,200

12 ´ 0.851 = 85

June Dec. 1,200

12 ´ 1.223 = 122

1,200

12 ´ 0.851 = 85

Seasonal Indices with Trend

• Changes could be due to trend, seasonal, or random

• Centered moving average (CMA) approach prevents trend being interpreted as seasonal

• Turner Industries sales contain both trend and seasonal components

Seasonal Indices with Trend

• Steps in CMA 1. Compute the CMA for each observation (where

possible)

2. Compute the seasonal ratio = Observation/CMA for that observation

3. Average seasonal ratios to get seasonal indices

4. If seasonal indices do not add to the number of seasons, multiply each index by (Number of seasons)/(Sum of indices)

Turner Industries

QUARTER YEAR 1 YEAR 2 YEAR 3 AVERAGE

1 108 116 123 115.67

2 125 134 142 133.67

3 150 159 168 159.00

4 141 152 165 152.67

Average 131.00 140.25 149.50 140.25

TABLE 5.9 – Quarterly Sales Data

Definite trend Seasonal pattern

Turner Industries

• To calculate the CMA for quarter 3 of year 1, compare the actual sales with an average quarter centered on that time period

• Use 1.5 quarters before quarter 3 and 1.5 quarters after quarter 3 – Take quarters 2, 3, and 4 and one half of quarters

1, year 1 and quarter 1, year 2

CMA(q3, y1) = 0.5(108) + 125 + 150 + 141 + 0.5(116)

4 = 132.0

Turner Industries

• Compare the actual sales in quarter 3 to the CMA to find the seasonal ratio

Seasonal ratio = Sales in quarter 3

CMA =

150

132.0 = 1.136

Turner Industries TABLE 5.10 – Centered Moving Averages and Seasonal Ratios

YEAR QUARTER SALES CMA SEASONAL RATIO

1 1 108

2 125

3 150 132.000 1.136

4 141 134.125 1.051

2 1 116 136.375 0.851

2 134 138.875 0.965

3 159 141.125 1.127

4 152 143.000 1.063

3 1 123 145.125 0.848

2 142 147.875 0.960

3 168

4 165

Turner Industries

• The two seasonal ratios for each quarter are averaged to get the seasonal index

Index for quarter 1 = I1 = (0.851 + 0.848)/2 = 0.85

Index for quarter 2 = I2 = (0.965 + 0.960)/2 = 0.96

Index for quarter 3 = I3 = (1.136 + 1.127)/2 = 1.13

Index for quarter 4 = I4 = (1.051 + 1.063)/2 = 1.06

 



 



 

Turner Industries

• Scatterplot of Turner Industries Sales Data and Centered Moving Average

CMA

Original Sales Figures

200 –

150 –

100 –

50 –

0 –

S a

le s

| | | | | | | | | | | |

1 2 3 4 5 6 7 8 9 10 11 12

Time Period

Trend, Seasonal, and Random

Variations

• Decomposition – isolating linear trend and seasonal factors to develop more accurate forecasts

• Five steps to decomposition – Compute seasonal indices using CMAs.

– Deseasonalize the data by dividing each number by its seasonal index

– Find the equation of a trend line using the deseasonalized data

– Forecast for future periods using the trend line

– Multiply the trend line forecast by the appropriate seasonal index

Deseasonalized Data

SALES ($1,000,000s)

SEASONAL INDEX

DESEASONALIZED SALES ($1,000,000s)

108 0.85 127.059

125 0.96 130.208

150 1.13 132.743

141 1.06 133.019

116 0.85 136.471

134 0.96 139.583

159 1.13 140.708

152 1.06 143.396

123 0.85 144.706

142 0.96 147.917

168 1.13 148.673

165 1.06 155.660

TABLE 5.11

Deseasonalized Data

• Find a trend line using the deseasonalized data where X = time

b1 = 2.34 b0 = 124.78

Ŷ = 124.78 + 2.34X

• Develop a forecast for quarter 1, year 4 (X = 13) using this trend and multiply the forecast by the appropriate seasonal index

Ŷ = 124.78 + 2.34(13)

= 155.2 (before seasonality adjustment)

• Find a trend line using the deseasonalized data where X = time

b1 = 2.34 b0 = 124.78

Deseasonalized Data

Ŷ = 124.78 + 2.34X

• Develop a forecast for quarter 1, year 4 (X = 13) using this trend and multiply the forecast by the appropriate seasonal index

Ŷ = 124.78 + 2.34(13)

= 155.2 (before seasonality)

Including the seasonal index

Ŷ ´I 1 = 155.2 ´ 0.85 = 131.92

200 –

150 –

100 –

50 –

0 –

S a

le s

| | | | | | | | | | | | | |

0 1 2 3 4 5 6 7 8 9 10 11 12 13

Time Period (Quarters)

Deseasonalized Data

FIGURE 5.5

xx x

x x

x x xx

x x x

x Deseasonalized Sales Data

Sales Data

Using Software

PROGRAM 5.6A – QM for Windows Input

Using Software

PROGRAM 5.6B – QM for Windows Output

Using Regression with Trend

and Seasonal

• Multiple regression can be used to forecast both trend and seasonal components – One independent variable is time

– Dummy independent variables are used to represent the seasons

– An additive decomposition model

Ŷ = a+b 1 X

1 +b

2 X

2 +b

3 X

3 +b

4 X

where

X1 = time period

X2 = 1 if quarter 2, 0 otherwise

X3 = 1 if quarter 3, 0 otherwise

X4 = 1 if quarter 4, 0 otherwise

Using Regression with Trend

and Seasonal

PROGRAM 5.7A – Excel QM Multiple Regression Initialization

Using Regression with Trend

and Seasonal

PROGRAM 5.7B –

Excel QM Multiple

Regression Output

Using Regression with Trend

and Seasonal

• Regression equation

Ŷ = 104.1 + 2.3X 1 + 15.7X

2 + 38.7X

3 + 30.1X

• Forecasts for first two quarters next year

Ŷ = 104.1 + 2.3(13) + 15.7(0) + 38.7(0) + 30.1(0) = 134

Ŷ = 104.1 + 2.3(14) + 15.7(1) + 38.7(0) + 30.1(0) = 152

• Regression equation

Using Regression with Trend

and Seasonal

Ŷ = 104.1 + 2.3X 1 + 15.7X

2 + 38.7X

3 + 30.1X

• Forecasts for first two quarters next year

Ŷ = 104.1 + 2.3(13) + 15.7(0) + 38.7(0) + 30.1(0) = 134

Ŷ = 104.1 + 2.3(14) + 15.7(1) + 38.7(0) + 30.1(0) = 152

• Different from the results using the multiplicative decomposition method

• Use MAD or MSE to determine the best model

Monitoring and Controlling

Forecasts

• Tracking signal measures how well a forecast predicts actual values – Running sum of forecast errors (RSFE) divided

by the MAD

Tracking signal = RSFE

MAD

= (forecast error)å

MAD

MAD = forecast errorå

Monitoring and Controlling

Forecasts

• Positive tracking signals indicate demand is greater than forecast

• Negative tracking signals indicate demand is less than forecast

• A good forecast will have about as much positive error as negative error

• Problems are indicated when the signal trips either the upper or lower predetermined limits

• Choose reasonable values for the limits

Monitoring and Controlling

Forecasts

Acceptable Range

Signal Tripped

Upper Control Limit

Lower Control Limit

0 MADs

–

Time

Tracking Signal

FIGURE 5.7 – Plot of Tracking Signals

Kimball’s Bakery Example • Quarterly sales of croissants (in thousands)

TIME PERIOD

FORECAST DEMAND

ACTUAL DEMAND ERROR RSFE

|FORECAST | | ERROR |

CUMULATIVE ERROR MAD

TRACKING SIGNAL

1 100 90 –10 –10 10 10 10.0 –1

2 100 95 –5 –15 5 15 7.5 –2

3 100 115 +15 0 15 30 10.0 0

4 110 100 –10 –10 10 40 10.0 –1

5 110 125 +15 +5 15 55 11.0 +0.5

6 110 140 +30 +35 35 85 14.2 +2.5

MAD = forecast errorå

n =

6 = 14.2

Tracking signal = RSFE

MAD =

14.2 = 2.5 MADs

For Period 6:

Adaptive Smoothing

• Computer monitoring of tracking signals and self-adjustment if a limit is tripped

• In exponential smoothing, the values of  and  are adjusted when the computer detects an excessive amount of variation

reproduced, stored in a retrieval 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.