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

[excellinginseattle]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sept[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Person 1[/TD]
[TD]200,000[/TD]
[TD]200,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Person 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]300,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

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]

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]

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

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Month
[/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]March
[/TD]
[TD]April
[/TD]
[TD]May
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]P1
[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]200
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]P2
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]400
[/TD]
[TD="align: right"]700
[/TD]
[TD="align: right"]700
[/TD]
[TD="align: right"]1200
[/TD]
[TD][/TD]
[TD]SUM(F2:F3)+E4
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tier
[/TD]
[TD="align: right"]2%
[/TD]
[TD="align: right"]2%
[/TD]
[TD="align: right"]10%
[/TD]
[TD="align: right"]10%
[/TD]
[TD="align: right"]20%
[/TD]
[TD][/TD]
[TD]VLOOKUP(F4,rewards,2)
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Possible split
[/TD]
[TD="align: center"]FALSE
[/TD]
[TD="align: center"]FALSE
[/TD]
[TD="align: center"]TRUE
[/TD]
[TD="align: center"]FALSE
[/TD]
[TD="align: center"]TRUE
[/TD]
[TD][/TD]
[TD]F5<>E5
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Threshold
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]500
[/TD]
[TD="align: right"]1000
[/TD]
[TD][/TD]
[TD]VLOOKUP(F4,rewards,1,TRUE)
[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Amount in upper rate
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]200
[/TD]
[TD][/TD]
[TD="align: right"]200
[/TD]
[TD][/TD]
[TD]IF(F6,F4-F7,"")
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Amount in lower rate
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100
[/TD]
[TD][/TD]
[TD="align: right"]300
[/TD]
[TD][/TD]
[TD]IF(F6,F3-F8,"")
[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Upper rate
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10%
[/TD]
[TD][/TD]
[TD="align: right"]20%
[/TD]
[TD][/TD]
[TD]IF(F6,F5,"")
[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Lower Rate
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2%
[/TD]
[TD][/TD]
[TD="align: right"]10%
[/TD]
[TD][/TD]
[TD]IF(F6,E5,"")
[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Earnings
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]70
[/TD]
[TD][/TD]
[TD]IF(F6,(F11*F9)+(F10*F8),F5*SUM(F2:F3))
[/TD]
[/TR]
</tbody>[/TABLE]


The reward structure is:

[TABLE="class: grid, width: 157"]
<tbody>[TR]
[TD] -
[/TD]
[TD="align: right"]2%
[/TD]
[/TR]
[TR]
[TD] 500.00
[/TD]
[TD="align: right"]10%
[/TD]
[/TR]
[TR]
[TD] 1,000.00
[/TD]
[TD="align: right"]20%
[/TD]
[/TR]
[TR]
[TD] 1,500.00
[/TD]
[TD="align: right"]25%
[/TD]
[/TR]
[TR]
[TD] 2,000.00
[/TD]
[TD="align: right"]20%
[/TD]
[/TR]
</tbody>[/TABLE]


Hope that helps

 

[excellinginseattle]

Thank you!

 

[excellinginseattle]

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]

Shoot...I can't post attachments.

I hope this link works: https://drive.google.com/file/d/0B2TmcbMjx25hdEp0T3k3ZWQ1Y1U/view?usp=sharing

 

[excellinginseattle]

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!

 

[excellinginseattle]

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!

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Who Closed What[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Territory Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rates[/TD]
[TD]2%[/TD]
[TD]10%[/TD]
[TD]20%[/TD]
[TD]25%[/TD]
[TD]20%[/TD]
[TD][/TD]
[TD]Commission Calcs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD][/TD]
[TD][/TD]
[TD]Milestone1[/TD]
[TD]Milestone 2[/TD]
[TD]Quota[/TD]
[TD]Stretch[/TD]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Method 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Territory 1[/TD]
[TD]Person 1[/TD]
[TD]200,000[/TD]
[TD]200,000[/TD]
[TD]300,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200,000[/TD]
[TD]200,000[/TD]
[TD]600,000[/TD]
[TD]-5,000[/TD]
[TD]350,000[/TD]
[TD][/TD]
[TD][/TD]
[TD]500,000[/TD]
[TD]1,000,000[/TD]
[TD]1,500,000[/TD]
[TD]2,000,000[/TD]
[TD][/TD]
[TD]4,000[/TD]
[TD]4,000[/TD]
[TD]22,000
[/TD]
[TD]40,000
[/TD]
[TD]39,600
[/TD]
[TD]Z6 and AA6 should not be populated[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Territory 1[/TD]
[TD]Person 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]300,000[/TD]
[TD]-5,000[/TD]
[TD]350,000[/TD]
[TD][/TD]
[TD]200,000[/TD]
[TD]200,000[/TD]
[TD]600,000[/TD]
[TD]-5,000[/TD]
[TD]350,000[/TD]
[TD][/TD]
[TD][/TD]
[TD]500,000[/TD]
[TD]1,000,000[/TD]
[TD]1,500,000[/TD]
[TD]2,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30,000
[/TD]
[TD]-500[/TD]
[TD]35,000[/TD]
[TD]Should be $69,000 ($345k@20% and $5k@10%)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Method 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Territory 1[/TD]
[TD]Person 1[/TD]
[TD]200,000[/TD]
[TD]200,000[/TD]
[TD]300,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200,000[/TD]
[TD]200,000[/TD]
[TD]600,000[/TD]
[TD]-5,000[/TD]
[TD]350,000[/TD]
[TD][/TD]
[TD][/TD]
[TD]500,000[/TD]
[TD]1,000,000[/TD]
[TD]1,500,000[/TD]
[TD]2,000,000[/TD]
[TD][/TD]
[TD]4,000[/TD]
[TD]4,000[/TD]
[TD]22,000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]Territory 1[/TD]
[TD]Person 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]300,000[/TD]
[TD]-5,000
[/TD]
[TD]350,000[/TD]
[TD][/TD]
[TD]200,000[/TD]
[TD]200,000[/TD]
[TD]600,000[/TD]
[TD]-5,000[/TD]
[TD]350,000[/TD]
[TD][/TD]
[TD][/TD]
[TD]500,000[/TD]
[TD]1,000,000[/TD]
[TD]1,500,000[/TD]
[TD]2,000,000[/TD]
[TD][/TD]
[TD]4,000[/TD]
[TD]4,000[/TD]
[TD]22,000
[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[miless2111s]

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.