commission plan

[stargazer]

I need help with what I think is a Vlookup issue. I am trying to develop a sales commission schedule
that pays, as an example, 45% of gross commission up to $35000; 50% to $45,000; 55% to $65,000...up
to 80% in 5% and $10,000 increments. Commissions are listed and paid depending on where they fall as
they build year to date (YTD) subtotals. For example, sales agent has earned YTD commissions of $34.000;
now has a sale with a $2,000 commission. I have developed a schedule which simply pays on the last
level reached on the schedule, but I need a formula that will now pay $1,000 of the commission at the
45% level and then $1,000 at the newly reached 50% level. I'd appreciate a model that would accomodate
this situation. Thanks very much in advance.

[Dave Patton]

What commission is earned at
34,000?

60,000?

Consider Vlookup (I named a lookup table
rComm)

=VLOOKUP(A2,rComm,2,1)+(A2-VLOOKUP(A2,rComm,1,1))*VLOOKUP(A2,rComm,3)

Consider a table of commission
AccumSales amount %

[stargazer]

Thank you for responding. The example I mentioned would deal with the issue of a $2000 commission now being earned by someone who had made $34000 so far in the year and would call for the commission plan to pay $1000 of the commission at the $25000 to $35000 commission step and the other $1000 at the next step level, which here would be the $35000 to $45000 step. The first $1000, therefore would be paid at 45% and the 2nd $1000 of the $2000 commission would kick the percentage up to 50%,following the commission schedule. It is the issue of computing this crossover from one step to another that I can't get. I hope I am clarifying and not confusing. It would be helpful if you could show a short example of a schedule as I am describing and how your formula would relate to it. See completed example below. Thanks very much.


Commission Schedule
Gross Revenue Sales Commission
0-$35000 45%
35001-45000 50%
45001-55000 55%
55001-65000 60%
65001-75000 65%
75001-85000 70%

Year to Date
Gross Revenue
$34,000


New Sale ---- Commission
$2,000 ---- ???

Formula needed to compute New Sale Commission based upon the incremental commission schedule.



[ This Message was edited by: stargazer on 2002-03-29 13:58 ]

[ This Message was edited by: stargazer on 2002-03-29 14:00 ]

[ This Message was edited by: stargazer on 2002-03-29 14:01 ]

[Dave Patton]

Did you try using Vlookups in a cell to calculate the commission?

I provided a formula that works with the usual commission structures.

Your commentary does not reconcile to the ranges of rates.

What amount do your calculate for

40,000? 60,000?

With your feedback, I can edit the lookup table and give you addtional information if necessary.

[stargazer]

The critical issue is to have a formula, some variety of vlookup, I believe, that relates to where to "new dollar" earned puts the sales agent on year to date earnings level, which determines what percentage that agent gets for the "new dollar" earned (in this case the $2000). In the example I am using to illustrate the need, I have purposely chosen to say that the agents year to date (YTD) earnings are at $ 34000, which would mean that the next dollar earned would be producing a 45% commission, up to the YTD $35000 level and then would jump to the next step on the schedule.
Since the schedule calls for the commission to jump to 50% for earnings over $35000, my $2000 example is going to require computing 2 different commission levels for this transaction: 45% for the first $1000 and 50% for the 2nd $1000 of the agents new sale, since the agent has earned $34000 so far in the example year. In my business we call this an "incremental commission plan", i.e., the more you make during the year, the more you get.
Relating to your question about $40000 and $60000, if they were individual new sales, they would be paid at different rates as they added to the year to date total earnings. So a $40,000 sale adding now to the existing year to date earnings of $ 34000 would mean that the first $1000 would result in a 45% commission, the next $9999 (the $35,001-$45,000 step) would be paid at 50%, etc., up to the full $40,000 amount, climbing the commission schedule. I hope I'm clarifying and do appreciate your patience and input here. Regards, Bob

[Dave Patton]

The calculation that you require can be done with the Vlookup formula that I provided earlier.

A total commission is calculated on the accumulated amount. You can calculate the net difference from previous calculations.

.. 0.. 0.. 45.0%
35,000 15,750 50.0%
45,000 20,750 55.0%
65,000 31,750 60.0%
75,000 37,750 65.0%
85,000 44,250 70.0%
95,000 51,250 75.0%
105,000 58,750 80.0%

=VLOOKUP(A2,rComm,2)+(A2-VLOOKUP(A2,rComm,1))*VLOOKUP(A2,rComm,3)

The table is named rComm

Your can test or review each part of the formula separately.

Total sales is entered in A2 in the above example; revise it as necessary.

I had hoped that you would calculate the expected result for a couple of amounts for clarity.



[ This Message was edited by: Dave Patton on 2002-03-30 10:18 ]

[stargazer]

To try your formula: Should I enter the table you sent, with 0 0 45% starting at A3, with A2 being the cell where I enter the new total revenue? If I understand you,your formula would go in cell where I want to see commission earned on the total year to date, including, in my example, the new $2000 amount. I would, separately, then need to subtract the previously computed total commission earned, from the NEW total commission, in order get what I would be paying the agent for the new $2000 deal?

[ This Message was edited by: stargazer on 2002-03-30 05:26 ]

[Dave Patton]

You can refer to the cell that contains the amount your want to calculate the commission on; revise A2 in the formula.

You can put the Lookup table anywhere; name it rComm.

-test the formula to see if it works correctly

- look at the examples in Help

[ This Message was edited by: Dave Patton on 2002-03-30 06:53 ]

[stargazer]

Dave, Thanks very much for your help here. The formula does indeed do the job. I am getting correct amounts. I will now start to develop some alternative scenarios and try to make the proper adjustments. Thanks again. Bob

[screenplay greg]

I'm trying to accomplish the same thing. Can you tell me how the table is set up ? I've never used the vlook up function.

Thanks,

Greg