excel forecasting
Calikush.
ForecastingPart2.pptxTime Series Forecasting: Part 2 Error Statistics, Moving Average, and Exponential Smoothing
Steven Coy Ph.D.
Professor of Management
Forecast Error
Forecast error formula
Some facts about forecast error:
Error is not really a bad thing
Error is inevitable
No forecasting model is always right!
Most forecasting models are always wrong!
=
Forecast Accuracy Metrics
Mean Absolute Deviation (MAD) or Mean Absolute Error (MAE) weights all errors evenly.
Mean Squared Error (MSE) weights errors according to their squared values.
Mean Absolute Percentage Error (MAPE_ weights errors according to relative error.
Bias measures the degree to which the forecast overstates or understates the actual value.
3
Measures of Forecast Accuracy Gasoline Sales
Forecasting method is called the Naïve method.
Next forecast is last observed value:
From Business Analytics by Camm, Cochran, Fry, Ohlmann, Anderson, Sweeney, and Williams. 2015
4
Measures of Forecast Accuracy Gasoline Sales
Compare the accuracy of the two forecasting methods by comparing the values of MAE, MSE, and MAPE for each method.
Which is the better technique for this data?
Material from Business Analytics by Camm, Cochran, Fry, Ohlmann, Anderson, Sweeney, and Williams. 2015
| Naïve method | Average of Past Values | |
| MAE | 3.73 | 2.69 |
| MSE | 16.27 | 8.10 |
| MAPE | 19.24% | 12.85% |
5
Moving Average
The average of the most recent k actual values in the time series.
Moving average forecast of k periods
6
General Merchandise Sales Example: Moving Average
Note: The Retail Sales Data appear in column J of the spreadsheet.
This example is covered in the Time Series Forecasting Examples Videos.
7
Line Plot of Actual and Moving Average Forecasts
8
Enlarged View of MA Forecasts
Increasing number of periods in MA results in smoother forecasts. Green is MA(3) and red is MA(6).
Exponential Smoothing
Exponential smoothing: Uses a weighted average of past time series values as a forecast.
Exponential Smoothing Forecast
= α + (1 – α)
In other courses, you may have learned the algebraic equivalent:
= α(- )
Smoothing constant (α)
The weight given to the actual value in period t.
Weight given to the forecast in period t is 1 – α.
Note: In Excel, the “damping factor is 1 – α.
Alpha value on past forecasts exponentially decays over time.
10
General Merchandise Sales Example: Exponential Smoothing
Note: The Retail Sales Data appear in column J, and the ES forecasts appear in column M.
This example is covered in the Time Series Forecasting Examples Videos.
11
Line Plot of Actual and Exponential Smoothing Forecasts
12
n
å
´
-
=
100
Actual
Forecast
Actual
MAPE
t
t
t
n
å
-
=
t
t
Forecast
Actual
MAD
(
)
2
t
t
1
Forecast
Actual
MSE
-
-
=
å
n
n
å
-
=
t
t
Forecast
Actual
Bias
