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.

	Total	Cumulative	% of Quota	Payout
January-16	35	35	11%
February-16	35	70	21%
March-16	35	105	32%
April-16	35	140	42%
May-16	35	175	53%
June-16	35	210	64%
July-16	35	245	74%
August-16	35	280	85%
September-16	35	315	95%
October-16	35	350	106%
November-16	35	385	117%
December-16	35	420	127%

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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
1	2016 Quota				330
2	Payout rate	up to	100%	of quota:	$25
3	Payout rate	up to	200%	of quota:	$50
4	Payout rate	over		of quota:	$75
5
6		Total	Cumulative	% of Quota	Payout
7	1-Jan-16	35	35	11%	875
8	1-Feb-16	35	70	21%	1,750
9	1-Mar-16	35	105	32%	2,625
10	1-Apr-16	35	140	42%	3,500
11	1-May-16	35	175	53%	4,375
12	1-Jun-16	35	210	64%	5,250
13	1-Jul-16	35	245	74%	6,125
14	1-Aug-16	35	280	85%	7,000
15	1-Sep-16	35	315	95%	7,875	315@25
16	1-Oct-16	35	350	106%	9,250	330@25 + 20@50
17	1-Nov-16	35	385	117%	11,000	330@25 + 55@50
18	1-Dec-16	350	735	223%	30,375	330@25+330@50+75@75

<tbody>
</tbody>

Sheet15​

Worksheet Formulas Cell Formula C7 =B7 D7 =C7/$E$1 E7 =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)) <tbody> </tbody>

<tbody>
</tbody>

 

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