Calculating a selling price from a given margin.

I would take a few minutes with paper and pencil and write out your formulas. Then use the algebra that I'm sure you remember from back in middle school to solve for selling price and contribution given cost price and margin. Then go back into your spreadsheet and enter the appropriate formulas.

Originally Posted by rwl518p

I have a spresadsheet that calculates our online selling prices. We input a cost and a selling price and it calculates a margin and contribution. All works fine, but I would like to enter a cost price and a margin and for it to return a selling price and contribution.
ATTACHED

See the worksheets "net rev margin" and "net profit margin" in the file "rwl sell price.xls" (click here), which you can download from https://app.box.com/s/3okzivh4tl4djhl74vq7i1wlg4lcfw4q.

In your original file, it appears that you want to determine the selling price based on what I call the "net revenue before COGS margin" (COGS = cost of goods sold), where "net revenue" is the selling price less costs except for the cost of the item sold (COGS).

Those are not really the correct terms to use. But I cannot think of anything better at the moment.

For that purpose, the formula in B3 (selling price) in the "net rev margin" worksheet is:

=ROUND((B1/(1-E3) + B12 + MAX(B10:E10)) / (1/(1+E1) - E2), 2)

where E1 is the %VAT, E2 is the %fees, and E3 is the required %margin.

Since you have some variable costs (B10:E10), I assume you want E3 to be interpreted as a minimum %margin. So the formula calculates the selling price for most expensive shipping method. That will result in the maximum price in B3.

As a business decision, you might prefer to apply the %margin requirement to a particular shipping method (e.g. the most common) so you can offer a lower price. That will result in lower %margins for more expensive shipping methods. In that case, replace MAX(B10:E10) with the shipping cost of choice, e.g. E10.

And in that case, the "net revenue before COGS" in B16 might be negative. Consequently, the %margin calculation in B21:E21 might be undesirable, since it would result in a positive %margin due to the ratio of two negative numbers. Since I would prefer to see a negative %margin in that case, I changed the formulas in B21:E21 as follows (in B21 for example):

=SIGN(B19) * ABS(B19/B16)

Note that it works as expected in the normal case when B16 is positive and B19 is positive or negative.

[EDIT] To reiterate, this is just a personal preference. When B16 is negative as well as B19, it is mathematically correct for the %margin to be positive.

FYI, one other change: all calculated currency amounts are rounded to the cent, since those are amounts that we actually pay or charge. The need to round percentage calculations should be obvious. I also round simple addition and subtraction calculations to avoid infinitesimal anomalies that arise in computer binary arithmetic. Even though they are small, such anomalies sometimes cause surprises when aggregated with other calculations.

-----

On the other hand, you might have intended to calculate the normal net profit margin, which is the selling price less all costs, including the cost of the item sold. To that end, your original formulas in B21:E21 are incorrect. The correct calculation is in B22:E22. And we want determine the selling price based the normal net profit.

For that purpose, the formula in B3 (selling price) in the "net profit margin" worksheet is:

=ROUND((B1 + B12 + MAX(B10:E10)) / (1/(1+E1) - E2 - E3), 2)