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
- How Do You Calculate Mean Absolute Percent Error (MAPE)?
- 1. Insert Actual And Forecasted values in Excel separately
- 2. Find the Absolute Percentage Error
- 3. Calculate the Average Of the Absolute Percentage Error
- Drawback Of Using Mean Absolute Percentage Error (MAPE)
- How Do You Calculate Weighted MAPE (WMAPE)?
- Why We Use Weighted MAPE?
- 1. Insert Actual And Forecasted values separately
- 2. Find Weighted Error
- 3. Calculate the Sum Of Actual Values
- 4. Calculate Weighted MAPE
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
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.
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.
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.
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.
3. Calculate the Sum Of Actual Values
Before finding the Weighted MAPE it is necessary to calculate the 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.
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.
