Tiered commission formula

[dip694]

I am trying to create a spreadsheet to calculate the tentative commission for a real estate business. The commission is structured in a tiered way under two situation.
Scenario 1 - Under this scenario the brokerage president is the one that handled the sale. The split would as seen from the screenshot below: Scenario 1: Commission Tiers. This assumes that no other ASD (ie sales guy was involved).

Scenario 2 - Under this scenario, the sale was predominantly handled by the ASD (ie sales guy) while managed by the president. As such the commission tier/structure would be as outlined in Scenario 2.

My question, is what is the best formula to calculate the commission for the president, ASD, and JA (company)?

I very much appreciate all your help.

 

[dip694]

Thank you for your reply. I hope to this helps answer your question and makes sense. I have removed anything related to the ASD for now. The commission is paid as soon as the deal closes and not as a yearly bonus. This is why I have sorted on a per line item:

Commission Split.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	#	GROSS PROFIT	JA Total Cumulative Commission	Net to President	Arythmetic for Column D	Net to JA Brokerage	Arythmetic for Column F
2	1	$ 100,000	$ 100,000	$ 15,000	$100,000 x 15%	$ 85,000	$100,000 x 85%		SCENARIO 1: Commission Tiers (No ASD)
3	2	$ 125,000	$ 225,000	$ 18,750	$125,000 x 15%	$ 106,250	$125,000 x 85%		JA Total Cumulative Commission >=	President	JA
4	3	$ 75,000	$ 300,000	$ 11,250	$ 75,000 x 15%	$ 63,750.0	$ 75,000 x 85%		$ -	15.0%	85.0%
5	4	$ 100,000	$ 400,000	$ 25,000	$100,000 x 25%	$ 75,000.0	$100,000 x 75%		$ 250,001	25.0%	75.0%
6	5	$ 200,000	$ 600,000	$ 50,000	$200,000 x 25%	$ 150,000	$200,000 x 75%		$ 500,000	40.0%	60.0%
7	6	$ 150,000	$ 750,000	$ 60,000	$150,000 x 40%	$ 90,000	$150,000 x 60%
JA Brokerage (3)

Cell Formulas
Range	Formula
C2	C2	=B2
D2	D2	=B2*J4
C3:C7	C3	=C2+B3
D3	D3	=B3*J4
D4:D5	D4	=B4*J4
D6:D7	D6	=B6*J5
K4:K6	K4	=100%-J4
F2	F2	=B2*K4
F3	F3	=B3*K4
F4:F5	F4	=B4*K4
F6:F7	F6	=B6*K5

Named Ranges
Name	Refers To	Cells
'JA Brokerage (3)'!aPJA	='JA Brokerage (3)'!$K$4:$K$6	F2:F4
'JA Brokerage (3)'!aPP	='JA Brokerage (3)'!$J$4:$J$6	K4, D2:D4

 

[Dave Patton]

No You did not answer my question.
My question was to try to get to the first step. Your expected amount for the President with one amount and your brackets and rates.


You may have an unusual spin on your approach to the calculations. I do not know if that is intentional.

I can help if you want to see calculations with the usual tier style formulas.