Working out Minimum Amazon Selling Price

[jamobe]

Hi Guys

I'm hoping someone can help.

I have a spread sheet with all my products which include the purchase price, shipping costs and fees etc.

I have a column for the minimum sale price, and this needs to be the break even price after VAT @ 20%, amazon fees, packaging costs etc

Here is an example

A1 Purchase Price = £2.00
A2 Packaging cost = £0.05
A3 Shipping cost = £1.40
A4 Amazon Fee = 15%
A5 VAT = 20%


Is there a calculation that can factor in these costs to accurately determine the minimum selling price as to not lose profit. The VAT and amazon fee is charged on the final selling price.

There are other costs I would like to include but will add these on at the end of the calculation myself, it is just the above that I would like to get right before I factor anything else in

I really hope someone can help.

Thanks in advance

 

[trunten]

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]£2.00[/TD]
[TD]Purchase Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]£0.05[/TD]
[TD]Packaging Cost[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]£1.40[/TD]
[TD]Shipping Cost[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]=0.15*SUM(A1:A3)[/TD]
[TD]Amazon Fee (15%)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]=SUM(A1:A4)*0.2[/TD]
[TD]VAT (20%)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]=SUM(A1:A5)[/TD]
[TD]Minimum Sale Price[/TD]
[/TR]
</tbody>[/TABLE]

For your example, I would have thought this would work.

 

[Dave Patton]

You could have provided a complete example with expected results.

Excel 2010
	A	B	C	D	E	F	G	H
1	Purchase	Packaging	Shipping	Cost	VAT	Amazon	Profit	Selling
2	2.00	0.05	1.40	3.45	0.20	0.15	0.00	5.31
3	2.00	0.05	1.40	3.45	1.06	0.80	0.00	5.31
1d

Cell Formulas
Range	Formula
H2	=(Cost+Profit)/(1-(VAT+Amazon))
D2	=SUM(A2:C2)
D3	=SUM(A3:C3)
E3	=H3*VAT
F3	=H3*Amazon
G3	=H3-SUM(D3:F3)

Named Ranges
Name	Refers To	Cells
Amazon	='1d'!$F$2
Cost	='1d'!$D$2
Profit	='1d'!$G$2
VAT	='1d'!$E$2
z	=zList!$A$2