Formula to calculate how many items I need to sell to break even, make 10% etc

[tonywatsonhelp]

Hi Everyone,
Thought this would be easy but not so much please help if you can
I have cacualated the cost of each item we sell and have two cost

Cell B17 is the set up cost to produce the products for example £50,000
Cell G17 is the individual cost to make each Item for example £15
and in cell D17 I have the retail price I sell the item for e.g. £49.50

Now what I would like is a formula or formulas that caculate the number of units I need to sell to break even, make 10% Margin etc.
The idea would be in Cell J17 if I put the desired Margin, it tellls me how many Items i need to sell

I hope thats clear,
please help if you can

Thanks
Tony

 

[JamesCanale]

MrExcelPlayground15.xlsx
	B	C	D	E	F	G	H	I	J	K
17	$50,000.00	Setup	$49.50	Sale Price		$15.00	Unit Cost		10%	Margin
18
19									1666.667	Units to break even plus margin
Sheet17

Cell Formulas
Range	Formula
J19	J19	=B17/(D17/(1+J17)-G17)

 

[Legacy 143009]

If I am not wrong, to be even, your formula should be:

=CEILING(B17/(D17-G17),1)

In your case it is 1450 pcs. It's total cost will be (15*1450)+50000 = 71750. Selling at least 1450 pcs. will bring you a revenue of 71775 which is slightly higher than what you need to compensate your costs. It makes sense to me.

On the contrary @JamesCanale 's suggestion, in my opinion, the right formula should be this to calculate with margin:

=CEILING((G17*(B17/(D17-G17))+B17)*(1+J17)/D17,1)

Assuming J17 is 0.1

 

[Legacy 143009]

On the contrary @JamesCanale 's suggestion, in my opinion, the right formula should be this to calculate with margin:

=CEILING((G17*(B17/(D17-G17))+B17)*(1+J17)/D17,1)

Assuming J17 is 0.1

@JamesCanale 's margin formula is right

Also, you have to specify if you want a Mark-up Margin or Gross Up margin?

 

[tonywatsonhelp]

Thanks guys,
I'm totally confused, but I have 3 formulas to to try and I know what I want it to caculate so I think I'llbe able to work out what one gives me the best results,
Really appreciate your help
Thanks
Tony

 

[Legacy 143009]

To be even without margin, use my first formula.

To get 10% margin, use @JamesCanale 's formula.