How to Calc Selling Cost When % Cost Added To Sell Price?

[kieren12345]

Hi All,

I have a costing problem, that seams simple but I must be missing something.

We buy a product for X and we want to make a certain margin on this product, say %M. We have postage costs say P and finally we have a transaction fee that is charged at the total sell price. So this fee is making it difficult for me to calculate what I need my sale price to be as when I increase the sale price so does the transaction fee.

What I would really like is a formula, so I can put this in our costing sheet on excel.

Any help would be greatly appreciated.

Thanks
Kieren

 

[HallsofIvy]

It's just a matter of adding in the all of those additional costs. The orginal cost to you is M and you want to make a profit of r% of M (I wouldn't say "margin" as that usually includes things like postage and "transaction fees"). That means your price would have to be M+ .01rM= M(1+ .01r). Now add postage and transaction fees to that. If the profit is to be, say, 10% of the price, postage is $0.50, and transaction fees are $2.00, then you should charge (1.1)M+ 2.50.

 

[kieren12345]

Thank you for your reply, but that's not what I am after. Sorry I probabily did not explain it very well.

M= margin %
P= Paypal Transaction Fee
S = Sell Price
R = Postage

What I would like to do is calculate in my price the sell price to include the margin and all costs.

If for instance I want to make 10% on a product that costs £10 and £2 to post. It would be this

(10+2)x(1+0.1) = £13.20 now if we sell at £13.20 then paypal will charge 3.9% +£0.2.

£13.20 *0.039+0.2 =£0.5148. If I then add this to £13.20 to try and cover all costs I get
£13.20 + £0.5148 = £13.7178.
If I then calculate this as sold again and paypal taking their fee it is know £13.71 *0.039+£0.2=£0.7348. as this shows, the current method I have not going to work.

I have tried playing with transposing the formula to get a good model for our spreadsheets, but I am just not knoledgable enough at maths.

Any suggestions please.

 

[DavidSnider]

You have a product, it's 10.00
It costs 2.00 to ship

You want to make a 10% margin on it:

12.00 * (1 + 0.10) = 13.20

Pay pal will charge you 3.9% + 0.2

13.20 * (1 + 0.039) + 0.2 = 13.9148

That number is what you want to charge the customer.

 

[kieren12345]

but 13.9148 * 3.9% +0.20 = 0.7424
0.7424-13.9148 = 13.1723
I have lost 3p out of the margin?

 

[CRGreathouse]

I think I understand what you're asking.

Call the base price B = X + R. You want to mark this up by some amount to get A, the price you tell PayPal; you then get to keep 0.961A - £0.20. (0.961 = 100% - 3.9%.) Your profits are π = 0.961A - £0.20 - B. You want π = M * A.

Set the two equal to each other:
0.961A - £0.20 - B = M * A

Move everything with A to one side and the rest to the other:
0.961A - M * A = £0.20 + B

Factor the left:
A(0.961 - M) = £0.20 + B

Divide:
A = (£0.20 + B) / (0.961 - M)

This is the price you should sell at. In your example, B = £12 and M = 0.1, giving A ≈ £14.17.

 

[DavidSnider]

but 13.9148 * 3.9% +0.20 = 0.7424
0.7424-13.9148 = 13.1723
I have lost 3p out of the margin?

Ahhh I'm sorry, my mistake. CRGreathouse has it right.

 

[kieren12345]

Thanks,

I have tried using the formula and found it is closer without the 0.20...

I have attached a spreadsheet, to check the costing and it is approximatley 1% out across a range of examples.

Please see attached

 

[kieren12345]

Any ideas?

 

[CRGreathouse]

I have tried using the formula and found it is closer without the 0.20...

The formula is exact (with the 0.2); your spreadsheet calculates the margin wrong. The sell price $14.17 and the profit $1.4170, thus 10% margin.

If you want 10% markup rather than 10% profit, you can derive a different formula fairly easily -- just follow my method in post #6.