Calculating Asking Price Based on Cost of Goods Sold

I am a reseller and I am looking to automate the process of pricing the items I put up for sale. I am trying to find an Excel formula that will do the following calculation. I am trying to calculate my asking price based on the price I buy something for. The Ebay fee structure makes this complicated. I am looking for a formula that I can apply to every row in an inventory list.

Here is what I am trying to do.

Calculate sales price "X"

X = The price I ask for my product

Cost of goods sold = my purchase price + Ebay and Paypal fees

Ebay fees being 12% of sales price between $0 and $50 + 6% of $50.01 - $1000 + 2% of $1000.01 and above + 3% Paypal fee

+ 15% profit margin

In other words:

X = Cost of goods sold + profit margin

I want to enter my purchase price of a product and have excel spit out a sales price that covers all costs and allows for a 15% margin. Is this possible?

I know this is complex, thanks in advance.

I have found a lot of Ebay fee calculators. This is not what I’m looking for. I am looking for a formula that turns the price I pay for a product into an Ebay sales price.

ConneXionLost's formula and mine will give you the same result, but if ConneXionLost I think you typed the wrong percentage for the category of 50.01 to 1000.

Here is my formula:

Please Login or Register  to view this content.

and here is the updated version of ConneXionLost's (which I think is more efficient):

Please Login or Register  to view this content.

abousetta

Thank you both for your responses. I am somewhat familiar with Excel, but user error is highly likely here.

Here is what I'm getting from your formulas after I replaced "A2" with the cell containing my cost for an item:

My cost = $1500
Result = $1800

My other spreadsheet where I have been calculating profit margin individually gives me the following results. The fields I enter on my own spreadsheet are the "cost" field and "asking price" field.:

Cost = $1500
Asking Price = $1800

The following fields are automatically calculated:

Ebay fees: $79.00 ------ ($50*.12) + ($950*.06) + ($800*.02) = $79.00
Paypal Fees: $54.00
Total Fees + item cost = $1633.00
Total Profit = $167.00
Profit Margin (Margin on Cost) = 11.13% ( $167.00 / $1500 )
The margin on revenue is even less.

I'm looking for a 15% margin, or at least something I can repeatedly and accurately calculate. The formula you guys gave me seems to spit out a different profit margin each time.

Am I doing something wrong?

Thanks again, I'm just about at my wits end... and it's past 4am local time.

You should try the Solver Add-In, this is tailor made for these kinds of problems.

Once you download it, use it on the attached spreadsheet. Change the 'cost' value, then run the solver tool - with the following parameters:

Set Target Cell: C12
Equal to: Value: 0.15
By changing cells: C9

Each time you put a new cost in, you can re-run this to give you your optimum sale price. You can also use to to make adjustments to your profit margin %.

@abousetta, thanks for your help. The circular nature of this problem is what is giving me the problems.

Basically I'm attempting to do algebra with excel... and it's kicking my butt.

@brokenbiscuits and scottylad2

Brokenbiscuits' table is exactly what my existing Ebay fee calculator does. I set up a table and it accurately calculates the fees and gives me a profit margin when I manually enter a sale price.

I plugged scottylad2's formula into brokenbiscuits' solver sheet and these are the results I got for sample "cost" entries:

$500 -- Result: $650 -- 19% Profit Margin (Note: any sales price below $1000 gives a negative figure on the 2% ebay fee range on the table.
$1000 -- Result: $1200 -- 9% Profit Margin
$1500 -- Result: $1800 -- 11% Profit Margin
$2000 -- Result: $2400 -- 12% Profit Margin
$4000 -- Result: $4800 -- 13% Profit Margin

Once the sales price gets above $1000, the calculated profit seems to increase linearly -- $200 / $1000.

The progressive nature of ebay fees should make a curve if you were to plot the profit in $'s on a graph. Below is what my ebay profit calculator gives me for the examples above. Note the sales prices from my calculations are not exact because I have to keep plugging in sales price figures until the calculated profit is reasonably close to 15% -- the tedium I am trying to avoid.

$1000 -- Result: $1255 – 14.86% Profit Margin
$1500 -- Result: $1860 – 14.89% Profit Margin
$2000 -- Result: $2468 – 15.05% Profit Margin
$4000 -- Result: $4885 – 14.93% Profit Margin

I am trying to automate the process so I'm not having to guess 4-5 times before I get the profit margin I want. Even if there is a “patch” formula that will be the calculated sales price close to the 15% profit margin I’ll be happy with it.

Thanks again everyone…. I know this is a tough one.

Hi,

This is close I can get. You need someone with a history of doing algebra (as you said) with Excel. I think you could probably do this with vba code but it would take a lot of head scratching and trial and error (at least for me).

Hope this puts you in the right direction.

abousetta

Did you run the Solver Add-In?

Because if so, I don't see why this wouldn't work for you, it does exactly what you're asking for.

Hi buddy,

OK... after a lot of head scratching and caffene pick me ups, I have written some code that seems to calculate what you are looking for pretty well, but I have a few questions:

1) What is the price range of items you buy (e.g. $100 - $5000)?

2) Do you mind if the results are per 25 cents or do you need them per cent? For example from my calculations, if you bought the item for $150.25 then you will need to sell it for $93.25 to get a net profit of 15.05%. Is this fine or are you looking for an exact 15%.

3) I am assuming that you sell items for a round number (e.g. $100 but not $100.03). Is this correct.

The reason I am asking these questions is to help tweak the code. The more precise you want the results the longer it will take to run the code. At the same time there is no reason to give you data that you will not use.

Please let me know and I will finalize the vba code.

abousetta