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!
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,234
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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%?
 

excellinginseattle

New Member
Joined
Mar 23, 2016
Messages
14
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.
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
255
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:

excellinginseattle

New Member
Joined
Mar 23, 2016
Messages
14
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.
 

excellinginseattle

New Member
Joined
Mar 23, 2016
Messages
14
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:

excellinginseattle

New Member
Joined
Mar 23, 2016
Messages
14
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>
 

miless2111s

Active Member
Joined
Feb 10, 2016
Messages
255
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,591
Members
412,328
Latest member
Mrcaff88
Top