Profit % margin calcaulator

[andrew_myers]

Hi,

I am attempting to construct a profit margin calculator and am having some difficulty achieving the desired result.

I have a basic profit calculator;

profit(a4) = saleprice(a1) - cost(a2) - minus overhead(a3)

and profit margin =(saleprice(a1)/profit(a4))*100

What I am trying to do is build a price matrix, where for a given price point (£1,£2,£3) etc I know what price I need to charge to achieve a 10% margin.

At the moment I have created column c with price points and column d with the saleprice which meets 10% margin and I am manually changing saleprice in the profit calculator until i reach a 10% margin and then manually repeating for the next price increment.

My question is, is there an automated (function) way in which I can create a list of price points and sales price which meet 10% margin?

I appreciate any help which can be provided.

Regards

Andrew

 

[VBA Geek]

you need to use Goal Seek or Data Table

 

[andrew_myers]

you need to use Goal Seek or Data Table

Hi,

Thank you - I understand the principal of the data table function, however I am struggling to understand how to create an argument that will create saleprices increments which hit a 10% margin.

Regards

 

[Lewis825]

Total Cost = Cost + Overhead

Total Cost/(1-0.01)

This will return the price point with a 10% margin

 

[andrew_myers]

Total Cost = Cost + Overhead

Total Cost/(1-0.01)

This will return the price point with a 10% margin

Hi Lewis,

Thanks for the reply - I understand how to calculate the profit margin figure. My figure is how to derive an automated price point which results in a 10% margin using the calculator provided above.

Regards