Commission structure and calculation

[Jimmya006]

So I’ve browsed all the previous threads and most already have a plan and structure this is firstly where we need help but then to put it into practice please.

Our business currently pays our only sales member of staff as per the formula below;

As it stands we do circa £100k gross of sales at around 20% which equates to £17k gross a month.

Sales Person 1 gets 5% of the first £8000 and then 10% thereafter, so on currently levels around £1400 commission That’s for all sales.

Now here is the problem... We’ve employeed a 2nd sales person and dont want to disincentivse 1st sales person.

So Sales person 2 doesn’t need to be the same but we need to keep focus on margin, ensure sales person 1 doesn’t go backwards, keep them caring about every order not just their own and drive it forward. I’m struggling as to how to do it.

Please can anyone help us as best we can. Any help is greatly appreciated. Someone else must have been in the same boat as us! Driving me mad!

 

[JamesCanale]

So to keep the money that doesn't go to sales at least the same, and keeping Sales1 person from taking a hit, you have to drive more sales. Here's a sheet that might help you see how things would play out - but you'll still be guess if and how much sales might increase with a second salesperson.

MrExcelPlayground.xlsm
	C	D	E	F	G
1	Gross	$ 85,000.00		$ 17,000.00
2			Perc1	threshold	perc2
3	Sales1	$ 1,300.00	5%	$ 8,000.00	10%
4
5
6	Not to Sales	$ 15,700.00
7
8
9	Gross	$ 91,000.00		$ 18,200.00
10			Perc1	threshold	perc2
11	Sales1	$ 1,420.00	5%	$ 8,000.00	10%
12	Sales2	$ 1,056.00	4%	$ 10,000.00	8%
13
14	Not to Sales	$ 15,724.00
15
16
17	Sales1	$ 120.00
18	Change in Not to Sales	$ 24.00
19	Increase in total sales	$ 6,000.00
Sheet27

Cell Formulas
Range	Formula
F1,F9	F1	=0.2*D1
D3,D11	D3	=E3*MIN(F3,F1)+IF(F1>F3,G3*(F1-F3),0)
D6	D6	=F1-D3
D12	D12	=E12*MIN(F12,F9)+IF(F9>F12,G12*(F9-F12),0)
D14	D14	=F9-D11-D12
D17	D17	=D11-D3
D18	D18	=D14-D6
D19	D19	=D9-D1

 

[Jimmya006]

Fantastic, and how simple once its set out. I’ll try this across a few scenarios thank you kindly!

 

[Jimmya006]

So to keep the money that doesn't go to sales at least the same, and keeping Sales1 person from taking a hit, you have to drive more sales. Here's a sheet that might help you see how things would play out - but you'll still be guess if and how much sales might increase with a second salesperson.

MrExcelPlayground.xlsm
	C	D	E	F	G
1	Gross	$ 85,000.00		$ 17,000.00
2			Perc1	threshold	perc2
3	Sales1	$ 1,300.00	5%	$ 8,000.00	10%
4
5
6	Not to Sales	$ 15,700.00
7
8
9	Gross	$ 91,000.00		$ 18,200.00
10			Perc1	threshold	perc2
11	Sales1	$ 1,420.00	5%	$ 8,000.00	10%
12	Sales2	$ 1,056.00	4%	$ 10,000.00	8%
13
14	Not to Sales	$ 15,724.00
15
16
17	Sales1	$ 120.00
18	Change in Not to Sales	$ 24.00
19	Increase in total sales	$ 6,000.00
Sheet27

Cell Formulas
Range	Formula
F1,F9	F1	=0.2*D1
D3,D11	D3	=E3*MIN(F3,F1)+IF(F1>F3,G3*(F1-F3),0)
D6	D6	=F1-D3
D12	D12	=E12*MIN(F12,F9)+IF(F9>F12,G12*(F9-F12),0)
D14	D14	=F9-D11-D12
D17	D17	=D11-D3
D18	D18	=D14-D6
D19	D19	=D9-D1

One question now I’ve had time to think about it. How hard would it be to add in 2 more thresholds for the 2 sales guys say one at 15k and one at 20k with different % targets to incentivise them even more as the business grows? Sorry you got me thinking!

 

[JamesCanale]

Rearranged it:

MrExcelPlayground.xlsm
	C	D	E	F	G	H	I	J	K	L
1	Gross	$ 85,000.00		Sales1				Sales2
2	20%	$ 17,000.00		$ -	5%	1		$ -	4%	1
3	Sales1	$ 1,340.00		$ 8,000.00	10%	2		$ 10,000.00	8%	2
4				$ 15,000.00	12%	3		$ 17,000.00	10%	3
5				$ 20,000.00	15%	4		$ 22,000.00	12%	4
6	Not to Sales	$ 15,660.00
7
8
9	Gross	$ 110,000.00
10	20%	$ 22,000.00
11	Sales1	$ 2,000.00
12	Sales2	$ 1,460.00
13
14	Not to Sales	$ 18,540.00
15
16
17	Sales1	$ 660.00
18	Change in Not to Sales	$ 2,880.00
19	Increase in total sales	$ 25,000.00
Sheet27

Cell Formulas
Range	Formula
D2,D10	D2	=D1*C2
D3	D3	=CHOOSE(VLOOKUP(D2,F2:H5,3,TRUE),G2*D2,G2*F3+G3*(D2-F3),G2*F3+G3*(F4-F3)+G4*(D2-F4),G2*F3+G3*(F4-F3)+G4*(F5-F4)+G5*(D2-F5))
D6	D6	=D2-D3
D11	D11	=CHOOSE(VLOOKUP(D10,F2:H5,3,TRUE),G2*D10,G2*F3+G3*(D10-F3),G2*F3+G3*(F4-F3)+G4*(D10-F4),G2*F3+G3*(F4-F3)+G4*(F5-F4)+G5*(D10-F5))
D12	D12	=CHOOSE(VLOOKUP(D10,J2:L5,3,TRUE),K2*D10,K2*J3+K3*(D10-J3),K2*J3+K3*(J4-J3)+K4*(D10-J4),K2*J3+K3*(J4-J3)+K4*(J5-J4)+K5*(D10-J5))
D14	D14	=D10-D11-D12
D17	D17	=D11-D3
D18	D18	=D14-D6
D19	D19	=D9-D1

 

[Jimmya006]

Wow this is I think perfect. Thank you kindly! I’ll try not to use more of your time. Greatly appreciated.

 

[Jimmya006]

Hello back again! Sorry.... these things evolve!

So personally I preferred the first version

but we’ve now decided how about throwing in milestone bonuses for example when sales person 1 and 2 hit £120k sales we give them £250 spot bonus, £150k £750 bonus and £200k £1250 bonus.

Does that make sense? Sorry!

Also how and where can I pay to thank you for this incredible help.

 

[Dave Patton]

Some alternatives that you can consider.
Evaluate the results and approach and then select the formulas that you prefer.
You can name the ranges or values to make the formulas more concise and readable.

Commission2020.xlsm
	A	B	C	D	E	F	G	H	I	J
1			Gross	85,000.00		Sales1			Sales2
2			20%	17,000.00		0.00	5%		0.00	4%
3			Sales1	1,340.00	1,340.00	8,000.00	10%		10,000.00	8%
4						15,000.00	12%		17,000.00	10%
5						20,000.00	15%		22,000.00	12%
6			Net to Sales	15,660.00
7						0	0
8						120,000.00	250.00
9			Gross	250,000.00		150,000.00	750.00
10			20%	50,000.00		200,000.00	1,250.00
11			Sales1	7,450.00
12			Sales2	6,070.00
1b

Cell Formulas
Range	Formula
D2,D10	D2	=D1*C2
D3	D3	=SUMPRODUCT(--(D2>F2:F5),D2-F2:F5,G2:G5-G1:G4)
E3	E3	=SUMPRODUCT(--(D2>{0;8000;15000;20000}),D2-{0;8000;15000;20000},{0.05;0.05;0.02;0.03})
D6	D6	=D2-D3
D11	D11	=SUMPRODUCT(--(D10>F2:F5),D10-F2:F5,G2:G5-G1:G4)+LOOKUP(D9,F7:G10)
D12	D12	=SUMPRODUCT(--(D10>I2:I5),D10-I2:I5,J2:J5-J1:J4)+LOOKUP(D9,{0,0;120000,250;150000,750;200000,1250})

 

[JamesCanale]

You can pay it forward.

MrExcelPlayground.xlsm
	C	D	E	F	G	H	I	J	K	L
1	Gross	$ 85,000.00		Sales1				Sales2
2	20%	$ 17,000.00		$ -	5%	1		$ -	4%	1
3	Sales1	$ 1,340.00		$ 8,000.00	10%	2		$ 10,000.00	8%	2
4				$ 15,000.00	12%	3		$ 17,000.00	10%	3
5				$ 20,000.00	15%	4		$ 22,000.00	12%	4
6	Not to Sales	$ 15,660.00
7
8				Bonus Schedule	Spot Bonus	Running Total
9	Gross	$ 120,000.00		$0.00	$0.00	$0.00
10	20%	$ 24,000.00		$120,000.00	$250.00	$250.00
11	Sales1	$ 2,550.00		$150,000.00	$750.00	$1,000.00
12	Sales2	$ 1,950.00		$200,000.00	$1,250.00	$2,250.00
13
14	Not to Sales	$ 19,500.00
15
16
17	Sales1	$ 1,210.00
18	Change in Not to Sales	$ 3,840.00
19	Increase in total sales	$ 35,000.00
Sheet27

Cell Formulas
Range	Formula
D2,D10	D2	=D1*C2
D3	D3	=CHOOSE(VLOOKUP(D2,F2:H5,3,TRUE),G2*D2,G2*F3+G3*(D2-F3),G2*F3+G3*(F4-F3)+G4*(D2-F4),G2*F3+G3*(F4-F3)+G4*(F5-F4)+G5*(D2-F5))
D6	D6	=D2-D3
D11	D11	=CHOOSE(VLOOKUP(D10,F2:H5,3,TRUE),G2*D10,G2*F3+G3*(D10-F3),G2*F3+G3*(F4-F3)+G4*(D10-F4),G2*F3+G3*(F4-F3)+G4*(F5-F4)+G5*(D10-F5))+VLOOKUP(D9,$F$9:$H$12,3,TRUE)
D12	D12	=CHOOSE(VLOOKUP(D10,J2:L5,3,TRUE),K2*D10,K2*J3+K3*(D10-J3),K2*J3+K3*(J4-J3)+K4*(D10-J4),K2*J3+K3*(J4-J3)+K4*(J5-J4)+K5*(D10-J5))+VLOOKUP(D9,$F$9:$H$12,3,TRUE)
H9	H9	=G9
H10:H12	H10	=G10+H9
D14	D14	=D10-D11-D12
D17	D17	=D11-D3
D18	D18	=D14-D6
D19	D19	=D9-D1

 

[Jimmya006]

Thank you let me try this out. And greatly appreciated!