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!

 

[excellinginseattle]

Re: Complex commission calculations - multiple people and tiers.

Yes. That does occur. The "Who Closed What" calculations take into account the portion closed that goes to each person.

 

[miless2111s]

Re: Complex commission calculations - multiple people and tiers.

Ok, so my method as published wouldn't take that into account however I guess you could work at a lower level of granularity than "month" and go to days or weeks and that would still work? You'd need to add a if statement "if person 1 has a number then value goes to them" sort of statement.
Or am I missing something
Regards
Miles

 

[excellinginseattle]

Re: Complex commission calculations - multiple people and tiers.

Hum...I'm not sure how I would do that

Thanks for your help thinking about this puzzle, Miles!

 

[excellinginseattle]

Re: Complex commission calculations - multiple people and tiers.

Do you think that the methods I proposed are viable solutions?

 

[shg]

[Table="width:, class:grid"][tr][td]Row\Col[/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][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][/tr][tr][td]

1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td][/td][td]

$ -​

[/td][td]

2%​

[/td][td="bgcolor:#E5E5E5"]

2%​

[/td][td="bgcolor:#E5E5E5"]D2: =C2-N(C1)[/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]

$ 500,000​

[/td][td]

10%​

[/td][td="bgcolor:#E5E5E5"]

8%​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td][/td][td]

$ 1,000,000​

[/td][td]

20%​

[/td][td="bgcolor:#E5E5E5"]

10%​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td]

$ 1,500,000​

[/td][td]

25%​

[/td][td="bgcolor:#E5E5E5"]

5%​

[/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]

$ 2,000,000​

[/td][td]

20%​

[/td][td="bgcolor:#E5E5E5"]

-5%​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/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]

8​

[/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Jan​

[/td][td="bgcolor:#F3F3F3"]

Feb​

[/td][td="bgcolor:#F3F3F3"]

Mar​

[/td][td="bgcolor:#F3F3F3"]

Apr​

[/td][td="bgcolor:#F3F3F3"]

May​

[/td][td="bgcolor:#F3F3F3"]

Jun​

[/td][td="bgcolor:#F3F3F3"]

Jul​

[/td][td="bgcolor:#F3F3F3"]

Aug​

[/td][td="bgcolor:#F3F3F3"]

Sept​

[/td][td="bgcolor:#F3F3F3"]

Oct​

[/td][td="bgcolor:#F3F3F3"]

Nov​

[/td][td="bgcolor:#F3F3F3"]

Dec​

[/td][/tr]
[tr][td]

9​

[/td][td]Alan[/td][td][/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]

10​

[/td][td]Barb[/td][td][/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]
[tr][td]

11​

[/td][td]Cain[/td][td][/td][td][/td][td][/td][td]

50,000​

[/td][td]

175,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]Dana[/td][td][/td][td][/td][td][/td][td][/td][td]

75,000​

[/td][td]

250,000​

[/td][td]

150,000​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]Eric[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

75,000​

[/td][td]

150,000​

[/td][td]

150,000​

[/td][td]

150,000​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/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]

15​

[/td][td="bgcolor:#F3F3F3"]

Commissions​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Jan​

[/td][td="bgcolor:#F3F3F3"]

Feb​

[/td][td="bgcolor:#F3F3F3"]

Mar​

[/td][td="bgcolor:#F3F3F3"]

Apr​

[/td][td="bgcolor:#F3F3F3"]

May​

[/td][td="bgcolor:#F3F3F3"]

Jun​

[/td][td="bgcolor:#F3F3F3"]

Jul​

[/td][td="bgcolor:#F3F3F3"]

Aug​

[/td][td="bgcolor:#F3F3F3"]

Sept​

[/td][td="bgcolor:#F3F3F3"]

Oct​

[/td][td="bgcolor:#F3F3F3"]

Nov​

[/td][td="bgcolor:#F3F3F3"]

Dec​

[/td][/tr]
[tr][td]

16​

[/td][td]Alan[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

4,000​

[/td][td="bgcolor:#E5E5E5"]

4,000​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td]

17​

[/td][td]Barb[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

22,000​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td]

18​

[/td][td]Cain[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

5,000​

[/td][td="bgcolor:#E5E5E5"]

17,500​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td]

19​

[/td][td]Dana[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

7,500​

[/td][td="bgcolor:#E5E5E5"]

50,000​

[/td][td="bgcolor:#E5E5E5"]

30,000​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[tr][td]

20​

[/td][td]Eric[/td][td]DoNotUse[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

15,000​

[/td][td="bgcolor:#E5E5E5"]

36,250​

[/td][td="bgcolor:#E5E5E5"]

37,500​

[/td][td="bgcolor:#E5E5E5"]

37,500​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][/tr]
[/table]

The formula in C16 and copied right and down is

=SUMPRODUCT((SUM($C$9:C9) > $B$2:$B$6) * (SUM($C$9:C9) - $B$2:$B$6) * $D$2:$D$6) - SUM($B$16:B16) - SUM(C$15:C15)

 

[excellinginseattle]

I'm getting a #Value! error with the formula.... Is it related to the last component?

 

[Dave Patton]

edit cell B2 to be 0 and try the formula.

The formula appears to be OK.

 

[excellinginseattle]

Oh, of course. That got the formula to work.

I'm now translating this to my live environment and will report back shortly if it worked. Thank you SHG and Dave!

 

[shg]

You're welcome.

 

[excellinginseattle]

SHG,

This is perfect! You really are an MVP! I've suffered over this problem for so long!

Two final questions
1. I don't conceptually understand the second to the last part of the formula (SUM($B$16:B16)). What is it doing there?
2. The only thing that I think this doesn't handle is if there is a negative total in the month (which unfortunately happens in my organization). Any way to deal with this?