Thread: 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.

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

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

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%.

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

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.

Perfect. Thank you.

It has been a long time since 8th grade. Any suggestions on resources for brushing up my algebra?

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

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.