@
eaxlns.... Hmm, 3 responses with 3 different answers. How can that be? Which should you use?
In part, that hinges on terminology and what you want profit to be based on: just the item cost, simply passing along the "operating costs" (shipping, ebay fees and paypal fees); or all of your costs.
Personally, I don't believe you should make a profit on shipping and fees. But that's entirely up to you.
The following demonstrates the formulas for the two different methods.
| A | B | C | D | E | F |
---|
1 | item cost | 1.72 | | | | |
2 | ship | 0.87 | | | | |
3 | ebay% | 10.00% | | | | |
4 | paypal% | 2.90% | | | | |
5 | pp fixed | 0.20 | | | | |
6 | prof% | 15.00% | | | | |
7 | | | | | | |
8 | | profit on | item cost | | profit on | all costs |
9 | | Exact | Rounded | | Exact | Rounded |
10 | price | 3.5517 | 3.56 | | 3.8696 | 3.87 |
11 | ship | -0.8700 | -0.87 | | -0.8700 | -0.87 |
12 | ebay | -0.3552 | -0.36 | | -0.3870 | -0.39 |
13 | paypal | -0.3030 | -0.30 | | -0.3122 | -0.31 |
14 | net rev | 2.0235 | 2.03 | | 2.3004 | 2.30 |
15 | prof% | 15.00% | 15.27% | | 15.00% | 14.99% |
<tbody>
</tbody>
Rich (BB code):
Formulas:
B10: =(B1/(1-B6)+B2+B5)/(1-B3-B4)
B11: =-B2
B12: =-B10*B3
B13: =-(B10*B4+B5)
B14: =SUM(B10:B13)
B15: =(B14-B1)/B14
C10: =ROUNDUP((B1/(1-B6)+B2+B5)/(1-B3-B4),2)
C11: =-B2
C12: =-ROUND(C10*B3,2)
C13: =-ROUND(C10*B4+B5,2)
C14: =SUM(C10:C13)
C15: =(C14-B1)/C14
E10: =(B1+B2+B5)/(1-B6-B3-B4)
E11: =-B2
E12: =-E10*B3
E13: =-(E10*B4+B5)
E14: =SUM(E10:E13)
E15: =(E10-B1+SUM(E11:E13))/E10
F10: =ROUNDUP((B1+B2+B5)/(1-B6-B3-B4),2)
F11: =-B2
F12: =-ROUND(F10*B3,2)
F13: =-ROUND(F10*B4+B5,2)
F14: =SUM(F10:F13)
F15: =(F10-B1+SUM(F11:F13))/F10
I highlighted the primary formulas.
The calculations in columns B and E are provided as a proof of concept. They demonstrate that exact profit margin is indeed calculated.
The calculations in columns C and F are the correct calculations, given the real-world requirement to round to unit of currency. This necessarily causes a difference in the profit margin. But it should be "close".
Note that if the profit is applied only to the item cost, excluding operating costs, the profit margin is really a percentage of net revenue (price minus operating costs), not the full price.
Since you specified a profit margin as a percentage of price per se, you would want the profit applied to all costs.
Note that my numbers different somewhat from the other 3 responses. This is explained in the comments below.
Can use the goal seek for this problem.
[....]
With your example values, it gives 3.77 as the selling price.
Comparable to my 3.87, I suspect. But since ParamRay did not show his formulas, we do not what the cause of the discrepancy is.
In you example your total cost, is the item cost plus the shipping: 1.72 + .87 = 2.59. To get a Gross Margin of 15% for item that costs 2.59, you must divide the cost by the reciprocal of the GM. So that is 2.59/(1-15%) = 3.05. [....] 3.73 which is your FINAL SELL Price.
My comparable numbers are 2.03 and 3.56. The difference is: I do not include shipping costs in the amount that the profit margin is based on.
If we exclude ebay and paypal operating costs, I see no reason not to also exclude the shipping cost. It is just another operating cost; that is, it is not a cost of goods sold.
After thought.... That depends on an interpretation of "shipping" in the OP. I interpret it to mean: the cost of shipping to the customer. Arguably, igold might interpret it to mean: the cost of shipping to the seller. Both interpretations are equally valid, IMHO. If igold's interpretation is correct, I would agree with his inclusion of shipping with cost of goods sold. In that case, change the B1/(1-B6)+B2 to (B1+B2)/(1-B6) in B10 and C10. Corresponding changes are also needed in the "proof of concept" formulas in 11:15. Let know if details are required.
In any case, note that igold's profit margin is based on the
net revenue (price minus operating costs), not on the
price, as eaxlns specified.
4 | Break even price | | Profit price |
5 | 3.203215 | | 3.683697 |
<tbody>
</tbody>
Your mistake is in simply multiplying the break-even price by (1+15%).
That modified price would change the ebay and paypay components in the derivation of the price, which is not reflected correctly.