CLgoneDuckin
New Member
- Joined
- Feb 21, 2014
- Messages
- 2
I have a tiered commission structure that is laid out as follows:
[TABLE="width: 300"]
<tbody>[TR]
[TD]Tier 1[/TD]
[TD]$1[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD]$5,000[/TD]
[TD]35%[/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD]$10,000[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]Tier 4[/TD]
[TD]$15,000[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD]Tier 5[/TD]
[TD]$20,000[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Tier 6[/TD]
[TD]$25,000[/TD]
[TD]55%[/TD]
[/TR]
[TR]
[TD]Tier 7[/TD]
[TD]$30,000[/TD]
[TD]60%[/TD]
[/TR]
</tbody>[/TABLE]
The commission percent is retroactive to $1.
For example: If you sell $16,000 in product, you earn a full 45% on all sales. $7,200 in commission. NOT 30% on the first $5k, 35% on the next $5k, 40% on the next 5k and so on.
Here is my goal: Create a workbook where a salesperson can enter their desired commission amount to find the volume of sales needed to reach their goal.
Where I am running into problems is when the sales volume needed to reach a particular commission amount, falls really close to the tier break points.
For example: $14,999 in sales will earn $6,000 in commission. $15,000 in sales will earn $6,750 in commission. So, if someone on my sales team wants to find the sales volume needed to earn $6,500 in commission, I'm unable to provide the correct answer.
Is this a problem that occurs simply because of the commission structure I've created or is there a way to find the values? I cannot seem to wrap my head around this.
Thanks
[TABLE="width: 300"]
<tbody>[TR]
[TD]Tier 1[/TD]
[TD]$1[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]Tier 2[/TD]
[TD]$5,000[/TD]
[TD]35%[/TD]
[/TR]
[TR]
[TD]Tier 3[/TD]
[TD]$10,000[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]Tier 4[/TD]
[TD]$15,000[/TD]
[TD]45%[/TD]
[/TR]
[TR]
[TD]Tier 5[/TD]
[TD]$20,000[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Tier 6[/TD]
[TD]$25,000[/TD]
[TD]55%[/TD]
[/TR]
[TR]
[TD]Tier 7[/TD]
[TD]$30,000[/TD]
[TD]60%[/TD]
[/TR]
</tbody>[/TABLE]
The commission percent is retroactive to $1.
For example: If you sell $16,000 in product, you earn a full 45% on all sales. $7,200 in commission. NOT 30% on the first $5k, 35% on the next $5k, 40% on the next 5k and so on.
Here is my goal: Create a workbook where a salesperson can enter their desired commission amount to find the volume of sales needed to reach their goal.
Where I am running into problems is when the sales volume needed to reach a particular commission amount, falls really close to the tier break points.
For example: $14,999 in sales will earn $6,000 in commission. $15,000 in sales will earn $6,750 in commission. So, if someone on my sales team wants to find the sales volume needed to earn $6,500 in commission, I'm unable to provide the correct answer.
Is this a problem that occurs simply because of the commission structure I've created or is there a way to find the values? I cannot seem to wrap my head around this.
Thanks