Marketplace Profit Calculator - Calculating Variable Costs

crwilson84

New Member
Joined
Oct 29, 2013
Messages
11
Hi,

I'm looking for help regarding the file below which I use for calculating our profit margins when selling on marketplaces Amazon and Ebay.

At the moment, with the sample products i've given in the file, I can calculate a profit margin based on manual input of Ebay and Amazon selling prices. However, 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%?

The difficulty lies in the variables. The Amazon or Ebay (+PayPal) fees are a percentage of your final selling fee (eg. Amazon take £3.00, 15%, of an item selling at £20.00). Therefore you only know the Amazon fee after the selling price is present.

Please have a look at the following excel file: http://www.craigmarloch.co.uk/craig/SAMPLE-Profit-Calculator.xls

Any help would be most appreciated, thanks.

Craig.

EDIT: Forgot to point out, you can unhide columns I to R within the excel file to see all the costs i'm calculating.
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
...of course the other variable is taking off the VAT (20%) from the selling price in my calculations. This can only be determined once a selling price has been set.
 
Upvote 0
...of course the other variable is taking off the VAT (20%) from the selling price in my calculations. This can only be determined once a selling price has been set.

To make things simpler, i thought i'd set out an example product below:

Description: 'Plant Product'
Sale of Item on Ebay: £5.99

Cost of Item: £0.80
Cost of Postage: £2.20 (<Royal Mail)
Fuel Surcharge: £0.07 (3% of the £2.20 Royal Mail cost of postage)
Craigmarloch Fee: £0.08 (I pay cost + 10% to my local wholesaler, Craigmarloch)
eSellerPro Fee: £0.06 (I pay 1% of the selling price, total turnover, to our software partner)
Printing & Stationary Cost: £0.05 (nominal fee for printing invoices and using a documents enclosed slip)
Ebay Fee: £0.60 (10% of selling price)
Paypal Fee: £0.28 (1.4% of selling price + £0.20)

Total Cost: £4.14

Selling Price (inc VAT): £5.99
Selling Price (exc VAT): £4.99

Gross Profit: £0.85
Gross Profit Margin: 17.09%


So in this example above i'm looking to see how I can setup a template that would allow me to set the GPM to 20% and the spreadsheet would automatically calculate the selling price needed to achieve this margin. This particular product would need to be priced at £6.26 on Ebay to achieve the desired 20% margin.
 
Upvote 0
I think this is correct:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr]
[tr][td]
1​
[/td][td]Cost[/td][td]
$ 0.80​
[/td][td][/td][/tr]

[tr][td]
2​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
3​
[/td][td]Cost-Based Amounts[/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]Craigmarloch[/td][td]
10.00%​
[/td][td][/td][/tr]

[tr][td]
5​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]Fixed Amounts[/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]Docs[/td][td]
$ 0.05​
[/td][td][/td][/tr]

[tr][td]
8​
[/td][td]Royal Mail[/td][td]
$ 2.27​
[/td][td][/td][/tr]

[tr][td]
9​
[/td][td]PayPal[/td][td]
$ 0.20​
[/td][td][/td][/tr]

[tr][td]
10​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]Sell-Based Amounts[/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]EBay[/td][td]
10.00%​
[/td][td][/td][/tr]

[tr][td]
13​
[/td][td]PayPal[/td][td]
1.40%​
[/td][td][/td][/tr]

[tr][td]
14​
[/td][td]eSeller[/td][td]
1.00%​
[/td][td][/td][/tr]

[tr][td]
15​
[/td][td]VAT[/td][td]
16.67%​
[/td][td][/td][/tr]

[tr][td]
16​
[/td][td]Gross Margin[/td][td]
20.00%​
[/td][td][/td][/tr]

[tr][td]
17​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
18​
[/td][td]Sell[/td][td]
$ 6.68​
[/td][td]B18: =(B1 * (1+B4) + SUM(B7:B9)) / (100% - SUM(B12:B16))[/td][/tr]
[/table]
 
Upvote 0
Hi shg,

Unfortunately this formula results in a #VALUE! error for me. Would it be possible to upload on a spreadsheet so I can take a look? Thanks.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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