Backwards - calculate required sales volume from desired commission amount on tiered commission structure

[karenlp97]

Hi there, sorry if this has been answered somewhere else but the closest thread I could find was this one and it's not quite what I need. https://www.mrexcel.com/forum/excel...ired-commission-amount-find-sales-volume.html

Real estate agents for a brokerage are on a tiered commission structure as follows:

Sales Volume Pct of Gross Commission to Agent
$0-999,999.00 65% of gross commission

$1,000,000-2,999,999.00 70% of gross commission
$3,000,000-3,999,999.00 75% of gross commission
$4,000,000-4,999,999.00 80% of gross commission​

Where "gross commission" is assumed to be 2.5% of the sale price. For example on a 500K property, gross commission is 12,500 to the brokerage, of which 65% (8,125) goes to the agent, but obviously the formula changes once the agent sells 1M.

I've gone round and round and I can't quite seem to wrap my head around this - is it even possible to create a worksheet where an agent can enter their desired earnings (after expenses) and calculate the required sales volume based on the above structure?

Thanks in advance!

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G
1	Gross Sales	$500,000			Bracket	Rate	Difference
2	Rate	2.50%			0	65%	65%
3	Commission	12,500.00			1,000,000	70%	5%
4	Share	65.00%			3,000,000	75%	5%
5	Agent Commission	$8,125.00			4,000,000	80%	5%
6
1c

Cell Formulas
Range	Formula
G2	=F2-N(F1)
B3	=B1*B2
B4	=SUMPRODUCT(--(B1>E2:E5),B1-E2:E5,G2:G5)/B1
B5	=B3*B4

Use Goal Seek to determine Gross Sales to yield required amount say $100,000.
For B5
Set amount to 100, 000.
Goal Seek yields 5,500,000.
Commission 137,500
Share 72.73%
Yields 100,000.

 

[shg]

Sans Goal Seek,

	A​	B​	C​	D​	E​
1​	Sales​	Rate​	Delta​	​	Formulas​
2​	$0​	1.625%​	1.625%​		C2: =B2-N(B1)
3​	$1,000,000​	1.750%​	0.125%​
4​	$3,000,000​	1.875%​	0.125%​
5​	$4,000,000​	2.000%​	0.125%​
6​
7​	Sales​	Commission​
8​	$0​	$0​			B8: =SUMPRODUCT((A8 > $A$2:$A$5) * (A8 - $A$2:$A$5) * $C$2:$C$5)
9​	$1,000,000​	$16,250​
10​	$3,000,000​	$51,250​
11​	$4,000,000​	$70,000​
12​	$50,000,000​	$990,000​
13​
14​	To Earn​	Must Sell​
15​	$10,000​	$615,385​			B15: =PERCENTILE($A$8:$A$12, PERCENTRANK($B$8:$B$12, A15, 12))
16​	$20,000​	$1,214,286​
17​	$30,000​	$1,785,714​
18​	$40,000​	$2,357,143​
19​	$50,000​	$2,928,571​
20​	$60,000​	$3,466,667​
21​	$70,000​	$4,000,000​
22​	$80,000​	$4,500,000​
23​	$90,000​	$5,000,000​
24​	$100,000​	$5,500,000​