Work out buying price by selling price and profit margin

[Johnny2by4]

Hi all,


I've had this challenge that I've not yet been able to figure out.

I'm trying to figure out a formula (to use in excel) that will work out the amount I should pay which depends on the current market selling price and profit margin.

E.G. If an item is selling on the market for £140 and I want to make 40% profit, I'll need to buy at £140/1.4 = £100

Unfortunately, it’s not that simple as there are selling costs of 13.4% of the sale price and buying costs of 40.5% of the buy price.
I'm struggling to work out how to find the ideal buy price based on profit margin.

If anyone can shed some light that would be amazing!

Thanks!

 

[tkhunny]

Build It.

Buy Price + Buying Fee + Profit Margin + Selling Fee = Sell Price

Translate each piece.

Buying Fee = 0.405 * Buy Price
Profit Margin = 0.40 * Buy Price
Selling Fee = 0.134 * Sell Price

********************** Warning! Digression Approaching *********************
** That is A LOT of work for one Sell Price to do. Yikes! Will people pay it? **
****************************** End of Digression ******************************

Put it all together.

Buy Price + (0.405 * Buy Price) + (0.40 * Buy Price) + (0.134 * Sell Price) = Sell Price

Some Algebra.

Buy Price + (0.405 * Buy Price) + (0.40 * Buy Price) = Sell Price - (0.134 * Sell Price)
Buy Price * (1 + 0.405 + 0.40) = Sell Price * (1 - 0.134)
Buy Price * (1.805) = Sell Price * (0.866)
Buy Price * 1.805 / 0.866 = Sell Price
Also
Sell Price = Buy Price * 0.866 / 1.805

Example #1 -- I know the Buy Price
Buy Price = 100.00
Buying Fee = 0.405 * 100.00 = 40.5
Profit Margin = 0.40 * 100.00 = 40.00
Sell Price = 100.00 * 1.805 / 0.866 = 208.43
Selling Fee = 0.134 * 208.43 = 27.93
100.00 + 40.50 + 40.00 + 27.93 = 208.43 <== I'm quite surprised there is NO rounding error. This will be the case for some examples.

Example #2 -- I know the Sell Price
Sell Price = 140.00
Selling Fee = 0.134 * 140.00 = 18.76
Buy Price = 140 * 0.866 / 1.805 = 67.16
Buying Fee = 0.405 * 67.16 = 27.20
Profit Margin = 0.40 * 67.16 = 26.86
67.16 + 27.20 + 26.86 + 18.76 = 139.98 <== You can expect some rounding error, but it should not amount to much more then a tuppence.