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

[avk_074]

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

Client	Type	Tiers	Percentage	Fixed
A	Tiered	1,20,000 €	3%	- €
A	Tiered	3,20,000 €	6%	- €
A	Tiered	5,50,000 €	9%	- €
A	Tiered	7,00,000 €	11%	- €
B	Fixed		0%	7,000 €
B	Variable		4%	- €
C	Tiered	1,50,000 €	2%	- €
C	Tiered	3,00,000 €	6%	- €
C	Tiered	6,50,000 €	10%	- €
D	Variable		5%	- €
E	Fixed		0%	1,500 €
E	Variable		6%	- €
F	Variable		7%	- €

Sales Figures, client wise

Client	Sales
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

 

[avk_074]

Hey all,
Can anyone please help with this?

Regards,
Amit

 

[Luke M]

For client F, you have expected result of 11%, but I think it should be 7%? See attached for proposed solution.

 

[avk_074]

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]

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]

"Possibly"

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.

Product	Client	Type	Tiers	Percentage	Fixed
Engine1	A	Tiered	1,20,000 €	3%	- €
Engine1	A	Tiered	3,20,000 €	6%	- €
Engine1	A	Tiered	5,50,000 €	9%	- €
Engine1	A	Tiered	7,00,000 €	11%	- €
Engine2	A	Tiered	1,50,000 €	4%	- €
Engine2	A	Tiered	3,75,000 €	7%	- €
Engine2	A	Tiered	5,90,000 €	10%	- €
Engine2	A	Tiered	7,23,000 €	12%	- €
Engine1	C	Tiered	1,50,000 €	2%	- €
Engine1	C	Tiered	3,00,000 €	6%	- €
Engine1	C	Tiered	6,50,000 €	10%	- €
Engine2	C	Tiered	1,75,000 €	3%	- €
Engine2	C	Tiered	3,25,000 €	7%	- €
Engine2	C	Tiered	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]

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]

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]

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



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

 

[totstiny89]

Can anyone please help with this?

 

[Luke M]

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.