SUMPRODUCT, Tiered Commissions, One for Old Customers, One for New Customers

M

mhlester

New Member
Joined
May 24, 2021
Messages
15
Office Version
  1. 365
Platform
  1. Windows
I am sorry if this question has been answered in the thread, Multiple Tiered Calculations, or another thread, but they are so complex, it is difficult for me to follow to know if they will help me.

It seems I have a comparatively simple calculation, but it's too hard for me with my limited knowledge of advanced Excel functions.

The Company keeps a percentage of each sale depending on the category (pre-existing customer or new customer), and depending on the Company's cumulative share of commissions year-to-date.

I have manually calculated what I think are the correct amounts in the mini-table, based on the tiered commission structure shown in the mini-sheet and the two categories, 1 and 2.

But I think there might be a SUMPRODUCT, or other EXCEL function, to calculate the correct commission for each sale, so I have created a column where the formula might go.

Thank you for taking a look and hopefully providing a solution.

Best wishes,
Michael

SUMPRODUCT.xlsx
ABCDEF
1 CommissionCompany Share of Commission Manually CalculatedCumulativeFormula?Category
265,000.0026,000.0026,000.00 SUMPRODUCT? 2.4*65000
360,000.0018,000.0044,000.00 SUMPRODUCT? 1.3*60000
475,000.0030,000.0074,000.00 SUMPRODUCT? 2.4*75000
585,000.0022,333.3396,333.33 SUMPRODUCT? 1 16000/0.3=53333.33*.3 + 31666.67*.2
680,000.0024,000.00120,333.33 SUMPRODUCT? 2.3*80000
7100,000.0029,889.09150,222.42 SUMPRODUCT? 229666.67/.3=99998.9*.3 + 1111.1*.2
8
9
10Tier StartTier EndCategory 1Category 2
1109000030%40%
129000015000020%30%
1315000020%20%
Sheet1
Cell Formulas
RangeFormula
B2,B4B2=A2*$D$11
C2:C7C2=SUM($B$2:B2)
B3B3=A3*$C$11
B5B5=16000+$C$12*(A5-(16000/0.3))
B6B6=A6*$D$12
B7B7=29666.67/0.3*$D$12+$D$13*(A7-(29666.37/0.3))
 
Sort by date Sort by votes
Thank you, Dave.

I think the confusion between us is over where the Gross Commission comes from in the first place.

The salesperson finds a customer a job. The customer pays the salesperson a commission. The salesperson gives the company a share of the commission for providing back-office and other services to the salesperson.

So, Customer X pays salesperson $100,000 for finding him a job as a CEO at Lockheed. The salesperson pays the Company he works for, Company A, 40%, 30%, or 20% of the commission, depending on whether Customer X was an old or new customer, and also depending on how much commission the salesperson has already paid Company A for the year to date (that is, Company A’s cumulative share of the commissions for the year to date).

But if we are in the bar drinking beer, and I am trying to hire you, I would explain it like this...

Me: I want to hire you away from Company B to work for my Company A. I will keep 30% of any commissions you earn on your existing customers (you will keep 70%) and I will keep 40% of the commissions you earn on new customers you sign while you are working for me (you will keep 60%).

You: But if I bring in a lot of commissions, I should get paid more.

Me: Okay, fair enough. When my share of the total commissions on both old and new customers combined reaches $90,000, I will drop my share of the commission from 40% to 30% on new customers, and from 30% to 20% on old customers. When my share reaches $150,000, I will drop my share of the commission to 20% across the board regardless of when you got the customer.

You: So, if I only make sales to my existing customers, I only have to give you 30% of what I earn until your share is $90,000? That means I have to bring in $300,000 in commissions before your share hits $90,000 and then I only have to give you 20%, right?

Me: Right!

You: But why do you get to keep 40% on new customers? Shouldn't you get a smaller percentage for new customers than for old customers?

Me: No, because it is our reputation and our advertising that helps you get the new customers... we have to get compensated for that.

You: Ah! Got it. So, for new customers, you keep a larger percentage of the commission to start, but you will reach the 90,000 and 150,000 levels sooner, so my percentage goes up and your percentage goes down faster with new customers, right?

Me: Yep! That's the incentive to bring in new business even though we get a bigger percentage in the beginning.

You: Deal! Bartender! Two more beers!

Definitions
  • Gross Commission: The total amount Customer X pays the outside salesperson for a sale
  • Company Share of Commission: The amount of Gross Commission from Customer X that the salesperson must give to the Company to compensate the Company for providing back-office services
    • a sliding scale of percentages decreasing from 40% to 20%, depending on the Company’s cumulative share of Gross Commissions
    • the Company Share decreases when its share of Gross Commissions reaches certain levels (90,000 and 150,000, in this case)
    • the salesperson keeps the rest of the commission
  • Company Cumulative Share of Commission: A running total of the Company’s Share of Commissions in chronological order of sale, regardless of the category of sale
  • Relevant Share of Commission: Since the Company Share of Commission changes when its Cumulative Share of Commission reaches certain levels, the Company’s Cumulative Share of Commissions is the Relevant share for determining the applicable percentage for each instance of Gross Commission
  • Category 1: New customers (the Company Share percentage is higher) 40%, 30% 20%
  • Category 2: Old customers (the Company Share percentage is lower) 30%, 20%, 20%
  • Net Result to Company: The company keeps a share of every commission the salesperson earns from its customers
  • Net Result to Salesperson: the salesperson keeps a percentage of every commission it earns, and the company gets the rest
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Since your calculation is based on the cumulative amount but at two distinct rate structures, a single formula is probably impossible. The results will vary by the timing and priority of categories.

I included another recap for your interest.

Commission2020.xlsm
ABCDEFGH
1Relevant ShareCumulativeCombinedCategory 1Category 2Category
265,00065,00026,000.0019,500.0026,000.0026,500.006,500.00
3100,000165,00042,000.0042,000.0057,000.00115,000.0015,000.00
475,000240,00072,000.0057,000.0072,000.002
5150,000390,00087,000.0087,000.00102,000.001
680,000470,000118,000.00103,000.00118,000.002
7120,000590,000127,000.00127,000.00142,000.001
8100,000690,000162,000.00147,000.00162,000.002
950,000740,000157,000.00157,000.00172,000.001
10
11Tier StartCategory 1Category 2
12
1300.30.4
14900000.20.3
151500000.20.2
1d
Cell Formulas
RangeFormula
B2:B9B2=A2+N(B1)
C2:C9C2=SUMPRODUCT(--(B2>aB),B2-aB,CHOOSE(MATCH(F2,{1,2},0),aR_1,aR_2))
D2:D9D2=SUMPRODUCT(--(B2>aB),B2-aB,aR_1)
E2:E9E2=SUMPRODUCT(--(B2>aB),B2-aB,aR_2)
G2:G3G2=E2-D2
H2H2=A2*(E13-D13)
H3H3=150000*0.1
 
Upvote 0
Does the following work?

Commission2020.xlsm
ABCDEFG
1Relevant ShareCumulativeCumulativeCategory 1Category
2
365,00065,00026,000.0026,000.0019,500.0026,500.00
450,000115,00032,000.0038,500.0032,000.0010.00
575,000190,00050,500.0057,000.0047,000.0023,500.00
6150,000340,00077,000.0087,000.0077,000.001
780,000420,00093,000.00103,000.0093,000.002
8120,000540,000117,000.00127,000.00117,000.001
9100,000640,000137,000.00147,000.00137,000.002
1050,000690,000147,000.00157,000.00147,000.001
11
12Tier StartCategory 1Category 2
13
1400.30.4
1590,0000.20.3
16150,0000.20.2
1d
Cell Formulas
RangeFormula
B3B3=A3+N(B1)
C3:C10C3=SUMPRODUCT(--(B3>aB),B3-aB,aR_1)+(F3=2)*MAX(0,MIN($A$16-B2,A3))*0.1
D3:D10D3=E3+SUM($G$3:G3)
E3:E10E3=SUMPRODUCT(--(B3>aB),B3-aB,aR_1)
B4:B10B4=A4+N(B3)
G3:G4G3=MIN(150000,B3)*0.1*(F3=2)
G5G5=MIN(150000-B4,A5)*0.1*(F5=2)
 
Upvote 0
Hi, Dave,

I don't think so...unless mine is wrong (see below). If mine is right, it is unwieldy, but it has the distinct advantage that it actually works. I only came up with the idea recently to use separate columns to break the formula down into manageable bites. This particular salesperson will hit the 150,000 mark in LLC share this pay period, so I had to face the music and figure this out. I spent 12 hours Sunday working on it and went to sleep exhausted, but happy. It just seems there should be an easier way. What if the next salesperson has more tiers and more categories! One of the salespersons has 8 tiers! but thankfully only one category. Can you imagine how difficult it would be to use my technique for eight tiers and five categories?! Still, it can be done this way. And maybe there is no other way. I figure, if there is another way, MrExcel might be able to figure it out, but I have no chance... I don't know the functions that well. I have to find the answer on the Internet if it exists and try it out. But I couldn't find the answer to this one. Which I think is weird, because it doesn't seem like an unusual situation.

I have created a series of formulas and put them in separate columns to keep them manageable... one for each possibility. That is, a formula for Cat 1 for (a) under 90,000; (b) straddling 90,000; (c) between 90,000-150,000; (d) straddling 150,000, and (e) over 150,000. I did the same for Cat 2. If my formulas are all correct, there can only be one amount in each row, because all the formulas are mutually exclusive.

Then, I created a total column at the end and use that to calculate the LLC share, which I replicate by referring to the total cells in the list of commissions.

Here is what it looks like (in three sections to keep the spreadsheet compact... at the top, the list of commissions and the Company Share; in the middle, the formulas; and at the end, the tiers).

Note, the amount in Row 21 is an even 20% even though it straddles 150,000. That is because it is Cat 1, which is 20% on both sides of 150,000. I tested this by changing Row 21 to Cat 2 and it seems to work. Then, I changed it back to Cat 1, like your example.

SUMPRODUCT.xlsx
ABCDEFGHIJK
1CommissionLLC ShareLLC CumulativeAdam ShareAdam CumulativeCategory
2-
365,000.0026,000.0026,000.0039,000.0039,000.002
4100,000.0030,000.0056,000.0070,000.00109,000.001
575,000.0030,000.0086,000.0045,000.00154,000.002
6150,000.0031,333.33117,333.33118,666.67272,666.671
780,000.0024,000.00141,333.3356,000.00328,666.672
8120,000.0024,000.00165,333.3396,000.00424,666.671
9100,000.0020,000.00185,333.3380,000.00504,666.672
1050,000.0010,000.00195,333.3340,000.00544,666.671
11740,000.00195,333.33544,666.67
12
13
142 Below 902 Above 1501 Below 901 Above 1502 Straddle 901 Straddle 902 Straddle 1501 Straddle 1502 Between 90 & 1501 Between 90 & 150Totals
15-
1626,000.00---------26,000.00
17--30,000.00-------30,000.00
1830,000.00---------30,000.00
19-----31,333.33----31,333.33
20--------24,000.00-24,000.00
21-------24,000.00--24,000.00
22-20,000.00--------20,000.00
23---10,000.00------10,000.00
24-----------
25
26
27Tier StartTier EndCategory 1Category 2
2809000030%40%
299000015000020%30%
3015000020%20%
Sheet2
Cell Formulas
RangeFormula
B3:B10B3=K16
C3:C10C3=SUM($B$3:B3)
E3:E10E3=SUM($D$3:D3)
A11:B11,D11A11=SUM(A2:A10)
D3:D10D3=A3-B3
K15K15=SUM(A15:H15)
A16:A24A16=IF(F3<>2,0,IF(C3<$E$28,A3*$G$28,0))
B16:B24B16=IF(F3<>2,0,IF(C2>$E$29,A3*$G$30,0))
C16:C24C16=IF(F3<>1,0,IF(C3<$E$28,A3*$F$28,0))
D16:D24D16=IF(F3<>1,0,IF(C2>$E$29,A3*$F$30,0))
E16:E24E16=IF(F3<>2,0,IF(AND(C2<$E$28,C2+A3*$G$28>$E$28,C2+A3*$G$28<$E$29),($E$28-C2)+(A3-(($E$28-C2)/$G$28))*$G$29,0))
F16:F24F16=IF(F3<>1,0,IF(AND(C2<$E$28,C2+A3*$F$28>$E$28,C2+A3*$F$28<$E$29),($E$28-C2)+(A3-(($E$28-C2)/$F$28))*$F$29,0))
G16:G24G16=IF(F3<>2,0,IF(AND(C2<$E$29,C2+A3*$G$29>$E$29,C2+A3*$G$29>$E$29),($E$29-C2)+(A3-(($E$29-C2)/$G$29))*$G$30,0))
H16:H24H16=IF(F3<>1,0,IF(AND(C2<$E$29,C2+A3*$F$29>$E$29,C2+A3*$F$29>$E$30),($E$29-C2)+(A3-(($E$29-C2)/$F$29))*$F$30,0))
I16:I24I16=IF(F3<>2,0,IF(AND(C2>$E$28,C2+(A3*$G$29)<$E$29),A3*$G$29,0))
J16:J24J16=IF(F3<>1,0,IF(AND(C2>$E$28,C2+(A3*$F$29)<$E$29),A3*$F$29,0))
K16:K24K16=SUM(A16:J16)
 
Upvote 0
You advised that the calculations are based on the cumulative amounts.
Are your calculations based on the cumulative amounts?

With the revised amounts, I revised the spreadsheet.

fyi see attached.

Commission2020.xlsm
ABCDEFGH
1Relevant ShareCumulativeCumulativeCategory 1Category
2
365,00065,00026,000.0026,000.0019,500.0026,500.00
4100,000165,00042,000.0048,500.0042,000.00142,000.00
575,000240,00057,000.0063,500.0057,000.002
6150,000390,00087,000.0093,500.0087,000.001
780,000470,000103,000.00109,500.00103,000.002
8120,000590,000127,000.00133,500.00127,000.001
9100,000690,000147,000.00153,500.00147,000.002
1050,000740,000157,000.00163,500.00157,000.001
11
12Tier StartCategory 1Category 2
13
1400.30.4
1590,0000.20.3
16150,0000.20.2
1d
Cell Formulas
RangeFormula
B3B3=A3+N(B1)
C3:C10C3=SUMPRODUCT(--(B3>aB),B3-aB,aR)+(F3=2)*MAX(0,MIN($A$16-B2,A3))*0.1
D3,D5:D10D3=E3+SUM($G$3:G3)
E3:E10E3=SUMPRODUCT(--(B3>aB),B3-aB,aR)
B4:B10B4=A4+N(B3)
D4D4=C4+SUM($G$3:G3)
G3G3=MIN(150000,B3)*0.1*(F3=2)
H4H4=90000*0.3+75000*0.2
 
Upvote 0
Dave Patton said:
You advised that the calculations are based on the cumulative amounts.
Are your calculations based on the cumulative amounts?

With the revised amounts, I revised the spreadsheet.

fyi see attached.

Commission2020.xlsm
ABCDEFGH
1Relevant ShareCumulativeCumulativeCategory 1Category
2
365,00065,00026,000.0026,000.0019,500.0026,500.00
4100,000165,00042,000.0048,500.0042,000.00142,000.00
575,000240,00057,000.0063,500.0057,000.002
6150,000390,00087,000.0093,500.0087,000.001
780,000470,000103,000.00109,500.00103,000.002
8120,000590,000127,000.00133,500.00127,000.001
9100,000690,000147,000.00153,500.00147,000.002
1050,000740,000157,000.00163,500.00157,000.001
11
12Tier StartCategory 1Category 2
13
1400.30.4
1590,0000.20.3
16150,0000.20.2
1d
Cell Formulas
RangeFormula
B3B3=A3+N(B1)
C3:C10C3=SUMPRODUCT(--(B3>aB),B3-aB,aR)+(F3=2)*MAX(0,MIN($A$16-B2,A3))*0.1
D3,D5:D10D3=E3+SUM($G$3:G3)
E3:E10E3=SUMPRODUCT(--(B3>aB),B3-aB,aR)
B4:B10B4=A4+N(B3)
D4D4=C4+SUM($G$3:G3)
G3G3=MIN(150000,B3)*0.1*(F3=2)
H4H4=90000*0.3+75000*0.2
Click to expand...
 
Upvote 0
Hi, Dave,

Sorry, if I was unclear. It's not so easy to explain. The commission calculation is based on each individual gross commission, not the accumulated commission, but the percentage to be applied to each individual commission is determined by reference to the accumulated company share of commissions using the tier chart and the category of each individual commission.

If I have misled you, I apologize. I think the above is a clearer explanation of what is supposed to happen.

I think my spreadsheet has the correct Company Share for each individual commission, so if your Company share is different than mine, I don't think the formula is there yet.

Thanks,
Michael
 
Upvote 0
I have doubt with the 6 Row. I Suppose your sought answer to row 6 is 24000/- which is incorrect considering Category 2.
Book11.xlsx
ABCDEF
1Total SalesCategoryCumulative Sales as per current SlabSlab0Net Commission
265,000.002.0065,000.001.0026,000.0026,000.00
360,000.001.001,46,666.671.0044,000.0018,000.00
475,000.002.001,85,000.001.0074,000.0030,000.00
585,000.001.003,31,666.672.0096,333.3322,333.33
680,000.002.003,26,111.112.001,27,833.3331,500.00
71,00,000.002.004,51,111.112.001,65,333.3337,500.00
8
9
10SlabCategory 1Category 2Slab SalesCumulative
1110.30.4300000300000
12900010.20.3300000600000
131500010.20.237500004350000
149000010.20.2
Sheet1
Cell Formulas
RangeFormula
C2:C7C2=SUM(IF(($B$12:$B$14-$B$11:$B$13)+IF((E1-($B$12:$B$14-1))>0,0,(E1-($B$12:$B$14-1)))<0,0,($B$12:$B$14-$B$11:$B$13)+IF((E1-($B$12:$B$14-1))>0,0,(E1-($B$12:$B$14-1))))/CHOOSE(B2,$C$11:$C$13,$D$11:$D$13))+A2
D2:D7D2=MATCH(C2,{0;300000;600000;4350000},1)
E2:E7E2=CHOOSE(D2,C2*CHOOSE(B2,$C$11,$D$11),(C2-300000)*CHOOSE(B2,$C$12,$D$12)+300000*CHOOSE(B2,$C$11,$D$11),(C2-600000)*CHOOSE(B2,$C$13,$D$13)+300000*CHOOSE(B2,$C$12,$D$12)+300000*CHOOSE(B2,$C$11,$D$11))
F2:F7F2=E2-SUM($F$1:F1)
E11:E13E11=(B12:B14-B11:B13)/(C11:C13)
F11:F13F11=SUBTOTAL(9,OFFSET(E11#,,,{1;2;3}))
Dynamic array formulas.
 
Upvote 0

mhlester​

Your post #14 may include circular references. I could not import it.
The amounts that I calculated appear to be correct.
If we assume for a moment that all the sales are either Category 1 or Category 2,
the upper and lower limits are as shown below.

If we do a manual calculation, the amount agrees to the amount that my formula shows.
Are you using the tiers and categories that you cited?

Commission2020.xlsm
ABCDEFGH
26Assume 100%FIFO
27TotalCategory 1Category 265,000.0026,000.00Category 2Tier 1
28740,000.00157,000.00172,000.0025,000.007,500.00Category 1Tier 1
29MinimumMaximum60,000.0012,000.00Category 1Tier 2
3015,000.003,000.00Category 1Tier 3
31FIFO per formula or Manual163,500.0025,000.005,000.00Category 2Tier 3
3250,000.0010,000.00Category 2Tier 3
33240,000.0063,500.00
34
3565,000.0026,000.00Tier 1
3625,000.007,500.00Tier 1
3760,000.0012,000.00Tier 2
38590,000.00118,000.00Tier 3
39740,000.00163,500.00
1d
 
Upvote 0
Hi, Dave,

Thank you for sticking with this...

Many of my spreadsheets contain circular references, and they work with automatic recalculation, even if they are sometimes a little buggie (sometimes, I have to overwrite a formula with a "1" and then hit undo to get rid of the errors that pop up). I haven't checked to see it this spreadsheet has circular references, but it does work for me either way.

Here is a manual calculation that seems to prove out my Row 6.

SUMPRODUCT.xlsx
MN
1Manual calculation of Row 6 (1st tier maximum)90,000.00
2Current Period Gross Commission150,000.00
3Cumulative Company share prior to Current Period Gross Commission86,000.00
4Category 1 (30% until Company Cumulative Share reaches $90,000, then 20%)1
5Percent if less than $90,00030%
6Percent if greater than $90,000 and less than $150,00020%
7Percent split between $90,000 and $150,000? Yes
8Amount of Current Period Gross Commission less than $90,000 (Company Share)4,000.00
9Amount of Gross Commission Equivalent to Company Share at less than $90,000 tier13,333.33
10Amount of Gross Commission Remaining to be split at $90,000 to $150,000 tier136,666.67
11Company Share of Remaining Gross Commission27,333.33
12Total Company Share of Current Period Gross Commission31,333.33
Sheet2
Cell Formulas
RangeFormula
N7N7=IF(N3+N2*0.3>90000, "Yes","no")
N8N8=N1-N3
N9N9=N8/N5
N10N10=N2-N9
N11N11=N10*N6
N12N12=N8+N11
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Will a SumProduct work with commission tiers
Replies
2
Views
3K
Dave Patton
D

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top