# Work backwards from desired commission amount to find sales volume

#### CLgoneDuckin

##### New Member
I have a tiered commission structure that is laid out as follows:

 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

#### CROY1985

##### Active Member
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
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.

#### BrianMH

##### Well-known Member
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
Actually this doesn't quite work due to your structure. I'm working on a solution.

#### shg

##### MrExcel MVP
 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
For better accuracy, use instead for I2,

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

#### CLgoneDuckin

##### New Member
Thanks for the help team

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

### 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...