Calculating commissions with tiers, by month, AND across multiple people.

excellinginseattle

New Member
Joined
Mar 23, 2016
Messages
14
My question is related to calculating sales commissions.

Here's my problem: determining a commission rate is based on what has been closed in a territory, but calculating the actual commission earned is based off what a person himself closed. I know how to do this with a ton of nested if statements, but there must be a better way?

For example, we have a territory called West 1

West 1 has the following milestones:
Milestone 1: $500,000
Milestone 2: $1,000,000
Quota: $1,500,000
Stretch Quota: $2,000,000

Commission rates are:
2% up to Milestone 1
10% up to Milestone 2
20% up to Quota
25% up to Stretch
20% above Stretch

Let's say there were 2 people that closed business in the territory

JanFebMarAprMayJunJulAugSeptOctNovDec
Person 1200,000200,000
Person 2300,000

<tbody>
</tbody>

I need to be able to calculate that Person 2 earns 2% on $100,00 and 10% on $200,000.

Calculations need to be cumulative each month.

Can someone help?

Thanks!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
based on your example let say Person 1 also closed 300,000 in March, what is the commission for both of them?
i.e. who is the lucky guy to get the 2%?
 
Upvote 0
Good question. Person 1 would get the 2% because person 1 was the first territory owner. He left, and person 2 arrived. There is never overlap - it's time based.
 
Upvote 0
This is a rather long winded "show your workings" sort of way but I'm sure it can be condensed if it's doing the right sort of things

ABCDEFGH
1Month
Jan
Feb
March
April
May
2P1
200
200
3P2
300
500
4200
400
700
700
1200
SUM(F2:F3)+E4
5Tier
2%
2%
10%
10%
20%
VLOOKUP(F4,rewards,2)
6Possible split
FALSE
FALSE
TRUE
FALSE
TRUE
F5<>E5
7Threshold
500
500
1000
VLOOKUP(F4,rewards,1,TRUE)
8Amount in upper rate
200
200
IF(F6,F4-F7,"")
9Amount in lower rate
100
300
IF(F6,F3-F8,"")
10Upper rate
10%
20%
IF(F6,F5,"")
11Lower Rate
2%
10%
IF(F6,E5,"")
12
13Earnings
4
4
22
70
IF(F6,(F11*F9)+(F10*F8),F5*SUM(F2:F3))

<tbody>
</tbody>


The reward structure is:

-
2%
500.00
10%
1,000.00
20%
1,500.00
25%
2,000.00
20%

<tbody>
</tbody>


Hope that helps
 
Last edited:
Upvote 0
I'm still having issues. :(

I think I've come up with two methods that are very close to working, but I need help.

Any experts out there who can help me with either a min(max()) or sumproduct solution? I think both could be very simple and efficient.

Please see the attached.
 
Upvote 0
I still haven't found a solution that works and am really struggling. Here is a screen shot of what I referenced in the link above. If anyone could help, I'd really appreciate it.


-- removed inline image ---


Formula in X12: =F12*$P$3-($P$3-$Q$3)*MIN(MAX(0,SUM($J12:L12)-(L12-F12)-$Q12),F12)
Formula in X13: =F13*$P$3-($P$3-$Q$3)*MIN(MAX(0,SUM($J13:L13)-$Q13),F13)
Formula in X19: =SUMPRODUCT((L19 >= $P19:$T19)*(L19 - $P19:$T19)*($P$3:$T$3))

Ideas? Is what I'm trying to do even possible without a load of if statements?

Thanks!
 
Last edited:
Upvote 0
Complex commission calculations - multiple people and tiers.

I'm sorry for the mess above...here is a cleaned up post:

I still haven't found a solution that works and am really struggling. Here is a mock up of what I referenced in the link above. If anyone could help, I'd really appreciate it.

COMMISSION RATES ARE BASED ON WHAT HAS BEEN CLOSED IN THE TERRITORY AT THE TIME A PERSON'S COMMISSIONS ARE CALCULATED
THE RATE IS THEN APPLIED TO WHO CLOSED WHAT

Method 1: Min(Max())
Issue 1: how do I modify the commission calculations to include all the milestones?
Issue 2: how do I modify the commission calculations so that only person 1's calculations don't include what person 2 closed but person 2's calculations do. See difference in cell Y6 vs. Y7.
Issue 3: how do I modify person 1's commission calculations so they don't show up after he is no longer closing business?

Method 2: Sumproduct - I have this working for 1 person…just not 2 :(
Issue 1: how do I make sumproduct take into account what's been closed in the territory (which is important when a milestone/rate changes) but calculate on what a person has closed?
Issue 2: how do I handle negative values in a month?

Formula in Y6: =G6*$Q$3-($Q$3-$R$3)*MIN(MAX(0,SUM($K6:M6)-(M6-G6)-$R6),G6)
Formula in Y7:=G7*$Q$3-($Q$3-$R$3)*MIN(MAX(0,SUM($K7:M7)-$R7),G7)
Formula in Y10:=SUMPRODUCT((M10 >= $Q10:$U10)*(M10 - $Q10:$U10)*($Q$3:$U$3))

Ideas? Is what I'm trying to do even possible without a load of if statements?

Thanks!

ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
2
3Who Closed WhatTerritory TotalsRates2%10%20%25%20%Commission Calcs
4JanFebMarAprMayJanFebMarAprMayMilestone1Milestone 2QuotaStretchJanFebMarAprMay
5Method 1
6Territory 1Person 1200,000200,000300,000200,000200,000600,000-5,000350,000500,0001,000,0001,500,0002,000,0004,0004,00022,000
40,000
39,600
Z6 and AA6 should not be populated
7Territory 1Person 2300,000-5,000350,000200,000200,000600,000-5,000350,000500,0001,000,0001,500,0002,000,00030,000
-50035,000Should be $69,000 ($345k@20% and $5k@10%)
8
9Method 2
10Territory 1Person 1200,000200,000300,000200,000200,000600,000-5,000350,000500,0001,000,0001,500,0002,000,0004,0004,00022,000
11Territory 1Person 2300,000-5,000
350,000200,000200,000600,000-5,000350,000500,0001,000,0001,500,0002,000,0004,0004,00022,000
0

<tbody>
</tbody>
 
Upvote 0
Re: Complex commission calculations - multiple people and tiers.

Do you have a situation where P1 and P2 are active in the same month, i.e. you need to split the month by some fraction between them? This is suggested by the 300K in March which was only P2 in the original example.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top