Help: Profit margin

[dance-evil]

hi there,

I would like to know what's the easy method for working out profi
margin especially for 17,000 products with variation price

all the variation price is at the column C:

but it would be nice to add variation profit margin for each price eg.

lower price (1.00 to 1000) = higher profit margin
higher price = (1001 to 60,000) lower profit margin

thank for ur hel

 

[Nigel]

I assume you have 'cost of product' in column B and your 'sell price' in
column C, starting in row 1 and ending in row 17000.

Compute the profit margin in column D (column C - Column B) the value you
wish to sum..
Then use the SUMIF command using the range in column C, a criteria of
"<=1000" for lower price and ">1000" for the higher price, summing column D

The formula are

=SUMIF(C1:C17000,"<=1000",D117000)
=SUMIF(C1:C17000,">1000",D117000)

Cheers
Nigel

 

[dance-evil]

Actually i'm not very experinece on Excel but i know how to use it i
very basic way, however that is what i'm trying to achieve:

Column A - Supplier's Price
Column B - RRP
Column C - Supplier's price + profit
Column D - profit margin



From Column D - u may see something like this

between price: 0.40 to 50.00 = add 20%
between price: 51.00 to 100.00 = add 15%
between price 101.00 to 200.00 add 10%

note: the price may start from 0.40 to 60,000+



from Column D this should automatically work out & add all profi
margin from Column A on to C. From time to time so i can tweak th
profit margin if need to be so i dont have to worry about Column C....

i hope u can understand what i'm trying to do

 

[Nigel]

The following formula put into Column C will do what you want. (I think ;-)

=IF(A1<=50,A1*1.2,IF(AND(A1>50,A1<=100),A1*1.15,A1*1.1))

It tests if the price in column A for the following....
A1 <= 50 then Column C will be Column A +20%
A1 >50 and <=100 then Column C will be Column A + 15%
A1 >100 then Column C will be Column A + 10%

Copy the formula into C1 and then copy it down for each row with prices

You do not say what should happen if the price is over 200 so the above
assumes +10%.

Since you are fixing the margin based on a cost plus (%) the margin is
fixed, but you can change the formula or put in some reference to another
cell to allow adjustment. Column D is not required unless you wish to show
the profit in value terms in which case put in cell D1 the following

=C1-A1

and copy this down into each row with prices as well.

Cheers
Nigel

 

[dance-evil]

cheers that information but i would like to ask you another question:

A: = price range
B: = profit margin
C: = price list
D: = output with price + profit

Column A

1.00
5.00
10.00
15.00
20.00

Column B
0.5
0.4
0.3
0.2
0.1

Column C
0.59
2.56
9.40
14.44
19.00

Column D
0.88


Now how do i check if C (less then or equal to) match any of those in
and use that specific margin on B?

eg:

0.59 <= 1.00

0.59*0.5 = 0.8