Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Commission forumlas
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. |
#2
|
|||
|
|||
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. -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=277291 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. In article , asdfasdf wrote: 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. |
#5
|
|||
|
|||
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 wrote in news:jemcgimpsey- : 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. In article , asdfasdf wrote: 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. |
#6
|
|||
|
|||
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$2:D2) In article , asdfasdf wrote: 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 |
#7
|
|||
|
|||
Thanks JE,
IT works perfectly, Dave JE McGimpsey wrote in : 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$2:D2) In article , asdfasdf wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for a temlate for sales and commission input? | Excel Discussion (Misc queries) | |||
sales commission template | Excel Discussion (Misc queries) | |||
Calculating commission schedule (will pay for help!) | Excel Worksheet Functions | |||
Calculating commission schedule (will pay for help!) | Excel Worksheet Functions | |||
Calculating commission schedule (will pay for help!) | Excel Worksheet Functions |