forecasting ppt @ doms
DESCRIPTION
Forecasting ppt @ domsTRANSCRIPT
![Page 1: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/1.jpg)
Forecasting
![Page 2: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/2.jpg)
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
12-2
![Page 3: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/3.jpg)
Forecasting
• Predicting the future
• Qualitative forecast methods• subjective
• Quantitative forecast methods• based on mathematical formulas
12-3
![Page 4: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/4.jpg)
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
12-4
![Page 5: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/5.jpg)
The Effect of Inaccurate Forecasting
12-5
![Page 6: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/6.jpg)
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
12-6
![Page 7: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/7.jpg)
Types of Forecasting Methods
• Depend on• time frame
• demand behavior
• causes of behavior
12-7
![Page 8: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/8.jpg)
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
• Long-range forecast• usually encompasses a period of time longer than two
years
12-8
![Page 9: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/9.jpg)
Demand Behavior
• Trend• a gradual, long-term up or down movement of demand
• Random variations• movements in demand that do not follow a pattern
• Cycle• an up-and-down repetitive movement in demand
• Seasonal pattern• an up-and-down repetitive movement in demand
occurring periodically
12-9
![Page 10: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/10.jpg)
Forms of Forecast Movement
12-10
Time(a) Trend
Time(d) Trend with seasonal pattern
Time(c) Seasonal pattern
Time(b) Cycle
Dem
and
Dem
and
Dem
and
Dem
and
Random movement
![Page 11: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/11.jpg)
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
12-11
![Page 12: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/12.jpg)
Qualitative Methods
• Management, marketing, purchasing, and engineering are sources for internal qualitative forecasts
• Delphi method• involves soliciting forecasts about technological
advances from experts
12-12
![Page 13: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/13.jpg)
Forecasting Process
12-13
6. Check forecast accuracy with one or more measures
4. Select a forecast model that seems appropriate for data
5. Develop/compute forecast for period of historical data
8a. Forecast over planning horizon
9. Adjust forecast based on additional qualitative information and insight
10. Monitor results and measure forecast accuracy
8b. Select new forecast model or adjust parameters of existing model
7.Is accuracy of
forecast acceptable?
1. Identify the purpose of forecast
3. Plot data and identify patterns
2. Collect historical data
No
Yes
![Page 14: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/14.jpg)
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
• moving average
• exponential smoothing
• linear trend line
12-14
![Page 15: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/15.jpg)
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• stable demand with no pronounced behavioral patterns
• Weighted moving average• weights are assigned to most recent data
12-15
![Page 16: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/16.jpg)
Moving Average: Naïve Approach
12-16
Jan 120
Feb 90
Mar 100
Apr 75
May 110
June 50
July 75
Aug 130
Sept 110
Oct 90
ORDERSMONTH PER MONTH
-120
90100
75110
5075
130110
90Nov -
FORECAST
![Page 17: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/17.jpg)
Simple Moving Average
12-17
MAn =
n
i = 1 Di
nwhere
n =number of periods in the moving average
Di =demand in period i
![Page 18: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/18.jpg)
3-month Simple Moving Average
12-18
Jan 120
Feb 90
Mar 100
Apr 75
May 110
June 50
July 75
Aug 130
Sept 110
Oct 90Nov -
ORDERS
MONTH PER MONTH
MA3 =
3
i = 1 Di
3
=90 + 110 + 130
3
= 110 orders for Nov
–––
103.388.395.078.378.385.0
105.0110.0
MOVING AVERAGE
![Page 19: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/19.jpg)
5-month Simple Moving Average
12-19
MA5 =
5
i = 1 Di
5
=90 + 110 + 130+75+50
5
= 91 orders for Nov
Jan 120
Feb 90
Mar 100
Apr 75
May 110
June 50
July 75
Aug 130
Sept 110
Oct 90Nov -
ORDERS
MONTH PER MONTH –
–– –
– 99.085.082.088.095.091.0
MOVING AVERAGE
![Page 20: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/20.jpg)
Smoothing Effects
12-20
150 –
125 –
100 –
75 –
50 –
25 –
0 –| | | | | | | | | | |
Jan Feb Mar Apr May June July Aug Sept Oct Nov
Actual
Ord
ers
Month
5-month
3-month
![Page 21: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/21.jpg)
Weighted Moving Average
12-21
• Adjusts moving average method to more closely reflect data fluctuations
WMAn = i = 1 Wi Di
where
Wi = the weight for period i,
between 0 and 100 percent
Wi = 1.00
n
![Page 22: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/22.jpg)
Weighted Moving Average Example
12-22
MONTH WEIGHT DATA
August 17% 130September 33% 110October 50% 90
WMA3 = 3
i = 1 Wi Di
= (0.50)(90) + (0.33)(110) + (0.17)(130)
= 103.4 orders
November Forecast
![Page 23: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/23.jpg)
Exponential Smoothing
12-23
• Averaging method • Weights most recent data more strongly• Reacts more to recent changes• Widely used, accurate method
![Page 24: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/24.jpg)
Exponential Smoothing
12-24
Ft +1 = Dt + (1 - )Ft
where:
Ft +1 =forecast for next period
Dt =actual demand for present period
Ft =previously determined forecast for present period
= weighting factor, smoothing constant
![Page 25: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/25.jpg)
0.0 1.0
If = 0.20, then Ft +1 = 0.20Dt + 0.80 Ft
If = 0, then Ft +1 = 0Dt + 1 Ft = Ft
Forecast does not reflect recent data
If = 1, then Ft +1 = 1Dt + 0 Ft =Dt Forecast based only on most recent data
Effect of Smoothing Constant
12-25
![Page 26: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/26.jpg)
Exponential Smoothing (α=0.30)
12-26
F2 = D1 + (1 - )F1
= (0.30)(37) + (0.70)(37)
= 37
F3 = D2 + (1 - )F2
= (0.30)(40) + (0.70)(37)
= 37.9
F13 = D12 + (1 - )F12
= (0.30)(54) + (0.70)(50.84)
= 51.79
PERIOD MONTHDEMAND
1 Jan 37
2 Feb 40
3 Mar 41
4 Apr 37
5 May 45
6 Jun 50
7 Jul 43
8 Aug 47
9 Sep 56
10 Oct 52
11 Nov 55
12 Dec 54
![Page 27: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/27.jpg)
Exponential Smoothing
12-27
FORECAST, Ft + 1
PERIOD MONTH DEMAND ( = 0.3) ( = 0.5)
1 Jan 37 – –2 Feb 40 37.00 37.003 Mar 41 37.90 38.504 Apr 37 38.83 39.755 May 45 38.28 38.376 Jun 50 40.29 41.687 Jul 43 43.20 45.848 Aug 47 43.14 44.429 Sep 56 44.30 45.71
10 Oct 52 47.81 50.8511 Nov 55 49.06 51.4212 Dec 54 50.84 53.2113 Jan – 51.79 53.61
![Page 28: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/28.jpg)
Exponential Smoothing
12-28
70 –
60 –
50 –
40 –
30 –
20 –
10 –
0 –| | | | | | | | | | | | |1 2 3 4 5 6 7 8 9 10 11 12 13
Actual
Ord
ers
Month
= 0.50
= 0.30
![Page 29: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/29.jpg)
Adjusted Exponential Smoothing
12-29
AFt +1 = Ft +1 + Tt +1
whereT = an exponentially smoothed trend factor
Tt +1 = (Ft +1 - Ft) + (1 - ) Tt
whereTt = the last period trend factor= a smoothing constant for trend0 ≤ ≤
![Page 30: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/30.jpg)
Adjusted Exponential Smoothing (β=0.30)
12-30
PERIOD MONTH DEMAND
1 Jan 37
2 Feb 40
3 Mar 41
4 Apr 37
5 May 45
6 Jun 50
7 Jul 43
8 Aug 47
9 Sep 56
10 Oct 52
11 Nov 55
12 Dec 54
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
![Page 31: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/31.jpg)
Adjusted Exponential Smoothing
12-31
FORECAST TREND ADJUSTEDPERIOD MONTH DEMAND Ft +1 Tt +1 FORECAST AFt +1
1 Jan 37 37.00 – –2 Feb 40 37.00 0.00 37.003 Mar 41 38.50 0.45 38.954 Apr 37 39.75 0.69 40.445 May 45 38.37 0.07 38.446 Jun 50 38.37 0.07 38.447 Jul 43 45.84 1.97 47.828 Aug 47 44.42 0.95 45.379 Sep 56 45.71 1.05 46.76
10 Oct 52 50.85 2.28 58.1311 Nov 55 51.42 1.76 53.1912 Dec 54 53.21 1.77 54.9813 Jan – 53.61 1.36 54.96
![Page 32: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/32.jpg)
Adjusted Exponential SmoothingForecasts
12-32
70 –
60 –
50 –
40 –
30 –
20 –
10 –
0 –| | | | | | | | | | | | |1 2 3 4 5 6 7 8 9 10 11 12 13
Actual
Dem
and
Period
Forecast ( = 0.50)
Adjusted forecast ( = 0.30)
![Page 33: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/33.jpg)
Linear Trend Line
12-33
y = a + bx
wherea = interceptb = slope of the linex = time periody = forecast for demand for period x
b =
a = y - b x
wheren = number of periods
x = = mean of the x values
y = = mean of the y values
xy - nxy
x2 - nx2
xn
yn
![Page 34: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/34.jpg)
Least Squares Example
12-34
x(PERIOD) y(DEMAND) xy x2
1 73 37 12 40 80 43 41 123 94 37 148 165 45 225 256 50 300 367 43 301 498 47 376 649 56 504 81
10 52 520 10011 55 605 12112 54 648 144
78 557 3867 650
![Page 35: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/35.jpg)
Least Squares Example
12-35
x = = 6.5
y = = 46.42
b = = =1.72
a = y - bx= 46.42 - (1.72)(6.5) = 35.2
3867 - (12)(6.5)(46.42)650 - 12(6.5)2
xy - nxyx2 - nx2
781255712
![Page 36: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/36.jpg)
12-36
Linear trend line y = 35.2 + 1.72x
Forecast for period 13 y = 35.2 + 1.72(13) = 57.56 units
70 –
60 –
50 –
40 –
30 –
20 –
10 – | | | | | | | | | | | | |1 2 3 4 5 6 7 8 9 10 11 12 13
Actual
Dem
and
Period
Linear trend line
![Page 37: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/37.jpg)
Seasonal Adjustments
12-37
Repetitive increase/ decrease in demand Use seasonal factor to adjust forecast
Seasonal factor = Si =Di
D
![Page 38: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/38.jpg)
Seasonal Adjustment
12-38
2002 12.6 8.6 6.3 17.5 45.0
2003 14.1 10.3 7.5 18.2 50.1
2004 15.3 10.6 8.1 19.6 53.6
Total 42.0 29.5 21.9 55.3 148.7
DEMAND (1000’S PER QUARTER)
YEAR 1 2 3 4 Total
S1 = = = 0.28 D1
D
42.0148.7
S2 = = = 0.20 D2
D
29.5148.7
S4 = = = 0.37 D4
D
55.3148.7
S3 = = = 0.15 D3
D
21.9148.7
![Page 39: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/39.jpg)
Seasonal Adjustment
12-39
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
y = 40.97 + 4.30x = 40.97 + 4.30(4) = 58.17
For 2005
![Page 40: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/40.jpg)
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
12-40
![Page 41: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/41.jpg)
Mean Absolute Deviation (MAD)
12-41
where t= period number
Dt= demand in period t
Ft= forecast for period t
n= total number of periods= absolute value
Dt - Ft nMAD =
![Page 42: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/42.jpg)
12-42
MAD Example
1 37 37.00 – –2 40 37.00 3.00 3.003 41 37.90 3.10 3.104 37 38.83 -1.83 1.835 45 38.28 6.72 6.726 50 40.29 9.69 9.697 43 43.20 -0.20 0.208 47 43.14 3.86 3.869 56 44.30 11.70 11.70
10 52 47.81 4.19 4.1911 55 49.06 5.94 5.9412 54 50.84 3.15 3.15
557 49.31 53.39
PERIOD DEMAND, Dt Ft ( =0.3) (Dt - Ft) |Dt - Ft|
![Page 43: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/43.jpg)
MAD Calculation
12-43
Dt - Ft nMAD =
=
= 4.85
53.3911
![Page 44: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/44.jpg)
Other Accuracy Measures
12-44
Mean absolute percent deviation (MAPD)
MAPD =|Dt - Ft|
Dt
Cumulative error
E = et
Average error
E =et
n
![Page 45: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/45.jpg)
Comparison of Forecasts
12-45
FORECAST MAD MAPD E (E)
Exponential smoothing (= 0.30) 4.85 9.6% 49.31 4.48
Exponential smoothing (= 0.50) 4.04 8.5% 33.21 3.02
Adjusted exponential smoothing 3.81 7.5% 21.14 1.92
(= 0.50, = 0.30)
Linear trend line 2.29 4.9% – –
![Page 46: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/46.jpg)
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
12-46
Tracking signal = =(Dt - Ft)
MAD
E
MAD
![Page 47: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/47.jpg)
Tracking Signal Values
12-47
1 37 37.00 – – –2 40 37.00 3.00 3.00 3.003 41 37.90 3.10 6.10 3.054 37 38.83 -1.83 4.27 2.645 45 38.28 6.72 10.99 3.666 50 40.29 9.69 20.68 4.877 43 43.20 -0.20 20.48 4.098 47 43.14 3.86 24.34 4.069 56 44.30 11.70 36.04 5.01
10 52 47.81 4.19 40.23 4.9211 55 49.06 5.94 46.17 5.0212 54 50.84 3.15 49.32 4.85
DEMAND FORECAST, ERROR E =PERIOD Dt Ft Dt - Ft (Dt - Ft) MAD
–1.002.001.623.004.255.016.007.198.189.2010.17
TRACKINGSIGNAL
TS3 = = 2.006.103.05
![Page 48: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/48.jpg)
Tracking Signal Plot
12-48
3 –
2 –
1 –
0 –
-1 –
-2 –
-3 –
| | | | | | | | | | | | |0 1 2 3 4 5 6 7 8 9 10 11 12
Tra
ckin
g si
gnal
(M
AD
)
Period
Exponential smoothing ( = 0.30)
Linear trend line
![Page 49: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/49.jpg)
Statistical Control Charts
12-49
=(Dt - Ft)2
n - 1
Using we can calculate statistical control limits for the forecast error
Control limits are typically set at 3
![Page 50: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/50.jpg)
Statistical Control Charts
12-50
Err
ors
18.39 –
12.24 –
6.12 –
0 –
-6.12 –
-12.24 –
-18.39 –
| | | | | | | | | | | | |0 1 2 3 4 5 6 7 8 9 10 11 12
Period
UCL = +3
LCL = -3
![Page 51: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/51.jpg)
Time Series Forecasting Using Excel
• Excel can be used to develop forecasts:• Moving average
• Exponential smoothing
• Adjusted exponential smoothing
• Linear trend line
12-51
![Page 52: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/52.jpg)
Exponentially Smoothed and Adjusted Exponentially Smoothed Forecasts
12-52
=B5*(C11-C10)+(1-B5)*D10
=C10+D10
=ABS(B10-E10)
=SUM(F10:F20)
=G22/11
![Page 53: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/53.jpg)
Demand and Exponentially Smoothed Forecast
12-53
Click on “Insert” then “Line”
![Page 54: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/54.jpg)
Data Analysis Option
12-54
![Page 55: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/55.jpg)
Forecasting With Seasonal Adjustment
12-55
![Page 56: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/56.jpg)
Forecasting With OM Tools
12-56
![Page 57: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/57.jpg)
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
12-57
![Page 58: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/58.jpg)
Linear Regression
12-58
y = a + bx a = y - b x
b =
wherea = interceptb = slope of the line
x = = mean of the x data
y = = mean of the y data
xy - nxy
x2 - nx2
xn
yn
![Page 59: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/59.jpg)
Linear Regression Example
12-59
x y(WINS) (ATTENDANCE) xy x2
4 36.3 145.2 166 40.1 240.6 366 41.2 247.2 368 53.0 424.0 646 44.0 264.0 367 45.6 319.2 495 39.0 195.0 257 47.5 332.5 49
49 346.7 2167.7 311
![Page 60: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/60.jpg)
Linear Regression Example
12-60
x= = 6.125
y= = 43.36
b=
=
= 4.06
a= y - bx= 43.36 - (4.06)(6.125)= 18.46
498
346.98
xy - nxy2
x2 - nx2
(2,167.7) - (8)(6.125)(43.36)(311) - (8)(6.125)2
![Page 61: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/61.jpg)
Linear Regression Example
12-61
| | | | | | | | | | |0 1 2 3 4 5 6 7 8 9 10
60,000 –
50,000 –
40,000 –
30,000 –
20,000 –
10,000 –
Linear regression line, y = 18.46 + 4.06x
Wins, x
Atte
ndan
ce, y
y= 18.46 + 4.06(7)= 46.88, or 46,880
Attendance forecast for 7 wins
![Page 62: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/62.jpg)
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
12-62
![Page 63: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/63.jpg)
n xy - x y
[n x2 - ( x)2] [n y2 - ( y)2]r =
Coefficient of determination r2 = (0.947)2 = 0.897
r =(8)(2,167.7) - (49)(346.9)
[(8)(311) - (49)2] [(8)(15,224.7) - (346.9)2]
r = 0.947
Computing Correlation
Copyright 2011 John Wiley & Sons, Inc. 12-63
![Page 64: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/64.jpg)
Regression Analysis With Excel
12-64
=INTERCEPT(B5:B12,A5:A12)
=CORREL(B5:B12,A5:A12)=SUM(B5:B12)
![Page 65: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/65.jpg)
Regression Analysis with Excel
12-65
![Page 66: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/66.jpg)
Regression Analysis With Excel
12-66
![Page 67: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/67.jpg)
Multiple Regression
12-67
Study the relationship of demand to two or more independent variables
y = 0 + 1x1 + 2x2 … + kxk
where0 =the intercept
1, … , k =parameters for the
independent variablesx1, … , xk=independent variables
![Page 68: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/68.jpg)
Multiple Regression With Excel
12-68
r2, the coefficientof determination
Regression equationcoefficients for x1 and x2
![Page 69: Forecasting ppt @ doms](https://reader035.vdocuments.us/reader035/viewer/2022081508/5565ca98d8b42a5b488b51cf/html5/thumbnails/69.jpg)
Multiple Regression Example
12-69
y = 19,094.42 + 3560.99 x1 + .0368 x2
y = 19,094.42 + 3560.99 (7) + .0368 (60,000)
= 46,229.35