Gross Profit Calculation

[CharlieGraham]

Hey, I'm really stumped on how to get a formula correct in Excel please have a crack at this: A product costs £10. Each product has 14 units in it. So with each unit costing 0.7 I need to be able to get a formula that gives me an 80% return on each unit. Does that make sense?

 

[Tjtrev89]

Hi, please try clarifying what you mean?

I have a few different formulas here that work out 80% return, but I'm usnure which applies as I'm unsure what you mean.

Thanks

 

[CharlieGraham]

From a bottle of Whisky I'm getting 14 measures. Say the Whisky costs £32. From that I would like to know the price of each individual measure (at cost) which I can garner by dividing the total by 14, but then I would like a formula that worked out a selling price to return an 80% Gross Profit. Any clearer?

 

[Tjtrev89]

In the mean time, here's some of the stuff I though could be useful, depending on what you need.
ABC and 123 refer to cell rows and columns.

A B C D

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> <col style="mso-width-source:userset;mso-width-alt:3949;width:81pt" width="108"> <col style="width:48pt" width="64"> </colgroup><tbody>
[TD="class: xl24, width: 64"] 1 Unit
[/TD]
[TD="class: xl24, width: 73"]Unit @80%

[/TD]
[TD="class: xl24, width: 108"]No. of units[/TD]
[TD="class: xl24, width: 64"]Product
[/TD]

[TD="class: xl24"] 2 0.7
[/TD]
[TD="class: xl24"]1.26[/TD]
[TD="class: xl24"]14[/TD]
[TD="class: xl24"]17.64[/TD]

</tbody>

[TABLE="width: 309"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]In A2 the sum is =A2*1.8
In D2 the sum is =B2*C2
[/TD]
[/TR]
[TR]
[TD]You would have the optio to change the numbers of units and the cost of the unit.

Thanks
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 309"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tjtrev89]

Yes much clearer, thanks.

The table i posted there would actually work that out but then you would have to calculate the unit cost. Would you not want that done for you?

If so, you could have something like this set-up, which you could use for different products:

--A ---------B
1Unit £32
2Meas 14
3Meas cost £2.29
4Sell price £4.11
5Overall Profit £25.60

For unit, you type the actual value yourself
For Meas, you put in how measurements you're going to get
For Meas Cost, simply typle number in formula =B1/B2
Sell price, use formula =B3*1.8
Overall profit cell, type formula =(B4*B2)-B1

Hope that all makes sense

 

[Tjtrev89]

123456 are the row numbers
A and B are column numbers.

In my example, the names of the calculations are in A1-A5 and the numberical values or formula answers are in cells B1-B5

 

[CharlieGraham]

Thank you. All over it now.

 

[shg]

Sell price, use formula =B3*1.8

Margin is measured as a fraction of revenue, not cost.

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

A​

[/td][td]

B​

[/td][td]

C​

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

1​

[/td][td]

Unit Cost​

[/td][td]

$ 32.00​

[/td][td]B1: Input[/td][/tr]

[tr][td]

2​

[/td][td]

Measures per Unit​

[/td][td]

14​

[/td][td]B2: Input[/td][/tr]

[tr][td]

3​

[/td][td]

Cost per Measure​

[/td][td]

$ 2.29​

[/td][td]B3: =B1/B2[/td][/tr]

[tr][td]

4​

[/td][td]

Target Gross Margin​

[/td][td]

80%​

[/td][td]B4: Input[/td][/tr]

[tr][td]

5​

[/td][td]

Price per Measure​

[/td][td]

$ 11.43​

[/td][td]B5: =B3/(1-B4)[/td][/tr]
[/table]

 

[Tjtrev89]

Oh I know, but the wording by OP made me think this is what he actually wanted.

Thanks for the input though