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
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.
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
Hmmm....are you sure? Shouldn't it be this way:
Now you pay the salesman his $3.18 fee, and you're left with $18.00,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
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!
That kinda agrees with your example; more precise : 4.62 / 23.08 = .20017Code:Item cost : 15.00 Seller fee : 3.46 (15% of 23.08) Profit margin : 4.62 (20% of 23.08) ===== Selling price : 23.08
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.