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

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sept

Oct

Nov

Dec

Person 1

200,000

200,000

Person 2

300,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!

 

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

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

Sales​

Rate​

Delta​

2​

$ -​

2%​

2%​

D2: =C2-N(C1)

3​

$ 500,000​

10%​

8%​

4​

$ 1,000,000​

20%​

10%​

5​

$ 1,500,000​

25%​

5%​

6​

$ 2,000,000​

20%​

-5%​

7​

8​

Sales​

Jan​

Feb​

Mar​

Apr​

May​

Jun​

Jul​

Aug​

Sept​

Oct​

Nov​

Dec​

9​

Alan

200,000​

200,000​

10​

Barb

300,000​

11​

Cain

50,000​

175,000​

12​

Dana

75,000​

250,000​

150,000​

13​

Eric

75,000​

150,000​

150,000​

150,000​

14​

15​

Commissions​

Jan​

Feb​

Mar​

Apr​

May​

Jun​

Jul​

Aug​

Sept​

Oct​

Nov​

Dec​

16​

Alan

DoNotUse

4,000​

4,000​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

17​

Barb

DoNotUse

0​

0​

22,000​

0​

0​

0​

0​

0​

0​

0​

0​

0​

18​

Cain

DoNotUse

0​

0​

5,000​

17,500​

0​

0​

0​

0​

0​

0​

0​

0​

19​

Dana

DoNotUse

0​

0​

0​

7,500​

50,000​

30,000​

0​

0​

0​

0​

0​

0​

20​

Eric

DoNotUse

0​

0​

0​

0​

0​

15,000​

36,250​

37,500​

37,500​

0​

0​

0​

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?