calculating a selling price based on gross profit percentage with one of the cost figures determined by selling price

[Corey1979]

I work for a manufacturing business that is trying to create an excel tool for calculating selling price based on the desired gross profit percentage. All but one of our "cost to make" figures are firm tangible amounts. Our "shop supply" expense category is the problem.

Shop supplies are calculated based on 7% of our labor selling price.

Below is the easy example with a profit margin of 50%

Cost
$200 labor (firm cost)
$150 lumber (firm cost)
$100 fabric (firm cost)
$100 delivery (firm cost)
$14 shop supplies (firm 50% of sell price shop supply amount)

Sell price
$400 Labor
$300 Lumber
$200 Fabric
$200 Delivery (firm price of double the cost)
$28 shop supplies (firm 7% of selling labor price)

Now lets take my above listed cost without factoring in the shop supplies expense which equals $550. I want to sell at a 42% gross profit margin. How do I calculate my selling price?

Thank you for any help, I have an excel program created which is currently usable but would be more efficient if I could solve the above problem.

 

[firemath]

Are you asking about the profit figure or the gross profit figure?

 

[JeffM]

First, calculate estimated shop supplies expense by multiplying labor cost by 7%.

Second, add up all the costs including your estimated shop supplies expense.

Third, divide that sum by

[MATH]100 \text { MINUS desired gross profit margin percebtage.}[/MATH]
Finally, multiply by 100, rounding to nearest cent. (Excel will automatically do the rounding correctly if you set the format to 2 decimals.)

Let's see how that works

[MATH]\text {Step 1: } 200 \times 7\% = \dfrac{400 * 7}{100} = 14.[/MATH]
[MATH]\text {Step 2: }200 + 150 + 100 + 100 + 14 = 564.[/MATH]
[MATH]\text {Step 3: } 564 \div (100 - 50) = 11.28.[/MATH]
[MATH]\text {Step 4: } 100 * 11.28 = 1,128.00.[/MATH]
Which is what you got in your example. Let's see what happens at a 42% gross profit margin. Steps 1 and 2 do not change.

[MATH]\text {Step 3: } 564 \div (100 - 42) \approx 9.7241.[/MATH]
[MATH]\text {Step 4: } 100 * 9.7241 \approx 972.41.[/MATH]
Is that correct? Let's see.

Gross profit margin =

[MATH]972.41 - 564.00 = 408.41[/MATH]
Gross margin percentage =

[MATH]100 * \dfrac{408.41}{972.41} \approx 42\%.[/MATH]