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!

 

[shg]

1. I don't conceptually understand the second to the last part of the formula (SUM($B$16:B16)). What is it doing there?

The formula calculates cumulative commissions, so to get monthly commissions, you need to subtract commissions already paid.

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?

I dunno. What happens when you plug in a negative number? What should happen?

 

[excellinginseattle]

1. Ah. Got it. Thanks for the explanation.

2. I must not have been looking at the right cell when I was testing the negative calculation. It works!

Thank you SO SO SO much, shg! You are a star! I appreciate your help tremendously.

 

[shg]

You're welcome.