Bonus payout calculator based on units sold not percentage of cash

[snakel87]

Mr. Excel Forum,

I have a sales team that gets paid based on units sold not dollars sold and then a percentage for bonus. I'm trying to figure out a formula that would allow them to track sales and see potential commissions.
For basic math sake they need to sell 60 units a quarter.
They only get paid if they hit the 50% threshold (30 units) at $125 until 60 units which equals $7500.00 . Everything after 60 units is $200 each with no cap.
I'm trying to find a formula that as they enter their weekly totals it calculates at the correct rate meaning as they go along it shows no commission earned until 30 units are entered and then it steps up again at 60... is this even possible?

All I have so far is a simple lookup tool that tells them the rate at which they are being paid out using VLOOKUP

=VLOOKUP(C10,$C$4:$D$6,2,TRUE)

[TABLE="class: cms_table, width: 319"]
<tbody>[TR]
[TD]Sales Tiers[/TD]
[TD]Tier Minimum[/TD]
[TD]Payout Rate[/TD]
[/TR]
[TR]
[TD]0-30[/TD]
[TD]0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]31-59[/TD]
[TD]31[/TD]
[TD]$125[/TD]
[/TR]
[TR]
[TD]$60[/TD]
[TD]60[/TD]
[TD]$200[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

David

 

[Beyond_avarice]

Build a worksheet that your sales team can add their weekly sales to and it will sum up at the bottom. Connect the following to your Sum total(eg sum=A25):

=IF(AND(31<=A25,A25<=59),(A25-30)*125, IF(A25>59,(59*125)+(A25-59)*200,""))

 

[snakel87]

Build a worksheet that your sales team can add their weekly sales to and it will sum up at the bottom. Connect the following to your Sum total(eg sum=A25):

=IF(AND(31<=A25,A25<=59),(A25-30)*125, IF(A25>59,(59*125)+(A25-59)*200,""))

Beyond- This is so close, however at 31 units it should be $3875 and it's coming up $125. 60 units should come up to $7500. It's just a little off and I've tweaked it here and there but still can't figure it out.

 

[Dave Patton]

Excel 2010
	A	B	C	D
1		Week 1	Week 2	Week3
2	Units	2	58	440
3	Cumulative	2	60	500
4	Payment total	0	7,500	95,500
5		0	7,500	88,000
6
7	IF formula	0	7,500	95,500
8	Prior	0	7,500	88,000
9
10	Gross arithmetic check			95500
7aaa

Any of the 3 alternatives suggested previously should work for you.

 

[Beyond_avarice]

Then your chart is confusing me. I thought all units up to and including 59 would be at $125= $7,375.

Your latest statement indicates a desire to have the first 60 units at $125= $7,500

Thus all units beyond 61 are at $200 each.

This will do that

=IF(AND(31<=A25,A25<=60),(A25)*125, IF(A25>60,(60*125)+(A25-60)*200,""))

 

[snakel87]

Then your chart is confusing me. I thought all units up to and including 59 would be at $125= $7,375.

Your latest statement indicates a desire to have the first 60 units at $125= $7,500

Thus all units beyond 61 are at $200 each.

This will do that

=IF(AND(31<=A25,A25<=60),(A25)*125, IF(A25>60,(60*125)+(A25-60)*200,""))

Beyond,

Thank you for your help. Sorry for the confusion, which is probably why I'm having such a hard time getting my head wrapped around the formulas. With your current formula string is it possible once the total is at =or<31 then all units count as $125.
In other words if they sell 30 units they get $0, however if they sell 31 units it counts as 31 x $125 = $3875.
60 units would equal $7500 and then every unit after is $200.
Are we making this too complicated?

Thanks,

David

 

[Dave Patton]

C3 has cumulative sales

=(C3>30)*(C3*125+(C3>60)*(C3-60)*(200-125))

You can step through the formally manually step by step or use Excel's Formulas Formula Evaluate.

 

[Beyond_avarice]

In other words if they sell 30 units they get $0, however if they sell 31 units it counts as 31 x $125 = $3875.
60 units would equal $7500 and then every unit after is $200.

The latest formula should do just that. I'll try to explain what is going on.

Set up cell A25 to =SUM("Fill in your range of cells that would be the number of sales inputted").  This is going to add up whatever numbers are placed into that range.

=IF(                                            An "IF statement" asks a question that will either return a True or False result

AND(31<=A25,A25<=60),           This is the first question I am asking. Is the summed up number in cell A25 greater than or equal to 31. At 30 units is not and will return a "False". The false condition does not trigger the "TRUE" response of (A25)*125. It thus goes to the False result, which again asks another "IF" (will address below).

If the SUM is 31, then 31 <=31 [COLOR=#FF0000]AND[/COLOR] 31<=60. Since both of these statements are TRUE; the TRUE result is triggered and A25=31 units will be multiplied by $125=$3,875.


,IF(A25>60                             IF A25 fails the first question (AND(31<=A25,A25<=60)), by being less than 31 or more than 60; then this second question is asked. If A25>60 then the question triggers the TRUE response of, (60*125)+(A25-60)*200. Your first 60 units have a commission of $125 each= $7,500. Plus all units after 60 (A25-60) at $200 each. (eg A25 =61; (60*125)+(61-60)*200)). For each unit over 60; you should see a $200 incremental change over $7,500.

In the second question, I asked if A25 was greater than 60. If A25 is less than 30, then it failed the first question (AND(31<=A25,A25<=60)). It then fails the second question (A25>60) and this triggers the False responses of ,[COLOR=#FF0000]"")      [/COLOR]This result of [COLOR=#FF0000]"" [/COLOR]will make the cell [COLOR=#FF0000][/COLOR]appear blank until a TRUE result happens.

I hope that clears it up.

 

[snakel87]

The latest formula should do just that. I'll try to explain what is going on.

Set up cell A25 to =SUM("Fill in your range of cells that would be the number of sales inputted").  This is going to add up whatever numbers are placed into that range.

=IF(                                            An "IF statement" asks a question that will either return a True or False result

AND(31<=A25,A25<=60),           This is the first question I am asking. Is the summed up number in cell A25 greater than or equal to 31. At 30 units is not and will return a "False". The false condition does not trigger the "TRUE" response of (A25)*125. It thus goes to the False result, which again asks another "IF" (will address below).

If the SUM is 31, then 31 <=31 [COLOR=#FF0000]AND[/COLOR] 31<=60. Since both of these statements are TRUE; the TRUE result is triggered and A25=31 units will be multiplied by $125=$3,875.


,IF(A25>60                             IF A25 fails the first question (AND(31<=A25,A25<=60)), by being less than 31 or more than 60; then this second question is asked. If A25>60 then the question triggers the TRUE response of, (60*125)+(A25-60)*200. Your first 60 units have a commission of $125 each= $7,500. Plus all units after 60 (A25-60) at $200 each. (eg A25 =61; (60*125)+(61-60)*200)). For each unit over 60; you should see a $200 incremental change over $7,500.

In the second question, I asked if A25 was greater than 60. If A25 is less than 30, then it failed the first question (AND(31<=A25,A25<=60)). It then fails the second question (A25>60) and this triggers the False responses of ,[COLOR=#FF0000]"")      [/COLOR]This result of [COLOR=#FF0000]"" [/COLOR]will make the cell appear blank until a TRUE result happens.

I hope that clears it up.

Beyond,

The formula is perfect!!! Thank you Thank you. I was beating my head against the wall on this one.

Very Much appreciated,

David

 

[Biz]

I was trying to solve this problem with Sumproducts.


Sheet1

	A	B	C	D
Sales Tiers	Tier Minimum	Payout Rate	Differential rate
0-30
31-59

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]$125[/TD]
[TD="align: right"]$125[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]$60[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$75[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"] $ 21,875.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"] $ 3,875.00[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"] $ 225,000.00[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"] $ 308,750.00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =C2-N(C1) D3 =C3-N(C2) D4 =C4-N(C3) B7 =SUMPRODUCT(--($A7>$B$2:$B$4),($A7-$B$2:$B$4),A7*$D$2:$D$4) B8 =SUMPRODUCT(--($A8>$B$2:$B$4),($A8-$B$2:$B$4),A8*$D$2:$D$4) B9 =SUMPRODUCT(--($A9>$B$2:$B$4),($A9-$B$2:$B$4),A9*$D$2:$D$4) B10 =SUMPRODUCT(--($A10>$B$2:$B$4),($A10-$B$2:$B$4),A10*$D$2:$D$4) <tbody> </tbody>

<tbody>
</tbody>

Does anyone know how to answer this question using Sumproducts?
Your help would be greatly appreciated.

Biz