Commission Model Help - Varying Payout Rates

[EMC0625]

I am stuck on an IF statement for my employee commission model and could use some help. The commission plan is quite simple and is based on new accounts the sales rep brings in. Here is a quick example with arbitrary numbers:

Sales Rep #1

2016 Account Quota: 330
Payout Rate per account up to 100% of quota: $25
Payout rate per account between 100-200% of quota: $50
Payout rate per account above 200% of quota: $75

While the quota is an annual quota we make monthly payouts (e.g. January new accounts of 50 pays a $1,250 commission)

Where I am stuck on my IF statement is when, in a given month, the sales rep crosses into the next tier and paying a blended rate. It's not as simple as using a lookup table with percentage payouts since this a flat rate payout based on the number of accounts a sales rep brings in.

In the example below you will see the sales rep jumps tiers in October and thus the payout will be a combination of tier 1 and tier 2. This is where I cannot develop an IF statement to work and pay a blended rate, and work for the remainder of the year at the next tier.


[TABLE="width: 351"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]Cumulative
[/TD]
[TD]% of Quota
[/TD]
[TD]Payout[/TD]
[/TR]
[TR]
[TD="align: right"]January-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]11%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]February-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]21%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]March-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]32%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]April-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]42%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]May-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]53%[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: right"]June-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]64%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]July-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]74%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]August-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]85%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]September-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]315
[/TD]
[TD="align: right"]95%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]October-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]106%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]November-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]117%[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]December-16[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]127%[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


ANY help or suggestions are greatly appreciated!

 

[DRSteele]

I think I noodled it out. Copy the formulas in Row 7 downwards. I altered the December figure to test the absurd-looking IF statement.

I happen to KNOW there is a superior way...I just can't remember it. But this will suffice, I hope.

	A	B	C	D	E	F
2016 Quota
Payout rate	up to	of quota:
Payout rate	up to	of quota:
Payout rate	over	of quota:
Total	Cumulative	% of Quota	Payout
315@25
330@25 + 20@50
330@25 + 55@50
330@25+330@50+75@75

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]330[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]100%[/TD]

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

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

[TD="align: right"]200%[/TD]

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

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

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

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

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

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

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

[TD="align: center"]7[/TD]
[TD="align: right"]1-Jan-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]11%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 875[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1-Feb-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]70[/TD]
[TD="bgcolor: #EDEDED, align: right"]21%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 1,750[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1-Mar-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]105[/TD]
[TD="bgcolor: #EDEDED, align: right"]32%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 2,625[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1-Apr-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]140[/TD]
[TD="bgcolor: #EDEDED, align: right"]42%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 3,500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1-May-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]175[/TD]
[TD="bgcolor: #EDEDED, align: right"]53%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 4,375[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1-Jun-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]210[/TD]
[TD="bgcolor: #EDEDED, align: right"]64%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 5,250[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1-Jul-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]245[/TD]
[TD="bgcolor: #EDEDED, align: right"]74%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 6,125[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1-Aug-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]280[/TD]
[TD="bgcolor: #EDEDED, align: right"]85%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 7,000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]1-Sep-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]315[/TD]
[TD="bgcolor: #EDEDED, align: right"]95%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 7,875[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]1-Oct-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]350[/TD]
[TD="bgcolor: #EDEDED, align: right"]106%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 9,250[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]1-Nov-16[/TD]
[TD="align: right"]35[/TD]
[TD="bgcolor: #EDEDED, align: right"]385[/TD]
[TD="bgcolor: #EDEDED, align: right"]117%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 11,000[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]1-Dec-16[/TD]
[TD="align: right"]350[/TD]
[TD="bgcolor: #EDEDED, align: right"]735[/TD]
[TD="bgcolor: #EDEDED, align: right"]223%[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 30,375[/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=B7[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=C7/$E$1[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E7[/TH]
[TD="align: left"]=IF(C7>$E$1*$C$3,$E$1*($E$2+$E$3)+(C7-$E$1*$C$3)*$E$4,IF(C7<=$E$1*$C$2,C7*$E$2,$E$1*$E$2+(C7-$E$1)*$E$3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Tricky problem. I managed to accomplish this with the help of a few helper columns, which you can hide after you set them up.

For the purposes of this example, I assumed you have a header in row 1, and your columns are A-D, with the payout in E. I also built a table in I2:J3 with your quota and payouts. This will make it easier to maintain in the future. This can be moved, to another sheet if necessary. Given that, enter these values/formulas:

E2: =F2*$J$2+G2*$J$3+H2*$J$4
F2: =MAX(MIN($I$3-C2+B2,B2),0)
G2: =B2-F2-H2
H2: =MIN(MAX(C2-$I$4,0),B2)

I2: 0
I3: 330
I4: 660
J2: 25
J3: 50
J4: 75

Then select E2:H2, copy it, then paste it to E3:H13.
The F, G, and H columns calculate how many accounts fall into each tier. The E-H formulas theoretically could be combined into 1 function, but it would be a monster.

Let me know how that works.