To optimize your forecast, whether moving average, exponential smoothing or another form of a forecast, you need to calculate and evaluate MAD, MSE, RMSE, and MAPE. With Excel 2016 or later, this is easy to do.

The **Mean Absolute Deviation (MAD) **is the sum of absolute differences between the actual value and the forecast divided by the number of observations. MAD is the same as MAE, Mean Absolute Error.

**Mean square error (MSE) **is probably the most commonly used error metric. It penalizes larger errors because squaring larger numbers has a greater impact than squaring smaller numbers. The MSE is the sum of the squared errors divided by the number of observations.

**The Root Mean Square Error (RMSE) **is the square root of the MSE. RMSE is used to convert MSE back into the same units as the actual data.

**Mean Absolute Percentage Error (MAPE) **is the average of absolute errors divided by actual observation values. MAPE should not be used if there are zeros or near-zeros in the actual data. **SMAPE,**** Symmetric Mean Absolute Percent Error**, can be used where there are zero or near-zeros values in the actual data.

**Symmetric Mean Absolute Percent Error (SMAPE) **is an alternative to Mean Absolute Percent Error (MAPE) when there are zero or near-zero values in your actual observations. SMAPE self-limits to an error rate of 200%, reducing the influence of zero or near-zeros observations. SMAPE is the forecast minus actuals divided by the sum of forecasts and actuals as expressed in this formula:

Here is an image of the Excel workbook you can download:

View the video explaining the workbook here: https://youtu.be/H_N5_vxs8Tg

Video explaining how to interpre the error metrics here: https://youtu.be/vxtxrcdmqbw

Thanks for the help, professor!

Amazing!

Thank you

Thank you for the positive feedback!

how did you get the forecast

This is just an example of a typical forecast. You can develop your forecast using many different methods: simple regression or one of the many time-series methods. These error metrics can be applied to a variety of numerical forecasts.

Thank you!!

God bless you richly, please I need such on AIC, BIC and loglikelihood. Thanks

Thank you sir, this really good, I’ve been trying to get MAPE on every way in excel but didn’t work until i got this, thank you.

Thank you, sir, I cannot download the excel file, is it still available? Please, could you provide me with it?

Please try again. I think I fixed the download link.