A moving average forecast uses a moving average of a certain number of previous periods to forecast the value of the next period.
The following example shows how to calculate a moving average forecast in Excel.
Example: How to Calculate Moving Average Forecast in Excel
Suppose we have the following dataset that shows the total sales made by some company during 20 consecutive days:
Suppose we would like to calculate a 5-day moving average forecast.
This means we will use the average sales value of the previous 5 days as the forecast value for the next day.
To do so, we can type the following formula into cell C8:
We can then click and drag this formula down to each remaining cell in column C:
The values in column C represent the 5-day moving average forecasted values of sales.
For example, the forecasted sales value on 1/6/2023 is 11.
To quantify how well our forecasted values match the actual sales values we can calculate the MAPE (mean absolute percentage error), which uses the following formula:
MAPE = (1/n) * Σ(|actual – forecast| / |actual|) * 100
- Σ – a symbol that means “sum”
- n – sample size
- actual – the actual data value
- forecast – the forecasted data value
MAPE is commonly used because it’s easy to interpret and easy to explain. For example, a MAPE value of 11.5% means that the average difference between the forecasted values and the actual values is 11.5%.
The lower the value for MAPE, the better a model is able to forecast values.
To calculate the MAPE for this forecast model, we can type the following formula into cell D7:
We can then click and drag this formula down to each remaining cell in column D:
Lastly, we can type the following formula into cell D22 to get the MAPE for this forecast model:
The following screenshot shows how to use this formula in practice:
We can see that the MAPE of this model is about 19.12%.
This tells us that the average difference between the sales values forecasted by the 5-day moving average and the actual sales values is 19.12%.
If we’d like, we can then repeat this process using a different interval such as a 10-day moving average forecast to determine if this forecast leads to a lower MAPE value.
The following tutorials explain how to perform other common tasks in Excel: