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:

Tier 1$130%
Tier 2$5,00035%
Tier 3$10,00040%
Tier 4$15,00045%
Tier 5$20,00050%
Tier 6$25,00055%
Tier 7$30,00060%

<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
 

CROY1985

Active Member
Joined
Sep 21, 2009
Messages
498
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.
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,376
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">6000</td><td style="text-align: right;;">0.4</td><td style="text-align: right;;">15000</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">6750</td><td style="text-align: right;;">0.45</td><td style="text-align: right;;">15000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C28</th><td style="text-align:left">=A28/B28</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C29</th><td style="text-align:left">=A29/B29</td></tr></tbody></table></td></tr></table><br />
 

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
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.00 30%6,500.00
3Tier 25,000.00 35%
4Tier 310,000.00 40%
5Tier 415,000.00 45%
6Tier 520,000.00 50%
7Tier 625,000.00 55%
8Tier 730,000.00 60%
Sheet1
 

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
Actually this doesn't quite work due to your structure. I'm working on a solution.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
A​
B​
C​
D​
E​
F​
G​
H​
I​
J​
1​
Tier​
Sales​
Rate​
Sales​
Commission​
Commission​
Sales​
F2: =E2 * VLOOKUP(E2, $B$2:$C$8, 2)
2​
1​
$1​
30%​
$ 1.00​
$ 0.30​
$ 1,000.00​
$ 3,315.33​
I2: =PERCENTILE($E$2:$E$15, PERCENTRANK($F$2:$F$15, H2))
3​
2​
$5,000​
35%​
$ 4,999.99​
$ 1,500.00​
$ 3,000.00​
$ 8,519.99​
4​
3​
$10,000​
40%​
$ 5,000.00​
$ 1,750.00​
$ 5,000.00​
$ 12,490.00​
5​
4​
$15,000​
45%​
$ 9,999.99​
$ 3,500.00​
$ 7,000.00​
$ 15,550.00​
6​
5​
$20,000​
50%​
$ 10,000.00​
$ 4,000.00​
$ 9,000.00​
$ 19,969.99​
7​
6​
$25,000​
55%​
$ 14,999.99​
$ 6,000.00​
$ 11,000.00​
$ 21,990.00​
8​
7​
$30,000​
60%​
$ 15,000.00​
$ 6,750.00​
$ 13,000.00​
$ 24,999.99​
9​
$ 19,999.99​
$ 9,000.00​
$ 15,000.00​
$ 27,255.02​
10​
$ 20,000.00​
$ 10,000.00​
$ 17,000.00​
$ 29,992.69​
11​
$ 24,999.99​
$ 12,500.00​
$ 19,000.00​
$ 30,840.00​
12​
$ 25,000.00​
$ 13,750.00​
13​
$ 29,988.99​
$ 16,493.94​
14​
$ 30,000.00​
$ 18,000.00​
15​
$ 100,000.00​
$ 60,000.00​
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,713
Office Version
2010
Platform
Windows
For better accuracy, use instead for I2,

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

Forum statistics

Threads
1,081,556
Messages
5,359,552
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top