How To Calculate MAPE & Weighted MAPE In Excel

Have you ever heard of Forecasting Accuracy? Forecasting Accuracy is nothing but the closeness of actual quantity to a particular quantity. Here the actual quantity is often called True Value.

Table of Contents

The concept of Mean Absolute Percentage Error (MAPE) is more focused on Forecasting Accuracy. We can measure the forecasting accuracy by using the MAPE model.

Actually, there are many other proven methods but we prefer this one because it’s easy to solve and explain and last it is more accurate. This method is preferred in Business fields when it comes to maintaining the record of sales and demand and supply.

In this post, we are going to cover How to Calculate Mean Absolute Percentage Error (MAPE) in Excel.

How Do You Calculate Mean Absolute Percent Error (MAPE)?

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

If you are willing to calculate MAPE then please follow the given steps carefully.

1. Insert Actual And Forecasted values in Excel separately

Insert Actual And Forecasted values in Excel separately

For calculating MAPE, we are considering working days. Column B indicates the actual values on the other hand Column C indicates Forecasted values.

2. Find the Absolute Percentage Error

Absolute Percentage Error= |actual-forecast| / |actual| * 100

In order to calculate the Absolute Percentage Error, we are using the above formula. We are going to calculate the Absolute Percentage Error for each and every row.

absolute Percentage Error

Column D represents the “Absolute Percentage Error” and Column E represents the formula that we have used in the previous column.

3. Calculate the Average Of the Absolute Percentage Error

If you are willing to calculate Mean Absolute Percentage Error then all you need to do is now just find the average of the absolute percentage error.

Mean Absolute Percentage Error

In my case, the MAPE model has a value of 11.1827.

Drawback Of Using Mean Absolute Percentage Error (MAPE)

Undefined When Actual Value is Zero:- We already know the formula of Absolute Percentage Error is |actual-forecast| / |actual|, it clearly shows that when any of the actual will be zero then we can not define Absolute Percentage Error.

Not Suitable For Low Volume Data:- Suppose, the actual value of any item is 4 and the forecasted value is 2 then the absolute percent error will be |4-2| / |4| = 50%. This represents the forecasting error is too high but we can figure out that it is not the case.

How Do You Calculate Weighted MAPE (WMAPE)?

Why We Use Weighted MAPE?

As we have already discussed that MAPE is not very suitable for Low Volume Data or when sales are very close to 0. thus, we use an alternate that we call Weighted MAPE or often called WMAPE. Again the formula is not too complicated it is very similar to the MAPE.

Weighted MAPE=|actual-forecast| / |actual| * 100 * actual

In order to determine the true error rate, we need to weigh the percentage of errors according to the volume.

1. Insert Actual And Forecasted values separately

As for now, I am using my currently used data that we have already used in MAPE.

Insert Actual And Forecasted values in Excel separately

2. Find Weighted Error

The next step is to calculate the weighted error for each row by using the formula |actual-forecast| / |actual| * 100 * actual. In my excel sheet, column D shows the weighted error, and column E shows the formula.

Find Weighted Error

3. Calculate the Sum Of Actual Values

Before finding the Weighted MAPE it is necessary to calculate the sum of actual values.

Calculate Sum Of Actual Values

4. Calculate Weighted MAPE

In order to calculate the Weighted MAPE, you need to find the total sum of the Weighted Error and divided it by the sum of the Actual Error.

Calculate Weighted MAPE

So, we get a Weighted MAPE of about 10.87

In this tutorial, we discussed “MAPE” and “Weighted MAPE”. We have also seen when we use Mean Absolute Percentage Error and when we use Weighted Mean Absolute Percentage Error In Excel.

Hope you find this post helpful.

Leave a Comment