Commissions data base HELP

Jasonroelofs · External Since: Jul 29, 2005 Posts: 1

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 · External Since: Feb 05, 2004 Posts: 3284

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

In article <CE6F227F-ECA8-49CC-8F4A-09ACC567770D.DeleteThis@microsoft.com>,
Jasonroelofs <Jasonroelofs.DeleteThis@discussions.microsoft.com> wrote:

> 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.

Biff · External Since: Dec 09, 2004 Posts: 3582

Hi!

See this:

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

Biff

"Jasonroelofs" <Jasonroelofs.RemoveThis@discussions.microsoft.com> wrote in message
news:CE6F227F-ECA8-49CC-8F4A-09ACC567770D@microsoft.com...
>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

Astrid · External Since: Nov 14, 2006 Posts: 1

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" wrote:

> 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
>
> In article <CE6F227F-ECA8-49CC-8F4A-09ACC567770D.TakeThisOut@microsoft.com>,
> Jasonroelofs <Jasonroelofs.TakeThisOut@discussions.microsoft.com> wrote:
>
> > 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.
>

JE McGimpsey · External Since: Feb 05, 2004 Posts: 3284

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)

In article <7218FA59-A54E-4471-84D6-0017E398D43B.TakeThisOut@microsoft.com>,
Astrid <Astrid.TakeThisOut@discussions.microsoft.com> wrote:

> 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).