Formula for calculating eBay selling price

[eaxlns]

Hi folks,
I'm not sure if this has been posted before or if there is a guide on how to do this on this forum, so apologies if there is.
I want a formula which I can use to work out my selling price on eBay, assuming I want to make a 15% profit of the selling price, and the following are my costs:

Item cost £1.72
Shipping £0.87
eBay fees 10% of final selling price
PayPal fees 2.9% + £0.20 of final selling price

Many thanks

 

[ParamRay]

Can use the goal seek for this problem.
Data --> Forecast --> What-If Analysis --> Goal Seek

With your example values, it gives £3.77 as the selling price.

 

[eaxlns]

That’s great, but is it possible to use this as a formula, as I need to apply it to 55,000 products.

 

[igold]

I can give you the logic but you will have to fill the formula to match your sheet layout.


The first thing you have to do is get the selling price of your item that generates a 15% Gross Margin.


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. Now we take care of the fees. First you add in the .20 Paypal Fee = 3.25. Now you have to get another GM of 12.9% which is the total of the Ebay 10% and the Paypal 2.9%.


Same as before you divide by the reciprocal. Now it is 3.25/(1-12.9%) = 3.73 which is your FINAL SELL Price. If you go backwards now you will see that


3.73 * 10% Ebay fee = .37
3.73 * 2.9% PPal fee = .11
also subtract the .20


These 3 add up to .68 subtract that from your sell of 3.73 and you have 3.05 which was your original sell price at a 15% margin.

If you layout all the base figures on a row you can generate a easy formula...

I hope this helps.

 

[Eric W]

See if this helps you out:

Excel 2012

	A	B	C	D	E
Profit	Shipping	eBay fees	PayPal %	PayPay fix
Cost	Break even price	Profit price
Cost
Shipping
eBay fees
Paypal %
paypal Fixed
Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]2.90%[/TD]
[TD="align: right"]0.2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1.72[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.203215[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.683697[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.72[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.320321[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.092893[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3.203215[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=(A5+$B$2+$E$2)/(1-$C$2-$D$2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E5[/TH]
[TD="align: left"]=C5*(1+$A$2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=A5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]=B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=C5*C2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=C5*$D$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C11[/TH]
[TD="align: left"]=$E$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]=SUM(C7:C11)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Everything from row 7 down is just for a check. You can just use the C5 and E5 formulas (or combine them) to get your answer.

 

[igold]

@Eric

If you use a 3.68 sell price, after you deduct the fixed fee of .20, the 10% Ebay = .37 and the PPal of 2.9% = .11 you are left with 3.01. If the item costs 2.59 (cost + shipping) that leaves a Gross Margin % = 11.41%.

 

[igold]

That was a typo, a 3.68 Selling Price leaves a 13.95% Gross Margin. Sorry about the confusion...

 

[joeu2004]

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


[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]item cost[/TD]
[TD="align: right"]1.72[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]ship[/TD]
[TD="align: right"]0.87[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]ebay%[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]paypal%[/TD]
[TD="align: right"]2.90%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]pp fixed[/TD]
[TD="align: right"]0.20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]prof%[/TD]
[TD="align: right"]15.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]profit on[/TD]
[TD]item cost[/TD]
[TD="align: right"][/TD]
[TD="align: right"]profit on[/TD]
[TD]all costs[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Exact[/TD]
[TD="align: right"]Rounded[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Exact[/TD]
[TD="align: right"]Rounded[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]price[/TD]
[TD="align: right"]3.5517[/TD]
[TD="align: right"]3.56[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.8696[/TD]
[TD="align: right"]3.87[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]ship[/TD]
[TD="align: right"]-0.8700[/TD]
[TD="align: right"]-0.87[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.8700[/TD]
[TD="align: right"]-0.87[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]ebay[/TD]
[TD="align: right"]-0.3552[/TD]
[TD="align: right"]-0.36[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.3870[/TD]
[TD="align: right"]-0.39[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]paypal[/TD]
[TD="align: right"]-0.3030[/TD]
[TD="align: right"]-0.30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.3122[/TD]
[TD="align: right"]-0.31[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]net rev[/TD]
[TD="align: right"]2.0235[/TD]
[TD="align: right"]2.03[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.3004[/TD]
[TD="align: right"]2.30[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]prof%[/TD]
[TD="align: right"]15.00%[/TD]
[TD="align: right"]15.27%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15.00%[/TD]
[TD="align: right"]14.99%[/TD]
[/TR]
</tbody>[/TABLE]

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.

Break even price	Profit price

<tbody>
[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3.203215[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.683697[/TD]

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

 

[igold]

I did read the shipping cost to be the cost of shipping to the seller. Therefore, I say that the true cost of the item is the "Landed Cost" which would include the shipping. Additionally, I took the OP to mean the Selling Price of the item before fees. Gross Margin is defined as the amount of money remaining after deducting the Cost of Good Sold. You would pay your operating costs out of your Gross Margin. The fees would be treated no different than the expense of keeping the lights on.

 

[Eric W]

I chose my final formula of

=(Break-even price)*(1+percent profit)

because the OP said "I want to make a 15% profit of the selling price". However, I really didn't do a complete analysis. Multiplying the break-even price by the desired profit ended up with higher fees, so it ended up as a 13% profit on the sale price, or a 24% profit on the cost. Neither one is a desired answer.

It appears joeu2004 has the most complete and accurate analysis, so I'd stick with that.


On a related note, I spent a lot of time once working out the same type of analysis for another eBay seller. His setup was much more complicated, with different types of products getting different fees and tax rates. I finally concluded in that case that the only solution was to create a UDF that would start at a base sale price, probably the cost times a percentage, then would increment by 1, checking all the fees and taxes, and quit when all constraints were met. There just were no fixed formulas that covered all situations for him.