online test chapters

ahlam6248

rsh_qam11_ch05.ppt

Home >Mathematics homework help >Statistics homework help >online test chapters

Chapter 5

To accompany
Quantitative Analysis for Management, Eleventh Edition, by Render, Stair, and Hanna

Power Point slides created by Brian Peterson

Forecasting

Learning Objectives

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

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

Seasonally adjust data.

Understand Delphi and other qualitative decision-making approaches.

Compute a variety of error measures.

After completing this chapter, students will be able to:

Chapter Outline

5.1 Introduction

5.2 Types of Forecasts

5.3 Scatter Diagrams and Time Series

5.4 Measures of Forecast Accuracy

5.5 Time-Series Forecasting Models

5.6 Monitoring and Controlling Forecasts

Introduction

Managers are always trying to reduce uncertainty and make better estimates of what will happen in the future.
This is the main purpose of forecasting.
Some firms use subjective methods: seat-of-the pants methods, intuition, experience.
There are also several quantitative techniques, including:
Moving averages
Exponential smoothing
Trend projections
Least squares regression analysis

Introduction

Eight steps to forecasting:

Determine the use of the forecast—what objective are we trying to obtain?

Select the items or quantities that are to be forecasted.

Determine the time horizon of the forecast.

Select the forecasting model or models.

Gather the data needed to make the forecast.

Validate the forecasting model.

Make the forecast.

Implement the results.

Introduction

These steps are a systematic way of initiating, designing, and implementing a forecasting system.
When used regularly over time, data is collected routinely and calculations performed automatically.
There is seldom one superior forecasting system.
Different organizations may use different techniques.
Whatever tool works best for a firm is the one that should be used.

Forecasting Models

Forecasting Techniques

Figure 5.1

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

Qualitative Models

Qualitative models incorporate judgmental or subjective factors.

These are useful when subjective factors are thought to be important or when accurate quantitative data is difficult to obtain.

Common qualitative techniques are:

Delphi method.

Jury of executive opinion.

Sales force composite.

Consumer market surveys.

Qualitative Models

Delphi Method – This is an iterative group process where (possibly geographically dispersed) respondents provide input to decision makers.
Jury of Executive Opinion – This method collects opinions of a small group of high-level managers, possibly using statistical models for analysis.
Sales Force Composite – This allows individual salespersons estimate the sales in their region and the data is compiled at a district or national level.
Consumer Market Survey – Input is solicited from customers or potential customers regarding their purchasing plans.

Time-Series Models

Time-series models attempt to predict the future based on the past.
Common time-series models are:
Moving average.
Exponential smoothing.
Trend projections.
Decomposition.
Regression analysis is used in trend projections and one type of decomposition model.

Causal Models

Causal models use variables or factors that might influence the quantity being forecasted.
The objective is to build a model with the best statistical relationship between the variable being forecast and the independent variables.
Regression analysis is the most common technique used in causal modeling.

Scatter Diagrams

Wacker Distributors wants to forecast sales for three different products (annual sales in the table, in units):

Table 5.1

YEAR	TELEVISION SETS	RADIOS	COMPACT DISC PLAYERS
1	250	300	110
2	250	310	100
3	250	320	120
4	250	330	140
5	250	340	170
6	250	350	150
7	250	360	160
8	250	370	190
9	250	380	200
10	250	390	190

Scatter Diagram for TVs

Figure 5.2a

Sales appear to be constant over time

Sales = 250

A good estimate of sales in year 11 is 250 televisions



330 –

250 –

200 –

150 –

100 –

50 –

| | | | | | | | | |

0 1 2 3 4 5 6 7 8 9 10

Time (Years)

Annual Sales of Televisions

(a)

Scatter Diagram for Radios

Sales appear to be increasing at a constant rate of 10 radios per year

Sales = 290 + 10(Year)

A reasonable estimate of sales in year 11 is 400 radios.

Figure 5.2b



420 –

400 –

380 –

360 –

340 –

320 –

300 –

280 –

| | | | | | | | | |

0 1 2 3 4 5 6 7 8 9 10

Time (Years)

Annual Sales of Radios

(b)

Scatter Diagram for CD Players

This trend line may not be perfectly accurate because of variation from year to year
Sales appear to be increasing
A forecast would probably be a larger figure each year

Figure 5.2c



200 –

180 –

160 –

140 –

120 –

100 –

| | | | | | | | | |

0 1 2 3 4 5 6 7 8 9 10

Time (Years)

Annual Sales of CD Players

(c)

Measures of Forecast Accuracy

We compare forecasted values with actual values to see how well one model works or to compare models.

Forecast error = Actual value – Forecast value

One measure of accuracy is the mean absolute deviation (MAD):

Measures of Forecast Accuracy

Using a naïve forecasting model we can compute the MAD:

Table 5.2

YEAR	ACTUAL SALES OF CD PLAYERS	FORECAST SALES	ABSOLUTE VALUE OF ERRORS (DEVIATION), (ACTUAL – FORECAST)
1	110	—	—
2	100	110	\|100 – 110\| = 10
3	120	100	\|120 – 110\| = 20
4	140	120	\|140 – 120\| = 20
5	170	140	\|170 – 140\| = 30
6	150	170	\|150 – 170\| = 20
7	160	150	\|160 – 150\| = 10
8	190	160	\|190 – 160\| = 30
9	200	190	\|200 – 190\| = 10
10	190	200	\|190 – 200\| = 10
11	—	190	—
Sum of \|errors\| = 160
MAD = 160/9 = 17.8

Measures of Forecast Accuracy

Table 5.2

Using a naïve forecasting model we can compute the MAD:

YEAR	ACTUAL SALES OF CD PLAYERS	FORECAST SALES	ABSOLUTE VALUE OF ERRORS (DEVIATION), (ACTUAL – FORECAST)
1	110	—	—
2	100	110	\|100 – 110\| = 10
3	120	100	\|120 – 110\| = 20
4	140	120	\|140 – 120\| = 20
5	170	140	\|170 – 140\| = 30
6	150	170	\|150 – 170\| = 20
7	160	150	\|160 – 150\| = 10
8	190	160	\|190 – 160\| = 30
9	200	190	\|200 – 190\| = 10
10	190	200	\|190 – 200\| = 10
11	—	190	—
Sum of \|errors\| = 160
MAD = 160/9 = 17.8

Measures of Forecast Accuracy

There are other popular measures of forecast accuracy.
The mean squared error:

The mean absolute percent error:

And bias is the average error.

Time-Series Forecasting Models

A time series is a sequence of evenly spaced events.
Time-series forecasts predict the future based solely on the past values of the variable, and other variables are ignored.

Components of a Time-Series

A time series typically has four components:

Trend (T) is the gradual upward or downward movement of the data over time.

Seasonality (S) is a pattern of demand fluctuations above or below the trend line that repeats at regular intervals.

Cycles (C) are patterns in annual data that occur every several years.

Random variations (R) are “blips” in the data caused by chance or unusual situations, and follow no discernible pattern.

Decomposition of a Time-Series

Average Demand over 4 Years

Trend Component

Actual Demand Line

Figure 5.3

Product Demand Charted over 4 Years, with Trend and Seasonality Indicated

Time

Demand for Product or Service

| | | |

Year Year Year Year

1 2 3 4

Seasonal Peaks

Decomposition of a Time-Series

There are two general forms of time-series models:
The multiplicative model:

Demand = T x S x C x R

The additive model:

Demand = T + S + C + R

Models may be combinations of these two forms.
Forecasters often assume errors are normally distributed with a mean of zero.

Moving Averages

Moving averages can be 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.
This methods tends to smooth out short-term irregularities in the data series.

Moving Averages

Mathematically:

Where:

= forecast for time period t + 1

= 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.
They have collected data for the past year.
They are using a three-month moving average to forecast demand (n = 3).

Wallace Garden Supply

Table 5.3

MONTH	ACTUAL SHED SALES	THREE-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.
This is often used when a trend or other pattern is emerging.

Mathematically:

where

wi = weight for the ith observation

Wallace Garden Supply

Wallace Garden Supply decides to try a weighted moving average model to forecast demand for its Storage Shed.
They decide on the following weighting scheme:

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

WEIGHTS APPLIED	PERIOD
3	Last month
2	Two months ago
1	Three months ago
6

Sum of the weights

Wallace Garden Supply

Table 5.4

MONTH	ACTUAL SHED SALES	THREE-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

Wallace Garden Supply

Program 5.1A

Selecting the Forecasting Module in Excel QM

Wallace Garden Supply

Program 5.1B

Initialization Screen for Weighted Moving Average

Wallace Garden Supply

Program 5.1C

Weighted Moving Average in Excel QM for Wallace Garden Supply

Exponential Smoothing

Exponential smoothing is a type of moving average that is easy to use and requires little record keeping of data.

New forecast = Last period’s forecast

+ (Last period’s actual demand

– Last period’s forecast)

Here  is a weight (or smoothing constant) in which 0≤≤1.

Exponential Smoothing

Mathematically:

Where:

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

Ft = 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

If actual demand in March was 136 autos, the April forecast would be:

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

= 142.6 or 143 autos

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.

Exponential Smoothing

Table 5.5

Port of Baltimore Exponential Smoothing Forecast for =0.1 and =0.5.

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

Exponential Smoothing

Table 5.6

Absolute Deviations and MADs for the Port of Baltimore Example

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

Port of Baltimore Exponential Smoothing Example in Excel QM

Program 5.2

Annie Puciloski (AP) - upper callout, 2nd line: insert this initial forecast, "delete" cells E10:H10

Exponential Smoothing with Trend Adjustment

Like all averaging techniques, exponential smoothing does not respond to trends.
A more complex model can be used that adjusts for trends.
The basic approach is to develop an exponential smoothing forecast, and then adjust it for the trend.

Forecast including trend (FITt+1) = Smoothed forecast (Ft+1)

+ Smoothed Trend (Tt+1)

Exponential Smoothing with Trend Adjustment

The equation for the trend correction uses a new smoothing constant  .
Tt must be given or estimated. Tt+1 is computed by:

where

Tt = smoothed trend for time period t

Ft = smoothed forecast for time period t

FITt = forecast including trend for time period t

α =smoothing constant for forecasts

 = smoothing constant for trend

Selecting a Smoothing Constant

As with exponential smoothing, 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 generally selected using a trial-and-error approach based on the value of the MAD for different values of .

Midwestern Manufacturing

Midwest Manufacturing has a demand for electrical generators from 2004 – 2010 as given in the table below.
To forecast demand, Midwest assumes:
F1 is perfect.
T1 = 0.
α = 0.3
β = 0.4.

Table 5.7

YEAR	ELECTRICAL GENERATORS SOLD
2004	74
2005	79
2006	80
2007	90
2008	105
2009	142
2010	122

Midwestern Manufacturing

According to the assumptions,

FIT1 = F1 + T1 = 74 + 0 = 74.

Step 1: Compute Ft+1 by:

FITt+1 = Ft + α(Yt – FITt)

= 74 + 0.3(74-74) = 74

Step 2: Update the trend using:

Tt+1 = Tt + β(Ft+1 – FITt)

T2 = T1 + .4(F2 – FIT1)

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

Midwestern Manufacturing

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

FIT2 = F2 + T2

= 74 + 0 = 74

Annie Puciloski (AP) - 2nd line: F s/b FIT sub t+1

Midwestern Manufacturing

For 2006 (period 3) we have:
Step 1: F3 = FIT2 + 0.3(Y2 – FIT2)

= 74 + .3(79 – 74)

= 75.5

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

= 0 + 0.4(75.5 – 74)

= 0.6

Step 3: FIT3 = F3 + T3

= 75.5 + 0.6

= 76.1

Midwestern Manufacturing Exponential Smoothing with Trend Forecasts

Table 5.8

Time (t)	Demand (Yt)	FITt+1 = Ft + 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+0.4(74-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.466+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

Midwestern Manufacturing

Program 5.3

Midwestern Manufacturing Trend-Adjusted Exponential Smoothing in Excel QM

Trend Projections

Trend projection fits a trend line to a series of historical data points.
The line is projected into the future for medium- to long-range forecasts.
Several trend equations can be developed based on exponential or quadratic models.
The simplest is a linear model developed using regression analysis.

Trend Projection

The mathematical form is

Where

= predicted value

b0 = intercept

b1 = slope of the line

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

Midwestern Manufacturing

Program 5.4A

Excel Input Screen for Midwestern Manufacturing Trend Line

Midwestern Manufacturing

Program 5.4B

Excel Output for Midwestern Manufacturing Trend Line

Midwestern Manufacturing Company Example

The forecast equation is

To project demand for 2011, we use the coding system to define X = 8

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

= 141.03, or 141 generators

Likewise for X = 9

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

= 151.57, or 152 generators

Midwestern Manufacturing

Figure 5.4

Electrical Generators and the Computed Trend Line

Midwestern Manufacturing

Program 5.5

Excel QM Trend Projection Model

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.
When no trend is present, the seasonal index can be found by dividing the average value for a particular season by the average of all the data.

Eichler Supplies

Eichler Supplies sells telephone answering machines.
Sales data for the past two years has been collected for one particular model.
The firm wants to create a forecast that includes seasonality.

Eichler Supplies Answering Machine Sales and Seasonal Indices

Table 5.9

MONTH	SALES DEMAND	AVERAGE TWO- YEAR DEMAND	MONTHLY DEMAND	AVERAGE SEASONAL INDEX
YEAR 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

Average two-year demand

Average monthly demand

Seasonal index =

1,128

12 months

Average monthly demand = = 94

Seasonal Variations

The calculations for the seasonal indices are

Jan.

July

Feb.

Aug.

Mar.

Sept.

Apr.

Oct.

May

Nov.

June

Dec.

Seasonal Variations with Trend

When both trend and seasonal components are present, the forecasting task is more complex.
Seasonal indices should be computed using a centered moving average (CMA) approach.
There are four steps in computing CMAs:

Compute the CMA for each observation (where possible).

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

Average seasonal ratios to get seasonal indices.

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

Turner Industries

The following table shows Turner Industries’ quarterly sales figures for the past three years, in millions of dollars:

Table 5.10

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

Definite trend

Seasonal pattern

Turner Industries

To calculate the CMA for quarter 3 of year 1 we compare the actual sales with an average quarter centered on that time period.
We will use 1.5 quarters before quarter 3 and 1.5 quarters after quarter 3 – that is we take quarters 2, 3, and 4 and one half of quarters 1, year 1 and quarter 1, year 2.

0.5(108) + 125 + 150 + 141 + 0.5(116)

CMA(q3, y1) = = 132.00

Turner Industries

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

Turner Industries

Table 5.11

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

There are two seasonal ratios for each quarter so these 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

Figure 5.5



CMA

Original Sales Figures

200 –

150 –

100 –

50 –

0 –

Sales

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

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

Time Period

The Decomposition Method of Forecasting with Trend and Seasonal Components

Decomposition is the process of isolating linear trend and seasonal factors to develop more accurate forecasts.
There are 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 for Turner Industries

Develop a forecast using this trend and multiply the forecast by the appropriate seasonal index.

Find a trend line using the deseasonalized data:

b1 = 2.34 b0 = 124.78

= 124.78 + 2.34X

= 124.78 + 2.34(13)

= 155.2 (forecast before adjustment for seasonality)

x I1 = 155.2 x 0.85 = 131.92

Deseasonalized Data for Turner Industries

Table 5.12

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

San Diego Hospital

A San Diego hospital used 66 months of adult inpatient days to develop the following seasonal indices.

Table 5.13

MONTH	SEASONALITY INDEX	MONTH	SEASONALITY INDEX
January	1.0436	July	1.0302
February	0.9669	August	1.0405
March	1.0203	September	0.9653
April	1.0087	October	1.0048
May	0.9935	November	0.9598
June	0.9906	December	0.9805

San Diego Hospital

Based on this model, the forecast for patient days for the next period (67) is:

Patient days = 8,091 + (21.5)(67) = 9,532 (trend only)

Patient days = (9,532)(1.0436)

= 9,948 (trend and seasonal)

= 8,091 + 21.5X

= forecast patient days

X = time in months

where

Using this data they developed the following equation:

San Diego Hospital

Program 5.6A

Initialization Screen for the Decomposition method in Excel QM

San Diego Hospital

Program 5.6B

Turner Industries Forecast Using the Decomposition Method in Excel QM

Using Regression with Trend and Seasonal Components

Multiple regression can be used to forecast both trend and seasonal components in a time series.
One independent variable is time.
Dummy independent variables are used to represent the seasons.
The model is an additive decomposition model:

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

Regression with Trend and Seasonal Components

Program 5.7A

Excel Input for the Turner Industries Example Using Multiple Regression

Using Regression with Trend and Seasonal Components

Program 5.7B

Excel Output for the Turner Industries Example Using Multiple Regression

Using Regression with Trend and Seasonal Components

The resulting regression equation is:

Using the model to forecast sales for the first two quarters of next year:

These are different from the results obtained using the multiplicative decomposition method.
Use MAD or MSE to determine the best model.

Monitoring and Controlling Forecasts

Tracking signals can be used to monitor the performance of a forecast.
A tracking signal is computed as the running sum of the forecast errors (RSFE), and is computed using the following equation:

where

Monitoring and Controlling Forecasts

Figure 5.6

Plot of Tracking Signals

Acceptable Range

Signal Tripped

Upper Control Limit

Lower Control Limit

0 MADs

–

Time

Tracking Signal

Monitoring and Controlling Forecasts

Positive tracking signals indicate demand is greater than forecast.
Negative tracking signals indicate demand is less than forecast.
Some variation is expected, but 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.
This indicates there has been an unacceptable amount of variation.
Limits should be reasonable and may vary from item to item.

Kimball’s Bakery

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

Adaptive Smoothing

Adaptive smoothing is the 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.

All rights reserved. No part of this publication may be 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.

error

forecast

MAD

160

error

forecast

MAD

error

)

(

MSE

MAPE

100

actual

error

periods

demands

Sum

forecast

average

Moving

...

)

(

)

)(

(

Weights

period

value

Actual

period

Weight

...

)

(

)

(

)

(

FIT

122

223

200

851

200

131

309

200

106

064

200

904

200

957

200

851

200

106

064

200

957

200

112

117

200

136

132

150

quarter

Sales

ratio

Seasonal

CMA

104

134

104

)

(

)

(

)

(

)

(

152

104

)

(

)

(

)

(

)

(

MAD

RSFE

signal

Tracking

error

forecast

MAD

error

forecast

MAD

MADs

signal

Tracking

MAD

RSFE