Work backwards from desired commission amount to find sales volume

[CLgoneDuckin]

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

 

[CROY1985]

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]

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
	A	B	C
28	6000	0.4	15000
29	6750	0.45	15000
Sheet1

Cell Formulas
Range	Formula
C28	=A28/B28
C29	=A29/B29

 

[BrianMH]

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
	A	B	C	D	E	F	G	H	I
1	Tier	Sales	Min Commission on Tier				Desired Commission Minimum	Required Sales Volume	Actual Commission on Required Sales Volume
2	Tier 1	1.00	0.30	30%			6,500.00	16,250.00	7,312.50
3	Tier 2	5,000.00	1,750.00	35%
4	Tier 3	10,000.00	4,000.00	40%
5	Tier 4	15,000.00	6,750.00	45%
6	Tier 5	20,000.00	10,000.00	50%
7	Tier 6	25,000.00	13,750.00	55%
8	Tier 7	30,000.00	18,000.00	60%
Sheet1

 

[BrianMH]

Actually this doesn't quite work due to your structure. I'm working on a solution.

 

[shg]

[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]

 

[shg]

For better accuracy, use instead for I2,

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

 

[CLgoneDuckin]

Thanks for the help team