Commission Calculations in PowerPivot

May 9, 2012

Guest post by David Churchward [Twitter]

Commissions Report

Firstly, I have to be clear that I’m not presenting a “one-size-fits-all” approach to sales commission calculations here.  That wouldn’t be possible because commission schemes vary extensively and, in my experience, some schemes aren’t even based on a logic that can derive a mathematical answer!  However, I’m presenting an approach here that will hopefully provide a template approach that can be modified to a number of different scenarios.

The Target Outcome

The scheme that I’m using in this example operates as follows:

  1. Commission is paid monthly based on the achievement in that month
  2. As a salesperson sells more, then accelerators trigger.  That is to say, for example, a salesperson may receive 1% of sales up to £10,000 and 2% of sales between £10,000 and £20,000.  The 2% is payable on the whole value.  Therefore, if a salesperson sells £11,000 then they would receive 2% of the full £11,000
  3. Percentages and bands can change monthly at management discretion
  4. This is a monthly threshold so each salesperson is reset to zero at the start of each month.
  5. Each product group carries a separate set of bands and rates
  6. Managers receive commission at a different rate to the sales team based on the total sales for their team.

Read the rest of this entry »