Profit Margin Formula

[Guest]

Howdy, again

Looking for a formula to calculate Profit Margin. Here's the scenario

1. Column A is the cost of each item
2. Columns B, C, D, E, F & G I want to show the selling price at various profit margins. (Column B = 10%, Column C = 15%, Column D = 20%,...... Column G = 35%
3. Remember, profit margin = (Selling Price - Cost) / Selling Pric

Thank you.

 

[JE McGimpsey]

One way:

B1: =$A1/(1-COLUMN()*5%)

Copy across to column G.

 

[adam schwartz]

GE,

Thanks but this doesn't work. As a reminder; Profit Margin =
Cost/Selling Price

 

[JE McGimpsey]

boomer/adam:

First, it's very unhelpful to say "this doesn't work". There are myriad
interpretations of "doesn't work", from "doesn't do anything" to "does
the wrong thing" to "crashes XL". *WHAT* doesn't work. What doesn't it
do that you expect it to do?

Second, you can't justifiably complain about something not working if
you change the definitions mid-stream. From your first post:

Remember, profit margin = (Selling Price - Cost) / Selling Price

from your second post:

As a reminder; Profit Margin = Cost/Selling Price

What I gave you works for your first definition. Your second definition
just doesn't any make sense whatsoever...

When you get your own conception of the problem straightened out, post
back with more detail on what you really want.

 

[adam schwartz]

JE,

Sorry for the confussion. Let's try this again.

I have a simple spreadsheet with roughly 2000 items, let's say. Column
A lists my cost for each item. I want to show the selling price based
on profit margin with the following;

Column B = 10%
Column C = 15%
Column D = 20%
Column E = 25%
Column F = 30%
Column G = 35%

Profit Margin = (Selling Price-Cost)/Selling Price.

Normally you would need the selling price and cost to figure out Profit
Margin but I'm looking for a formulk that figures this out automatically
based on preset Profit Margin percentages.

I hope I explained this more clearly this time.

Adam

(PS - I use $100 to test this formula and get the following, as an
FYI...)

($105.27-$100)/105.27 = 5%

 

[JE McGimpsey]

Which is exactly what the formula I gave you in my first reply returns.
Did you even try it?

 

[adam schwartz]

JE,

What if my cost was located in Column G and I wanted to show Profit
Margins beginning with 10% starting in Column K?

Adam

 

[JE McGimpsey]

Just add an offset to column():

=$G1/(1-5%*(COLUMN()-9))

copy across.

 

[adam schwartz]

JE,

It works! Thank you for all your help and patience.

Best regards,

Adam

 

[GL]

Hi

Just for your information.

(S-C)/S = profit ratio not profit margin

I'm sure you knew it.

Howdy, again,

Looking for a formula to calculate Profit Margin. Here's the scenario;

1. Column A is the cost of each item.
2. Columns B, C, D, E, F & G I want to show the selling price at various

profit margins. (Column B = 10%, Column C = 15%, Column D = 20%,......
Column G = 35%)

 

[JE McGimpsey]

I think you'll find that the term profit margin is as or more often used
for (S-C)/S than profit ratio, and that profit ratio is often defined as
(S-C)/S x 100.

However, the terms are used rather fluidly, and are usually, though not
always, self-evident.