Margin Calculations

[aprd01]

Hi,

I currently have an issue where i would like to find what % margin i am making from selling a product on ebay. I would like a column where i can type 20 which will then show me the price i need to sell at in another column to make a 20% margin. The issue comes when i add in the formula to calculate the % which ebay will take. Here are some example costs below

Unit price Ex VAT = £10
Shipping Ex VAT = £1

I would like to make 20% on the above calculations however, ebay will take 12.5% & £0.20 of the final value including VAT.

So when i calculate 10 + 1 = 11. 11 *1.2 = £13.2. This would usually give me 20%, however ebay now take 12.5% + £0.20 of the final value fee including vat so:

13.2*1.2 = £15.84.
(15.84 / 100 *12.5) +0.2 = £2.18
11 - 13.2 = £2.2
£2.18 - £2.2 = £0.02

As a result once the ebay fee is taken i would be left with £0.02 profit which is a margin of 0.0015%

I know this is tricky to understand but any help would be greatly appreciate,

Kind Regards Dan

 

[oldbrewer]

Hi,

I currently have an issue where i would like to find what % margin i am making from selling a product on ebay. I would like a column where i can type 20 which will then show me the price i need to sell at in another column to make a 20% margin. The issue comes when i add in the formula to calculate the % which ebay will take. Here are some example costs below

Unit price Ex VAT = £10
Shipping Ex VAT = £1

I would like to make 20% on the above calculations however, ebay will take 12.5% & £0.20 of the final value including VAT.

So when i calculate 10 + 1 = 11. 11 *1.2 = £13.2. This would usually give me 20%, however ebay now take 12.5% + £0.20 of the final value fee including vat so:

13.2*1.2 = £15.84.
(15.84 / 100 *12.5) +0.2 = £2.18
11 - 13.2 = £2.2
£2.18 - £2.2 = £0.02

As a result once the ebay fee is taken i would be left with £0.02 profit which is a margin of 0.0015%

I know this is tricky to understand but any help would be greatly appreciate,

Kind Regards Dan

100	120	15	0.2	104.8
100	125	15.625	0.2	109.175
100	130	16.25	0.2	113.55
100	140	17.5	0.2	122.3
100	138	17.25	0.2	120.55			100	137.3714
100	137.5	17.1875	0.2	120.1125
100	137.4	17.175	0.2	120.025
100	137.38	17.1725	0.2	120.0075
100	137.378	17.17225	0.2	120.0058
100	137.375	17.17188	0.2	120.0031
100	137.372	17.1715	0.2	120.0005
100	137.371	17.17138	0.2	119.9996
	the table shows the answer for a cost price of 100
	the formula on the right gets the right answer
	=((1.2*H5)+0.2)/0.875
	the 100 is in H5

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[FranzV]

You could also use a formula that takes the values as variables in case you want to calculate with a different markup or eBay changes the fee. The formula in the column [FullPrice] (K11:K15) calculates the suggested retail price using the variables in K4:K7 and the [Cost] column (J11:J15). The formulas in columns [eBay], [exVAT], [Profit], and [%] are not used to calculate the price, but rather serve as a way of checking that the MarkUp is indeed as expected.

The usefull formula is:

RetailPrice	=((([Cost]*mrkUP)+eBayFixed)*(1+VAT))/(1-eBayPrc*(1+VAT))

<tbody>
</tbody>

	J	K	L	M	N	O
3	Variable	Value
4	mrkUP	120.0%
5	VAT	20.0%
6	eBayPrc	12.5%
7	eBayFixed	0.20
8
9
10	Cost	FullPrice	eBay	exVAT	Profit	%
11	10.00	17.22	2.35	14.35	2.00	0.20
12	11.00	18.92	2.56	15.76	2.20	0.20
13	20.00	34.16	4.47	28.47	4.00	0.20
14	35.00	59.58	7.65	49.65	7.00	0.20
15	100.00	169.69	21.41	141.41	20.00	0.20

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

Worksheet Formulas Cell Formula K11 =((([Cost]*mrkUP)+eBayFixed)*(1+VAT))/(1-eBayPrc*(1+VAT)) L11 =[FullPrice]*eBayPrc+eBayFixed M11 =[FullPrice]/(1+VAT) N11 =[exVAT]-[eBay]-[Cost] O11 =[Profit]/[Cost] <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To eBayFixed =$K$7 eBayPrc =$K$6 mrkUP =$K$4 VAT =$K$5 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[aprd01]

Hi there, quick question. Say if there is a £0.85 shipping charge how i would i implement this to this above?

Kind Regards Dan

 

[aprd01]

Okay i have actually managed to do that, the issue i have now is:

If the price is under £10 i would like to add a shipping charge of £5, otherwise there is no shipping charge.

Kind Regards Dan

 

[aprd01]

This is what i am currently trying to do:

Margin	Website Price Ex VAT	PayPal Fee %	Ebay Fixed Fee	Total Website Fees	Net Margin % (1st Item Only)	Net Profit 1st Item	Total Website Fees 2 Items	Net Margin % (2+ Items)	Net Profit on second item +	Ebay Item Cost + VAT
125.00%	18.37	2.80%	£0.20	£0.82	25.00%	£2.47	£0.82	77.63%	£7.67	22.04

<colgroup><col><col><col><col span="5"><col><col><col></colgroup><tbody>
</tbody>

What i would like to do is for example, if the price is under £100 ex vat then there must be a £4.99 shipping charge otherwise there is no shipping charge. However as you can see above i also need to work the margin out for the amount of products sold, so if i sell 2 products i will only pay shipping on 1. If the cost of 2 items is over £100 there must be no shipping charge otherwise add a £4.99 shipping charge which is built into the margin. I am not sure if i am going about this the rite way but any help would be much appreciated.

Kind Regards Dan

 

[aprd01]

Cost price is 9.54 ex VAT and shipping cost is 5.2 ex vat,

 

[aprd01]

I would like a column which states margin and profit if X x amount of items sold. For example if i sell 10 of this item the margin will be X and profit will be Y. Along side the margin for the sale of a single item,