# 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: 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.