Tiered Commission Structure - HELP ASAP PLEASE

[KimberlyWelty]

So, we have a commission structure where I need to calculate the payout each month, based on what percent of quota they have achieved. I know I need to use a sumproduct, but I am not able to make it work. The ICF% should be based on the % to Quota Running Total Revenue to Quota column (P). But the pay monthly pay would only be based on the month of sales. In april, they in T1 so the commission would be at 1.5% through June. In July, they are right at the level to cross into Tier 2. If they cross over by 100,000 in revenue, a portion should be at the 1.5% and the reminder should be at 3.47% based on the annual quota, but I must subtract what has already been paid.

Any assistance you can provide would be greatly appreciated!



M N O P Y Z AA AB
[TABLE="width: 761"]
<colgroup><col><col><col span="2"><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD] Fiscal Pd [/TD]
[TD] Month [/TD]
[TD] Total Revenue to Quota [/TD]
[TD] Running Total Revenue to Quota [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Min [/TD]
[TD]Max [/TD]
[TD]ICF %[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]April[/TD]
[TD] $ 132,270.55[/TD]
[TD] $ 132,270.55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T1[/TD]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]50.00%[/TD]
[TD="align: right"]1.50%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]May[/TD]
[TD] $ 150,499.90[/TD]
[TD] $ 282,770.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T2[/TD]
[TD="align: right"]50.01%[/TD]
[TD="align: right"]80.00%[/TD]
[TD="align: right"]3.47%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]June[/TD]
[TD] $ 113,007.59[/TD]
[TD] $ 395,778.04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T3[/TD]
[TD="align: right"]80.01%[/TD]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]4.22%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]July[/TD]
[TD] $ 1,213,637.43[/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T4[/TD]
[TD="align: right"]100.01%[/TD]
[TD="align: right"]110.00%[/TD]
[TD="align: right"]6.25%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]August[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T5[/TD]
[TD="align: right"]110.01%[/TD]
[TD="align: right"]120.00%[/TD]
[TD="align: right"]6.50%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]September[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T6[/TD]
[TD="align: right"]120.01%[/TD]
[TD="align: right"]130.00%[/TD]
[TD="align: right"]6.75%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]October[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T7[/TD]
[TD="align: right"]130.01%[/TD]
[TD="align: right"]150.00%[/TD]
[TD="align: right"]7.00%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]November[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]December[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]January[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]February[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]March[/TD]
[TD] $ - [/TD]
[TD] $ 1,609,415.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Grand Total [/TD]
[TD] [/TD]
[TD] $ 1,609,415.47[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

What's the quota revenue number for that example?

 

[KimberlyWelty]

What's the quota revenue number for that example?

[TABLE="width: 227"]
<tbody>[TR]
[TD="class: xl75, width: 114"] FY 2019 Quota [/TD]
[TD="class: xl76, width: 113"] $
3,218,831 [/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

R/Q​

[/td][td="bgcolor:#F3F3F3"]

ICF %​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td]

0%​

[/td][td]

1.50%​

[/td][td="bgcolor:#CCFFCC"]

1.50%​

[/td][td="bgcolor:#CCFFCC"]E2: =D2-N(D1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td]

50%​

[/td][td]

3.47%​

[/td][td="bgcolor:#CCFFCC"]

1.97%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td][/td][td]

80%​

[/td][td]

4.22%​

[/td][td="bgcolor:#CCFFCC"]

0.75%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td]

100%​

[/td][td]

6.25%​

[/td][td="bgcolor:#CCFFCC"]

2.03%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td]

110%​

[/td][td]

6.50%​

[/td][td="bgcolor:#CCFFCC"]

0.25%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td]

120%​

[/td][td]

6.75%​

[/td][td="bgcolor:#CCFFCC"]

0.25%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td]

130%​

[/td][td]

7.00%​

[/td][td="bgcolor:#CCFFCC"]

0.25%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Quota​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td][/td][td][/td][td][/td][td]

3,218,831.00​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td="bgcolor:#F3F3F3"]

Period​

[/td][td="bgcolor:#F3F3F3"]

Month​

[/td][td="bgcolor:#F3F3F3"]

Rev​

[/td][td="bgcolor:#F3F3F3"]

Cumu​

[/td][td="bgcolor:#F3F3F3"]

Comm​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

1​

[/td][td]Apr[/td][td]

132,270.55​

[/td][td="bgcolor:#E5E5E5"]

132,270.55​

[/td][td="bgcolor:#CCFFFF"]

1,984.06​

[/td][td="bgcolor:#CCFFFF"]E14: =SUMPRODUCT((D14 > $C$2:$C$8 * $D$12) * (D14 - $C$2:$C$8 * $D$12) * $E$2:$E$8) - SUM(E$13:E13)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

2​

[/td][td]May[/td][td]

150,499.90​

[/td][td="bgcolor:#E5E5E5"]

282,770.45​

[/td][td="bgcolor:#CCFFFF"]

2,257.50​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

3​

[/td][td]Jun[/td][td]

113,007.59​

[/td][td="bgcolor:#E5E5E5"]

395,778.04​

[/td][td="bgcolor:#CCFFFF"]

1,695.11​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

4​

[/td][td]Jul[/td][td]

1,213,637.43​

[/td][td="bgcolor:#E5E5E5"]

1,609,415.47​

[/td][td="bgcolor:#CCFFFF"]

18,204.56​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

5​

[/td][td]Aug[/td][td]

749,773.65​

[/td][td="bgcolor:#E5E5E5"]

2,359,189.12​

[/td][td="bgcolor:#CCFFFF"]

26,017.15​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

6​

[/td][td]Sep[/td][td]

709,454.05​

[/td][td="bgcolor:#E5E5E5"]

3,068,643.17​

[/td][td="bgcolor:#CCFFFF"]

28,319.89​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

7​

[/td][td]Oct[/td][td]

562,770.11​

[/td][td="bgcolor:#E5E5E5"]

3,631,413.28​

[/td][td="bgcolor:#CCFFFF"]

32,351.07​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

8​

[/td][td]Nov[/td][td]

608,305.56​

[/td][td="bgcolor:#E5E5E5"]

4,239,718.84​

[/td][td="bgcolor:#CCFFFF"]

40,620.76​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

9​

[/td][td]Dec[/td][td][/td][td="bgcolor:#E5E5E5"]

4,239,718.84​

[/td][td="bgcolor:#CCFFFF"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

10​

[/td][td]Jan[/td][td][/td][td="bgcolor:#E5E5E5"]

4,239,718.84​

[/td][td="bgcolor:#CCFFFF"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

11​

[/td][td]Feb[/td][td][/td][td="bgcolor:#E5E5E5"]

4,239,718.84​

[/td][td="bgcolor:#CCFFFF"]

0.00​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

25​

[/td][td]

12​

[/td][td]Mar[/td][td][/td][td="bgcolor:#E5E5E5"]

4,239,718.84​

[/td][td="bgcolor:#CCFFFF"]

0.00​

[/td][td][/td][/tr]
[/table]

 

[KimberlyWelty]

Thank you so much for the quick response! something I am still doing is not working, but I will figure it out!

 

[KimberlyWelty]

Just wanted to get back to you and say THANK YOU VERY MUCH!, both for your quick response and for the help!

 

[shg]

You're welcome.

 

[KimberlyWelty]

HELP AGAIN --- Wondering if you might be able to help with an adjustment to this formula? How can I make this formula only give m the commission earned after they achieve each tier? If they do not achieve the tier they do not get anything. I used this formula you gave me for another tier structure that only is supposed to pay when they reach the next tier, which for that formula is at every 10%. Any assistance you can provide on this would be much appreciated!!!

Kimberly

 

[KimberlyWelty]

Wondering if you could assist one more time on this???

Now I need to take the formula you provided and have it not pay anything until it reaches each Tier. So, in this example, they get nothing till it hits 50%, and then again nothing till they hit 80%. I have actually applied this formula to another group of data, and they only get the commission after they earn each 10% of the quota. Does this make sense?

Any assistance you can provide would be greatly appreciated!
Kimberly