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.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate a Weighted Moving Average in Excel

How to Calculate a Cumulative Average in Excel

How to Calculate an Exponential Moving Average in Excel