Often times in analyzing forecasting metrics you are asked to “weight” the results based on something like number of units sold, or dollar value of the item.  You can use the =SUMPRODUCT function to achieve this, but if you want to use a pivot table to quickly analyze the data in multiple ways then it does not quite cut it.  Luckily there is a way to do this in pivot tables!

I am assuming you already know the formulas for MAPE and MAD as well as the basics of pivot tables so I will just jump right into how to weight them.  Download Exercise file here, beginning and finished tabs are provided.    

Step By Step

  1. Create columns titled MAPE Weights and MAD Weights
  2. Choose weighting factors (units, value, importance)
  3. Multiply weights by MAPE or MAD in the MAPE/MAD Weights column
  4. Create a pivot table from the data
  5. Create a calculated field in the pivot table (Analyze > Field, Items, & Sets > Calculated Field…)
  6. Name the calculated field “Weighted MAPE” with the formula =’Weighted MAPE’/ ‘Actual Sales’
    1. Do the same thing for the MAD =’Weighted MAD’/ ‘Actual Sales’
    2. Select “OK” or “Add” after each calculated field
  7. Manipulate the data in your pivot table as you see fit

Explanation With Images

Download Exercise file here, beginning and finished tabs are provided.  On the “Beginning Data” tab create columns titled “MAPE Weights” and “MAD Weights”, these will be used in the pivot table later to manipulate the data on the fly. 

The next step is to determine your weights.  In this example I will use “Actual Sales” as my weights, but you could use anything you want such as dollar value, or product importance (IE a 1-5 scale). 

Once you have a weighting factor determined, multiply your weight by the MAPE and MAD for their respective columns that you just created.

Next take all of your data and insert a pivot table (Insert > Pivot Table). 

Let’s take a look at what the average unweighted MAPE for each category by month is in the pivot table. We will use this as a reference to see the difference between the unweighted and weighted values.  Let’s focus on MAPE for now and we will do MAD after (it will follow the same process).

Next we will create a calculated field, this is where the weighting actually happens, (Analyze > Field, Items, & Sets > Calculated Field…). Name the calculated field “Weighted MAPE” with the formula  =’MAPE Weights’/ ‘Actual Sales’   

That’s it!  Now you can manipulate the data as you see fit.  Just follow the same process for weighted MAPE.  As you can see in the photos the MAPE and Weighted MAPE had a huge difference for the Accessories category in February, this was because of a forecast that was off by 207 units or 422%.   Note that you are not able to change the “Summarize Values By” for the calculated field.  So it will say “Sum of Weighted MAPE” but it is really the average of the weighted MAPE. 

As you can see this is a very useful skill to add to your Excel pivot table arsenal.  It can be used to weight any kind of data from simple averages to metrics on forecasting errors.