week 3 stats hw

kentucky79

chapter5.pptx

Home >Mathematics homework help >Statistics homework help >week 3 stats hw

Forecasting

To accompany Quantitative Analysis for Management, Twelfth Edition,

by Render, Stair, Hanna and Hale

Power Point slides created by Jeff Heyl

After completing this chapter, students will be able to:

LEARNING OBJECTIVES

5 – 2

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.

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

5 – 3

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

5 – 4

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

5 – 5

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

Delphi method

Jury of executive opinion

Sales force composite

Consumer market surveys

5 – 6

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

5 – 7

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

5 – 8

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

5 – 9

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)

5 – 10

Components of a Time Series

5 – 11

Series 4: Trend, Seasonal and Random Variations

Series 3: Trend and Random Variations

Series 2: Seasonal Variations Only

Series 1: Random Variations Only

Sales

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

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

5 – 12

Sales

| | | | | | | | | |

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

5 – 13

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

5 – 14

Measure of accuracy

Mean absolute deviation (MAD):

Measures of Forecast Accuracy

5 – 15

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

5 – 16

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

5 – 17

TABLE 5.1 – Computing the Mean Absolute Deviation (MAD)

Measures of Forecast Accuracy

Other common measures

Mean squared error (MSE)

Mean absolute percent error (MAPE)

Bias is the average error

5 – 18

Forecasting Random Variations

No other components are present

Averaging techniques smooth out forecasts

Moving averages

Weighted moving averages

Exponential smoothing

5 – 19

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

5 – 20

Moving average forecast =

Sum of demands in previous n periods

Moving Averages

Mathematically

5 – 21

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)

5 – 22

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

5 – 23

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

5 – 24

where

wi = weight for the ith 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

	6

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

5 – 26

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

5 – 27

Exponential Smoothing

Mathematically

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

5 – 28

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

5 – 29

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

5 – 30

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

5 – 31

Port of Baltimore Example

TABLE 5.5 – Absolute Deviations and MADs

5 – 32

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

5 – 33

PROGRAM 5.1A – Selecting the Forecasting Model

Using Software

5 – 34

PROGRAM 5.1B – Initializing Excel QM

Using Software

5 – 35

PROGRAM 5.1C – Excel QM Output

Using Software

5 – 36

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

Using Software

5 – 37

PROGRAM 5.2B – Entering Data

Using Software

5 – 38

PROGRAM 5.2C – Selecting the Model and Entering Data

Using Software

5 – 39

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)

5 – 40

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

+ a(Last error)

5 – 41

Exponential Smoothing with Trend

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

Smoothed forecast

Previous forecast including trend

b(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)

5 – 42

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 

5 – 43

Midwestern Manufacturing

Demand for electrical generators from 2007 – 2013

Midwest assumes F1 is perfect, T1 = 0, a = 0.3, b = 0.4

5 – 44

YEAR	ELECTRICAL GENERATORS SOLD
2007	74
2008	79
2009	80
2010	90
2011	105
2012	142
2013	122

TABLE 5.6 – Demand

Midwestern Manufacturing

For 2008 (time period 2)

Step 1: Compute Ft+1

F2 = FIT1 + a(Y1 – FIT1)

= 74 + 0.3(74 – 74) = 74

Step 2: Update the trend

T2 = T1 + b(F2 – FIT1)

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

5 – 45

Midwestern Manufacturing

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

FIT2 = F2 + T2

= 74 + 0 = 74

5 – 46

Midwestern Manufacturing

For 2009 (time period 3)

Step 1: F3 = FIT2 + a(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

5 – 47

Midwestern Manufacturing

5 – 48

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 + 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.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

5 – 49

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

5 – 50

Trend Projections

Mathematical formula

5 – 51

where

= predicted value

b0 = intercept

b1 = slope of the line

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

Midwestern Manufacturing

Based on least squares regression, the forecast equation is

(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

5 – 52

Year 2014 is coded as X = 8

For X = 9

Midwestern Manufacturing

5 – 53

PROGRAM 5.4 – Output from Excel QM for Trend Line

Midwestern Manufacturing

5 – 54

PROGRAM 5.5 – Output from QM for Trend Line

Midwestern Manufacturing

5 – 55

FIGURE 5.4 – Generator Demand Based on Trend Line

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

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

180 –

160 –

140 –

120 –

100 –

80 –

60 –

40 –

20 –

0 –

Generator Demand

Time Period

Projected demand for next 3 years is shown on the trend line

Trend Line

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

5 – 56

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

5 – 57

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

5 – 58

Seasonal Indices with No Trend

5 – 59

MONTH	SALES DEMAND		AVERAGE 2- 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

Seasonal index =

Average 2-year demand

Average monthly demand

Average 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

5 – 60

Jan.

July

Feb.

Aug.

Mar.

Sept.

Apr.

Oct.

May

Nov.

June

Dec.

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

5 – 61

Seasonal Indices with Trend

Steps in CMA

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)

5 – 62

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

5 – 63

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

5 – 64

Turner Industries

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

5 – 65

Turner Industries

TABLE 5.10 – Centered Moving Averages and Seasonal Ratios

5 – 66

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

5 – 67

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

5 – 68

CMA

Original Sales Figures

200 –

150 –

100 –

50 –

0 –

Sales

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

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

5 – 69

Deseasonalized Data

5 – 70

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

5 – 71

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

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

b1 = 2.34 b0 = 124.78

Deseasonalized Data

5 – 72

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

Including the seasonal index

200 –

150 –

100 –

50 –

0 –

Sales

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

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

Time Period (Quarters)

Deseasonalized Data

5 – 73

FIGURE 5.5

Deseasonalized Sales Data

Sales Data

Using Software

5 – 74

PROGRAM 5.6A – QM for Windows Input

Using Software

5 – 75

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

5 – 76

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

5 – 77

PROGRAM 5.7A – Excel QM Multiple Regression Initialization

Using Regression with Trend and Seasonal

5 – 78

PROGRAM 5.7B – Excel QM Multiple Regression Output

Using Regression with Trend and Seasonal

Regression equation

5 – 79

Forecasts for first two quarters next year

Regression equation

Using Regression with Trend and Seasonal

5 – 80

Forecasts for first two quarters next year

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

5 – 81

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

5 – 82

Monitoring and Controlling Forecasts

5 – 83

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)

5 – 84

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

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

5 – 85

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.

1.6 The Role of Computers and Spreadsheet Models in the Quantitative Analysis Approach

1.7 Possible Problems in the Quantitative Analysis Approach

1.8 Implementation—Not Just the Final Step

1.1 Introduction 1.2 What Is Quantitative Analysis? 1.3 Business Analytics 1.4 The Quantitative Analysis Approach 1.5 How to Develop a Quantitative Analysis Model

CHAPTER OUTLINE

5. Use computers and spreadsheet models to perform quantitative analysis.

6. Discuss possible problems in using quantitative analysis.

7. Perform a break-even analysis.

1. Describe the quantitative analysis approach. 2. Understand the application of quantitative analysis

in a real situation. 3. Describe the three categories of business analytics. 4. Describe the use of modeling in quantitative

analysis.

After completing this chapter, students will be able to:

Introduction to Quantitative Analysis

1CHAPTER

LEARNING OBJECTIVES

M01_REND7331_12_SE_C01_pp2.indd 1 01/10/13 9:50 AM

1.6 The Role of Computers and Spreadsheet Models in the Quantitative Analysis Approach

1.7 Possible Problems in the Quantitative Analysis Approach

1.8 Implementation—Not Just the Final Step

1.1 Introduction 1.2 What Is Quantitative Analysis? 1.3 Business Analytics 1.4 The Quantitative Analysis Approach 1.5 How to Develop a Quantitative Analysis Model

CHAPTER OUTLINE

5. Use computers and spreadsheet models to perform quantitative analysis.

6. Discuss possible problems in using quantitative analysis.

7. Perform a break-even analysis.

1. Describe the quantitative analysis approach. 2. Understand the application of quantitative analysis

in a real situation. 3. Describe the three categories of business analytics. 4. Describe the use of modeling in quantitative

analysis.

After completing this chapter, students will be able to:

Introduction to Quantitative Analysis

1CHAPTER

LEARNING OBJECTIVES

M01_REND7331_12_SE_C01_pp2.indd 1 01/10/13 9:50 AM

MAD = forecast error∑

MAD=

forecast error

MAD = forecast error∑

n =

160 9

=17.8

MAD=

forecast error

160

=17.8

MAPE =

error actual∑ n

100%

MAPE=

error

actual

100%

MSE = (error)2∑ n

MSE=

(error)

Ft+1 = Yt +Yt−1 +...+Yt−n+1

t+1

t-1

+...+Y

t-n+1

Ft+1 = (Weight in period i)(Actual value in period)∑

(Weights)∑

t+1

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

(Weights)

Ft+1 = w1Yt +w2Yt−1 +...+wnYt−n+1

w1 +w2 +...+wn

t+1

t-1

+...+w

t-n+1

+...+w

Ft+1 = Ft +α(Yt −Ft )

t+1

+a(Y

-F

)

Enter the number of periods to be averaged.

Click OK.

Enter a title. Enter the number of past periods of data.

Enter the demand data and the weights. The calculations will automatically be performed.

The measures of accuracy are shown here.

The forecast for the next period is here.

In the Forecasting module, click New and Time-Series Analysis.

Enter a title.

Enter the number of past periods of data.

Click OK.

Enter the value for the smoothing constant.

Enter the data. Then click Solve at the top of the page.

Click here to see the models. Other input areas appear based on the model. Select Exponential Smoothing and a window opens for you to enter the smoothing constant.

Additional output is available under Window.

The measures of accuracy are shown here.

The forecast for next period is here.

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

t+1

=FIT

+a(Y

-FIT

)

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

t+1

+b(F

t+1

-FIT

)

FITt+1 = Ft+1 +Tt+1

FIT

t+1

FIT1 = F1 +T1 = 74+0 = 74

FIT

=74+0=74

Ŷ = b0 + b1X

Y=b

Ŷ

Ŷ = 56.71+10.54X

Y=56.71+10.54X

To forecast other time periods, enter the time period here.

The forecast for next period is here.

Forecasts for future time periods are shown here.

The trend line is shown over two lines.

Ŷ = 56.71+10.54X

Y=56.71+10.54X

1,200 12

× 0.957 = 96

1,200

´ 0.957 = 96

1,200 12

× 0.851 = 85

1,200

´ 0.851 = 85

1,200 12

× 1.223 = 122

1,200

´ 1.223 = 122

1,200 12

× 0.851 = 85

1,200

´ 0.851 = 85

1,200 12

× 1.117 = 112

1,200

´ 1.117 = 112

1,200 12

× 0.851 = 85

1,200

´ 0.851 = 85

1,200 12

× 1.064 = 106

1,200

´ 1.064 = 106

1,200 12

× 0.904 = 90

1,200

´ 0.904 = 90

1,200 12

× 0.957 = 96

1,200

´ 0.957 = 96

1,200 12

× 1.064 = 106

1,200

´ 1.064 = 106

1,200 12

× 0.851 = 85

1,200

´ 0.851 = 85

1,200 12

× 1.309 = 131

1,200

´ 1.309 = 131

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

= 132.0

CMA(q3, y1) =

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

= 132.0

Seasonal ratio = Sales in quarter 3 CMA

= 150

132.0 =1.136

Seasonal ratio=

Sales in quarter 3

CMA

150

132.0

=1.136

Ŷ =124.78+2.34X

Y=124.78+2.34X

Ŷ =124.78+2.34(13) =155.2 (before seasonality adjustment)

Y=124.78+2.34(13)

=155.2 (before seasonality adjustment)

Ŷ =124.78+2.34(13) =155.2 (before seasonality)

Y=124.78+2.34(13)

=155.2 (before seasonality)

Ŷ × I1 =155.2×0.85 =131.92

Y´I

=155.2´0.85=131.92

Select Rescale: set average to 1

Specify Centered Moving Average approach

Specify the number of seasons (4 for quarterly data)

Select Multiplicative Decomposition from the drop-down menu.

Input the data

Ŷ = a+ b1X1 + b2X 2 + b3X3 + b4X 4

Y=a+b

Enter the number of past period of data.

Enter the number of independent variables.

Enter the values of Y and X1–X4 as shown.

The regression coef!cients are shown here.

Enter the values of the variables to obtain any forecast.

Ŷ =104.1 + 2.3X1 + 15.7X2 + 38.7X3 + 30.1X 4

Y=104.1 + 2.3X

+ 15.7X

+ 38.7X

+ 30.1X

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

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

Tracking signal = RSFE MAD

= (forecast error)∑

MAD

Tracking signal=

RSFE

MAD

(forecast error)

MAD

MAD = forecast error∑

MAD=

forecast error

MAD = forecast error∑

n =

85 6 =14.2

Tracking signal = RSFE MAD

= 35

14.2 = 2.5 MADs

MAD=

forecast error

=14.2

Tracking signal=

RSFE

MAD

14.2

=2.5 MADs