CLgoneDuckin
New Member
- Joined
- Feb 21, 2014
- Messages
- 2
I have a tiered commission structure that is laid out as follows:
<tbody>
</tbody>
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
Tier 1 | $1 | 30% |
Tier 2 | $5,000 | 35% |
Tier 3 | $10,000 | 40% |
Tier 4 | $15,000 | 45% |
Tier 5 | $20,000 | 50% |
Tier 6 | $25,000 | 55% |
Tier 7 | $30,000 | 60% |
<tbody>
</tbody>
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