Ebay question: Calculating Asking Price Based on Cost of Goods Sold

buddy5954 · Apr 11, 2011

I am a reseller and I am looking to automate the process of pricing the items I put up for sale. I am trying to find an Excel formula that will do the following calculation. I am trying to calculate my asking price based on the price I buy something for. The Ebay fee structure makes this complicated.

Here is what I am trying to do.

Calculate sales price "X"

X = The price I ask for my product

Cost of goods sold = my purchase price + Ebay and Paypal fees

Ebay fees being 12% of sales price between $0 and $50 + 6% of $50.01 - $1000 + 2% of $1000.01 and above + 3% Paypal fee

+ 15% profit margin

In other words:

X = Cost of goods sold + profit margin

I want to enter my purchase price of a product and have excel spit out a sales price that covers all costs and allows for a 15% margin. Is this possible?

I know this is complex, thanks in advance.

I have found a lot of Ebay fee calculators. This is not what I’m looking for. I am looking for a formula that turns the price I pay for a product into an Ebay sales price.

buddy5954 · Apr 11, 2011

I am looking for a formula that I can apply to every row in an inventory list.

Dryver14 · Apr 11, 2011

I think this is it

Excel Workbook

A

B

1

50

66.125

2

51

63.9285

3

1000

1253.5

4

10001

12076.21

5

100

125.35

6

101

126.6035

Sheet1

buddy5954 · Apr 11, 2011

Thank you for your help with this. I think you may be on the right track.

The problem that I am seeing is that the sale prices generated by your formula do not equal 15% profit margin... it is calculating the $100 cost line as being around 10%. I think the issue may be that the ebay and paypal fees are based on the sales price, not my cost for the product.

I think I'm basically trying to do algebra with Excel.

Solve for "X" when some of the other variables are dependent on the value of "X".

Dryver14 · Apr 11, 2011

I get what you are saying and think I have done it right however,
If you put those values I have listed in a table with actual results expected then I can work out which formula you want

buddy5954 · Apr 11, 2011

Thanks again for your help. I eliminated a couple of additional costs in my original example to keep the question simple… namely an Ebay transaction charge of $.35/sale and a Paypal transaction charge of $.30 / sale. I’m really not worried about these, because most of what we sell is valued in the hundreds of dollars and these charges will have minimal impact on our profit margin. Because of this, I’ll start the examples @ $100 so as to not skew the curve too much because my calculator takes these charges into account. .

I’m not looking for exact, I’m just looking for close enough (+/- .25% margin).

Here are the expected results that I got from my profit margin calculator. It takes a lot of trial and error to hit 15%... I’m trying to streamline so I can get more product online.

$100 Result: $135 --- 14.65% margin
$1000 Result: $1255 --- 14.86% margin
$2000 Result: $2465 --- 14.91% margin
$3000 Result: $3680 --- 15.08% margin
$4000 Result: $4890 --- 15.05% margin
$5000 Result: $6100 --- 15.03% margin
$6000 Result: $7310 --- 15.01% margin
$7000 Result: $8520 --- 15.01% margin
$8000 Result: $9730 --- 15% margin
$9000 Result: $10935---14.94% margin
$10001 Result: $12150 --- 14.98% margin

gsistek · Apr 11, 2011

Could you provide the calculation used to obtain those profit margins (preferably with an example or two)? They look like they're off and I think that knowing that calculation will really help. I've tried multiple caluclations from different angles and I can't come up with what you determined.

Dryver14 · Apr 11, 2011

Sorry I am at work so don't have excel genie

<TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=331><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 104pt; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2679047 class=xl63 height=21 width=139 align=right>100</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64></TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 104pt; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 height=21 width=139 align=right>6</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right>6%</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" colSpan=2>E Bay Charge</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=21 align=right>106</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=21 align=right>3.18</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align=right>0.03</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" colSpan=2>PayPal Fee</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=21 align=right>109.18</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 align=right>16.377</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 align=right>15%</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Profit</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 align=right>125.557</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR></TBODY></TABLE>

How do you get this to 135?

buddy5954 · Apr 11, 2011

Dryver14 said:
Sorry I am at work so don't have excel genie

<table style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" border="0" cellpadding="0" cellspacing="0" width="331"><colgroup><col style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width="139"><col style="WIDTH: 48pt" span="3" width="64"></colgroup><tbody><tr style="HEIGHT: 15.95pt; mso-height-source: userset" height="21"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 104pt; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id="td_post_2679047" class="xl63" align="right" height="21" width="139">100</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width="64">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width="64">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width="64">
</td></tr><tr style="HEIGHT: 15.95pt; mso-height-source: userset" height="21"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 104pt; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl63" align="right" height="21" width="139">6</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl64" align="right">6%</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" colspan="2">E Bay Charge</td></tr><tr style="HEIGHT: 15.95pt; mso-height-source: userset" height="21"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align="right" height="21">106</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td></tr><tr style="HEIGHT: 15.95pt; mso-height-source: userset" height="21"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align="right" height="21">3.18</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align="right">0.03</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8; mso-ignore: colspan" colspan="2">PayPal Fee</td></tr><tr style="HEIGHT: 15.95pt; mso-height-source: userset" height="21"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.95pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align="right" height="21">109.18</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td></tr><tr style="HEIGHT: 15pt" height="20"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align="right" height="20">16.377</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class="xl64" align="right">15%</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">Profit</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td></tr><tr style="HEIGHT: 15pt" height="20"><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" align="right" height="20">125.557</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td><td style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8">
</td></tr></tbody></table>

How do you get this to 135?

Sorry, that was supposed to be $130

$100 cost
$6 12% ebay fees on first $50 of $130 sale
$4.80 6% ebay fees on last $80 of $135 sale
$3.90 3% paypal fees on $130 sale
$.65 Ebay and Paypal transaction fees
------
$115.35

$130 - $115.35 = $14.65 profit

$14.65 / $100 "cost" = .1465

buddy5954 · Apr 11, 2011

gsistek said:
Could you provide the calculation used to obtain those profit margins (preferably with an example or two)? They look like they're off and I think that knowing that calculation will really help. I've tried multiple caluclations from different angles and I can't come up with what you determined.

To calculate the ebay fees (12% of first $50, 6% of $50.01 - $1000, and 2% of $1000.01 +) I use a table. These are cells H2:J5.

<table border="0" cellpadding="0" cellspacing="0" width="303"><colgroup><col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <col style="mso-width-source:userset;mso-width-alt:3840;width:79pt" width="105"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> </colgroup><tbody><tr style="height:15.75pt" height="21"> <td class="xl66" style="height:15.75pt;width:83pt" height="21" width="110"> Threshold</td> <td class="xl66" style="width:79pt" width="105"> Marginal Rate</td> <td class="xl66" style="width:66pt" width="88"> Diff. Rate</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt" align="right" height="21">$0.00 </td> <td class="xl67" style="border-left:none" align="right">12%</td> <td class="xl67" style="border-left:none" align="right">12%</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;border-top:none" align="right" height="21">$50 </td> <td class="xl67" style="border-top:none;border-left:none" align="right">6%</td> <td class="xl67" style="border-top:none;border-left:none" align="right">-6%</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl65" style="height:15.75pt;border-top:none" align="right" height="21">$1,000.00 </td> <td class="xl67" style="border-top:none;border-left:none" align="right">2%</td> <td class="xl67" style="border-top:none;border-left:none" align="right">-4%
</td> </tr> </tbody></table>

To calculate the Ebay fees I use the formula:

=(SUMPRODUCT(--(B8>$H$2:$H$5),(B8-$H$2:$H$5),$J$2:$J$5)+0.35)

B8 being the cell of the selling price.

In another cell, I calculate paypal fees as being selling price:

=(B8*0.03)+0.3

I add the total ebay fees and paypal fees together in another cell:

=B10+B11

I then calculate the profit by subtracting the cost and fees from the purchase price.

=B8-B7-B12

I then calculate the profit margin by dividing the profit by the purchase price.

=B13/B7

My previous post is exactly how the excel sheet calculates profit margin.

Is there something wrong with my calculations?

Ebay question: Calculating Asking Price Based on Cost of Goods Sold

buddy5954

New Member

Excel Facts

buddy5954

New Member

Dryver14

Well-known Member

buddy5954

New Member

Dryver14

Well-known Member

buddy5954

New Member

gsistek

Well-known Member

Dryver14

Well-known Member

buddy5954

New Member

buddy5954

New Member

Similar threads

Forum statistics

Share this page

Ebay question: Calculating Asking Price Based on Cost of Goods Sold

New Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock