Marketplace Profit Calculator - Calculating Variable Costs

Hiten Vakharia

Board Regular
Joined
Sep 25, 2013
Messages
66
I'm looking for help regarding calculating profit margins when selling on marketplaces Amazon


I'd like to know if it would be possible to set the profit margin to eg. 20% and work backwards from there; have excel
calculate what my selling price should be to make the 20%?


Fixed Expenses: Amazon fees 15% on Retail Price Set+VAT on Retail Price 20%+Post and Packing £2.30

Any help would be most appreciated, thanks.

Hiten
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
A​
B​
C​
1​
Amazon fee​
15.00%​
B1: Input
2​
VAT​
16.67%​
B2: Input
3​
Margin​
20.00%​
B3: Input
4​
Post & Packing​
$2.30​
B4: Input
5​
Cost​
$10.00​
B5: Input
6​
Sell​
$25.45​
B6: =(B5 + B4) / (1 - SUM(B1:B3))
7​
8​
Check:
9​
Amazon takes​
$3.82​
B9: =B1 * B6
10​
Taxes take​
$4.24​
B10: =B2 * B6
11​
Post & package​
$2.30​
B11: =B4
12​
You get​
$15.09​
B12: =B6 - SUM(B9:B11)
13​
Profit​
$5.09​
B13: =B12 - B5
14​
Margin​
20.00%​
B14: =B13 / B6
 
Upvote 0
I don't have any information for eBay. I didn't have any information for Amazon until you posted it.
 
Upvote 0
Thank you for your reply shg,

I will try to input the eBay data and see if that works else I will let you know the requirements.

Once again, many thanks,

Regards,
Hiten
 
Upvote 0
Hi shg,

There is one more bit on eBay, if you could help!

eg. eBay fees - 10% of selling price, PayPal fees - 0.05% of selling price, VAT - 20%, Margin 20%, Post & Packing - £2.30

How to find selling price, as I was unable to add & calculate the PayPal fees on the spreadsheet

Regards,
Hiten
 
Upvote 0
Hi shg,

Apolgoies!

Please disregard previous post.

There is one more bit on eBay, if you could help!

eg. eBay fees - 10% of selling price,
eg. when selling price is £11.65, PayPay charges is 0.42 - when I try to convert in percentage and then do a check it does not give me the right figure.
VAT - 20%,
Margin 20%,
Post & Packing - £2.30


How to find selling price, as I was unable to add & calculate the PayPal fees on the spreadsheet

Regards,
Hiten
 
Upvote 0
eg. eBay fees - 10% of selling price,
eg. when selling price is £11.65, PayPay charges is 0.42
I get the 10% eBay fee.

In the US, PayPal charges a percentage plus a fixed fee (2.9% + $0.30). What is it for you?
 
Upvote 0
I'll let you plug in the numbers:

A​
B​
C​
1​
Sell-Based Percentages
2​
eBay​
15.00%​
B2: Input
3​
VAT​
16.67%​
B3: Input
4​
PayPal​
2.90%​
B4: Input
5​
Margin​
20.00%​
B5: Input
6​
Fixed Costs
7​
Post & Packing​
$2.30​
B7: Input
8​
PayPal​
$0.30​
B8: Input
9​
Cost
10​
Direct Cost​
$10.00​
B10: Input
11​
12​
Sell Price
$27.73​
B12: =(B10 + SUM(B7:B8)) / (1 - SUM(B2:B5))
13​
14​
Check:
15​
eBay​
$4.16​
B15: =B2 * B12
16​
VAT​
$4.62​
B16: =B3 * B12
17​
PayPal (variable)​
$0.80​
B17: =B4 * B12
18​
Post & Packing​
$2.30​
B18: =B7
19​
PayPal (fixed)​
$0.30​
B19: =B8
20​
Your Net​
$15.55​
B20: =B12 - SUM(B15:B19)
21​
Profit​
$5.55​
B21: =B20 - B10
22​
Margin​
20.00%​
B22: =B21 / B12
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top