Table of Contents
Weighted MAPE (Mean Absolute Percentage Error) is a commonly used metric for measuring the accuracy of forecasting models. It takes into account both the magnitude and direction of forecast errors. In order to calculate Weighted MAPE in Excel, you will need to have two sets of data: the actual values and the forecasted values. Once you have this data, you can use the formula =SUMPRODUCT(ABS(Actual-Forecast),Actual)/SUM(Actual) to calculate the Weighted MAPE. This formula takes into account the weight of each data point, giving a more accurate representation of the forecast error. By following this method, you can easily determine the accuracy of your forecasting model and make necessary adjustments for future predictions.
Calculate Weighted MAPE in Excel
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 MAPE, 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.
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%.