Forecasting Methods and Errors in Decision Models
Explore forecasting methods such as exponential smoothing, regression, and trend analysis, along with measuring forecast errors like Mean Absolute Error and Root Mean Squared Error in decision models. Learn how to analyze historical data, project future patterns, and assess forecast quality effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Overview Forecasting Methods Exponential Smoothing Simple Trend (Holt s Method) Seasonality (Winters Method) Regression Trend Seasonality Lagged Variables Decision Models -- Prof. Juran 2
Forecasting 1. Analysis of Historical Data Time Series (Extrapolation) Regression (Causal) 2. Projecting Historical Patterns into the Future 3. Measurement of Forecast Quality Decision Models -- Prof. Juran 3
Measuring Forecasting Errors Mean Absolute Error Mean Absolute Percent Error Root Mean Squared Error R-square Decision Models -- Prof. Juran 4
Mean Absolute Error n = i MAE = i 1 n Decision Models -- Prof. Juran 5
Mean Absolute Percent Error n =1 i Y i MAPE= i 100 % * n n =1 i Y i Or, alternatively = i 100 % * n Decision Models -- Prof. Juran 6
Root Mean Squared Error n ( ) = 2 i RMSE = i 1 n SSE = n Decision Models -- Prof. Juran 7
R-Square SSE= SSR = 1 2 R TSS TSS Decision Models -- Prof. Juran 8
Trend Analysis Part of the variation in Y is believed to be explained by the passage of time Several convenient models available in an Excel chart Decision Models -- Prof. Juran 9
Example: Revenues at GM GM Revenue 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 10
Right-click on the data series Superimpose a trend line on the graph: Decision Models -- Prof. Juran 11
GM Revenue - Linear Trend 60000 50000 40000 Revenue 30000 20000 y = 340.23x + 31862 R2 = 0.6618 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 12
GM Revenue - Logarithmic Trend 60000 50000 40000 Revenue 30000 20000 y = 5162.3Ln(x) + 24937 R2 = 0.6601 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 13
GM Revenue - Polynomial Trend 60000 50000 40000 Revenue 30000 20000 y = -5.6121x2 + 604x + 29752 R2 = 0.6872 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 14
GM Revenue - Power Trend 60000 50000 40000 Revenue 30000 20000 y = 26532x0.1372 R2 = 0.6783 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 15
GM Revenue - Exponential Trend 60000 50000 40000 Revenue 30000 y = 32044e0.0088x R2 = 0.6505 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 16
You can also show moving-average trend lines, although showing the equation and R-square are no longer options: GM Revenue - 4-Period Moving Average 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 17
GM Revenue - 3-Period Moving Average 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 18
GM Revenue - 2-Period Moving Average 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 19
Simple Exponential Smoothing Basically, this method uses a forecast formula of the form: = tF+ t L k Forecast k periods in the future= Current Level = Weighted Current Observed Value + Weighted Previous Level ( ) 1 1 + = t t L Y Note that the weights must add up to 1.0. Decision Models -- Prof. Juran 20
Why is it called exponential? ( ) = = + + 1 tL Y Y 1 L t t Y 1 ( ) ( ) ( ) 2 3 + + + 1 Y 1 Y ... t t 1 t 2 t 3 Decision Models -- Prof. Juran 21
Example: GM Revenue GM Revenue 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 22
In this spreadsheet model, the forecasts appear in column G. A B 0.100 C D E F G H I J K error^2 L M 1 2 3 4 5 6 7 8 9 Alpha GM_Rev 29200 31300 28900 33600 32000 35200 29400 35800 35000 36658 30138 37268 37495 40392 34510 42553 SmLevel 29200.0 29410.0 29359.0 29783.1 30004.8 30524.3 30411.9 30950.7 31355.6 31885.9 31711.1 32266.8 32789.6 33549.8 33645.8 34536.6 Forecast Error abs(error) abs(%error) 1-91 2-91 3-91 4-91 1-92 2-92 3-92 4-92 1-93 2-93 3-93 4-93 1-94 2-94 3-94 4-94 =AVERAGE(I3:I47) =SQRT(AVERAGE(K3:K47)) =AVERAGE(J3:J47) MAE RMSE MAPE 4014.376 4690.9738 11.148% 29200.0 29410.0 29359.0 29783.1 30004.8 30524.3 30411.9 30950.7 31355.6 31885.9 31711.1 32266.8 32789.6 33549.8 33645.8 2100.0 -510.0 4241.0 2216.9 5195.2 -1124.3 5388.1 4049.3 5302.4 -1747.9 5556.9 5228.2 7602.4 960.2 8907.2 2100.0 510.0 4241.0 2216.9 5195.2 1124.3 5388.1 4049.3 5302.4 1747.9 5556.9 5228.2 7602.4 960.2 8907.2 7.2% 1.7% 14.4% 17986081.0 7.4% 4914645.6 17.3% 26990206.8 3.7% 1264075.3 17.7% 29031838.1 13.1% 16396895.8 16.9% 28115204.6 5.5% 3055016.2 17.5% 30879421.8 16.2% 27334420.4 23.2% 57796633.2 2.9% 26.5% 79337354.0 4410000.0 260100.0 =$B$1*E7+(1-$B$1)*F6 =F8 10 11 12 13 14 15 16 17 =E11-G11 =ABS(H13) =ABS(H15/G15) 921924.0 =H17^2 Note that our model assumes that there is no trend. We use a default alpha of 0.10. Decision Models -- Prof. Juran 23
GM Revenue - Simple Smoothing (alpha 0.10) 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 24
We use Solver to minimize RMSE by manipulating alpha. A B 0.350 C D E F G H I J K error^2 1 2 3 4 5 6 7 8 9 Alpha GM_Rev 29200 31300 28900 33600 32000 35200 29400 35800 35000 36658 30138 37268 37495 40392 34510 42553 SmLevel 29200.0 29935.9 29572.9 30984.1 31340.1 32692.7 31538.9 33032.1 33721.7 34750.6 33134.3 34582.8 35603.3 37281.4 36310.2 38497.9 Forecast Error abs(error) abs(%error) 1-91 2-91 3-91 4-91 1-92 2-92 3-92 4-92 1-93 2-93 3-93 4-93 1-94 2-94 3-94 4-94 MAE RMSE MAPE 3275.989 3653.2722 8.584% 29200.0 29935.9 29572.9 30984.1 31340.1 32692.7 31538.9 33032.1 33721.7 34750.6 33134.3 34582.8 35603.3 37281.4 36310.2 2100.0 -1035.9 4027.1 1015.9 3859.9 -3292.7 4261.1 1967.9 2936.3 -4612.6 4133.7 2912.2 4788.7 -2771.4 6242.8 2100.0 1035.9 4027.1 1015.9 3859.9 3292.7 4261.1 1967.9 2936.3 4612.6 4133.7 2912.2 4788.7 2771.4 6242.8 7.2% 3.5% 13.6% 16217616.5 3.3% 1032075.0 12.3% 14898909.3 10.1% 10841859.2 13.5% 18157357.9 6.0% 3872765.0 8.7% 8621982.5 13.3% 21276434.3 12.5% 17087842.8 8.4% 8480779.8 13.5% 22931441.8 7.4% 7680620.3 17.2% 38972198.5 4410000.0 1073072.4 10 11 12 13 14 15 16 17 After optimizing, we see that alpha is 0.350 (instead of 0.10). This makes an improvement in RMSE, from 4691 to 3653. Decision Models -- Prof. Juran 25
GM Revenue - Simple Smoothing (alpha 0.35) 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 26
Exponential Smoothing with Trend: Holt s Method Weighted Current Level = = L + ( tF+ kT ) ( + k t t ( )( )( ) ) ( + )( ) + + 1 1 Y L T k L L T 1 1 1 1 t t t t t t Weighted Current Observation Weighted Current Trend Decision Models -- Prof. Juran 27
A B C D E F G H Forecast I J K L error^2 M N 1 2 3 4 5 6 7 8 9 Smoothing constant(s) Level (alpha) Trend (beta) GM_Rev 29200 31300 28900 33600 32000 35200 29400 35800 35000 36658 30138 37268 37495 40392 34510 42553 SmLevel 29200.000 29757.957 29549.579 30637.081 31048.143 32212.293 31564.039 32760.991 33465.944 34443.591 33457.270 34585.269 35507.934 36980.394 36542.128 38331.485 SmTrend 0.000 26.659 15.429 66.652 83.108 134.760 97.348 149.887 176.407 214.690 157.306 203.686 238.038 297.019 261.887 334.869 Error abs(error) abs(%error) 0.266 0.048 1-91 2-91 3-91 4-91 1-92 2-92 3-92 4-92 1-93 2-93 3-93 4-93 1-94 2-94 3-94 4-94 29200.000 29784.616 29565.008 30703.733 31131.251 32347.053 31661.387 32910.877 33642.352 34658.282 33614.577 34788.955 35745.973 37277.413 36804.015 2100.0 -884.6 4035.0 1296.3 4068.7 -2947.1 4138.6 2089.1 3015.6 -4520.3 3653.4 2706.0 4646.0 -2767.4 5749.0 2100.0 884.6 4035.0 1296.3 4068.7 2947.1 4138.6 2089.1 3015.6 4520.3 3653.4 2706.0 4646.0 2767.4 5749.0 7.2% 3.0% 13.6% 4.2% 13.1% 9.1% 13.1% 6.3% 9.0% 13.0% 10.9% 7.8% 13.0% 7.4% 15.6% 4410000.0 782545.7 16281159.9 1680308.2 16554716.2 8685120.1 17128119.6 4364433.2 9094133.3 20432945.1 13347499.7 7322680.1 21585567.4 7658572.1 33050827.6 =$B$2*E4+(1-$B$2)*(F3+G3) MAE RMSE MAPE 3094.683 3568.391 8.01% =$B$3*(F6-F5)+(1-$B$3)*G5 =F7+G7 =E10-H10 10 11 12 13 14 15 16 17 =ABS(I12) =ABS(I14/H14) =I16^2 Decision Models -- Prof. Juran 28
GM Revenue - Holts Method (Smoothing with Trend) 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Holt s model with optimized smoothing constants. This model is slightly better than the simple model (RMSE drops from 3653 to 3568). Decision Models -- Prof. Juran 29
Exponential Smoothing with Seasonality: Winters Method This method includes an explicit term for seasonality, where M is the number of periods in a season. We will use M = 4 because we have quarterly data. Y )( ) ( = + + t 1 L T tL Level: t 1 t 1 S ( L t M ) ( + ) = t T L 1 T Trend: t t 1 t 1 Y ( ) = + t 1 S tS Seasonality: t M L t Now, for any time k periods in the future, the forecast is given by: tF+ ( )( ) = + L kT S + k t t t k M Note that the trend term is additive, and the seasonality term is multiplicative. Decision Models -- Prof. Juran 30
Weighted Current Seasonal Factor Y ( ) = + t 1 S tS t M L t Weighted Seasonal Factor from Last Year Decision Models -- Prof. Juran 31
A B C D E F G H I J K L M error^2 1 2 3 4 5 6 7 8 9 Smoothing constant(s) Level (alpha) Trend (beta) Seasonality (gamma) GM_Rev 29200 31300 28900 33600 32000 35200 29400 35800 35000 36658 30138 37268 37495 40392 SmLevel 29200.000 29855.671 29573.917 30839.828 31242.711 32527.642 31631.252 32987.284 33649.376 34502.621 33394.029 34492.750 35401.921 36772.011 SmTrend 0.000 24.178 12.897 59.102 71.779 116.515 79.164 126.249 146.008 172.088 124.862 160.774 188.371 231.948 SmSeason 1.000 1.000 1.000 1.000 1.005 1.017 0.986 1.017 1.012 1.026 0.969 1.030 1.022 1.040 Forecast Error abs(error) abs(%error) 0.312 0.037 0.202 1-91 2-91 3-91 4-91 1-92 2-92 3-92 4-92 1-93 2-93 3-93 4-93 1-94 2-94 MAE RMSE MAPE 2670.440 3233.995 6.82% 30898.931 31314.491 32644.157 31710.415 33275.398 34355.316 34181.443 34095.266 35069.261 36509.418 1101.1 3885.5 -3244.2 4089.6 1724.6 2302.7 -4043.4 3172.7 2425.7 3882.6 1101.1 3885.5 3244.2 4089.6 1724.6 2302.7 4043.4 3172.7 2425.7 3882.6 3.6% 12.4% 9.9% 12.9% 5.2% 6.7% 11.8% 9.3% 6.9% 10.6% 1212353.7 15097181.1 10524553.4 16724701.7 2974252.4 5302351.4 16349433.8 10066242.9 5884208.4 15074445.5 =$B$2*(E8/H4)+(1-$B$2)*(F7+G7) =$B$3*(F10-F9)+(1-$B$3)*G9 10 11 12 13 14 15 =$B$4*(E12/F12)+(1-$B$4)*(H8) =(F13+G13)*H10 Winters model with optimized smoothing constants. This model is better than the simple model and the Holt s model (as measured by RMSE). Decision Models -- Prof. Juran 32
GM Revenue - Winters Method (Smoothing with Trend and Seasonality) 60000 50000 40000 Revenue 30000 20000 10000 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Decision Models -- Prof. Juran 33
Forecasting with Regression A B C D E F G H 1 2 3 4 5 6 7 8 9 GM_Rev 29200 31300 28900 33600 32000 35200 29400 35800 35000 36658 30138 37268 37495 40392 34510 42553 43285 42204 GM_EPS -1.28 -1.44 -1.88 -4.25 -0.53 -1.18 -1.86 -1.25 Trend 1Q 2Q 3Q 1-91 2-91 3-91 4-91 1-92 2-92 3-92 4-92 1-93 2-93 3-93 4-93 1-94 2-94 3-94 4-94 1-95 2-95 1 2 3 4 5 6 7 8 9 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 10 11 12 13 14 15 16 17 18 19 0.42 0.92 -0.49 1.28 1.86 2.23 0.4 1.74 2.51 2.39 10 11 12 13 14 15 16 17 18 Decision Models -- Prof. Juran 34
B C D E F G 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 Regression Statistics Multiple R R Square Adjusted R Square Standard Error Observations 0.8852 0.7835 0.7624 2736.1392 46 ANOVA df SS MS F 37.1026 Significance F Regression Residual Total 4 1111067275.3109 277766818.8277 41 306944777.4065 45 1418012052.7174 0.0000 7486457.9855 Coefficients 33286.7628 335.8508 -1289.9144 423.4015 -4582.6038 Standard Error 1101.4629 t Stat P-value Intercept Trend 1Q 2Q 3Q 30.2205 11.0444 -1.1290 0.3707 -3.9265 0.0000 0.0000 0.2655 0.7128 0.0003 30.4091 1142.5337 1142.1290 1167.0899 Decision Models -- Prof. Juran 36
Which Method is Better? The most reasonable statistic for comparison is probably RMSE for smoothing models vs. standard error for regression models, as is reported here: Revenue Regression $99.37 $112.23 $109.22 $2,736.14 $154.25 $12,836.41 $279.45 $1,164.02 $164.20 $969.14 Winters' $76.44 $84.92 $135.33 $3234.00 $103.91 $14,622.26 $191.94 $1,184.06 $258.02 $1,648.61 EPS Regression $0.0874 $0.0205 $0.3727 $1.3882 $0.0687 Winters' $0.1327 $0.0295 $0.5285 $1.2603 $0.0635 Mattel McD Lilly GM MSFT ATT Nike GE Coke Ford $0.6879 $0.5719 $0.2080 $0.6591 $0.0164 $0.4988 $0.2687 $0.7587 $0.0228 $1.3475 The regression models are superior most of the time (6 out of 10 revenue models and 7 out of 10 EPS models). Decision Models -- Prof. Juran 37
For GM, a regression model seems best for forecasting revenue, but a Winters model seems best for earnings: GM Revenue - Winters Method (Smoothing with Trend and Seasonality) GM Revenue - Regression 60000 60000 50000 50000 40000 40000 Revenue Revenue 30000 30000 20000 20000 10000 10000 0 0 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Quarters GM EPS - Winters Method (Smoothing with Trend and Seasonality) GM EPS - Regression 4 4 3 3 2 2 1 1 0 0 Revenue Revenue -1 -1 -2 -2 -3 -3 -4 -4 -5 -5 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 1-91 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 Quarters Quarters Decision Models -- Prof. Juran 38
For Nike, the Winters model is better for revenue, and the regression model is best for earnings. Nike Revenue (Winters) Nike Revenue (Regression) 3500 3500 3000 3000 2500 2500 2000 2000 Revenue Revenue 1500 1500 1000 1000 500 500 0 0 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 1-03 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 1-03 Quarters Quarters Nike EPS (Winters) Nike EPS (Regression) 1.4 1.4 1.2 1.2 1 1 0.8 0.8 0.6 0.6 Revenue Revenue 0.4 0.4 0.2 0.2 0 0 -0.2 -0.2 -0.4 -0.4 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 1-03 1-92 1-93 1-94 1-95 1-96 1-97 1-98 1-99 1-00 1-01 1-02 1-03 Quarters Quarters Decision Models -- Prof. Juran 39
Time series characterized by relatively consistent trends and seasonality favor the regression model. If the trend and seasonality are not stable over time, then Winters method does a better job of responding to their changing patterns. Decision Models -- Prof. Juran 40
Lagged Variables Only applicable in a causal model Effects of independent variables might not be felt immediately Used for advertising s effect on sales Decision Models -- Prof. Juran 41
Example: Motel Chain B C D E F G H I 1 2 3 4 5 6 7 8 9 Sales 1200 880 1800 1050 1700 350 2500 760 2300 1000 1570 2430 1320 1400 1890 3200 2200 1440 4000 4100 Quarter Adv Adv-Lag1 Qtr_1 Qtr_2 Qtr_3 30 * 20 30 15 20 40 15 10 40 50 10 5 50 40 5 20 40 10 20 60 10 5 60 35 5 15 35 70 15 25 70 30 25 60 30 80 60 50 80 1 2 3 4 5 6 7 8 9 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 10 11 12 13 14 15 16 17 18 19 20 21 10 11 12 13 14 15 16 17 18 19 20 Decision Models -- Prof. Juran 42
A B C D E F G 3 4 5 6 7 8 9 Summary measures Multiple R R-Square Adj R-Square StErr of Est 0.9856 0.9714 0.9571 213.2 ANOVA Table 10 11 12 13 14 15 16 17 18 19 20 21 22 df 6 18515047.32 3085841.22 12 545531.63 SS MS F p-value 0.0000 Regression Residual 67.88 45460.97 Regression coefficients Coefficient 98.36 41.58 Std Err 174.96 13.56 3.25 3.13 157.66 145.37 143.04 t-value p-value Lower limit Upper limit 0.5622 0.5843 3.0672 0.0098 1.3959 0.1880 10.8759 0.0000 1.7799 0.1004 -3.3817 0.0055 3.7235 0.0029 Constant Quarter Advertising Advertising_Lag1 Qtr_1 Qtr_2 Qtr_3 -282.9 12.0 -2.5 27.2 -62.9 -808.3 221.0 479.6 71.1 11.6 40.9 624.1 -174.9 844.2 4.53 34.03 280.62 -491.59 532.60 Decision Models -- Prof. Juran 43
A Qtr Sales 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 B C D E F G H I J K L M O Adv Adv-Lag1 41.58 4.53 N P Q R Qtr_2 S 1 2 3 4 5 6 7 8 9 Quarter Adv Adv-Lag1 Qtr_1 Qtr_2 Qtr_3 30 * 20 30 15 20 40 15 10 40 50 10 5 50 40 5 20 40 10 20 60 10 5 60 35 5 15 35 70 15 25 70 30 25 60 30 80 60 50 80 50 50 50 50 50 50 50 50 Forecast Constant Quarter 98.36 Qtr_1 Qtr_3 1200 880 1800 1050 1700 350 2500 760 2300 1000 1570 2430 1320 1400 1890 3200 2200 1440 4000 4100 1 2 3 4 5 6 7 8 9 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 Coefficient 34.03 280.62 -491.59 532.60 802 1504 957 1994 423 2646 783 2205 749 1701 2662 1248 1448 2083 3259 2073 1648 3826 3879 3181 2450 3516 3025 =$M$2+SUMPRODUCT($N$2:$S$2,D5:I5) 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Decision Models -- Prof. Juran 44
Simple Exponential Smoothing Forecast 6,000 Observation Forecast 5,250 4,500 3,750 3,000 2,250 1,500 750 - 1 2 3 4 5 6 7 8 9 10 11 12 Quarter 13 14 15 16 17 18 19 20 21 22 23 24 Decision Models -- Prof. Juran 45
Holt's Forecast 6,000 Observation Forecast 5,250 4,500 3,750 3,000 2,250 1,500 750 - 1 2 3 4 5 6 7 8 9 10 11 12 Quarter 13 14 15 16 17 18 19 20 21 22 23 24 Decision Models -- Prof. Juran 46
Winters' Forecast 6,000 Observation Forecast 5,250 4,500 3,750 3,000 2,250 1,500 750 - 1 2 3 4 5 6 7 8 9 10 11 12 Quarter 13 14 15 16 17 18 19 20 21 22 23 24 Decision Models -- Prof. Juran 47
Multiple Regression Forecast (with Lagged Advertising) 6,000 Observation Forecast 5,250 4,500 3,750 3,000 2,250 1,500 750 - 1 2 3 4 5 6 7 8 9 10 11 12 Quarter 13 14 15 16 17 18 19 20 21 22 23 24 Decision Models -- Prof. Juran 48
Here are measures of model fit for the non-regression models: Simple 769.6 939.9 50.5% Holt's 766.8 866.6 36.7% Winters' 708.0 845.6 47.3% MAE RMSE MAPE The regression model has a standard error of only 213, which is much better than any of the other models. Decision Models -- Prof. Juran 49
Summary Forecasting Methods Exponential Smoothing Simple Trend (Holt s Method) Seasonality (Winters Method) Regression Trend Seasonality Lagged Variables Decision Models -- Prof. Juran 50
