Marketplace Profit Calculator - Calculating Variable Costs

[crwilson84]

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.

 

[crwilson84]

...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.

 

[crwilson84]

...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.

 

[shg]

I think this is correct:

Row\Col	A​	B​	C​
1​	Cost	$ 0.80​
2​
3​	Cost-Based Amounts
4​	Craigmarloch	10.00%​
5​
6​	Fixed Amounts
7​	Docs	$ 0.05​
8​	Royal Mail	$ 2.27​
9​	PayPal	$ 0.20​
10​
11​	Sell-Based Amounts
12​	EBay	10.00%​
13​	PayPal	1.40%​
14​	eSeller	1.00%​
15​	VAT	16.67%​
16​	Gross Margin	20.00%​
17​
18​	Sell	$ 6.68​	B18: =(B1 * (1+B4) + SUM(B7:B9)) / (100% - SUM(B12:B16))

 

[crwilson84]

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.

 

[shg]

Cleaned-up version at https://app.box.com/s/6m4mvgx6yw17mlb8z15a