Using Solver or Goal Seek to find commission structure break even point

[nineseven]

Hi all, I am having issues trying to find the break even point of a commission structure trying to be implemented at my company. The detail are as follows:

Monthly Fixed Cost: $2165.00
Commission Structure:
Revenue $0-$2500 - 5% of Revenue earned in commissions
Revenue $2501-5000 - 10% of Revenue earned in commissions
Revenue $5000> - 15% of Revenue earned in commissions
Average Unit Cost $28.33

I think this is the minimum amount of information required to find the break even point. Our salesmen have a base monthly salary of $2165.00 and they receive a portion of the revenue they bring in to the company. So far, it is not capped. Of what they sell the average unit price is $28.33.

Been at it for most of the morning and cant find a way to structure this so that it would work in solver or goal seek.


Any help is appreciated. Thank you.

 

[Scott R]

Doesn't there need to be a Sales Price / Unit?

 

[nineseven]

unit cost is $28.33. What I am looking for is basically the break-even point where their fixed income and commissions amount matches the revenue they brought into the company from their sales.

 

[63falcondude]

What is the variable (i.e. What do you want to solve for)? Revenue?

 

[nineseven]

I want to solve for Revenue, yes. The point of Revenue (what they have to sell) that reaches their fixed expense and the commissions they have accumulated up until that revenue point.

 

[63falcondude]

Try this:

Excel 2010
	A	B
1	Monthly Fixed Cost	$2,165.00
2	Commission	$270.00
3	Avg Unit Cost	$28.33
4	Revenue	$2,700.00
5
6	Company Profit	$265.00
Sheet1

Cell Formulas
Range	Formula
B2	=B4*LOOKUP(B4,{0,2501,5001},{0.05,0.1,0.15})
B6	=B4-SUM(B1:B2)

Data > Solver > Set Objective: $B$6 > To: Value Of: 0 > By Changing Variable Cells: $B$4 > Solve

You should get an answer of $2,278.95.

 

[nineseven]

Their commission would would not be $270.00 is they brought in $2700.00 in revenue.

They would make 5% off the first $2500 = $125.00, and 10% from the remaining 200 = $20. Total= $145.00. Sorry if I didnt make this clear.

 

[63falcondude]

Their commission would would not be $270.00 is they brought in $2700.00 in revenue.

They would make 5% off the first $2500 = $125.00, and 10% from the remaining 200 = $20. Total= $145.00. Sorry if I didnt make this clear.

You are looking for tiered commissions then. These are more complicated and I am not too familiar with them but I will give it a shot. Try this:

Excel 2010
	A	B	C	D	E	F	G	H
1	Monthly Fixed Cost	$2,165.00			Revenue Low	Revenue High	Rate	Commission
2	Commission	$145.00			$0	$2,500	5%	$125.00
3	Avg Unit Cost	$28.33			$2,500	$5,000	10%	$20.00
4	Revenue	$2,700.00			$5,000	$99,999,999	15%	$0.00
5
6	Company Profit	$390.00
Sheet1

Cell Formulas
Range	Formula
B2	=SUM(H2:H4)
B6	=B4-SUM(B1:B2)
H2	=MAX(0,(MIN(F2,$B$4)-E2))*G2

 

[nineseven]

this seems like the trick. Thank you so much the help. Now i can apply this to my big sales guys.

 

[63falcondude]

this seems like the trick. Thank you so much the help. Now i can apply this to my big sales guys.

Great, glad to help!