2 percentage simultaneously

[DavidJill]

Hi, need to calculate prices for Amazon, I'm calculating: cost to acquire + 50% margin + 15% Amazon fee's, And both percentage should adjust accordingly. e.g. my cost to acquire is $50 the results should be $98.69, currently I'm using a website to do the calculations ( https://salecalc.com/amazon?dp=50&c=50&sc=0&p=11&w=0&s=0&sp=0&st=fx&sh=0&pm=off&pt=1 ) but I have a excel sheet with 35,000 items and I'm looking for a formula, function or macro to do the job. Thanks

 

[igold]

Hi DavidJill,

There is something wrong with either the way you are stating your question or the numbers you are using.

In the very first part, you say that you are calculating:

cost to acquire + 50% margin

Are you saying that you want to make a 50% gross margin on your product. If that is the case then given the basic formula for GM%, when you know both your cost price and selling price is:

(Retail - Cost) / Retail or Profit / Retail.

Given that basic formula, if you know your cost and you know your desired Gross Margin and are trying to determine the selling price then the formula is the cost divided by the reciprocal of your desired gross margin % or stated as a formula:

Cost / (1-GM%). In your case if an item costs you $50 and you want a 50% Gross Margin then:

$50/ (1-.5) = $100.

Therefore leaving the Amazon fees out of the equation for the moment, your selling price on an item that costs you $50 and you wish to make a 50% gross margin is $100. The selling price of $98.69 is way off. I looked at the link you supplied and if you click the gross margin radio button at the bottom of the page you will see that the sale price changes to $145.68 to generate a 50% GM after Amazon fees.

If you would like more help please re-state exactly what you are trying determine, and I will happily try to help you...

igold

 

[DavidJill]

Hi igold,

English is not my first language, so I've found a similar thread that may better explain you: http://www.mrexcel.com/forum/excel-questions/873621-how-arrive-sales-price-calculation.html .

I know the cost and I know the desired profit percentage the problem is the Amazon fee's, my desired profit should be included in the Amazon fee's and the Amazon fee's adds up to the desired profit, it can keep on adding and adding.
This link will show you the correct awnser for an example of $50 + 50% desired profit + 15% Amazon fee's: https://salecalc.com/amazon?dp=50&c=50&sc=0&p=11&w=0&s=0&sp=0&st=fx&sh=0&pm=on&pt=1 . And this is the awnser I wanna get en masse in excel.

I hope you'll in better understand now.

Thanks

 

[igold]

If I understand correctly... for an item that costs you $50 and you want to make a 50% desired profit after Amazon takes their 15%, I am defining 50% desired profit as 50% of the sales price. I come up with a sales price of $117.65.

At 117.65 if you deduct the 15% that Amazon takes that would be 117.65 * 85% or $100.

50% of the $100 gives you a profit of $50 and a product cost of $50.

My formula is:

=(Cost/(1-profit percent))/(1-Ebay Percent)

=($50/(1-50%))/(1-15%)

=$100/85%

=$117.6471

Does that make any sense...

igold