Marketplace Profit Calculator - Calculating Variable Costs

[Hiten Vakharia]

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

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]

Amazon fee​

[/td][td]

15.00%​

[/td][td]B1: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

VAT​

[/td][td]

16.67%​

[/td][td]B2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

Margin​

[/td][td]

20.00%​

[/td][td]B3: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

Post & Packing​

[/td][td]

$2.30​

[/td][td]B4: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

Cost​

[/td][td]

$10.00​

[/td][td]B5: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

Sell​

[/td][td="bgcolor:#CCFFCC"]

$25.45​

[/td][td="bgcolor:#CCFFCC"]B6: =(B5 + B4) / (1 - SUM(B1:B3))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Check:[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

Amazon takes​

[/td][td="bgcolor:#E5E5E5"]

$3.82​

[/td][td]B9: =B1 * B6[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

Taxes take​

[/td][td="bgcolor:#E5E5E5"]

$4.24​

[/td][td]B10: =B2 * B6[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

Post & package​

[/td][td="bgcolor:#E5E5E5"]

$2.30​

[/td][td]B11: =B4[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

You get​

[/td][td="bgcolor:#E5E5E5"]

$15.09​

[/td][td]B12: =B6 - SUM(B9:B11)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

Profit​

[/td][td="bgcolor:#E5E5E5"]

$5.09​

[/td][td]B13: =B12 - B5[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

Margin​

[/td][td="bgcolor:#CCFFFF"]

20.00%​

[/td][td="bgcolor:#CCFFFF"]B14: =B13 / B6[/td][/tr]
[/table]

 

[Hiten Vakharia]

Great, Many thanks shg

Would you have the same for eBay?

Regards,
Hiten

 

[shg]

I don't have any information for eBay. I didn't have any information for Amazon until you posted it.

 

[Hiten Vakharia]

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

 

[shg]

You're welcome.

 

[Hiten Vakharia]

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

 

[Hiten Vakharia]

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

 

[shg]

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?

 

[shg]

I'll let you plug in the numbers:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td]Sell-Based Percentages[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

eBay​

[/td][td]

15.00%​

[/td][td]B2: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

VAT​

[/td][td]

16.67%​

[/td][td]B3: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

PayPal​

[/td][td]

2.90%​

[/td][td]B4: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

Margin​

[/td][td]

20.00%​

[/td][td]B5: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Fixed Costs[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

Post & Packing​

[/td][td]

$2.30​

[/td][td]B7: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

PayPal​

[/td][td]

$0.30​

[/td][td]B8: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Cost[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

Direct Cost​

[/td][td]

$10.00​

[/td][td]B10: Input[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Sell Price[/td][td="bgcolor:#CCFFCC"]

$27.73​

[/td][td="bgcolor:#CCFFCC"]B12: =(B10 + SUM(B7:B8)) / (1 - SUM(B2:B5))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Check:[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

eBay​

[/td][td="bgcolor:#E5E5E5"]

$4.16​

[/td][td]B15: =B2 * B12[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

VAT​

[/td][td="bgcolor:#E5E5E5"]

$4.62​

[/td][td]B16: =B3 * B12[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]

PayPal (variable)​

[/td][td="bgcolor:#E5E5E5"]

$0.80​

[/td][td]B17: =B4 * B12[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]

Post & Packing​

[/td][td="bgcolor:#E5E5E5"]

$2.30​

[/td][td]B18: =B7[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

19​

[/td][td]

PayPal (fixed)​

[/td][td="bgcolor:#E5E5E5"]

$0.30​

[/td][td]B19: =B8[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

20​

[/td][td]

Your Net​

[/td][td="bgcolor:#E5E5E5"]

$15.55​

[/td][td]B20: =B12 - SUM(B15:B19)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

21​

[/td][td]

Profit​

[/td][td="bgcolor:#E5E5E5"]

$5.55​

[/td][td]B21: =B20 - B10[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

22​

[/td][td]

Margin​

[/td][td="bgcolor:#CCFFFF"]

20.00%​

[/td][td="bgcolor:#CCFFFF"]B22: =B21 / B12[/td][/tr]
[/table]