Hello everyone!
i want to create a formula that can work out how many pours of a 700ml
bottle you can get at 25ml a shot, at £2.00 each, cost price of the 700ml
bottle is £14.31.
so basically i want to have a sheet were i can see bottle quantity, bottle
volume, cost price, selling price, profit per bottle, then show a profit
margin on that particular product.

i have already created such a formula but when its in place it says my
profit margin on that product is 2084%
this is the formula; =IF(L22=0,0,(L22/J3))

http://www.filedropper.com/example_3

thanks, Chris

Chris,

Your profit rate is calculated like this:

=((700/25)*2-14.31)/14.31

(Or about 2.91, which when formatted for percentage gives 291%)

You can convert the values to the proper cell references.

HTH,
Bernie
MS Excel MVP


"Chris N" <Chris wrote in message
...
Hello everyone!
i want to create a formula that can work out how many pours of a 700ml
bottle you can get at 25ml a shot, at £2.00 each, cost price of the 700ml
bottle is £14.31.
so basically i want to have a sheet were i can see bottle quantity, bottle
volume, cost price, selling price, profit per bottle, then show a profit
margin on that particular product.

i have already created such a formula but when its in place it says my
profit margin on that product is 2084%
this is the formula; =IF(L22=0,0,(L22/J3))

http://www.filedropper.com/example_3

thanks, Chris

Hi,

Suppose your data is layed out like:

Vol Shots Price/Shot Price/Bottle Profit Margin
700 25 2 14.31 41.69 2.91334731

the formula in E2 is

=C2*A2/B2-D2

and the formula in F2 is

=E2/D2

formatted F2 as %.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Chris N" wrote:

Hello everyone!
i want to create a formula that can work out how many pours of a 700ml
bottle you can get at 25ml a shot, at £2.00 each, cost price of the 700ml
bottle is £14.31.
so basically i want to have a sheet were i can see bottle quantity, bottle
volume, cost price, selling price, profit per bottle, then show a profit
margin on that particular product.

i have already created such a formula but when its in place it says my
profit margin on that product is 2084%
this is the formula; =IF(L22=0,0,(L22/J3))

http://www.filedropper.com/example_3

thanks, Chris