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,034
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,084,752
Messages
5,379,649
Members
401,618
Latest member
Tapper1326

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top