Commissions data base HELP

[Jasonroelofs]

I need help programing a data base to figuare out commisions. I need a data
base to multiply 0-250 by $.50 then from 251-500 by $.65 then from 501-750 by
$.75 then from 751-1000 by $.85 then from 1001-1500 by $1.00 then from
1501-2000 by $1.15 and then from 2001 + by $1.25. So if someone would enter
2000 I need the data base to multiply the first 250 by $.50 then the next 250
by $.65 then the next 250 by $.75 and so on. Any suggestions will help.
Thanks
Jason

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1>{0,250,500,750,1000,1500,2000}),
(A1-{0,250,500,750,1000,1500,2000}), {0.5,0.15,0.1,0.1,0.15,0.15,0.1})

for an explanation, and more flexible ways of accomplishing this, take a
look at

http://www.mcgimpsey.com/excel/variablerate.html

 

[Biff]

Hi!

See this:

http://mcgimpsey.com/excel/variablerate.html

Biff

 

[Astrid]

Hello,
I am looking for a similar Commissions Table but a bit more "complicated".
I use what we call a Base Commission rate (BCR) for each Account Executive,
then we have 2 different Accelerator Commissions rates (ACR1 and ACR2).
Also each Account Executive has a Sales Goal (SG) per Quater.
If the AE books less or equal to 100% of his sales goal, he gets:
Revenue booked*BCR
For sales in excess of 100% but inferior to 125%, the ACR1=BCR*1.25 and it
is applied to the part of the revenue booked by the AE that is between 100%
and 125% of the SG.

Then for sales in excess of 125% of the SG, the ACR=BCR*1.50 AND and it is
applied to the part of the revenue booked by the AE that is over 125% of the
SG.


For example if:
SG= 800,000.00
Revenue Booked=$1,450,000.00
BCR=2.50%
then ACR1=2.5*1.25=3.125%
ACR2=2.5%*1.50=3.75%
and the guy will
earn=(800,000*2.5%)+(200,000*3.13%)+(450,000*3.75%)=$43,135.00

If someone out there could help me figure out how to create my commissions
table, I would appreciate it a lot (you have no idea).

thanks,

Astrid

 

[JE McGimpsey]

You could just modify one of the examples in the reference page that I
gave. Your situation is a very straightforward case of the commissions
example listed there. For instance, if your commission table is:

J K L
1 Sales Marginal Rate Differential Rate
2 0 2.5% =K2
3 800000 =K2*1.25 =K3-K2
4 =J3*1.25 =K2*1.5 =K4-K3

Then, assuming Revenue Booked is in A1, the formula is simply:

=SUMPRODUCT(--(A1>$J$2:$J$4),(A1-$J$2:$J$4),$L$2:$L$4)