How to Calculate a Moving Average Forecast in Excel

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:

=AVERAGE(B2:B6)

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

where:

Σ – 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:

=ABS(B7-C7)/B7*100

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:

=AVERAGE(D7:D21)

The following screenshot shows how to use this formula in practice:

Excel moving average forecast

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.