How to calcuale a selling price based on a minimum gross margin

I want to calculate a selling price based on a set minimum gross margin, I have the following information,

The value of my base costs is in cell (W33) and it equals £59.70
The base cost including markups is in cell (S42) and it equals £65.81
I have to achieve a minimum gross margin of cell (V49) and it equals 47%

I want to put in cell (A42) the percentage I have to add onto my original selling price of (S42) to achieve a (V49) margin with the new selling price going into cell (W42)

Therefore based on the above, the cell values should equal the following.

W33 = £59.70 (Base Cost)
S42 = £65.81 (Base Cost + Markups)
V49 = 47% (Margin to be achieved)
A42 = 66% (How much the Base Cost + Markups hast to be marked up by)
W42 = £109.24 (Base Cost + Markups x 66%)

Therefore I would have to markup the original selling price of £65.81 by 66% to give me a new selling price of £109.24 which gives me a 47% gross margin.

I also want to be able to change my gross margin depending on who my customers were, thats why I want it in its own cell.

Best regards
DB

My apologies Base Cost should be £57.90 and not £59.70 stupid typo...

The base costs for this is £57.90 I am marking up my purchased parts within this which gives me a price of £65.81, my selling price needs to be £109.24 to achieve a 47% margin.

I therefore need to increase the £65.81 price by 66% to achieve £109.24 with a 47% margin.

(£109.24-£57.90)/£109.24 = 47%
£65.81*66% = £109.24

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

I want to calculate a selling price based on a set minimum gross margin, I have the following information,

The value of my base costs is in cell (W33) and it equals £57.90
The base cost including markups is in cell (S42) and it equals £65.81
I have to achieve a minimum gross margin of cell (V49) and it equals 47%

I want to put in cell (A42) the percentage I have to add onto my original selling price of (S42) to achieve a (V49) margin with the new selling price going into cell (W42)

Therefore based on the above, the cell values should equal the following.

W33 = £57.90 (Base Cost)
S42 = £65.81 (Base Cost + Markups)
V49 = 47% (Margin to be achieved)
A42 = 66% (How much the Base Cost + Markups hast to be marked up by)
W42 = £109.24 (Base Cost + Markups x 66%)

Therefore I would have to markup the original selling price of £65.81 by 66% to give me a new selling price of £109.24 which gives me a 47% gross margin.

I also want to be able to change my gross margin depending on who my customers were, thats why I want it in its own cell.

Best regards
DB

Sorry for being a hassle basically I am an estimator for a sub-contract machine shop.

In the example I have given previously the values that make up my base price are as follows,

Production Quantity is for 100off components
Total machine setup is 220 minutes charged at £20.00 per/hour
=((Setup/Quantity)/60)*Hourly Rate
=((220/100)/60)*20
=£0.73 Actual Setup Cost

Total machine run time is 15.5 minutes charged at £20.00 per/hour
=(Run/60)*Hourly Rate
=(15.5/60)*20
=£5.17 Actual Run Cost

Actual material cost is £50.00 each
Actual processing cost is £2.00 each

This gives me a base price of - £57.90 each

The setup & run costs are marked up by 90%

Setup =£0.73 * 90% = £1.39
Run =£5.17 * 90% = £9.82

The material and processing costs are marked up by 5%

Material = £50.00 * 5% = £52.50
Processing = £2.00 * 5% = £2.10

This gives me a markup price of - £65.81
The markups vary depending on who our customers are.

Based on the above I need to achieve a gross margin of 47%, which is also variable depending on our customers, this means that I have to further mark up the £65.81 price by 66% to achieve this as shown below.
£65.81 * 66% = £109.24
This means that
(Sell Price – Actual Price) / Sell Price = (£109.24 - £57.90) / £109.24)
= a gross margin of 47%

All the above is already calculated in my spreadsheet however I want the spreadsheet to automatically calculate how much has to be marked up (ie the 66%) to give me a new selling price (ie £109.24) based on whatever the gross margin is set at (ie 47%).

Hope this explains a bit better.

Bruce

Thanks very much for that it worked perfect, you are a star and that has made one of my jobs a whole lot easier. It is very much appreciated.

DB