Saturday, October 30, 2010

Excel's Most Basic Forecasting Tool - The Simple Moving Average

Excel's Most Basic

Forecasting Tool -

The Simple Moving

Average



Forecasting involves prediction based upon events that have already occurred. Although it is impossible to guarantee an outcome, prediction is necessary to allow for planning. Howard H. Stevenson writes that prediction in business "... is at least two things: Important and hard."


The ability to forecast sales and marketing data is an essential prerequisite of marketing intelligence. Marketing analytics correctly adapted for demand forecasting provides a solid pillar for any effective marketing plan and strategy.



The statistical method that will be discussed in this and the next few article called time series forecasting. a time series is a sequence of data points, measured typically at successive times spaced at uniform time intervals.

The most basic forecasting technique is called the Simple Moving Average. This article will explain how to implement this forecasting method. This article will also show how to use a technique called the Mean Absolute Deviation (MAD) evaluate the accuracy of the Simple Moving Average.


Simple Moving Average

The Simple Moving Average provides an expected result for a new period based upon the average of the results of the n previous periods. The following formula shows how to calculate the Simple Moving Average for n previous periods is:


Forecasted Result for New Period = Average of all results for the most recent n periods


= ( Σ Result for each of previous n periods ) / n

For example, For the following data:
Year      Actual
1            4000
2            5000
3            5000
4            5500
5            6000
6            5000
7            6000
8            6570

Here are the calculations for a 2-year Simple Moving Average:


Excel's Most Basic Forecasting Tool - The Simple Moving Average
Click On Image to See Larger Version



MAD – Mean Absolute Deviation

MAD is a technique used to gauge the accuracy of a forecasting technique. The MAD is the average absolute error.


The larger the MAD, the less accurate the forecasting technique is. The MAD for n forecasted periods is the following:


MAD for n periods = ( Σ Actual result – Forecasted Result for each period ) / n


The MAD for the above 6 forecasted period would be calculated as follows:

Excel's Most Basic Forecasting Tool - The Simple Moving Average
Click On Image to See Larger Version

We can see that the MAD using the Simple 2-Year Moving Average is 678. That means that this forecasting method had an average error of 678 for each forecasted period.


 Let’s calculate the MAD using the Simple 3-Year Moving Average to determine whether the 3-Year Moving Average provides a more accurate forecast for this data than the 2-Year Moving Average.

Here are the calculations for a 3-year Simple Moving Average:

Excel's Most Basic Forecasting Tool - The Simple Moving Average
Click On Image to See Larger Version

The MAD for the above 5 forecasted period would be calculated as follows:

Excel's Most Basic Forecasting Tool - The Simple Moving Average
Click On Image to See Larger Version

We can see that the MAD using the Simple 3-Year Moving Average is 714. That means that this forecasting method had an average error of 678 for each forecasted period.



Since the MAD for the Simple 2-Year Moving Average (678) is lower than the MAD for the Simple 3-Year Moving Average (714), we can state that the Simple 2-Year Moving Average has provided a more accurate forecast than the Simple Moving 3-Year Average.



Here is a link to this article if you wish to link to it:

Excel's Most Basic Forecasting Tool - The Simple Moving Average
Kruskal-Wallis Nonparametric statistical test in excel

If You Like This, Then Share It...
Excel's Most Basic Forecasting Tool - The Simple Moving Average Excel's Most Basic Forecasting Tool - The Simple Moving Average Excel's Most Basic Forecasting Tool - The Simple Moving Average Excel's Most Basic Forecasting Tool - The Simple Moving Average Excel's Most Basic Forecasting Tool - The Simple Moving Average Excel's Most Basic Forecasting Tool - The Simple Moving Average Excel's Most Basic Forecasting Tool - The Simple Moving Average

1 comment:

  1. This Helped me a lot. I am working on a forecasting problem, and that is just what I needed

    ReplyDelete