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!
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,962
Office Version
365, 2019, 2016
Platform
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.
 

Forum statistics

Threads
1,082,139
Messages
5,363,363
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top