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.

gsistek · Apr 14, 2011

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Purchase Price</TD><TD style="TEXT-ALIGN: right">$2,000.00</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Selling Price</TD><TD style="TEXT-ALIGN: right">$2,468.62</TD><TD style="TEXT-ALIGN: right">2468.615601</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Ebay Fees</TD><TD style="TEXT-ALIGN: right">$92.73</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>PayPal Fees</TD><TD style="TEXT-ALIGN: right">$74.36</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Total Fees</TD><TD style="TEXT-ALIGN: right">$167.09</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Profit</TD><TD style="TEXT-ALIGN: right">$301.53</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Profit Margin</TD><TD style="TEXT-ALIGN: right">15.08%</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B8</TH><TD style="TEXT-ALIGN: left">=ROUNDUP(C8,2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B10</TH><TD style="TEXT-ALIGN: left">=ROUNDUP((SUMPRODUCT(--(B8>$H$3:$H$5),(B8-$H$3:$H$5),$J$3:$J$5)+0.35),2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B11</TH><TD style="TEXT-ALIGN: left">=ROUNDUP((B8*0.03)+0.3,2)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B12</TH><TD style="TEXT-ALIGN: left">=B10+B11</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B13</TH><TD style="TEXT-ALIGN: left">=B8-B7-B12</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B14</TH><TD style="TEXT-ALIGN: left">=B13/B7</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Then use...

Code:

Sub FindPrice()
    Range("B14").GoalSeek Goal:=0.15, ChangingCell:=Range("C8")
End Sub

buddy5954 · Apr 14, 2011

gsistek said:
Excel 2007<table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><colgroup><col style="BACKGROUND-COLOR: #e0e0f0" width="25"><col><col><col></colgroup><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>
</th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td style="TEXT-ALIGN: center; COLOR: #161120">7</td><td>Purchase Price</td><td style="TEXT-ALIGN: right">$2,000.00</td><td style="TEXT-ALIGN: right">
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">8</td><td>Selling Price</td><td style="TEXT-ALIGN: right">$2,468.62</td><td style="TEXT-ALIGN: right">2468.615601</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">9</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">10</td><td>Ebay Fees</td><td style="TEXT-ALIGN: right">$92.73</td><td style="TEXT-ALIGN: right">
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">11</td><td>PayPal Fees</td><td style="TEXT-ALIGN: right">$74.36</td><td style="TEXT-ALIGN: right">
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">12</td><td>Total Fees</td><td style="TEXT-ALIGN: right">$167.09</td><td style="TEXT-ALIGN: right">
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">13</td><td>Profit</td><td style="TEXT-ALIGN: right">$301.53</td><td style="TEXT-ALIGN: right">
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">14</td><td>Profit Margin</td><td style="TEXT-ALIGN: right">15.08%</td><td style="TEXT-ALIGN: right">
</td></tr></tbody></table><table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>Sheet1</th></tr></thead><tbody></tbody></table>

<table style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules="all" cellpadding="2" width="85%"><tbody><tr><td style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<table style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2" width="100%"><thead><tr style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th width="10">Cell</th><th style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</th></tr></thead><tbody><tr><th style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width="10">B8</th><td style="TEXT-ALIGN: left">=ROUNDUP(C8,2)</td></tr><tr><th style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width="10">B10</th><td style="TEXT-ALIGN: left">=ROUNDUP((SUMPRODUCT(--(B8>$H$3:$H$5),(B8-$H$3:$H$5),$J$3:$J$5)+0.35),2)</td></tr><tr><th style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width="10">B11</th><td style="TEXT-ALIGN: left">=ROUNDUP((B8*0.03)+0.3,2)</td></tr><tr><th style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width="10">B12</th><td style="TEXT-ALIGN: left">=B10+B11</td></tr><tr><th style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width="10">B13</th><td style="TEXT-ALIGN: left">=B8-B7-B12</td></tr><tr><th style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width="10">B14</th><td style="TEXT-ALIGN: left">=B13/B7</td></tr></tbody></table></td></tr></tbody></table>
Then use...

Code:

Sub FindPrice() Range("B14").GoalSeek Goal:=0.15, ChangingCell:=Range("C8") End Sub

Thanks a lot, I think this is what I'm looking for. This is where my lack of knowledge will really show... how do I apply the code:

]Sub FindPrice()
Range("B14").GoalSeek Goal:=0.15, ChangingCell:=Range("C8")
End Sub

?

Again, thanks everyone for all of your help.

gsistek · Apr 14, 2011

Press Alt + F11 to open VBA editor. On the left side, open your project. Right-click on your project and choose Insert-->Module. Open the Module folder and double click Module1. Add the code in the module and press the "Play" button. Anytime you want to run this macro, open it up and press "Play".

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

buddy5954

New Member

gsistek

Well-known Member

Excel Facts

buddy5954

New Member

gsistek

Well-known Member

Similar threads

Forum statistics

Share this page

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

buddy5954

New Member

gsistek

Well-known Member

Excel Facts

buddy5954

New Member

gsistek

Well-known 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