Thread
:
Calculate tiered commisions on commulative monthly sales
View Single Post
#
2
Posted to microsoft.public.excel.misc
Chip Pearson
external usenet poster
Posts: 7,247
Calculate tiered commisions on commulative monthly sales
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)
On Fri, 16 Jan 2009 08:28:21 -0800 (PST),
wrote:
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!
Reply With Quote
Chip Pearson
View Public Profile
Find all posts by Chip Pearson