Amazon Pricing Formula

[Lucas56]

Hi

I was wondering if anyone could help me.

I sell CDs on Amazon. My supplier send an excel file with their net prices but they have thousands of items and I couldn't possibly reprice them manually.

eg.

A CD at £5.99 net + Vat is £7.19

I need to list this item on Amazon for £10.02 to make a minimum of £1.00 profit after Amazon takes their fees.

I need a formula that..

Can add 20% VAT,
Add a shipping credit of £0.98,
deduct 17.25% Amazon fee,
deduct my postage cost of £0.80 to get a profit minimum of £1.00.

Can anyone help please?

Thanks, Mike

 

[Bodsy]

What's the order of deductions?! Do Amazon take a fee of your inc Vat or Ex Vat price?

 

[Lucas56]

Hi

Thanks for your reply.

I'm not VAT registered but my supplier is so I have to add VAT on the NET prices they supply me.

Amazon charge 17.25% of whatever the listed price is and then add £0.98 postal credit.

Thanks, Mike

 

[freesurf]

I'm also a little confused.

Let's say you charge 10.02. You say that Amazon charge 17.25% of the listed price, presumably 10.02 in your example. This means 1.73, roughly. To this they add 0.98 shipping credit.

So, this suggests that before anything else is considered your money available for profit after Amazon costs is 7.31 (i.e. 10.02 less 17.25 fee on this, plus a flat 0.95).

You then need to pay 0.80 in postage, so the funds available for profit are 6.51 before allowing for the cost of buying the CD.

The bit where I'm confused is the cost of buying the CD. You mention that your supplier charges 5.99. After 20% VAT this means 7.19. However if you listed at 10.02, presumably you make a loss of 0.68 - i.e. 6.51 less 7.19

 

[Lucas56]

I'm also a little confused.

Let's say you charge 10.02. You say that Amazon charge 17.25% of the listed price, presumably 10.02 in your example. This means 1.73, roughly. To this they add 0.98 shipping credit.

So, this suggests that before anything else is considered your money available for profit after Amazon costs is 7.31 (i.e. 10.02 less 17.25 fee on this, plus a flat 0.95).

You then need to pay 0.80 in postage, so the funds available for profit are 6.51 before allowing for the cost of buying the CD.

The bit where I'm confused is the cost of buying the CD. You mention that your supplier charges 5.99. After 20% VAT this means 7.19. However if you listed at 10.02, presumably you make a loss of 0.68 - i.e. 6.51 less 7.19

Hi

For me to make a minimum £1.00 profit on a £5.99 + VAT CD...

Amazon have a weird way of calculating fees....

£10.02 - 17.25% commission = £8.29 - closing fee of £0.28 = 8.01 + shipping credit £1.26 = 9.27 - £7.19 (Cost of CD) = £2.08 - £0.80 actual shipping cost = £1.28 (profit).

Thanks, Mike

 

[Bodsy]

Hi Lucas

Didn't know how to add the actual sheet into this thread.

If you use this table - you can change the parameters you want to apply.

The cell containing "Amazon Fee" is A1.

<TABLE style="WIDTH: 425pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=566><COLGROUP><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 3474" width=190><COL style="WIDTH: 282pt; mso-width-source: userset; mso-width-alt: 6875" width=376><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 143pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=190>Amazon Fee</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 282pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=376>0.1725</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>Closing Fee</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>0.28</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>Shipping Credit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>1.26</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>Postage Charge</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>0.8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>Minimum Profit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17>VAT rate</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0.2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>Cost</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0">Retail</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69 height=17>5.99</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>=(((SUM($B$4:$B$5)+(A9*(1+$B$6)))-$B$3)+$B$2)/(1-$B$1)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 height=17></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68></TD></TR></TBODY></TABLE>

 

[freesurf]

I get the same results so looks good

 

[theguyIsHere]

How would you modify this where you are vat registered and needed to pay vat on the full sale price but still needed to make the minimum profit (as a 30% of original supplier price)?