# Margin Calculations

#### aprd01

##### New Member
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### oldbrewer

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

##### Board Regular
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>

JKLMNO
3VariableValue
4mrkUP120.0%
5VAT20.0%
6eBayPrc12.5%
7eBayFixed 0.20
8
9
10CostFullPriceeBayexVATProfit%
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

</tbody>

Worksheet Formulas
CellFormula
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]

</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
eBayFixed=\$K\$7
eBayPrc=\$K\$6
mrkUP=\$K\$4
VAT=\$K\$5

</tbody>

<tbody>
</tbody>

#### aprd01

##### New Member
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

##### New Member

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

##### New Member
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

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

#### aprd01

##### New Member
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,

Replies
6
Views
802
Replies
4
Views
821
L
Replies
1
Views
316
Replies
0
Views
206
Replies
9
Views
344

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,494
Messages
5,832,018
Members
430,104
Latest member
briannnnleong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back