Accurate forecasting is a crucial part of quality management because it
lowers costs so companies can be competitive
enables companies to produce better goods and services
allows companies to provide products and services when they are demanded
provides companies more flexibility in their production processes
Question 21 pts
The ______________ effect is caused in part by distortion in product demand
information caused by inaccurate forecasts.
regression
butterfly
bullwhip
causal
Question 31 pts
A forecast where the current period’s demand is used as the next period’s forecast is
known as a
moving average forecast
naive forecast
weighted moving average forecast
Delphi method
Question 41 pts
In an exponential smoothing forecast, the closer the smoothing constant, α, is to 1.0 the
greater the reaction to the most recent demand
greater the dampening, or smoothing, effect
more accurate the forecast
less accurate the forecast
Question 51 pts
The adjusted exponential smoothing forecast consists of the exponential smoothing
forecast with an adjustment for
cycles
seasonal patterns
random variations
trend
Question 61 pts
An oscillating movement in demand that occurs repetitively over the short-run is referred
to as a
seasonal pattern
cycle
trend
time series forecast
Question 71 pts
Which of the following is generally true about short-term forecasting?
The most common models are qualitative.
Many companies are shifting from long-term to short-term forecasts for strategic planning.
Time-series methods are common forecasting methods used in the short-term.
Short-term forecasts are often used to help design supply chains.
Question 81 pts
If a company’s product demand is 100 units in month 1, 75 units in month 2, 110 units in
month 3 and 50 units in month 4, its 4-month moving average prediction for month 5 is
103.35
50
71.25
83.75
Question 91 pts
A mathematical technique for forecasting that relates a dependent variable to an
independent variable is
correlation analysis
exponential smoothing
linear regression
weighted moving average
Question 101 pts
All of the following are true about time-series and causal modeling EXCEPT
Time-series techniques assume that what has occurred in the past will continue to occur in the future.
Causal forecasting methods attempt to develop a mathematical relationship between demand and
factors that cause demand to behave the way it does.
A causal forecasting method in which time is used as the independent variable is considered a form of
time-series modeling.
Causal forecasting methods are based on judgment, opinion, past experience, or best guesses.
Chapter 12
Forecasting
Russell and Taylor
Operations and Supply Chain Management,
9th Edition
Lecture Outline
• Strategic Role of Forecasting in Supply Chain
Management
• Components of Forecasting Demand
• Time Series Methods
• Forecast Accuracy
• Time Series Forecasting Using Excel
• Regression Methods
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
• Develop forecasting models with linear and multiple
regression analysis
Forecasting
• Predicting the future
• Qualitative forecast methods
• subjective
• Quantitative forecast methods
• based on mathematical formulas
Important Principles
• Decisions are made under uncertainty
• Good forecasts reduce uncertainty
• Forecasts are part of ops decision making
• Inventory control
• Capacity and facility location
• Manpower requirements
• Forecasts are always wrong!
• Based on past information, guesses about the future
Strategic Role of Forecasting in
Supply Chain Management
• Accurate forecasting determines inventory levels
in the supply chain
• Continuous replenishment
•
•
•
•
supplier & customer share continuously updated data
typically managed by the supplier
reduces inventory for the company
speeds customer delivery
• Variations of continuous replenishment
•
•
•
•
quick response
JIT (just-in-time)
VMI (vendor-managed inventory)
stockless inventory
The Effect of Inaccurate Forecasting
Forecasting
• Quality Management
• Accurately forecasting customer demand is a key to
providing good quality service
• Strategic Planning
• Successful strategic planning requires accurate
forecasts of future products and markets
Components of Forecasting Demand
•
•
•
•
•
Time frame
Demand behavior
Causes of behavior
(Data availability)
(Forecasting budget and available
expertise)
• (Consequence of a bad forecast)
Time Frame
• Indicates how far into the future is forecast
• Short- to mid-range forecast
• typically encompasses the immediate future
• daily up to two years
• more data required for accuracy
• Long-range forecast
• usually encompasses a period of time longer than
two years
• less data, more subject area knowledge
Demand Behavior
• Trend
• a gradual, long-term up or down movement of demand
• Random variations
• movements in demand that do not follow a pattern
• Seasonal pattern
• an up-and-down repetitive movement in demand
occurring periodically (e.g., weeks, months, qtrs
• Cycle
• an up-and-down repetitive movement in demand
• usually longer in nature, e.g., recession
Forms of Forecast Movement
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
Qualitative Methods
• Management, marketing, purchasing, and
engineering are sources for internal qualitative
forecasts
• Delphi method
• involves soliciting forecasts about technological
advances from experts
Forecasting Process
1. Identify the
purpose of forecast
2. Collect historical
data
3. Plot data and identify
patterns
6. Check forecast
accuracy with one or
more measures
5. Develop/compute
forecast for period of
historical data
4. Select a forecast
model that seems
appropriate for data
7.
Is accuracy of
forecast
acceptable?
No
8b. Select new
forecast model or
adjust parameters of
existing model
Yes
8a. Forecast over
planning horizon
9. Adjust forecast based
on additional qualitative
information and insight
10. Monitor results
and measure forecast
accuracy
Time Series
• Assume that what has occurred in the past will
continue to occur in the future
• Relate the forecast to only one factor - time
• Include
•
•
•
•
naïve forecast
moving average
exponential smoothing
linear trend line
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 data from past periods
Moving Average: Naïve Approach
MONTH
Jan
Feb
Mar
Apr
May
June
July
Aug
Sept
Oct
Nov
ORDERS
PER MONTH
120
90
100
75
110
50
75
130
110
90
-
FORECAST
Moving Average: Naïve Approach
MONTH
Jan
Feb
Mar
Apr
May
June
July
Aug
Sept
Oct
Nov
ORDERS
PER MONTH
120
90
100
75
110
50
75
130
110
90
-
FORECAST
120
90
100
75
110
50
75
130
110
90
Simple Moving Average
n
Di
i=1
MAn =
n
where
n = number of periods in
the moving average
Di = demand in period i
3-month Simple Moving Average
MONTH
Jan
Feb
Mar
Apr
May
June
July
Aug
Sept
Oct
Nov
ORDERS
PER MONTH
120
90
100
75
110
50
75
130
110
90
-
MOVING
AVERAGE
3
Di
i=1
MA3 =
3
3-month Simple Moving Average
MONTH
Jan
Feb
Mar
Apr
May
June
July
Aug
Sept
Oct
Nov
ORDERS
PER MONTH
120
90
100
75
110
50
75
130
110
90
-
MOVING
AVERAGE
–
–
–
103.3
88.3
95.0
78.3
78.3
85.0
105.0
110.0
3
Di
i=1
MA3 =
=
3
90 + 110 + 130
3
= 110 orders for Nov
5-month Simple Moving Average
MONTH
Jan
Feb
Mar
Apr
May
June
July
Aug
Sept
Oct
Nov
ORDERS
PER MONTH
120
90
100
75
110
50
75
130
110
90
-
MOVING
AVERAGE
5
Di
i=1
MA5 =
5
5-month Simple Moving Average
MONTH
Jan
Feb
Mar
Apr
May
June
July
Aug
Sept
Oct
Nov
ORDERS
PER MONTH
120
90
100
75
110
50
75
130
110
90
-
MOVING
AVERAGE
–
–
–
–
–
99.0
85.0
82.0
88.0
95.0
91.0
5
Di
i=1
MA5 =
=
5
90 + 110 + 130+75+50
5
= 91 orders for Nov
Smoothing Effects
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
Weighted Moving Average Example
MONTH
August
September
October
WEIGHT
DATA
17%
33%
50%
130
110
90
3
November Forecast
WMA3 =
Wi Di
i=1
Weighted Moving Average Example
MONTH
August
September
October
WEIGHT
DATA
17%
33%
50%
130
110
90
3
November Forecast
WMA3 =
Wi Di
i=1
= (0.50)(90) + (0.33)(110) + (0.17)(130)
= 103.4 orders
Exponential Smoothing
• Averaging method
• Weights most recent data more strongly
▪ Parameter, a, determines level of smoothing
and speed of reaction to differences between
forecasts and actual occurrences
▪Small values lessen the effects of random changes
▪Larger values keep up with rapid change
• Reacts more to recent changes
• Widely used, accurate method
• “Adaptive” forecasting involves changing a
over time to increase accuracy
Exponential Smoothing
Ft +1 = a Dt + (1 - a)Ft
where:
Ft +1 = forecast for next period
Dt = actual demand for present period
Ft = previously determined forecast for
present period
a=
weighting factor, smoothing constant
Effect of Smoothing Constant
0.0 a 1.0
If a = 0.20, then Ft +1 = 0.20 Dt + 0.80 Ft
If a = 0, then Ft +1 = 0 Dt + 1 Ft = Ft
Forecast does not reflect recent data
If a = 1, then Ft +1 = 1 Dt + 0 Ft = Dt
Forecast based only on most recent data
Exponential Smoothing (α=0.30)
PERIOD
1
2
3
4
5
6
7
8
9
10
11
12
MONTH
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
DEMAND
37
40
41
37
45
50
43
47
56
52
55
54
F2 = aD1 + (1 - a)F1
F3 = aD2 + (1 - a)F2
F13 = aD12 + (1 - a)F12
Exponential Smoothing (α=0.30)
PERIOD
1
2
3
4
5
6
7
8
9
10
11
12
MONTH
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
DEMAND
37
40
41
37
45
50
43
47
56
52
55
54
F2 = aD1 + (1 - a)F1
= (0.30)(37) + (0.70)(37)
= 37
F3 = aD2 + (1 - a)F2
= (0.30)(40) + (0.70)(37)
= 37.9
F13 = aD12 + (1 - a)F12
= (0.30)(54) + (0.70)(50.84)
= 51.79
Exponential Smoothing
PERIOD
MONTH
DEMAND
1
2
3
4
5
6
7
8
9
10
11
12
13
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
37
40
41
37
45
50
43
47
56
52
55
54
–
FORECAST, Ft + 1
(a = 0.3)
(a = 0.5)
–
–
Exponential Smoothing
PERIOD
MONTH
DEMAND
1
2
3
4
5
6
7
8
9
10
11
12
13
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
37
40
41
37
45
50
43
47
56
52
55
54
–
FORECAST, Ft + 1
(a = 0.3)
(a = 0.5)
–
37.00
37.90
38.83
38.28
40.29
43.20
43.14
44.30
47.81
49.06
50.84
51.79
–
37.00
38.50
39.75
38.37
41.68
45.84
44.42
45.71
50.85
51.42
53.21
53.61
Exponential Smoothing
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
Adjusted Exponential Smoothing (β=0.30)
PERIOD
MONTH
DEMAND
1
2
3
4
5
6
7
8
9
10
11
12
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
37
40
41
37
45
50
43
47
56
52
55
54
T3
= (F3 - F2) + (1 - ) T2
AF3 = F3 + T3
T13 = (F13 - F12) + (1 - ) T12
AF13 = F13 + T13 =
Adjusted Exponential Smoothing (β=0.30)
PERIOD
MONTH
DEMAND
1
2
3
4
5
6
7
8
9
10
11
12
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
37
40
41
37
45
50
43
47
56
52
55
54
T3
= (F3 - F2) + (1 - ) T2
= (0.30)(38.5 - 37.0) + (0.70)(0)
= 0.45
AF3 = F3 + T3 = 38.5 + 0.45
= 38.95
T13 = (F13 - F12) + (1 - ) T12
= (0.30)(53.61 - 53.21) + (0.70)(1.77)
= 1.36
AF13 = F13 + T13 = 53.61 + 1.36 = 54.97
Adjusted Exponential Smoothing
PERIOD
MONTH
DEMAND
1
2
3
4
5
6
7
8
9
10
11
12
13
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
37
40
41
37
45
50
43
47
56
52
55
54
–
FORECAST
Ft +1
TREND
Tt +1
ADJUSTED
FORECAST AFt +1
Adjusted Exponential Smoothing
PERIOD
MONTH
DEMAND
FORECAST
Ft +1
1
2
3
4
5
6
7
8
9
10
11
12
13
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
37
40
41
37
45
50
43
47
56
52
55
54
–
37.00
37.00
38.50
39.75
38.37
38.37
45.84
44.42
45.71
50.85
51.42
53.21
53.61
TREND
Tt +1
ADJUSTED
FORECAST AFt +1
–
0.00
0.45
0.69
0.07
0.07
1.97
0.95
1.05
2.28
1.76
1.77
1.36
–
37.00
38.95
40.44
38.44
38.44
47.82
45.37
46.76
58.13
53.19
54.98
54.96
Adjusted Exponential Smoothing
Forecasts
Linear Trend Line
y = a + bx
where
a = intercept
b = slope of the line
x = time period
y = forecast for
demand for period x
xy - nxy
b =
x2 - nx2
a = y-bx
where
n = number of periods
x
x =
= mean of the x values
n
y
y = n = mean of the y values
Least Squares Example
x(PERIOD)
1
2
3
4
5
6
7
8
9
10
11
12
y(DEMAND)
73
40
41
37
45
50
43
47
56
52
55
54
xy
x2
Least Squares Example
x(PERIOD)
y(DEMAND)
xy
x2
1
2
3
4
5
6
7
8
9
10
11
12
73
40
41
37
45
50
43
47
56
52
55
54
37
80
123
148
225
300
301
376
504
520
605
648
1
4
9
16
25
36
49
64
81
100
121
144
78
557
3867
650
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 =
x2 - nx2
3867 - (12)(6.5)(46.42) =1.72
650 - 12(6.5)2
a = y - bx
= 46.42 - (1.72)(6.5) = 35.2
Linear trend line y = 35.2 + 1.72x
Forecast for period 13 y = 35.2 + 1.72(13) = 57.56 units
Seasonal Adjustments
▪ Repetitive increase/ decrease in demand
▪ Use seasonal factor to adjust forecast
Seasonal factor = Si =
Di
D
Seasonal Adjustment
YEAR
2002
2003
2004
DEMAND (1000’S PER QUARTER)
1
2
3
4
Total
12.6
14.1
15.3
8.6
10.3
10.6
6.3
7.5
8.1
17.5
18.2
19.6
D1
S1 =
=
D
D3
S3 =
=
D
D2
S2 =
=
D
D4
S4 =
=
D
Seasonal Adjustment
YEAR
2002
2003
2004
Total
DEMAND (1000’S PER QUARTER)
1
2
3
4
Total
12.6
14.1
15.3
42.0
8.6
10.3
10.6
29.5
6.3
7.5
8.1
21.9
17.5
18.2
19.6
55.3
45.0
50.1
53.6
148.7
D1
42.0
S1 =
=
= 0.28
D 148.7
D3
21.9
S3 =
=
= 0.15
D 148.7
D2
29.5
S2 =
=
= 0.20
D 148.7
D4
55.3
S4 =
=
= 0.37
D 148.7
Seasonal Adjustment
For 2005
y=
SF1 = (S1) (F5) =
SF2 = (S2) (F5) =
SF3 = (S3) (F5) =
SF4 = (S4) (F5) =
Seasonal Adjustment
For 2005
y = 40.97 + 4.30x = 40.97 + 4.30(4) = 58.17
SF1 = (S1) (F5) = (0.28)(58.17) = 16.28
SF2 = (S2) (F5) = (0.20)(58.17) = 11.63
SF3 = (S3) (F5) = (0.15)(58.17) = 8.73
SF4 = (S4) (F5) = (0.37)(58.17) = 21.53
Using Seasonal Indices
• Many ways to model seasonality
• Don’t forget to check accuracy of this forecast
against historical data, as for any other forecast!
• Note: text doesn’t calculate entire forecast for this
method and check accuracy
Forecast Accuracy
• Forecast error
• difference between forecast and actual demand
• MAD
• mean absolute deviation
• MAPD
• mean absolute percent deviation
• Cumulative error
• Average error or bias
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
MAD Example
PERIOD
1
2
3
4
5
6
7
8
9
10
11
12
DEMAND, Dt
Ft (a =0.3)
37
40
41
37
45
50
43
47
56
52
55
54
37.00
37.00
37.90
38.83
38.28
40.29
43.20
43.14
44.30
47.81
49.06
50.84
(Dt - Ft)
–
|Dt - Ft|
–
MAD Example
PERIOD
1
2
3
4
5
6
7
8
9
10
11
12
DEMAND, Dt
Ft (a =0.3)
(Dt - Ft)
|Dt - Ft|
37
40
41
37
45
50
43
47
56
52
55
54
37.00
37.00
37.90
38.83
38.28
40.29
43.20
43.14
44.30
47.81
49.06
50.84
–
3.00
3.10
-1.83
6.72
9.69
-0.20
3.86
11.70
4.19
5.94
3.15
–
3.00
3.10
1.83
6.72
9.69
0.20
3.86
11.70
4.19
5.94
3.15
49.31
53.39
557
MAD Calculation
Dt - Ft
MAD =
n
MAD Calculation
Dt - Ft
MAD =
n
53.39
=
11
= 4.85
Other Accuracy Measures
Mean absolute percent deviation (MAPD)
|Dt - Ft|
MAPD =
Dt
Cumulative error
E = et
Average error
et
E= n
Comparison of Forecasts
FORECAST
MAD
MAPD
E
(E)
Exponential smoothing (a = 0.30)
Exponential smoothing (a = 0.50)
Adjusted exponential smoothing
(a = 0.50, = 0.30)
Linear trend line
4.85
4.04
3.81
9.6%
8.5%
7.5%
49.31
33.21
21.14
4.48
3.02
1.92
2.29
4.9%
–
–
Forecast Control
• Tracking signal
• monitors the forecast to see if it is biased high or low
• 1 MAD ≈ 0.8 б
• Control limits of 2 to 5 MADs are used most frequently
Tracking signal =
(Dt - Ft)
E
=
MAD
MAD
Tracking Signal Values
PERIOD
DEMAND
Dt
FORECAST,
Ft
1
2
3
4
5
6
7
8
9
10
11
12
37
40
41
37
45
50
43
47
56
52
55
54
37.00
37.00
37.90
38.83
38.28
40.29
43.20
43.14
44.30
47.81
49.06
50.84
ERROR
Dt - Ft
–
3.00
3.10
-1.83
6.72
9.69
-0.20
3.86
11.70
4.19
5.94
3.15
E =
(Dt - Ft)
MAD
–
3.00
6.10
4.27
10.99
20.68
20.48
24.34
36.04
40.23
46.17
49.32
–
3.00
3.05
2.64
3.66
4.87
4.09
4.06
5.01
4.92
5.02
4.85
Tracking Signal Values
PERIOD
DEMAND
Dt
FORECAST,
Ft
1
2
3
4
5
6
7
8
9
10
11
12
37
40
41
37
45
50
43
47
56
52
55
54
37.00
37.00
37.90
38.83
38.28
40.29
43.20
43.14
44.30
47.81
49.06
50.84
TS3 =
ERROR
Dt - Ft
–
3.00
3.10
-1.83
6.72
9.69
-0.20
3.86
11.70
4.19
5.94
3.15
E =
(Dt - Ft)
MAD
–
3.00
6.10
4.27
10.99
20.68
20.48
24.34
36.04
40.23
46.17
49.32
–
3.00
3.05
2.64
3.66
4.87
4.09
4.06
5.01
4.92
5.02
4.85
6.10
= 2.00
3.05
TRACKING
SIGNAL
–
1.00
2.00
1.62
3.00
4.25
5.01
6.00
7.19
8.18
9.20
10.17
Tracking Signal Plot
Statistical Control Charts
▪ Using we can calculate statistical control limits
for the forecast error
▪ Control limits are typically set at 3
=
(Dt - Ft)2
n-1
▪Mean squared error (MSE)
▪Average of squared forecast errors
Statistical Control Charts
Time Series Forecasting Using Excel
• Excel can be used to develop forecasts:
•
•
•
•
Moving average
Exponential smoothing
Adjusted exponential smoothing
Linear trend line
Exponentially Smoothed and Adjusted
Exponentially Smoothed Forecasts
=B5*(C11-C10)+
(1-B5)*D10
=C10+D10
=ABS(B10-E10)
=SUM(F10:F20)
=G22/11
Demand and Exponentially Smoothed
Forecast
Data Analysis Option
Forecasting With Seasonal Adjustment
Regression Methods
• Linear regression
• mathematical technique that relates a dependent
variable to an independent variable in the form of a
linear equation
• Correlation
• a measure of the strength of the relationship between
independent and dependent variables
Linear Regression
y = a + bx
a = y-bx
xy - nxy
b =
x2 - nx2
where
a = intercept
b = slope of the line
x
x =
= mean of the x data
n
y
y = n = mean of the y data
Linear Regression Example
x
(WINS)
y
(ATTENDANCE)
4
6
6
8
6
7
5
7
36.3
40.1
41.2
53.0
44.0
45.6
39.0
47.5
xy
x2
Linear Regression Example
x
(WINS)
y
(ATTENDANCE)
xy
x2
4
6
6
8
6
7
5
7
36.3
40.1
41.2
53.0
44.0
45.6
39.0
47.5
145.2
240.6
247.2
424.0
264.0
319.2
195.0
332.5
16
36
36
64
36
49
25
49
49
346.7
2167.7
311
Linear Regression Example
x=
y=
xy - nxy2
b=
x2 - nx2
a = y - bx
Linear Regression Example
49
= 6.125
8
346.9
y=
= 43.36
8
x=
xy - nxy2
b=
x2 - nx2
(2,167.7) - (8)(6.125)(43.36)
=
(311) - (8)(6.125)2
= 4.06
a = y - bx
= 43.36 - (4.06)(6.125)
= 18.46
Linear Regression Example
60,000 –
Attendance, y
50,000 –
40,000 –
30,000 –
Linear regression line, y
= 18.46 + 4.06x
20,000 –
Attendance forecast for 7 wins
y = 18.46 + 4.06(7)
= 46.88, or 46,880
10,000 –
|
0
|
1
|
2
|
3
|
4
|
5
Wins, x
|
6
|
7
|
8
|
9
|
10
Correlation and Coefficient of
Determination
• Correlation, r
• Measure of strength of relationship
• Varies between -1.00 and +1.00
• Coefficient of determination, r2
• Percentage of variation in dependent variable
resulting from changes in the independent variable
Computing Correlation
r=
n xy - x y
[n x2 - ( x)2] [n y2 - ( y)2]
Computing Correlation
r=
n xy - x y
[n x2 - ( x)2] [n y2 - ( y)2]
(8)(2,167.7) - (49)(346.9)
r=
[(8)(311) - (49)2] [(8)(15,224.7) - (346.9)2]
r = 0.947
Coefficient of determination
r2 = (0.947)2 = 0.897
Regression Analysis With Excel
=INTERCEPT(B5:B12,A5:A12)
=SUM(B5:B12)
=CORREL(B5:B12,A5:A12)
Regression Analysis with Excel
Regression Analysis With Excel
Multiple Regression
Study the relationship of demand to two or more
independent variables
y = 0 + 1x1 + 2x2 … + kxk
where
0 = the intercept
1, … , k = parameters for the
independent variables
x1, … , xk = independent variables
Multiple Regression With Excel
r2, the coefficient
of determination
Regression equation
coefficients for x1 and x2
Multiple Regression Example
y = 19,094.42 + 3560.99 x1 + .0368 x2
Attendance for 7 wins and $60,000 promotion
y=
Multiple Regression Example
y = 19,094.42 + 3560.99 x1 + .0368 x2
Attendance for 7 wins and $60,000 promotion
y = 19,094.42 + 3560.99 (7) + .0368 (60,000)
= 46,229.35
Basic Forecasting Process in Excel
• Plot data to detect general behavior
• Be aware of outliers
• Try simple models -- MA, WMA, ES, LR
• Check accuracy
• Increase complexity of model using seasonality,
etc., if desired
• Check accuracy
• Choose best model to forecast future
Notes on Forecasting in Excel
• Don’t do linear regression equations!
• SLOPE and INTERCEPT functions
• Optional: use Solver to determine optimal
smoothing constants and weights
• minimize MAD or other accuracy measure
Purchase answer to see full
attachment