Forecasting
Forecastingtechniques (pg. 436 Exhibit 11.1)
1. Statistical (Time Series, Causal)
2. Judgement/Qualitative (Expert opinion, Market Survey, Delphi)
Time series analysis
1. Simple moving average
2. Weighted moving average
3. Exponential smoothing
4. Regression analysis
An Example
Month | Demand | 3-month Moving Average | 3-month Wt. Moving Average (weights: 0.2, 0.3, 0.5) | Exponential Smoothing (alpha = 0.1) |
1 | 650 | |||
2 | 700 | 0.1*650+0.9*650 = 650 | ||
3 | 810 | 0.1*700+0.9*650 = 655 | ||
4 | 800 | (650+700+810)/3 = 720 | 0.2*650+0.3*700+0.5*810 = 745 | 0.1*810+0.9*655 = 670.5 |
5 | 900 | (700+810+800)/3 = 770 | 0.2*700+0.3*810+0.5*800 = 783 | 0.1*800+0.9*670.5 = 683.5 |
6 | 700 | (810+800+900)/3 = 837 | 0.2*810+0.3*800+0.5*900 = 852 | 0.1*900+0.9*683.5 = 705.2 |
7 | <![if !supportEmptyParas]><![endif]> | (800+900+700)/3 = 800 | 0.2*800+0.3*900+0.5*700 = 780 | 0.1*700+0.9*705.2 = 704.7 |
Illustration
Pg.472 Problem 2
pg473 Problem 9
Forecasting errors
Mean Absolute Deviation (MAD) (pg.448)
An Example
Demand | 3-month Moving Average | Deviation | Absolute Deviation |
800 | 720 | 800-720 = 80 | 80 |
900 | 770 | 900-770 = 130 | 130 |
700 | 837 | 700-837 = -137 | 137 |
Sum of Absolute Deviation = 80+130+137 = 347
MAD = 347/3 = 115.7
Demand | 3-month Wt. Moving Average | Deviation | Absolute Deviation |
800 | 745 | 800-745 = 55 | 55 |
900 | 783 | 900-783 = 117 | 117 |
700 | 852 | 700-852 = -152 | 152 |
Sum of Absolute Deviation = 55+117+152 = 324
MAD = 324/3 = 108
Demand | Exponential Smoothing | Deviation | Absolute Deviation |
800 | 670.5 | 800-670.5 = 129.5 | 129.5 |
900 | 683.5 | 900-683.5 = 216.5 | 216.5 |
700 | 705.2 | 700-705.2 = -5.2 | 5.2 |
<![if !supportEmptyParas]><![endif]>
Sum of Absolute Deviation =129.5+216.5+5.2 = 351.2
<![if !supportEmptyParas]><![endif]>
MAD = 351.2/3 = 117.1
<![if !supportEmptyParas]><![endif]>
Hence, the 3-mth weighted movingaverage has the lowest MAD and is the best forecast method among the three.
<![if !supportEmptyParas]><![endif]>
Control limits for a range of MADs (Pg.450 Exhibit 11.11)
Number of MADs | Accuracy |
+/- 1 | 57% |
+/- 2 | 88.9% |
+/- 3 | 98.3% |
+/- 4 | 99.9% |
With 57% accuracy, the forecast demand for July using 3-mth Wt. MovingAverage = 780 +/- 108 (672 to 888)
With 88.9% accuracy, the forecast demand for July using 3-mth Wt. MovingAverage = 780+/- 2*108 (564 to 996)
Exercise:
pg.471 Problem 3, 11
Assumptions
1. Linear -- the past data and future projections are fall about astraight line (least squares method: minimize the sum of squared forecasterror)
2. Time is the independent variable, x
Y = a + bx
An example
Month (x) | Profit (y) | xy | x2 | |
1 | 31 | 31 | 1 | |
2 | 40 | 80 | 4 | |
3 | 30 | 90 | 9 | |
4 | 34 | 136 | 16 | |
5 | 25 | 125 | 25 | |
6 | 20 | 120 | 36 | |
Total | 21 | 180 | 582 | 91 |
Average | 3.50 | 30.00 | 97.00 | 15.17 |
b = (582-6*3.5*30)/(91-6*3.5*3.5) = -2.7
a = 30-(-2.7)*3.5 = 39.6
Y = 39.6 - 2.7x
Coding the time variable to simplify computation (i.e.,
Case 1: odd number of time elements
Time | Code |
January | -2 |
February | -1 |
March | |
April | 1 |
May | 2 |
Case 2: even number of time elements
Time | Code |
January | -3 |
February | -1 |
March | 1 |
April | 3 |
Ifthen
Example
<![if !supportEmptyParas]><![endif]> | Month | Profit (y) | Code (x) | x2 | xy |
<![if !supportEmptyParas]><![endif]> | 1 | 31 | -5 | 25 | -155 |
<![if !supportEmptyParas]><![endif]> | 2 | 40 | -3 | 9 | -120 |
<![if !supportEmptyParas]><![endif]> | 3 | 30 | -1 | 1 | -30 |
<![if !supportEmptyParas]><![endif]> | 4 | 34 | 1 | 1 | 34 |
<![if !supportEmptyParas]><![endif]> | 5 | 25 | 3 | 9 | 75 |
<![if !supportEmptyParas]><![endif]> | 6 | 20 | 5 | 25 | 100 |
Total | 21 | 180 | 70 | -96 | |
Average | 3.50 | 30.00 | 0.00 | 11.67 | -16.00 |
b = -96/70 = -1.4
a = 30
Y = 30 - 1.4x
Forecasting error
Standard error of estimate
or
<![if !supportEmptyParas]><![endif]> | Month (x) | Profit (y) | xy | y2 |
<![if !supportEmptyParas]><![endif]> | 1 | 31 | 31 | 961 |
<![if !supportEmptyParas]><![endif]> | 2 | 40 | 80 | 1600 |
<![if !supportEmptyParas]><![endif]> | 3 | 30 | 90 | 900 |
<![if !supportEmptyParas]><![endif]> | 4 | 34 | 136 | 1156 |
<![if !supportEmptyParas]><![endif]> | 5 | 25 | 125 | 625 |
<![if !supportEmptyParas]><![endif]> | 6 | 20 | 120 | 400 |
Total | 21 | 180 | 582 | 5642 |
= 5.25or
= 5.25
Number of Syx | Accuracy |
+/- 1 | 68% |
+/- 2 | 95.5% |
+/- 3 | 99.7% |
Formula review (pg.466)
Exercise:
Pg.471 Problems 17
Using Excel
1. Click Tools, Click Data Analysis
2. Choose Moving Average/Exponential Smoothing/Regression
Method | Parameter | Excel terminology | Reminder |
N-mth Moving average | N | Interval | Output range should be one cell lower than the input range |
Exponential Smoothing | 1-a | Damping factor | Output range should be at the same row as the input range |
Regression | a b | Intercept X variable or Label | Label should be checked if you include the column heading in your input ranges |
<![if !supportEmptyParas]><![endif]>