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

[mhlester]

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
	A	B	C	D	E	F
1	Commission	Company Share of Commission Manually Calculated	Cumulative	Formula?	Category
2	65,000.00	26,000.00	26,000.00	SUMPRODUCT?	2	.4*65000
3	60,000.00	18,000.00	44,000.00	SUMPRODUCT?	1	.3*60000
4	75,000.00	30,000.00	74,000.00	SUMPRODUCT?	2	.4*75000
5	85,000.00	22,333.33	96,333.33	SUMPRODUCT?	1	16000/0.3=53333.33*.3 + 31666.67*.2
6	80,000.00	24,000.00	120,333.33	SUMPRODUCT?	2	.3*80000
7	100,000.00	29,889.09	150,222.42	SUMPRODUCT?	2	29666.67/.3=99998.9*.3 + 1111.1*.2
8
9
10	Tier Start	Tier End	Category 1	Category 2
11	0	90000	30%	40%
12	90000	150000	20%	30%
13	150000		20%	20%
Sheet1

Cell Formulas
Range	Formula
B2,B4	B2	=A2*$D$11
C2:C7	C2	=SUM($B$2:B2)
B3	B3	=A3*$C$11
B5	B5	=16000+$C$12*(A5-(16000/0.3))
B6	B6	=A6*$D$12
B7	B7	=29666.67/0.3*$D$12+$D$13*(A7-(29666.37/0.3))

 

[CA_Punit]

can you please explain how did you get commission of Rs 22333.33 in row(5).

 

[mhlester]

can you please explain how did you get commission of Rs 22333.33 in row(5).

Thank you for your question, CA_Punit.

I took the maximum amount for the first tier of 90,000 in Company share and subtracted the cumulative Company share of gross commissions to date (74,000), leaving 16,000 of Company share that is eligible for the 1st tier for Category 1 (30%). I know that 16,000 is 30% of 53,333.33. So that is how much of the 85,000 in gross commissions for the month has been used up (in tier 1) so far.

If I subtract 53,333.33 (gross commissions used up in tier 1) from gross commissions of 85,000 that leaves 31,666.67 in gross commissions eligible for the 2nd tier (90,000-150,000) in Category 1 (20%). 20% of 31,666.67 is 6,333.33.

16,000 (30% * 53,333.33)+ 6,333.33 (20% * 31,666.67) equals 22,333.33.

I hope this helps.

Best,
Michael

 

[Dave Patton]

I made some assumptions relating to your post.
Review the following for Category 2

Commission2020.xlsm
	A	B	C	D	E	F	G	H
1	Commission Cumulative	Company Share of Commission Manually Calculated	Cumulative Manual	Sumproduct either		Category
2	65000	26000	26000	26,000.00	26,000.00	2		39000
3	75000	4000	30000	30,000.00	30,000.00	2
4	80000	2000	32000	32,000.00	32,000.00	2
5	100000	7000	39000	39,000.00	39,000.00	2
6
1d

Cell Formulas
Range	Formula
B2:B5	B2	=C2-N(C1)
C2:C4	C2	=MIN(90000,A2)*$D$10+(A2>90000)*(A2-900000)*0.03
D2:D5	D2	=SUMPRODUCT(--(A2>$A$10:$A$12),A2-$A$10:$A$12,$D$10:$D$12-$D$9:$D$11)
E2:E5	E2	=SUMPRODUCT(--(A2>{0;90000;150000}),A2-{0;90000;150000},{0.4;-0.1;-0.1})
C5	C5	=MIN(90000,A5)*$D$10+(A5>90000)*(A5-90000)*D11
H2	H2	=90000*0.4+10000*0.3

 

[mhlester]

I made some assumptions relating to your post.
Review the following for Category 2

Commission2020.xlsm
	A	B	C	D	E	F	G	H
1	Commission Cumulative	Company Share of Commission Manually Calculated	Cumulative Manual	Sumproduct either		Category
2	65000	26000	26000	26,000.00	26,000.00	2		39000
3	75000	4000	30000	30,000.00	30,000.00	2
4	80000	2000	32000	32,000.00	32,000.00	2
5	100000	7000	39000	39,000.00	39,000.00	2
6
1d

Cell Formulas
Range	Formula
B2:B5	B2	=C2-N(C1)
C2:C4	C2	=MIN(90000,A2)*$D$10+(A2>90000)*(A2-900000)*0.03
D2:D5	D2	=SUMPRODUCT(--(A2>$A$10:$A$12),A2-$A$10:$A$12,$D$10:$D$12-$D$9:$D$11)
E2:E5	E2	=SUMPRODUCT(--(A2>{0;90000;150000}),A2-{0;90000;150000},{0.4;-0.1;-0.1})
C5	C5	=MIN(90000,A5)*$D$10+(A5>90000)*(A5-90000)*D11
H2	H2	=90000*0.4+10000*0.3

Hi, Dave,

Thank you for your reply.

It appears you are applying gross commissions to the tier, and not the Company share as is required by contract between company and salesperson. Also, the sales will alternate randomly between Cat 1 and Cat 2, which seems to complicate the formula beyond my expertise, and when that gets added in I hope it will work.

 

[Dave Patton]

Quote "It appears you are applying gross commissions to the tier, and not the Company share as is required by contract between company"

Please explain. What is the commission calculated on?
What is the share calculation.
Clarify with an example.

We can amend the formula to work with the different categories after we determine how the calculation works for one category.

 

[mhlester]

The Company share of commission is calculated on the gross commission, but the different levels are only reached once the Company share reaches the different levels.

For example, salesperson earns a gross commission of $100,000. This is all in the 1st tier, 0-$90,000, because the Company share for category 2 is 40%, or $40,000. The salesperson will not reach the 2nd tier, $90,000 to $150,000, until the Company share reaches $90,000.

The next month, the salesperson earns a gross commission of $200,000. This will be split into two tiers, because the Company share for category 2 in the 1st tier is 40%. If the Company share were computed entirely at the 1st tier percent, it would equal $80,000, bringing the cumulative Company share over the $90,000 threshold to $120,000 ($40,000 in month one, and $80,000 in month two.

So, only the first $50,000 in Company share will fall in the 1st tier. This $50,000 in Company share is equivalent to $125,000 in gross commissions ($50,000/.4), leaving $75,000 in commissions to be split in the 2nd tier, at 30% to the company, or $22,500. The total Company share on the $200,000 gross commission in month two is $50,000 + $22,500 = $72,500. The cumulative Company share is now $112,500 ($40,000 from month one, and $72,500 from month two).

The 3rd tier will be reached when the Company share reaches $150,000, another $37,500 in Company share ($150,000 - $112,500), which equates to $125,000 in gross commissions at the 30% 2nd tier rate ($37,500/.3).

 

[Dave Patton]

Hopefully someone else can follow the above.

Perhaps you can adapt the following.
I named the Brackets array aB and the Rate Differential array aR.

Commission2020.xlsm
	A	B	C	D	E	F	G
1	Relevant Share				Category	Commission 2 rates
2	65,000			26,000.00	2
3				0.00	1
4	75,000			30,000.00	2
5				0.00	1
6	80,000			32,000.00	2
7				0.00
8	100,000			39,000.00	2		39,000.00
9	200,000			0.00
1d

Cell Formulas
Range	Formula
G8	G8	=90000*0.4+10000*0.3
D2:D9	D2	=SUMPRODUCT(--(SUMIFS($A2:A2,$E2:E2,2)>aB),SUMIFS($A2:A2,$E2:E2,2)-aB,aR)

 

[mhlester]

Thank you, Dave.

I don't know how to apply your formula, and the formulas I've found and tried that work with tiered commissions, don't work when there are two categories.

Comparing the SUMPRODUCT formula with the manual calculations shows this formula fails as soon as it gets to the first threshold.

(Column C of the Tier Table is Category 1 and Column D is Category 2. I put 0's instead of descriptions, so the formula would work.)

SUMPRODUCT.xlsx
	A	B	C	D	E	F
1	Commission	Company Share of Commission Manually Calculated	Company Share Cumulative	Formula?	Category
2	65,000.00	26,000.00	26,000.00	26,000.00	2
3	60,000.00	18,000.00	44,000.00	18,000.00	1
4	75,000.00	30,000.00	74,000.00	30,000.00	2
5	85,000.00	22,333.33	96,333.33	25,500.00	1
6	80,000.00	24,000.00	120,333.33	32,000.00	2
7	100,000.00	29,889.09	150,222.42	43,000.00	2
8
9
10	Tier Start	Tier End	0	0
11	0	90000	30%	40%
12	90000	150000	20%	30%
13	150000		20%	20%
Sheet1

Cell Formulas
Range	Formula
B2,B4	B2	=A2*$D$11
C2:C7	C2	=SUM($B$2:B2)
D2:D7	D2	=IF(E2=1,SUMPRODUCT(--(A2>$A$11:$A$13),A2-$A$11:$A$13,$C$11:$C$13),SUMPRODUCT(--(A2>$A$11:$A$13),A2-$A$11:$A$13,$D$11:$D$13))
B3	B3	=A3*$C$11
B5	B5	=16000+$C$12*(A5-(16000/0.3))
B6	B6	=A6*$D$12
B7	B7	=29666.67/0.3*$D$12+$D$13*(A7-(29666.37/0.3))

 

[Dave Patton]

You are not using the correct syntax for the SumProduct formula; you need the rate differential.

If you will explain the how the formula is supposed to work, I can adapt the formula.
How would you explain the net calculation to your CEO or to a sales person?

Commission? Company Share? Relevant Share? Cumulative? Category 1? Category 2? Net result for Company? For Sales Person?

Commission2020.xlsm
	A	B	C	D	E	F	G	H
1	Relevant Share	Cumulative				Category
2	65,000	65,000			26,000.00	2
3	100,000	100,000			29,000.00	1
4	75,000	140,000			51,000.00	2
5	150,000	250,000			59,000.00	1
6	80,000	220,000			68,000.00	2
7	120,000	370,000			83,000.00	1
8	100,000	320,000			88,000.00	2	88,000.00
9	50,000	420,000			93,000.00	1	93,000.00	93,000.00
10
1d

Cell Formulas
Range	Formula
G8	G8	=A14*E13+(A15-A14)*E14+(B8-A15)*E15
B2:B9	B2	=IF(F2=1,SUMIFS($A$2:A2,$F$2:F2,1),IF(F2=2,SUMIFS($A$2:A2,$F$2:F2,2),0))
E2:E9	E2	=IF(F2=1,SUMPRODUCT(--(B2>aB),B2-aB,aR_1),IF(F2=2,SUMPRODUCT(--(B2>aB),B2-aB,aR_2),0))
G9	G9	=90000*D13+(150000-90000)*D14+(B9-A15)*D15
H9	H9	=90000*D13+(B9-90000)*D14