Commission Formula

Glen Urquhart

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.

https://app.smartsheet.com/b/publish?EQBCT=ac1088651162463ab17ec002a18af8eb

Thanks in advanced, any suggestions welcome :D

Cheers,

Glen.

Bassam Khalil

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))))))))))

https://app.smartsheet.com/b/publish?EQBCT=d2feeca2af894bbc851ca2879f196b75

Bassam Khalil

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.

https://app.smartsheet.com/b/publish?EQBCT=d2feeca2af894bbc851ca2879f196b75

Paul Newcome

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?

Bassam Khalil

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))))))))))

https://app.smartsheet.com/b/publish?EQBCT=d2feeca2af894bbc851ca2879f196b75

Paul Newcome

After taking another look at the sheet you provided... Where exactly are you wanting to populate the formula(s)?

Glen Urquhart

https://community.smartsheet.com/discussion/comment/276642#Comment_276642

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.

Glen Urquhart

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;

https://docs.google.com/spreadsheets/d/1ypdvQTobmlXsDQbEgOGXz6ZBjbZGB67Y-JLcv3jOHtc/edit#gid=32203406

Glen Urquhart

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?

Glen Urquhart

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:

https://app.smartsheet.com/b/publish?EQBCT=24825d0e376d4e3b921523c49d3b787d

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.

Paul Newcome

Glad you were able to get it working! 👍️