Re: Help with Commission forumlas

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: asdfasdf (asdfasdf_at_asdfasdf.com)
Date: 11/11/04 

Date: 11 Nov 2004 07:54:16 GMT

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 <Aladin.Akyurek.1fjpfa@excelforum-nospam.com> wrote in
news:Aladin.Akyurek.1fjpfa@excelforum-nospam.com:

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

Relevant Pages

  • Re: Software sales man options
    ... > computer skills and a very good knowledge of the industry I'm targeting. ... > to get some sales done, he wants to get a salary, and I've agreed to this. ... > commission that is normal in the industry, ... For example, say at the beginning he only sells 1 app per month, and the ...
    (borland.public.delphi.non-technical)
  • Re: Processor Speed
    ... commission on a PC, and then only if he sells a customer ... Turnover in PC retail sales is extremely high because ... not worth it to put up with ultra-cheap urealistic customers ... And at my store, we processed an average of over 50 ...
    (microsoft.public.windowsxp.basics)
  • Software sales man options
    ... people have given their insight about the issue. ... computer skills and a very good knowledge of the industry I'm targeting. ... get some sales done, he wants to get a salary, and I've agreed to this. ... commission that is normal in the industry, ...
    (borland.public.delphi.non-technical)
  • Re: reconiling spreadsheets
    ... I am looking at a spreadsheet of what I sold and then I am comparing ... it to what I was paid by the company. ... For example, I have recoreded 6 sales of tv's, recorded by customer name, ... the $45 commission I am owed was paid... ...
    (microsoft.public.excel.worksheet.functions)
  • Help with Commission forumlas
    ... Salesguys are paid monthly based on commission. ... sales levels reached cumulatively for the year. ... The next month, he sells $50,000. ...
    (microsoft.public.excel.worksheet.functions)