Work backwards from desired commission amount to find sales volume

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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
It is impossible to earn 6,500 on your commission structure.

They can sell $14999.99 and earn $6000.

They can sell $.01 more ($15000.00) and they earn $6750.

If you had a structure which did 30% on first 5k, 35% on next etc as you describe as not having above, then it would be possible to work back to sales value based on commission required.
 
Upvote 0
This is due to the commission structure that you have chosen and the fact that you are rounding up to the nearest dollar.

As you say $15000 at 45% would result in a commission of $6750
The problem is when you go one cent lower or even one dollar lower, $14999 at 40% results in a commission of $5999.6 which you are rounding up to $6000

If you return $6000 dollars back to its sales amount using the rate of 40% you will get $15000.

You should now notice a clash in your figures.

Due to the rounding of commission and your commission rates a Sales figure of $15000 can satisfy both the 45% rate and the 40% rate.


Excel 2010
ABC
2860000.415000
2967500.4515000
Sheet1
Cell Formulas
RangeFormula
C28=A28/B28
C29=A29/B29
 
Upvote 0
It is impossible to earn 6,500 on your commission structure.

You can however let them enter a minimum commission they want to earn and tell them how much sales would be required to match or beat that commission and then advise on that sales volume how much commission would be received.

Excel Workbook
ABCDEFGHI
1TierSalesMin Commission on TierDesired Commission MinimumRequired Sales VolumeActual Commission on Required Sales Volume
2Tier 11.000.3030%6,500.0016,250.007,312.50
3Tier 25,000.001,750.0035%
4Tier 310,000.004,000.0040%
5Tier 415,000.006,750.0045%
6Tier 520,000.0010,000.0050%
7Tier 625,000.0013,750.0055%
8Tier 730,000.0018,000.0060%
Sheet1
 
Upvote 0
Actually this doesn't quite work due to your structure. I'm working on a solution.
 
Upvote 0
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][td]
I​
[/td][td]
J​
[/td][/tr]
[tr][td]
1​
[/td][td]
Tier​
[/td][td]
Sales​
[/td][td]
Rate​
[/td][td][/td][td]
Sales​
[/td][td]
Commission​
[/td][td][/td][td]
Commission​
[/td][td]
Sales​
[/td][td]F2: =E2 * VLOOKUP(E2, $B$2:$C$8, 2)[/td][/tr]

[tr][td]
2​
[/td][td]
1​
[/td][td]
$1​
[/td][td]
30%​
[/td][td][/td][td]
$ 1.00​
[/td][td]
$ 0.30​
[/td][td][/td][td]
$ 1,000.00​
[/td][td]
$ 3,315.33​
[/td][td]I2: =PERCENTILE($E$2:$E$15, PERCENTRANK($F$2:$F$15, H2))[/td][/tr]

[tr][td]
3​
[/td][td]
2​
[/td][td]
$5,000​
[/td][td]
35%​
[/td][td][/td][td]
$ 4,999.99​
[/td][td]
$ 1,500.00​
[/td][td][/td][td]
$ 3,000.00​
[/td][td]
$ 8,519.99​
[/td][td][/td][/tr]

[tr][td]
4​
[/td][td]
3​
[/td][td]
$10,000​
[/td][td]
40%​
[/td][td][/td][td]
$ 5,000.00​
[/td][td]
$ 1,750.00​
[/td][td][/td][td]
$ 5,000.00​
[/td][td]
$ 12,490.00​
[/td][td][/td][/tr]

[tr][td]
5​
[/td][td]
4​
[/td][td]
$15,000​
[/td][td]
45%​
[/td][td][/td][td]
$ 9,999.99​
[/td][td]
$ 3,500.00​
[/td][td][/td][td]
$ 7,000.00​
[/td][td]
$ 15,550.00​
[/td][td][/td][/tr]

[tr][td]
6​
[/td][td]
5​
[/td][td]
$20,000​
[/td][td]
50%​
[/td][td][/td][td]
$ 10,000.00​
[/td][td]
$ 4,000.00​
[/td][td][/td][td]
$ 9,000.00​
[/td][td]
$ 19,969.99​
[/td][td][/td][/tr]

[tr][td]
7​
[/td][td]
6​
[/td][td]
$25,000​
[/td][td]
55%​
[/td][td][/td][td]
$ 14,999.99​
[/td][td]
$ 6,000.00​
[/td][td][/td][td]
$ 11,000.00​
[/td][td]
$ 21,990.00​
[/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
7​
[/td][td]
$30,000​
[/td][td]
60%​
[/td][td][/td][td]
$ 15,000.00​
[/td][td]
$ 6,750.00​
[/td][td][/td][td]
$ 13,000.00​
[/td][td]
$ 24,999.99​
[/td][td][/td][/tr]

[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td]
$ 19,999.99​
[/td][td]
$ 9,000.00​
[/td][td][/td][td]
$ 15,000.00​
[/td][td]
$ 27,255.02​
[/td][td][/td][/tr]

[tr][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td]
$ 20,000.00​
[/td][td]
$ 10,000.00​
[/td][td][/td][td]
$ 17,000.00​
[/td][td]
$ 29,992.69​
[/td][td][/td][/tr]

[tr][td]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td]
$ 24,999.99​
[/td][td]
$ 12,500.00​
[/td][td][/td][td]
$ 19,000.00​
[/td][td]
$ 30,840.00​
[/td][td][/td][/tr]

[tr][td]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td]
$ 25,000.00​
[/td][td]
$ 13,750.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td][/td][td][/td][td][/td][td][/td][td]
$ 29,988.99​
[/td][td]
$ 16,493.94​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td]
$ 30,000.00​
[/td][td]
$ 18,000.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td][/td][td][/td][td][/td][td][/td][td]
$ 100,000.00​
[/td][td]
$ 60,000.00​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
For better accuracy, use instead for I2,

=FLOOR(PERCENTILE($E$2:$E$15, PERCENTRANK($F$2:$F$15, H2, 10)), 0.01)
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top