Excel Formula-Figuring Sell Price based on Cost 4

[rcrecelius]

I have a SS with over 16000 part numbers and our cost. I want to calculate our selling price based on the cost of each item. Ex...
if the item costs less than 2.00, markup 45%(/.55)
2.01-3.00 markup 40%(/.6)
3.01-4.00 markup 34%(/.66)
The percentages vary on up from there but you get the picture.
I could do this by creating other columns but I would like to know if this is possible in a single formula?

 

[jfhewitt]

Am I missing something? Don't you get the cost plus markup by multiplying the cost by 1.xx? Like, 40% of 100 (100*1.40) equals 140.

 

[vaneagle]

I agree with Kenwright...in relation to how it should be calculated.

If Cost is $100
Markup say 40%

Option A:
you would 100/0.60 = $166.67 at sell
Gross profit of $66.67 ($166.67-$100)
with a rate of 40% (($166.67-$100)/$166.67).

Option B:
100*1.4 = $140 at sell
Gross Profit = $40 ($140-$100)
With a rate of 28.57% (($140-$100)/$140)).

Difference between the A and B is that you would be $26.67 worse off with Option B.

Ootion A is the correct calc for markup calculations which has been shown in the posts above. So the vlookup table is correct.

Value Markup
0.00 .55
2.01 .6
3.01 .64
4.01 .66

It just means that the calulation may need checking to come up with the correct sell value.

Regards,

vaneagle

 

[jfhewitt]

Maybe you guys should go to work for the Office of Management and Budget...a 40% markup resulting in a profit of $66.66 would sure help our economy. We could even get employment statistics up.

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[rcrecelius]

I assure you, we cant make those margins on everything!

 

[KenWright]

But which one was the correct scenario?

>>if the item costs less than 2.00, markup 45%(/.55)

Did you want to:-

a) Markup by 45%
or
b) Divide by 0.55

as each will give you very different answers

Dividing by 0.55 as in b) is the same as 'Marking Up' by 82%

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[rcrecelius]

Sorry, I didnt read one of the previous posts before I replied.
Option A is correct...Around here we may call it a "markup" but it is actually figuring Gross Profit.

 

[KenWright]

OK cheers

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[vaneagle]

Maybe you guys should go to work for the Office of Management and Budget...a 40% markup resulting in a profit of $66.66 would sure help our economy. We could even get employment statistics up.

lol...maybe...


Where i work we use markup and gross profit... so I am familiar with these concepts..

Regards,
vaneagle