# Formula to determine appropriate sales price to meet specific profit margin

• Sep 23rd 2010, 05:34 AM
onestop
Formula to determine appropriate sales price to meet specific profit margin
I hope this is the correct forum.

I have a large inventory. Each item has a different amount for my cost. Currently I use complex spreadsheets to determine pricing and profit margin per. I am trying to come up with a formula that can be applied across the board and nix the spreadsheets. Here are the variables.

Each product has a cost: product cost
When I sell the product I have a fee based on the total sales price: seller fee
I want a specific profit margin: Profit margin

The only constant in this equation is the product cost. For this exercise lets put the seller's fee at 15% of total sales price, and the desired profit margin at 20%.

What I would like to do is apply some sort of variable to all products that will determine the appropriate sales price to always return 20% profit margin. The problem is that as the cost of different items gets larger a variable applied doesn't return enough profit. Also, as the sales price rises the fee becomes a larger number. As I said the only constant is the product cost. I am guessing you have to work from that point.

As an example lets use these numbers:
product A: product cost \$10
product b: product cost \$30
product c: product cost \$100
product d: product cost \$225

I'm not incredibly smart, but if you can figure it out I think you are.
• Sep 23rd 2010, 08:54 AM
RHandford
Hi

Let me get this straight:

You have a product that had a cost of c.
Do you add the sales fee and then add 20% profit or do you add the profit and then deduct the sellers fee.
Perhaps you could give a worked example or email me the spreadsheet
• Sep 23rd 2010, 09:08 AM
onestop
Quote:

Originally Posted by RHandford
Hi

Let me get this straight:

You have a product that had a cost of c.
Do you add the sales fee and then add 20% profit or do you add the profit and then deduct the sellers fee.
Perhaps you could give a worked example or email me the spreadsheet

The seller's fee is deducted as a % of the total sales price. I calculate profit margin as a percentage of total sales price. I kind of work backwards adjusting total sales price to meet the appropriate profit margin. I have a spreadsheet that quickly shows me what each price adjustment returns. Then I have another spreadsheet that I use to analyze all inventory on a weekly basis as my cost per item changes. Unfortunately, they are huge and I can't send. However, example below.

ex:
item cost: \$15
seller fee: 15% of total sale price
The sales price would have to be \$22.99.

\$22.99*.85 (15% fee) = \$19.54 gross profit
\$19.54-\$15 (item cost) = \$4.54 Net profit
\$4.54/\$22.99 = .197 or 20% profit margin on the total sale
• Sep 23rd 2010, 12:08 PM
Wilmer
Quote:

Originally Posted by onestop
item cost: \$15
seller fee: 15% of total sale price
The sales price would have to be \$22.99.

\$22.99*.85 (15% fee) = \$19.54 gross profit
\$19.54-\$15 (item cost) = \$4.54 Net profit
\$4.54/\$22.99 = .197 or 20% profit margin on the total sale

Hmmm....are you sure? Shouldn't it be this way:
Code:

```Item cost    : 15.00 Seller fee  :  3.18 (15% of 21.18) Profit margin:  3.00 (20% of 15.00)                 ===== Selling price : 21.18```
Now you pay the salesman his \$3.18 fee, and you're left with \$18.00,
so a \$3.00 profit since item cost is \$15.00, or 20%.
• Sep 23rd 2010, 12:21 PM
onestop
That would be a 20% ROI. I'm looking for a 20% profit margin on sale.

Basically (net profit/Total Revenue)*100=profit margin. I need a way to use ROI to reach the 20% PM since my cost is the only constant.

Profit margin - Wikipedia, the free encyclopedia
• Sep 23rd 2010, 01:54 PM
Wilmer
HOKAY...now that we got that straight!
Code:

```Item cost    : 15.00 Seller fee    :  3.46 (15% of 23.08) Profit margin :  4.62 (20% of 23.08)                 ===== Selling price : 23.08```
That kinda agrees with your example; more precise : 4.62 / 23.08 = .20017

Seems straightforward enough:
c = item cost (given)
f = seller fee % (given)
m = profit margin % (given)
p = selling price (calculated)
General case formula:
p = c / (1 - f - m)

With above example: p = 15 / (1 - .15 - .20) = 15 / .65 = 23.08

So, for each sale, this would happen:
get c,f,m
p = c / (1 - f - m)
PRINT c : 15.00
PRINT p*f : 3.46
PRINT p*m :4.62
PRINT p : 23.08

And, of course, c,f and m can be ANY reasonable amounts.
• Sep 24th 2010, 06:53 AM
onestop
Perfect. Thank you.

It has been a long time since 8th grade. Any suggestions on resources for brushing up my algebra?
• Sep 24th 2010, 08:45 AM
Wilmer
If you stopped after grade 8, I don't know what to tell you...
• Sep 24th 2010, 01:31 PM
onestop
Quote:

Originally Posted by Wilmer
If you stopped after grade 8, I don't know what to tell you...

Didn't stop after 8th grade. I stopped after Cal II in 12th. They don't require math in college if you have already gone that far with AP credit. I has just been a long time since I have needed to use any algebra. Looking for a good refresher resource.