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
Sales Figures, client wise
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 hardcoded numbers while calculating fixed commission separately in a different column and achieved the numbers. But, it is not a longterm solution as clients and their types may get added over a period of time. Changing hardcoded formula every single time is not very Excelish.
Looking forward to any kind of help on this one. File is attached.
Regards,
Amit
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 hardcoded numbers while calculating fixed commission separately in a different column and achieved the numbers. But, it is not a longterm solution as clients and their types may get added over a period of time. Changing hardcoded formula every single time is not very Excelish.
Looking forward to any kind of help on this one. File is attached.
Regards,
Amit
Attachments

11.5 KB Views: 6