Margin Calculations

aprd01

New Member
Joined
Jan 12, 2017
Messages
7
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
Joined
Apr 11, 2010
Messages
11,005
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

100120150.2104.8
10012515.6250.2109.175
10013016.250.2113.55
10014017.50.2122.3
10013817.250.2120.55100137.3714
100137.517.18750.2120.1125
100137.417.1750.2120.025
100137.3817.17250.2120.0075
100137.37817.172250.2120.0058
100137.37517.171880.2120.0031
100137.37217.17150.2120.0005
100137.37117.171380.2119.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
Joined
Dec 27, 2016
Messages
178
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

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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]

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

aprd01

New Member
Joined
Jan 12, 2017
Messages
7
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
Joined
Jan 12, 2017
Messages
7

ADVERTISEMENT

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
Joined
Jan 12, 2017
Messages
7
This is what i am currently trying to do:

Margin Website Price Ex VATPayPal Fee %Ebay Fixed FeeTotal Website FeesNet Margin % (1st Item Only)Net Profit 1st ItemTotal Website Fees 2 ItemsNet Margin % (2+ Items)Net Profit on second item +Ebay Item Cost + VAT
125.00%18.372.80%£0.20£0.8225.00%£2.47£0.8277.63%£7.6722.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
Joined
Jan 12, 2017
Messages
7
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,
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top