Thanks for the link Aladin,
I checked the site out and it is on the right track, but there are
differences that I can't work out.
Here is what I tried to work with from McGimpsey's.
Say a salesperson had the following commission schedule:
Sales Marginal
Low Threshold High Threshold commission rate
$ 0 $ 100,000 10%
$ 100,001 $1,000,000 8%
$1,000,001 and over 12%
We again calculate the differential rates,
J K L
1 Threshold Marginal Rate Diff. rate
2 0 10% = K2 which returns: 10%,
or 0.10 3 100000 8% = K3 - K2 which
returns: -2%, or -0.02 4 1000000 12% = K4 - K3
which returns: 4%, or 0.04
then construct a SUMPRODUCT() formula that calculates the commissions in
one step:
=SUMPRODUCT(--(A1$J$2:$J$4),(A1-$J$2:$J$4), $L$2:$L$4)
Note that unlike the tax example above, since the commission applies to
all sales, we need to include the bottom of the lowest band.
For sales of $500,000, the formula returns $42,000:
Sum(--{TRUE;TRUE;FALSE} * {500,000;400,000;0} * {0.10;-0.02;0.04}),
or Sum({50,000;-80,00;0}), or
$42,000
For sales of $2,500,000, the formula returns $262,000:
Sum(--{TRUE;TRUE;TRUE} * {2,500,000;2,400,000;1,500,000} *
{0.10;-0.02;0.04}), or Sum({250,000;-48,000;60,000}), or
$262,000
Here is why it didn't work. I have a running total for yearly sales to
date. Say from January through March. Here again is the rate schedule.
Rates are as follows
$1 - $100,000 30%
$100,001 - $300,000 33%
$300,001 - up 35%
The numbers above are cumulative for the year.
But, I have to look at the sales for each month individually and
calculate what the commission % should be.
See what I am saying ? I end up with two columns. One being sales for
the month, the other is total sales for the year. I need some forumla
that will look at total sales for the year, compare it to the commission
schedule, and then return the commission rate(s). That rate then has to
be multiplied by the actual dollars sold that month.
So, say a saleman was at $60K in total sales for the year. This month,
he sold $50K. His yearly total is now at $110K. The formula would have
to look at the $50K in sales this month and figure out what commission
rates apply.
The first $40K would be at $30 because yearly sales are still tier 1
($100K-$60K = $40K). The remaining $10K of this months sales would be
paid at 33% as he has reached the second tier.
Next month, if he sells either $5000 or $50,000, he would be paid at 33%
as he is still in the second tier.
See what I am getting at ?
I just can't seem to figure out how to modify what is at mcgimpsey.com
to do precisely what I need to have done.
Demo
Aladin Akyurek wrote in
:
See
http://www.mcgimpsey.com/excel/variablerate.html
for it seems to fit your problem description.
asdfasdf Wrote:
I didn't set this system up, so please don't shoot me.
Salesguys are paid monthly based on commission. Rates change based
upon
sales levels reached cumulatively for the year.
Rates are as follows
$1 - $100,000 30%
$100,001 - $300,000 33%
$300,001 - up 35%
Here is where the problem is. Sales are tracked monthly and checks
are
cut. Say in the first month a salesman sells $60,000, his
commission would be 30% of $60,000 as he falls into the first
category.
The next month, he sells $50,000. The commission on the first
$40,000 would be at 30% while the remaining $10,000 would be at 33%.
Now, say the next month the guy only sells $1000. His commission
would
still be paid at 33% because he is over $100,001 in sales for the
year.
I am currently running into two problems. First, most commission
formula/functions that I have found are somewhat regressive. That
being,
if you sell under X amount, the entire amount is commissioned at Y%
while
if you are over X amount, you are paid Z%. That is not the case
here.
The second problem is when trying to do a bunch of IF, AND,
statements, I
run into trouble whenever a single month's sales crosses from one
tier to
the next.
I would appreciate any input that some of you fine people might be
able to
provide.
Demo
email: Demo AT wilsonpages DOT com
(not sure if that cuts down on spam, but heck, it's worth a shot.