Cumulative Sales Commissions with Tiers

[Smitty93]

Hi all,

I am looking for your help. What is the most efficient way to formulate a cumulative sales commission excel workbook, where there are tiers?

Please see the attached excel workbook, which has the commission structure, intervals, and base commission %. Also - commissions on any given account are capped at 50k.

I'm looking to see if there is a SUMPRODUCT, IF, MIN/MAX, or any other formula which can be applied to assist the very 'manual' calculation which has to occur when an account's profit crosses into the next 'tier'.

Thanks!!

 

[John Jairo V]

Hi, @Smitty93 !

You can try this formula:
[E17] : =MIN(D$6,SUMPRODUCT(--(SUM(D$17:D17)>D$9:D$12),SUM(D$17:D17)-D$9:D$12,G$9:G$12-N(+G$8:G$11))-SUM(E$16:E16))

Blessings!

 

[Smitty93]

Hi, @Smitty93 !

You can try this formula:
[E17] : =MIN(D$6,SUMPRODUCT(--(SUM(D$17:D17)>D$9:D$12),SUM(D$17:D17)-D$9:D$12,G$9:G$12-N(+G$8:G$11))-SUM(E$16:E16))

Blessings!

Hi John Jairo,

I was just testing the formula you suggested. The formula runs into trouble, when a CAPPED deal is hit early on (say Account #2) is capped, all of the deals going forward are also "Capped" at 50k, when they shouldn't be. The regular commission scale should apply to them.

Thoughts?

Thanks!!

 

[p45cal]

I calculated these values; can you confirm the results I got in red-on-green are wrong compared with your yellow cells?!

For Account#3, at 150,000, that's well inside Tier 1 @ 12%: 150,000 x .12 = 18,000 No?
For Account#4, at 200,000, that's well inside Tier 1 @ 12%: 200,000 x .12 = 24,000 No?

Oops, I was looking at the wrong file perhaps.
This is my calculations on the first file:

Are those right?

 

[Peter Bartholomew]

Hi
The attached is not really intended to be a definitive solution to your problem. It is more a study of the strengths and weaknesses of dynamic array methods in the context of the of your problem. One of the conclusions that I drew is that your bonus scheme is dependent upon the order in which the accounts are processed. Those processed later attract more commission so the effect of any cap is more significant.



BTW, I was very impressed by @John Jairo V 's solution even if there are still issues.

 

[John Jairo V]

Hi to all!

Just separing in another column the commision paid por true "CAPing". Blessings!

 

[Smitty93]

Hi
The attached is not really intended to be a definitive solution to your problem. It is more a study of the strengths and weaknesses of dynamic array methods in the context of the of your problem. One of the conclusions that I drew is that your bonus scheme is dependent upon the order in which the accounts are processed. Those processed later attract more commission so the effect of any cap is more significant.

View attachment 65253

BTW, I was very impressed by @John Jairo V 's solution even if there are still issues.

You are certainly correct - the order in which the accounts are processed is very key to the outcome, as they are cumulative over defined time period.

Thanks for this, I'll spend some time looking at ways I can utilize the arrays.

 

[Smitty93]

Hi to all!

Just separing in another column the commision paid por true "CAPing". Blessings!

Excellent! I see by removing the min from the first formula, you've fixed the issue, and have just separated out the actual paid commission in an additional column.

Thanks!!

 

[Peter Bartholomew]

@Smitty93 Thanks for setting the challenge. It provided an interesting test of array methods and helped demonstrate a number of areas in which new functions are needed if array formula are to work seamlessly (without breaking them down into components). Out of interest, I tried replacing the cap on commission per account and, instead, limited the profit eligible for commission for each account. The complexity of the calculation all but disappeared as it was no longer necessary to calculate a breakdown into tiers for each account. The order in which the accounts are processed was no longer a factor, which might be an advantage or disadvantage.