Margin Formula for Excel.

Margin Formula for Excel.

TOPIC CLOSED
TOPIC CLOSED
Author
Discussion

Broccers

Original Poster:

3,236 posts

252 months

Friday 11th November 2005
quotequote all
Guys,

For the last however many years my trusty margin calculator has marked up our prices. We now have a large project on where I need to apply Margin (not mark up) to a set of costs in Excel.

Can anyone tell me the formula for this calculation please.

Simon.

Thelemming

4,319 posts

264 months

Friday 11th November 2005
quotequote all
when you say "apply margin" do you mean you want to work out, from a final price, what your margin is on it?

Ie products sells for £50, you make 10% margin, so you want a formula to return a margin of £5?

Which seems a little too simple, so am I missing something more fundamental?

Its a quiet morning so will happily sort it out for you if you could offer a little more detailed explanation.

Edited to add:

Yep, seems like I was having a retarded moment.



>> Edited by Thelemming on Friday 11th November 14:18

Broccers

Original Poster:

3,236 posts

252 months

Friday 11th November 2005
quotequote all
Margin and Mark up are 2 very different calculations. From your example ;

Cost : £ 50
Margin : 10 percent
Sell is the £ 55.55

Quite a lot of people confuse markup with margin.

In the meantime from posting this a good friend of mine has provided the formula I needed to input in Excel.

For those interested it is :

X=(100/(100-B7))*B6

Where B7 = Margin
B6 = Cost
X = Sell

Eric Mc

121,680 posts

264 months

Friday 11th November 2005
quotequote all
Mark Up is the extra you add to the cost price to achieve your selling price.

Margin is the achieved difference between cost and sale price expressed as a % of the Sale Value.

The two ratios are actually quite inextricably linked.

Alanq

209 posts

283 months

Friday 11th November 2005
quotequote all
Just devide it by the margin you want
eg for 30% margin devide by 0.7

GreenV8S

30,129 posts

283 months

Friday 11th November 2005
quotequote all
Broccers said:

For those interested it is :

X=(100/(100-B7))*B6


Not trying to be funny, but if a simple formula like that isn't obvious to you, I would question whether you are the right person to be working out margins.

Broccers

Original Poster:

3,236 posts

252 months

Friday 11th November 2005
quotequote all
GreenV8S said:
Not trying to be funny, but if a simple formula like that isn't obvious to you, I would question whether you are the right person to be working out margins.


Apologies for not being an expert in Excel - my remit as company owner doesn't extend that far.

Thanks for the constructive post.

GreenV8S

30,129 posts

283 months

Friday 11th November 2005
quotequote all
Broccers said:
GreenV8S said:
Not trying to be funny, but if a simple formula like that isn't obvious to you, I would question whether you are the right person to be working out margins.


Apologies for not being an expert in Excel - my remit as company owner doesn't extend that far.

Thanks for the constructive post.



No disrespect intended, not everyone is a computer nerd or happy playing with numbers. From your posts I have got the impression that you needed help with the equation itself as well as help expressing it as an Excel formula. If that's true, then it strikes me as risky for you to work out the margins on your own. The good thing about computers is that they make it very easy to get the right answer. The bad thing is that they make it very easy to get the wrong answer too, and unless your common sense and mental arithmetic is up to the job, it's all too easy for mistakes to go unnoticed - for some reason people seem to trust computers not to make mistakes, but they are only as good as the person programming them and people always make mistakes. This sounds like something that is probably important to get right, hence my comment. If that offends you, I apologize.

roadsweeper

3,786 posts

273 months

Saturday 12th November 2005
quotequote all
GreenV8S said:
apologise.

You are not American!

(I hope! )

cccs

365 posts

226 months

Saturday 12th November 2005
quotequote all
[quote=Broccers]"Apologies for not being an expert in Excel - my remit as company owner doesn't extend that far.

Thanks for the constructive post."

You asked for advice and didn't get the most accurate or best articulated answers, re-read your explanation of margin and mark up and see how clear you were.


Don't be so abusive!

GreenV8S

30,129 posts

283 months

Saturday 12th November 2005
quotequote all
roadsweeper said:
GreenV8S said:
apologise.

You are not American!

(I hope! )


Oops! No not American but I do work for a faceless American corporation, and some of their spelling foibles have rubbed off ...

sorry
TOPIC CLOSED
TOPIC CLOSED