Commissions With Tiers Formula

[shmeetbag]

Hello,

I need a formula that will calculate a commission payout based on a commission table. As an example, if a rep is in a specific quota category (15,000), then I need a formula that will return a tiered payout based on the quota category and how much over their quota they reached. I can get this information with the formula below, but the challenge is the higher % rate needs to be paid only on the overage amount, not the entire sales amount. Hopefully the example below will better show what I am looking for.

Commission Table:

Quota <=100% 110% 125% 150% 200% 200% +
<=10,000 5.0% 7.0% 8.0% 9.0% 10.0%11.0%
$11,250 5.0% 7.0% 8.0% 9.0% 10.0%11.0%
$15,000 5.3% 7.3% 8.3% 9.3% 10.3%11.3%
$20,000 5.8% 7.8% 8.8% 9.8% 10.8%11.8%
$25,000 7.5% 9.0% 10.0%11.0%12.0%13.0%

Quota 15,000
Sales 17,500
Attainment 117%
Payout 973 795 (15,000*5.3%) + 183 (2,500*7.3%)


What I used to get an overall number, but this won't separate the rate and amounts as needed in the example.
INDEX($A$1:$H$7,MATCH(A10,$A$1:$A$7,1),MATCH(A11,$A$1:$H$1,1))

Thanks!

[mikeTRON]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[JohnTopley]

With table in A1:G6

=INDEX($B$2:$G$6,MATCH($A10,$A$2:$A$6,1),1)*$A9+IF(A10>A9,INDEX($B$2:$G$6,MATCH($A10,$A$2:$A$6,1),MATCH($A10/$A9,$B$1:$G$1,1))*($A10-$A9),0)


See attached.

[shmeetbag]

This looks great, Thanks!

[shmeetbag]

Actually, when I try to change the quota say from 15,000 to 25,000 and leave the total at 17,500, it doesn't calculate correctly. In fact, it seems it isn't working correctly for any number that falls below the quota. Is there a way around this with the formula?

[JohnTopley]

I'll look at later or tomorrow.

If you can post a file showing problem it will be appreciated.

[shmeetbag]

I used the same file you attached. I just changed the 15,000 to 25,000 and it shows 1325.00 when it should be 1312.5. Also, if you replace the 15,000 in A9 with 10,000, and put a lower number in A10, then it shows an error. File is attached.

[JohnTopley]

It will show error as table does not allow for quota less than 10000: it needs another entry with 0 (to 9999 effectively).

I think I need clarification of the calculation: if a swap A9/A10 in the first part of the formula with a quota of 25000 and actual of 17500 I get 1312.50. However if I have a quota of 15000 and achieved of 17500 I get an result of 111 rather than the 977.50 result previously!

To leave this for today as it is late here in UK

[JohnTopley]

If Quota is 25000 but Sales Achieved is less then Quota i.e. 17500, then commission is based on Quota % * Sales Achieved. i.e 17500 * 6.3%

If Quota is 15000 but Sales Achieved is greater (17500) then commission is based Quota % * Quota + % over achievement * (Sales Achieved - Quota).

Is this correct?


=INDEX($B$2:$G$6,MATCH($A9,$A$2:$A$6,1),1)*MIN($A$9,$a$10)+IF(A10>A9,INDEX($B$2:$G$6,MATCH($A10,$A$2:$A$6,1),MATCH($A10/$A9,$B$1:$G$1,1))*($A10-$A9),0)

Added new line to table for Quota less than 10000: you need to verify this.

See attached.

[shmeetbag]

Sorry for the delay, I have been out and unable to access email. Yes, what you outlined looks correct, and when I enter an amount less than quota is is correct.

I am still not getting the exact total when testing an amount with higher attainment, and not quite sure why. I have updated the excel file with a "New Example" tab that shows the breakdown for a new total I have entered. It seems the formula is about 8% off of what it should be. In my example the calculated total should be 2381.36, but it is showing 2577.13. Do you think it is because it is not calculating the amount over 150% correctly?

Thanks again for your help! Your formula is quite good.

[JohnTopley]

I missed the "tiered" part of your request so my fault.

Try the following (See attached).

I created a "dynamic" commission table (I2:J8) so I can use the SUMPRODUCT calculation in B12

I2 is always 0

I3 onwards : =IFERROR($A$9*INDEX($B$1:$G$1,ROWS($1:1)),"") Commission tiers (Quota * Tier %)

J2: =INDEX($B$2:$G$7,MATCH($A$9,$A$2:$A$7,1),ROWS($1:1)) 100% commission %

J3 onwards: =IFERROR(INDEX($B$2:$G$7,MATCH($A$9,$A$2:$A$7,1),ROWS($1:2))-SUM($J$2:J2),"") Difference in commission tire rates

e.g 7.8% - 5.8% = 2%


in B12

Commission calculation ....

=SUMPRODUCT(($A$10>=$I$2:$I$7)*($A$10-$I$2:$I$7),$J$2:$J$7)

[shmeetbag]

That works, thanks for all your help!

Out of curiosity, would it be possible to create the commission table for each quota in a formula? If I have a list of multiple individuals with different quotas, I would need to create a separate commission table for each quota in order to reference. I know this wouldn't work with the current formulas, but I was thinking something like this:

=VLOOKUP(A9,{0,INDEX($B$2:$G$7,MATCH($A$9,$A$2:$A$7,1),ROWS($1:1));IFERROR($A$9*INDEX($B$1:$G$1,ROWS($1:1)),""),IFERROR(INDEX($B$2:$G$7,MATCH($A$9,$A$2:$A$7,1),ROWS($1:2))-SUM($J$2:J2),"")},2,FALSE)

[JohnTopley]

I have a VBA solution (see attached).

It simply loops through the data in columns M to P and puts the required data into B9 (named range "Quota") and B10 (named range "Sales") and returns the commission from B12 (named range "Commission").

Please Login or Register  to view this content.