Interesting Commission Tier Calculator Wrinkle

[liter123]

I've been looking through multiple posts and am familiar with utilizing SUMPRODUCT to calculate commission tiers that pay out different rates based on sales thresholds.
As an example: Pay 10% from $0-$99; 15% from $100-$199; 20% from $200-$299

We have a new wrinkle in the comp plans this year that requires different rate payouts for each sale. Each now has a recurring component and a miscellaneous component that are paid at different rates (in addition to taking the tiers into account). I can't figure out how to accommodate it. Here's an example:

The more straightforward part first. Commission is paid out on a monthly basis. There is a running cumulative total for sales that is to be paid out based on the following tiers:

• Tier 1 (14% Rate) $0.00
• Tier 2 (25% Rate) $196,000.00
• Tier 3 (40% Rate) $392,000.00

So if $190,000 is closed in January, commission is paid at 14%. Then with another $100,000 closed in February, the running total is now $290,000.00. $6,000.00 is paid at 14% and $94,000 is paid at 25%, etc for March, April, May, and so on.

The wrinkle is with the 2 components applied to every sale. #1 ) Recurring (to be paid at the tier rates ie. 14%, 25%, 40%) #2 Miscellaneous at 5%.

So now if you close $190,000 in January the breakdown components could be $150,000 as Recurring paid at 14%, and $40,000 as Miscellaneous paid at 5%. Once in February, The $100,000 closed at $80,000 Recurring and $20,000 Miscellaneous, would still pay the $20,000 at 5%, but the $80,000 would have to take into account the old tier of 14% and the new one of 25%. The way the different from Tier 1 to Tier 2 of $6,000 is accounted for (as you cross into the new tier) is by taking the percentage of Recurring and Miscellaneous for that month ie. 80% Recurring / 20% Miscellaneous for February, chopping up the $6,000 as 80% (paid at 14%) and 20%, then moving into Tier 2 with the 25% payout number for the remaining amount.

It's pretty confusing. I'd like to hopefully find a way to manage this with Excel. Thank you!

 

[CyrusTheVirus]

Welcome to the forums. Can you please copy/paste from excel an example of your data in terms of what it looks like and also what you want your results to be?

 

[joeu2004]

I assume that the tier breakpoints are based on total sales, not the 80% allocated to "recurring" commission.

The following demonstrates one design.

	A	B	C	D
1		Recurring	Misc
2	Allocation	80%	20%
3
4	Misc rate	5%
5	Recurring rate
6	Sales >=...	Rate	Diff rate
7	$0.00	14.00%	14.00%
8	$196,000.00	25.00%	11.00%
9	$392,000.00	40.00%	15.00%
10
11		Sale	Recurring Comm	Misc Comm
12	Jan	$190,000.00	$21,280.00	$1,900.00
13	Feb	$100,000.00	$19,472.00	$1,000.00
14	Mar	$200,000.00	$51,760.00	$2,000.00

<tbody>
</tbody>

Formulas
C7: =B7 - N(B6)
Copy C7 into C8:C9

C12: =ROUND($B$2*SUMPRODUCT((SUM($B$12:B12)>=$A$7:$A$9)*(SUM($B$12:B12)-$A$7:$A$9),$C$7:$C$9) - SUM($C$11:C11), 2)
D12: =ROUND(B12*$C$2*$B$4, 2)
Copy C12:D12 into C13:D14

The SUMPRODUCT expression calculates the cumulative commission based on cumulative sales. But only 80% applies to "recurring" commissions, less the sum of the previous monthly "recurring" commissions.

20% of the monthly sales earns commissions at the "misc" rate.

To demonstrate the correctness, the following produces the same results based on manual calculations.

Jan 190,000       Recurring                     Misc
                  ---------------------------   ------------------------
Tier1 = 190,000   *80% = 152,000*14% = 21,280   *20% = 38,000*5% = 1,900


Feb 100,000                           Recurring                    Misc
Cuml  = 100,000 + 190,000 = 290,000   --------------------------   ------------------------
Tier1 = 196,000 - 190,000 =   6,000   *80% =  4,800*14% =    672   *20% =  1,200*5% =    60
Tier2 = 290,000 - 196,000 =  94,000   *80% = 75,200*25% = 18,800   *20% = 18,800*5% =   940
                            -------          ------       ------          ------      -----
                            100,000   *80% = 80,000       19,472   *20% = 20,000*5% = 1,000


Mar 200,000                           Recurring                     Misc
Cuml  = 200,000 + 290,000 = 490,000   ---------------------------   ------------------------
Tier2 = 392,000 - 290,000 = 102,000   *80% =  81,600*25% = 20,400   *20% = 20,400*5% = 1,020
Tier3 = 490,000 - 392,000 =  98,000   *80% =  78,400*40% = 31,360   *20% = 19,600*5% =   980
                            -------          -------       ------          ------      -----
                            200,000   *80% = 160,000       51,760   *20% = 40,000*5% = 2,000

Note that you were wrong about the 80/20% amounts for Jan: $152,000 instead of $150,000; $38,000 instead of $40,000.

 

[joeu2004]

The way the different from Tier 1 to Tier 2 of $6,000 is accounted for (as you cross into the new tier) is by taking the percentage of Recurring and Miscellaneous for that month ie. 80% Recurring / 20% Miscellaneous for February

Note that you were wrong about the 80/20% amounts for Jan: $152,000 instead of $150,000; $38,000 instead of $40,000.

No: my mistake. I did not notice that the allocation between "recurring" and misc might vary each month.

The following takes that into account.

	A	B	C	D	E	F
1	Misc rate	5%
2	Recurring rate
3	Sales >=...	Rate	Diff rate
4	$0.00	14%	14%
5	$196,000.00	25%	11%
6	$392,000.00	40%	15%
7
8		Sales	Recurring Allocation	Misc Allocation	Recurring Comm	Misc Comm
9	Jan	$190,000.00	$150,000.00	$40,000.00	$21,000.00	$2,000.00
10	Feb	$100,000.00	$80,000.00	$20,000.00	$19,472.00	$1,000.00
11	Mar	$200,000.00	$40,000.00	$160,000.00	$12,940.00	$8,000.00

<tbody>
</tbody>

Formulas:

C4: =B4 - N(B3)
D9: =ROUND(B9 - C9, 2)
E9: =ROUND((SUMPRODUCT((SUM($B$8:B9)>=$A$4:$A$6) * (SUM($B$8:B9)-$A$4:$A$6), $C$4:$C$6)
            - SUMPRODUCT((SUM($B$8:B8)>=$A$4:$A$6) * (SUM($B$8:B8)-$A$4:$A$6), $C$4:$C$6)) * C9/B9, 2)
F9: =ROUND(D9*$B$1, 2)
Copy C4 into C5:C6
Copy D9:F9 into D10:F11

The SUMPRODUCT expression is the difference between the current month and previous month total commissions (not prorated). The difference is prorated by the "recurring" allocation factor.

To minimize quantization "error" due to rounding, I would record the "recurring" and misc allocation amounts, not their percentages -- although you might calculate one as a percentage of sales, explicitly rounding. But the other (misc, in my case) should be calculated by the difference, again to minimize quantization error.

(The purpose of rounding in D9 is to avoid binary arithmetic anomalies: infinitesimal "errors" that arise because most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which is what Excel uses to represent numeric values internally.)


The following manual calculations produce the same results.

Jan 190,000       Recurring              Misc
                  --------------------   ------------------------
Tier1 = 190,000   150,000*14% = 21,000   40,000*5% = 2,000


Feb 100,000                           Recurring                             Misc
Cuml  = 100,000 + 190,000 = 290,000   -----------------------------------   ---------------------------------
Tier1 = 196,000 - 190,000 =   6,000   *80000/100000 =  4,800*14% =    672   *20000/100000 =  1,200*5% =    60
Tier2 = 290,000 - 196,000 =  94,000   *80000/100000 = 75,200*25% = 18,800   *20000/100000 = 18,800*5% =   940
                            -------                   ------       ------                   ------      -----
                            100,000                   80,000       19,472                   20,000*5% = 1,000


Mar 200,000                           Recurring                              Misc
Cuml  = 200,000 + 290,000 = 490,000   ------------------------------------   -----------------------------------
Tier2 = 392,000 - 290,000 = 102,000   *40000/200000 =  20,400*25% =  5,100   *160000/200000 =  81,600*5% = 4,080
Tier3 = 490,000 - 392,000 =  98,000   *40000/200000 =  19,600*40% =  7,840   *160000/200000 =  78,400*5% = 3,920
                            -------                    ------       ------                    -------      -----
                            200,000                    40,000       12,940                    160,000*5% = 8,000

(Re: my Mar example.... I suspect that "recurring" allocation is always greater than misc. But I made it less for demonstration purposes, since it exposed a design flaw in an intermediate solution of mine.)