Calculate tiered commisions on commulative monthly sales

koshain

New Member
Joined
Jan 16, 2009
Messages
7
Hi -
I have done a lot of reseach regarding tiered commision formulas in excel but none of them addresses how to calculate tiered commisions on cummulative monthly sales.

The annual quota is $1,200,000.

There are four commision tiers
0 - 80% of quota = 3% commision
80%- 100% of quota = 9% comission
100%-110% of quota = 5% commision
110%- > of quota = 8% commision

I have variable sales made in each of 12 months. I need to calculate the comission on cummulative sales basis each month.

For instance if in first month the whole $1,200,000 is achieved the comission will be as follow:
3% on 960000
9% on rest 240000

On other hand if the sales for first month were $20,000 and sales for seond month were $1,400,000

then the commision for first month would be 3% on $20,000
and
comission for second month would be as follow
3% on 940,000 (960000-20000) as commision is paid on annual cumulative sales.
9% on 260,000
5% on 12,000
8% on 80,000

Now this is my dilema as usually tiered comission are paid on monthly or quarterly basis and not on commulative annual sales. I can seem to get my head around this.

Any help would be appreciated.

Thanks

AL!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,005
If I'm not mistaken, I believe the calculation for your last example is not correct. If so, let's assume that B2 contains the sales for the first month, C2 contains the sales for the second month, and so on. Try...

B3, copied across:

=SUMPRODUCT(--(SUM($A$2:B2)>{0,960000,1200000,1320000}),B2-{0,960000,1200000,1320000},{0.03,0.06,-0.04,0.03})-SUM($A$4:A4)

Note that Column A is needed in the calculation. It can be either empty/blank or contain a text value, such as a row header.

Hope this helps!
 

Forum statistics

Threads
1,081,765
Messages
5,361,156
Members
400,615
Latest member
inzimam

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top