*58*

One of the most common metrics used to measure the forecasting accuracy of a model is **MAPE**, which stands for **mean absolute percentage error**.

The formula to calculate MAPE is as follows:

**MAPE** = (1/n) * Σ(|actual – forecast| / |actual|) * 100

where:

**Σ**– a fancy 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 8% means that the average difference between the forecasted value and the actual value is 8%.

However, MAPE performs poorly with low volume data. For example, if the actual demand for some item is 2 and the forecast is 1, the value for the absolute percent error will be |2-1| / |2| = 50%, which makes it seem like the forecast error is quite high, despite the forecast only being off by one unit.

Thus, an alternative to MAPE is **Weighted M****APE**, which is calculated as:

**Weighted MAPE** = Σ(|actual – forecast| / |actual|) * 100 * actual / Σ(actual)

By weighting the percentage errors based on volume, we can get a better idea of the true error.

This tutorial explains how to calculate Weighted MAPE in Excel.

**Example: Weighted MAPE in Excel**

To calculate Weighted MAPE in Excel, we can perform the following steps:

**Step 1: Enter the actual values and forecasted values in two separate columns.**

**Step 2: Calculate the weighted error for each row.**

Recall that the weighted error is calculated as: |actual-forecast| / |actual| * 100 * actual. We will use this formula to calculate the weighted error for each row.

Column D displays the weighted error and Column E shows the formula we used:

We will repeat this formula for each row:

**Step 3: Find the sum of actual values.**

**Step 4: Calculate the Weighted MAPE.**

Lastly, we will calculated the Weighted MAPE by dividing the total weighted errors by the sum of the actual values:

The Weighted MAPE turns out to be **5.92****%**.

**Additional Resources**

How to Calculate Mean Absolute Percentage Error (MAPE) in Excel

How to Calculate Mean Squared Error (MSE) in Excel