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