Amazon eBay Pricing Formula

[theguyIsHere]

Hi everyone, I sell furniture on Amazon and need to be able to calculate the end ‘Sell price’ taking into account various cost factors which can change (in some instances)

E.g A chair’s cost price from supplier is £10 (including vat)

I want the formula to calculate a minimum profit of 25% after all costs or £5 (whichever is greater).
Costs include:

Amazon Fee: 17.5% of sale price
VAT: 20% of sale price
Shipping: £10 (static)

How can I formulate that in excel folks? Many thanks in Advance

 

[d'Holbach]

[TABLE="class: grid, width: 392"]
<tbody>[TR]
[TD]Product Cost
[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amazon Fee: 17.5%[/TD]
[TD="align: right"]17.50%[/TD]
[TD]on sales price[/TD]
[/TR]
[TR]
[TD]VAT[/TD]
[TD="align: right"]20%[/TD]
[TD]on sales price[/TD]
[/TR]
[TR]
[TD]Shipping[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Amazon Fees[/TD]
[TD="align: right"]9.33333345[/TD]
[TD]=C2*C13[/TD]
[/TR]
[TR]
[TD]VAT amount[/TD]
[TD="align: right"]10.6666668[/TD]
[TD]=C3*C13[/TD]
[/TR]
[TR]
[TD]Fixed Costs[/TD]
[TD="align: right"]20[/TD]
[TD]=C1+C4[/TD]
[/TR]
[TR]
[TD]Total Costs[/TD]
[TD="align: right"]40.00000025[/TD]
[TD]=SUM(C7:C9)[/TD]
[/TR]
[TR]
[TD]Percentage Margin[/TD]
[TD="align: right"]13.3333335[/TD]
[TD]=C13*0.25[/TD]
[/TR]
[TR]
[TD]Fixed Margin[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Sales Price[/TD]
[TD="align: right"]53.333334[/TD]
[TD="align: right"]53.333334[/TD]
[/TR]
[TR]
[TD]Check[/TD]
[TD="align: right"]2.5E-07[/TD]
[TD]=C13-C10-LARGE(C11:C12,1)[/TD]
[/TR]
</tbody>[/TABLE]


I've used Solver to solve this by asking it to make Check cell as close to 0 as possible by manipulating Sales Price cell.

 

[theguyIsHere]

what formula did you get sales price and how do you tie that back to the 25% profit (or min £5)? it doesn't add up for me

 

[d'Holbach]

Sales price is not derived from a formula, it's a amount derived by Solver (as explained in my previous post).

What do you mean it doesn't add up, the calculation is as follows:

0~53.34-(53.34*62.5%)-20

62.5%= Amazon Fee + VAT + Margin