• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Client wise Sales Commission Calculation (Tiered, Variable and Fixed - All in One)

avk_074

New Member
Hi all,
Hope you are doing great. I am facing a seemingly easy yet frustrating challenge.

I have a client wise multiple commission approaches i.e. Fixed, Variable, Tiered. (Mentioned as commission types)

Fixed is flat fee yearly.
Variable is sales * variable percentage (simple)
Tier wise is where if sales cross x amount then a%, if sales crossed y amount then b%, and so on.

The ultimate formula should capture Client, its type (Tiered or Variable or fixed) and if Tiered then identify in which percentage bracket it falls into as well as if there is a fixed charge for each client then add that to the final amount.

Here are the tables.

Client wise Commission Types and figures
ClientTypeTiersPercentageFixed
ATiered 1,20,000 €3% - €
ATiered 3,20,000 €6% - €
ATiered 5,50,000 €9% - €
ATiered 7,00,000 €11% - €
BFixed0% 7,000 €
BVariable4% - €
CTiered 1,50,000 €2% - €
CTiered 3,00,000 €6% - €
CTiered 6,50,000 €10% - €
DVariable5% - €
EFixed0% 1,500 €
EVariable6% - €
FVariable7% - €

Sales Figures, client wise
ClientSales
A 9,50,153
B 22,819
C 3,44,691
D 38,47,062
E 5,02,638
F 1,42,942

E.g. Commission of Client B's Sales =4% of 22,819 + 7000 Euros
Commission of Client A' Sales = 11% of 9,50,153 (>700000 Tier)
Commission of Client E's Sales = 6% (Variable) of 5,02,638 + 1500 Euros (Fixed)

I have done nested IFs with hard-coded numbers while calculating fixed commission separately in a different column and achieved the numbers. But, it is not a long-term solution as clients and their types may get added over a period of time. Changing hard-coded formula every single time is not very Excel-ish. :(

Looking forward to any kind of help on this one. File is attached. :)

Regards,
Amit
 

Attachments

avk_074

New Member
Dear Luke,
Your solution is simply elegant! Thank you a million!!
Take a bow, Excel Ninja! :)

Also, I am just wondering if such can be applied even more complex scenarios - lets say another dimension gets added - would it be possible to put that into the same formula?

Regards,
Amit
 

Luke M

Excel Ninja
Staff member
would it be possible to put that into the same formula?
Like many things in Excel...."possibly". ;)

If it's a different tier or a new customer, that can be very easily added to existing table and formula will handle it. If it's a completely new thing that's not a tier or fixed %, I'd have to know more about what the new thing is. But, if you can describe in human terms the logic behind the decision, then I'm 99% sure you can build it in XL.
 

avk_074

New Member
"Possibly" :p

I will try to explain.

As you know the table and its structure - Imagine there another column called "Product" gets added. So, now there Product wise Client wise Type wise % calculation. Here is a table.

ProductClientTypeTiersPercentageFixed
Engine1ATiered 1,20,000 €3% - €
Engine1ATiered 3,20,000 €6% - €
Engine1ATiered 5,50,000 €9% - €
Engine1ATiered 7,00,000 €11% - €
Engine2ATiered 1,50,000 €4% - €
Engine2ATiered 3,75,000 €7% - €
Engine2ATiered 5,90,000 €10% - €
Engine2ATiered 7,23,000 €12% - €
Engine1CTiered 1,50,000 €2% - €
Engine1CTiered 3,00,000 €6% - €
Engine1CTiered 6,50,000 €10% - €
Engine2CTiered 1,75,000 €3% - €
Engine2CTiered 3,25,000 €7% - €
Engine2CTiered 6,75,000 €11% - €

So, how will the current formula take that into consideration? I am just trying to understand the formula and its elasticity, even more.

Regards,
Amit
 

Luke M

Excel Ninja
Staff member
There are two parts to the formula currently. One tries to do a lookup using the tiered system, and if that fails, it tries to find a Variable value. If that also fails, result is 0. The latter is easier, so I'll start there

SUMIFS(D:D,A:A,H2,B:B,"Variable")

This is a simple SUMIFS. To factor in your product, we'll just add another criteria (shifting ranges to right since you added a column)

SUMIFS(E:E, A:A, "SomeProduct, B:B, "ClientName", C:C, "Variable")

Like I said, pretty easy. The formula for 'Fixed' will be almost the same thing, just add another criteria. You can do that part.
The first part of our % formula is a bit trickier. Here's what we have currently:
LOOKUP(I2,INDEX(C:C,MATCH(H2,A:A,0)):INDEX(C:C,MATCH(H2,A:A,0)-1+COUNTIF(A:A,H2)),
INDEX(D:D,MATCH(H2,A:A,0)):INDEX(D:D,MATCH(H2,A:A,0)-1+COUNTIF(A:A,H2)))

At the heart of it, we are doing a basic lookup, but the magic is in telling XL how to define our the start and end point of the lookup range. Before, this was done by doing a MATCH to find the starting point, and then using COUNTIF to find how many records (aka, how big the range was). Then we repeat for the return lookup range.

Thankfully, MATCH will let us concatenate arrays, and we can use COUNTIFS to add additional criteria. So, the start of the "lookup range" will be defined as
INDEX(D1:D100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0))

The end will be
INDEX(D1:D100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0)-1+COUNTIFS(A:A, ProductName, B:B, ClientName))

Then we will repeat for the return lookup range, just changing the 1st argument of INDEX to give us these two bits:
INDEX(E1:E100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0))
INDEX(E1:E100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0)-1+COUNTIFS(A:A, ProductName, B:B, ClientName))

Ok, putting everything together:
=IFERROR(LOOKUP(INDEX(D1:D100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0)):
INDEX(D1:D100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0)-1+COUNTIFS(A:A, ProductName, B:B, ClientName)),
INDEX(E1:E100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0)):
INDEX(E1:E100, MATCH(ProductName&ClientName, A1:A100&B1:B100, 0)-1+COUNTIFS(A:A, ProductName, B:B, ClientName))),
SUMIFS(E:E, A:A, "SomeProduct, B:B, "ClientName", C:C, "Variable"))
 

avk_074

New Member
Hi Luke!

The approach and the formula is absolutely clean! Explanation made it really easy.
Thanks a tonne for guiding through it!! :)

Wish you even more success in life!

Regards,
Amit
 

bosco_yip

Excel Ninja
and the formula and it
Another formula solution option.

1] Table type A, In J2 formula copied down :

=IFERROR(LOOKUP(I2,OFFSET(C$1,MATCH(H2,A:A,)-1,,COUNTIF(A:A,H2),2)),SUMIFS(D:D,A:A,H2,B:B,"Variable"))

77943

2] Table type B, In K2 formula copied down :

=IFERROR(LOOKUP(J2,OFFSET(D$1,MATCH(1,INDEX((A$1:A$100=H2)*(B$1:B$100=I2),0),)-1,,COUNTIFS(A:A,H2,B:B,I2),2)),SUMIFS(E:E,A:A,H2,B:B,I2,C:C,"Variable"))

77946
 

Attachments

Last edited:

Luke M

Excel Ninja
Staff member
Can anyone please help with this?
We are not sure to what you are referring. You're not the author of this post, nor a previous poster in the thread. If you have a similar question, I'd suggest creating your own thread so others see a thread that hasn't been answered, and thus are more likely to reply.
 
Top