Goal Seek Formula

[Branagan_1]

Hi,

I would like to mimic goal seek for the following but unsure how?

Current RRP
New RRP
Current Selling price
New Selling Price
Current Margin = (RRP-VAT) - Selling Price

I would like to change the current RRP (i.e £2.50) to a New RRP (£2.40) which then changes the current selling price (i.e £18.35) to the new selling price (i.e 17.52) whilst holding the Current Margin (i.e £2.48).

but instead of typing in the new selling price to equal the current margin i want the new selling price to be change when i type the New RRP in.

I hope that makes sense??

Thanks in advanced..

Ben

 

[gcwind]

Shouldn't that be Current margin = Selling price - (RRP-VAT), otherwise you will end up with a negative number

Also shouldn't VAT be post sales as it is applicable to the sale prices not the unit cost. I know you will pay it on the unit purchase and so it can be paid forward i.e. the customer will absorb some of the costs

G.

 

[gcwind]

So here is the algebra

Margin (m) = Sales Price (s) - RRP (x) - VAT (t)
m = s - x - t
Assuming VAT = 20%, t = 0.2s
m = s - x -0.2s
m = 0.8s - x
there s = (m+x)/0.8

So if you know your original margin, you can set this to auto generate the new price.

Hope this helps