Calculate tiered commisions on commulative monthly sales

[koshain]

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!

 

[Chip Pearson]

See http://www.cpearson.com/excel/pricing.htm

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)