Sales commission formula help

[DarrenCl]

Hi All,

I need some help getting a formula right for sales commission.

I've attached the sheet I'm working on, I have an issue getting the commission to work out correctly if they are over target. They should get 5% up to target and then 10% on anything over it each month.

Then once they have hit $1.5m in accumulative commission they go to a 10% flat rate. But in the month they hit that $1.5m they should get 5% up to the $1.5m then 10% over it.

Any help would be great as my brain is frazzled figuring it out. I do have a formula in there working out the commission but I'm not sure if it's helpful to you.

Thanks in advance

[Glenn Kennedy]

Total confusion. What's the 3% and 5% got to do with it??

As a starter, see the yellow-shaded cells.

[Glenn Kennedy]

The presented formulae calculate at 5% for everything up to 1.5 m and 10 % thereafter, with a further formula to show the monthly bonus. Below the yellow shaded cells there is a whole pile of stuff that is not mentioned in the description in your thread. I have no idea what this is meant to be about, so I ignored it.

[KOKOSEK]

Excel 2016 (Windows) 32 bit

	I	J	K
21			$ 143,333.33
22			$ 3,333.33
23		October commision total	$ 146,666.67

Sheet: Sheet1

I can't paste part with codes or paste formulas (firewall discover HTML :-)) so on picture:
Capture.JPG
yellow formulas are array so accept them with Ctrl+Shift+Enter not just Enter.

[DarrenCl]

Total confusion. What's the 3% and 5% got to do with it??

As a starter, see the yellow-shaded cells.

Sorry for the confusion.

So if they are under target they get 3%, on target they get 5% and anything over they get 10%. My forumla in B5 calculates that.

In January if they do 150k of sales they will get 5% on the target amount of 133k and then 10% of anything over (150k-133k = 17k) so total commission would be 133k @ 5% and 17k @ 10%. Total commission $8333.

This all then has a kick on effect to them hitting the $1.5m in cumulative sales. So on my sheet they hit $1.5m in October but $66k should be at 5% and the rest 10%. Then all months after that should be 10%.

[Glenn Kennedy]

Pleae manually calculate some expected results. Make sure you calculate On target and below target for cumulative total < 1.5M and > 1.5 M.

[Glenn Kennedy]

Is the target cumulative or judged one onth at a time.

Jan Target 10
Feb Target 10

Jan Sales 5
Feb Sales 20

Cumulatively Jan and Feb are above target. Separately Feb is and Jan is not. Which applies?

[DarrenCl]

Each month is judged separately when it comes to monthly commission. It's only the $1.5m that is judge cumulatively.

I calculated the expected results in row 16. I've calulated the expected result for October in cell K23

[Glenn Kennedy]

Guess 2:

In B9, copied across:

=IF(B3>=B2,B3*0.05,B3*0.03)+IF(SUM($B3:B3)>=1500000,B3-B2,0)*0.05

[Glenn Kennedy]

Just seen your last post....

[Glenn Kennedy]

If the manually calculated result is in row 16.... how come K23 is not equal to K16??????

My previous formula replicates row 16....

[DarrenCl]

Sorry row 16 is only calculated up to column J correctly, I put the calculation below to make it easier to see how it's worked out.

Your formula works out $7500 as the commission for January, but it should be $8333 My original formula calculates $7500 too. It needs to work out 5% up to target and 10% of the additional over target

[Glenn Kennedy]

Let us restate the rules.

Cumulative total <1.5M
Under target 3%
>=Target 5%

Once cumulative total >= 1.5 M
Under target =??
>=Target =10% on amount that is > target and 5% on the rest.

Please supply manually calculated results that are correct!!!

[DarrenCl]

From column 9 it says what the results should be, IF its under target 3%, IF it's on target 5%, IF it's over target 5% + 10%. It's all calculated. I've attached again with how you want to see it. Rows 11, 12 & 16 are the expected results for the different scenarios.

Commission Structure
3% of delivered revenue if under target
5% of delivered revenue if on target
10% of revenue delivered that is over target (so you get 5% for hitting and then 10% for anything delivered above target)
10% of revenue delivered after $1.5m

To answer restating the rules:

Cumulative total <1.5M
Under target 3%
=Target 5%
>Target 5% + 10%

Once cumulative total >= 1.5 M
Under target =??
>=Target =10% on amount that is > target and 5% on the rest.
Everything after they reach 1.5m is 10% irrespective of what percentage of the monthly earned is

[Glenn Kennedy]

I must be being especially stupid today!!! How come this guy is getting ANYTHING at 10% long, long before he has hit 1.5 M cumulative. (Jan & Mar)?

[DarrenCl]

He gets 10% on anything over the monthly target, so 5% up to monthly target, 10% on the additional only.

When he hits 1.5m he gets everything over 10%.

It's just the way we do it

[Glenn Kennedy]

I think (finally) the penny has dropped....

[Glenn Kennedy]

I have created a helper row showing cumulative income. I think this is it...

[DarrenCl]

That's excellent Glenn! Thanks so much for that really appreciate it.

[Glenn Kennedy]

Sherlock Holmes used to talk about a 2 pipe problem. Being Irish - it was, for me, a two pint problem. Inspiration struck midway down No 2. Had to abandon the Big Boss for a while to complete it.

There's probably a neater way to get there. But sometimes you just need Brute Force & Ignorance.

[Glenn Kennedy]

The elephant in the room.

What would the results have been for October if:

a) the monthly target was exceeded
b) met
c) not met?

I suspect that we are not finished, yet!!

[DarrenCl]

thanks Glenn, ok it will be as:

Monthly target not met - 3% of the amount up to cumulative $1.5m, 10% on anything over the $1.5m
Monthly Target Met - 5% up to $1.5m cumulative, 10% on anything over the $1.5m
Monthly Target Exceeded - 5% up to $1.5m cumulative, 10% on anything over the $1.5m

So at the moment the formula works out 5% up to the $1.5m mark, I just need it to work out if the monthly target is not met in the same month the cumulative hits $1.5m then it works the under $1.5m as 3%

Hopefully that makes sense.

[slickrick666]

Thanks for this

[Glenn Kennedy]

Hi. You will see that there are 2 versions of the formula. I'm pretty certain that V2 is correct. However, I'm in a bit of a flat spin today (kitchen renovation is also happening as well as the other things I mentioned in my PM.

Check it carefully for the cross-over month (October) when the annual target is met.
Im 99% certain it's V2 you need... but over to you for testing.

[DarrenCl]

That is spot on Glenn, thanks so much for that! V2 was right

[Glenn Kennedy]

Woo Hoo!!!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.