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:

[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]

 

[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