Commission Formula
Evening,
Hope you're all well.
I’m hitting a brick wall inserting a formula into a tracker for my sales teams commission structure which is being redone.
It’s a tiered system whereby the salesperson will get commission in brackets until they reach target, and then a higher percentage on anything over target; (0.1% for the first 20%) of their target, (0.2% on the next 20%), (0.3% on the next 20%) so on.
Until they reach target, at which point everything over target they are rewarded at 2%.
If they are under target, they will still receive commission, but only within the brackets.
If anyone who’s done a similar structure that would be able to take a look and let me know their thoughts, it would be really appreciated, as I seem to be going around in circles.
I've extracted a section and published it below.
Thanks in advanced, any suggestions welcome :D
Cheers,
Glen.
Best Answer
-
Hi @Paul Newcome @Glen Urquhart
Hope you are fine, i do it using the following formula, please confirm if this ok for you or we must change the factors of calculation.
=IF([Month End Sales Actual]@row > [2021 Target]@row, (0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + 0.003 * [Third 20%]@row + 0.004 * [Last 20%]@row + ([Over Target]@row * 0.02)), (IF(AND([Month End Sales Actual]@row <= [Last 20%]@row, [Month End Sales Actual]@row > [Third 20%]@row), ((0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + 0.003 * [Third 20%]@row + (([Month End Sales Actual]@row - [Third 20%]@row) * 0.004))), (IF(AND([Month End Sales Actual]@row > [Second 20%]@row, [Month End Sales Actual]@row <= [Third 20%]@row), ((0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + ([Month End Sales Actual]@row - [Second 20%]@row) * 0.003)), (IF(AND([Month End Sales Actual]@row > [First 20%]@row, [Month End Sales Actual]@row <= [Second 20%]@row), (0.001 * [First 20%]@row + ([Month End Sales Actual]@row - [First 20%]@row) * 0.002), (IF(AND([Month End Sales Actual]@row > Divider@row, [Month End Sales Actual]@row <= [First 20%]@row), 0.001 * ([Month End Sales Actual]@row - Divider@row))))))))))
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
Hi @Glen Urquhart
Hope you are fine, i designed the following sheet using the information you mentioned in your question to calculate the Actual Commission please check.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I want to make sure I understand this correctly...
Lets assume a target of $10,000.00 for easier math.
If they reach their target, do they get 2% of 10,000, or is it
.1% of 2,000
.2% of 2,000
.3% of 2,000
.4% of 2,000
.5% of 2,000
all added together?
Then if they were to go to maybe $15,000 they would get 2$ of 5,000?
-
Hi @Paul Newcome @Glen Urquhart
Hope you are fine, i do it using the following formula, please confirm if this ok for you or we must change the factors of calculation.
=IF([Month End Sales Actual]@row > [2021 Target]@row, (0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + 0.003 * [Third 20%]@row + 0.004 * [Last 20%]@row + ([Over Target]@row * 0.02)), (IF(AND([Month End Sales Actual]@row <= [Last 20%]@row, [Month End Sales Actual]@row > [Third 20%]@row), ((0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + 0.003 * [Third 20%]@row + (([Month End Sales Actual]@row - [Third 20%]@row) * 0.004))), (IF(AND([Month End Sales Actual]@row > [Second 20%]@row, [Month End Sales Actual]@row <= [Third 20%]@row), ((0.001 * [First 20%]@row + 0.002 * [Second 20%]@row + ([Month End Sales Actual]@row - [Second 20%]@row) * 0.003)), (IF(AND([Month End Sales Actual]@row > [First 20%]@row, [Month End Sales Actual]@row <= [Second 20%]@row), (0.001 * [First 20%]@row + ([Month End Sales Actual]@row - [First 20%]@row) * 0.002), (IF(AND([Month End Sales Actual]@row > Divider@row, [Month End Sales Actual]@row <= [First 20%]@row), 0.001 * ([Month End Sales Actual]@row - Divider@row))))))))))
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
After taking another look at the sheet you provided... Where exactly are you wanting to populate the formula(s)?
-
Hi both, thank you for your input.
Correct- as they increase through the tiers, the commision increases as you say.
0.2% of the first 2k
0.3% of the next 2k, so on.
And then any sales that surpass the target 2% on the 'over'
i.e target of 10k, and sales of 15k, would result in 2% of the 5k over the target.
If they achieve for example 5k sales however, they should recieve:
0.1% of the first 2k
0.2% of the second 2k
0.3% of the remaining 1k.
= 2 + 4 + 3...
The numbers are actually different per salesperson / area / agreed percentage etc, but i can't figue the basis for it.
I'm looking for a formula to populate the (now) red filled cells.
-
The template was inspired by the furthest to the right "Tiered Commision" scheme on the attached, however there are a couple of errors in the original;
-
It may be that the first approch was over complicated for what is actually needed, and infact the below would suffice, however im still having trouble with these ifs rules, is the below formula description logical / possible?
-
Hi Both,
Thank you for your help and input.
I have achieved what I was looking to have as an output now.
@Paul Newcome , it was actually a thread that you answered in 2018 that wa the final piece to my puzzle.
The below is an example of what I was going for:
The "Actual sales" field is fed live from another sheet, which is linked to our CRM via Automate.io, collecting sales reps monthly sales figures.
Thanks again for your help and comments.
-
Help Article Resources
Categories
Check out the Formula Handbook template!
