what price will achieve target percentage

[gibbsyns3]

wonder if anybody here would be kind enough to help me with this headscratcher.

i am working on a gross profit calculator for a restaurant.

i want to include 3 methods of viewing the affect on gp.

increase to (new price) method, and increase by (fixed amount) method are straightforward enough, by the reverse calculation needed for achieve given GP % is frying my head a bit

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]cost price[/TD]
[TD]sale price[/TD]
[TD]gross profit €[/TD]
[TD]gross profit %[/TD]
[TD]to achieve gross profit of X%[/TD]
[TD]the new sale price needs to be P[/TD]
[/TR]
[TR]
[TD]100€[/TD]
[TD]200€[/TD]
[TD]100€[/TD]
[TD]100%[/TD]
[TD]200%[/TD]
[TD]Formula Required[/TD]
[/TR]
[TR]
[TD]4.75€[/TD]
[TD]18.95€[/TD]
[TD]14.20€[/TD]
[TD]299%[/TD]
[TD]350%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


any help gratefully received - new to the forum, but hope to give as much as i take.

 

[gibbsyns3]

Ha!

Laying it out in that hugely simplified format I figured it quite easily

(E/D) x B

well thanks anyway forum - still fixed it as a result of being here

 

[jtakw]

Hi,

Not sure I understand your question and/or your own solution (E/D) x B, but if I do understand correctly, wouldn't it be this:

Book1
	A	B	C	D	E	F
1	cost price	sale price	gross profit €	gross profit %	to achieve gross profit of X%	the new sale price needs to be P
2	100 €	200 €	100 €	100%	200%	300
3	4.75 €	18.95 €	14.20 €	299%	350%	21.375
Sheet126

Cell Formulas
Range	Formula
F2	=A2*(1+E2)

 

[gibbsyns3]

Thanks jtakw. Yours is certainly the more logical and elegant solution, and yes you have understood it - much appreciated.

 

[jtakw]

You're welcome, glad it worked for you.

Welcome to the forum.

 

[gibbsyns3]

You're welcome, glad it worked for you.

Welcome to the forum.

Thank you. Could you point me towards a post that will show me how to insert/use the mini spreadsheet in your post?

 

[shg]

Profit is measured as a percentage of sell price, not cost, so it must be < 100%.

 

[jtakw]

Thank you. Could you point me towards a post that will show me how to insert/use the mini spreadsheet in your post?

I use MrExcel HTML Maker, but there are a few options, have a look:

Here: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

and Here: https://www.mrexcel.com/forum/about-board/508133-attachments.html