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,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Formulas​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

$0​

[/td][td]

1.625%​

[/td][td="bgcolor:#CCFFCC"]

1.625%​

[/td][td][/td][td="bgcolor:#CCFFCC"]C2: =B2-N(B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

$1,000,000​

[/td][td]

1.750%​

[/td][td="bgcolor:#CCFFCC"]

0.125%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

$3,000,000​

[/td][td]

1.875%​

[/td][td="bgcolor:#CCFFCC"]

0.125%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

$4,000,000​

[/td][td]

2.000%​

[/td][td="bgcolor:#CCFFCC"]

0.125%​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Commission​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

$0​

[/td][td="bgcolor:#CCFFFF"]

$0​

[/td][td][/td][td][/td][td="bgcolor:#CCFFFF"]B8: =SUMPRODUCT((A8 > $A$2:$A$5) * (A8 - $A$2:$A$5) * $C$2:$C$5)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

$1,000,000​

[/td][td="bgcolor:#CCFFFF"]

$16,250​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

$3,000,000​

[/td][td="bgcolor:#CCFFFF"]

$51,250​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

$4,000,000​

[/td][td="bgcolor:#CCFFFF"]

$70,000​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

$50,000,000​

[/td][td="bgcolor:#CCFFFF"]

$990,000​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#F3F3F3"]

To Earn​

[/td][td="bgcolor:#F3F3F3"]

Must Sell​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

$10,000​

[/td][td="bgcolor:#FFFF99"]

$615,385​

[/td][td][/td][td][/td][td="bgcolor:#FFFF99"]B15: =PERCENTILE($A$8:$A$12, PERCENTRANK($B$8:$B$12, A15, 12))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

$20,000​

[/td][td="bgcolor:#FFFF99"]

$1,214,286​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

$30,000​

[/td][td="bgcolor:#FFFF99"]

$1,785,714​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

$40,000​

[/td][td="bgcolor:#FFFF99"]

$2,357,143​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

$50,000​

[/td][td="bgcolor:#FFFF99"]

$2,928,571​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

$60,000​

[/td][td="bgcolor:#FFFF99"]

$3,466,667​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

$70,000​

[/td][td="bgcolor:#FFFF99"]

$4,000,000​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

$80,000​

[/td][td="bgcolor:#FFFF99"]

$4,500,000​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

23​

[/td][td]

$90,000​

[/td][td="bgcolor:#FFFF99"]

$5,000,000​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

24​

[/td][td]

$100,000​

[/td][td="bgcolor:#FFFF99"]

$5,500,000​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]