Help with Commission forumlas

[asdfasdf]

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.

 

[Aladin Akyurek]

See

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

for it seems to fit your problem description.

 

[asdfasdf]

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.


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

 

[JE McGimpsey]

I would guess that something like this will suit your needs:


J K L
1 Threshold Marginal Rate Diff. Rate
2 0 30% =K2
3 100000 33% =K3-K2
4 300000 35% =K4-K3


Now, assume your monthly sales start in B2, with year-to-date sales in
C2. Then the first month's commission is

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

subsequent months' commissions will then just be the total calculated
commission, less the commission paid to date:

D3: =SUMPRODUCT(--(C3>$J$2:$J$4),(C3-$J$2:$J$4),$L$2:$L$4) - D2

Copy D3 down as far as necessary.

 

[asdfasdf]

Thanks a bazillion JE

What you posted is sooooooooo much shorter and cleaner than what I had
been able to come up with so far.


I believe I did exactly as you said and ran into two small problems
unfortunately. Perhaps I put something in the wrong place ?


Monthly sales start in B2 with ytd in c2. C3 reads =c2+b3.


One problem is with the cells for future months with nothing entered.
Here is what I get.


Monthly YTD running total Amt Owed
50000 50000 15000
49000 99000 14700
11000 110000 18600
0 110000 14700
0 110000 18600
0 110000 14700
0 110000 18600
0 110000 14700



For the future months, the YTD carries down via my forumla and an AMT
OWED is calculated. I tried to hide the YTD by doing IF(b5=0,0,b5+c4)
and while it put zeroes in the YTD, the amt owed was still calculated.



The second (and most important) problem is located on line 4 were the
YTD splits tiers.

YTD was at 99,000 and 11,000 was added. End result should be ($1000*.3)
and ($10,000*.33) which would give you $300+$3300, or $3600 in amt owed.
But it is coming up with $18600.

Unfortunately, I can't go in and tinker around with what you provided as
I have absolutely no idea whatsoever about SUMPRODUCT functions.


I have double checked to make sure that I followed your directions, but
must admit that I could very easily be missing something.


Could you pop the same numbers in on your side and see if you get the
same results ?


Thanks,


Dave

 

[JE McGimpsey]

Yeah, I didn't think that through very well. Try this in D3, and copy
down:

=SUMPRODUCT(--(C3>$J$2:$J$4),(C3-$J$2:$J$4),$L$2:$L$4)-SUM(D$22)

 

[asdfasdf]

Thanks JE,

IT works perfectly,





Dave

Yeah, I didn't think that through very well. Try this in D3, and copy
down:

=SUMPRODUCT(--(C3>$J$2:$J$4),(C3-$J$2:$J$4),$L$2:$L$4)-SUM(D$22)