Solved: Emergency...gross margin algorithm!

[DanOfEarth]

We just sent out excel sheets that would automatically mark-up our price sheets based on 10-50% or whatever they typed into the input box. However the customers need a an input box that they can type in and give them a predetermined margin (20%, etc.)

Anybody knows how to figure a gross margin (1- cost divided by price), but what's the backwards equation to calculate a predetermined margin for a spreadsheet. I know it's gotta be simple, but frankly I've never used it...or is it a function I'm not aware of.

This was a stupidly huge error.

[MaximS]

sample workbook is always make us to help you faster

[DanOfEarth]

Ok,

Hit the Markup tab. Those "Mark-up" input boxes need to be "Margin".

By the way, if any of the sheets are protected, the password is lowercase "dan".

And of course, the buttons are disabled.

[DanOfEarth]

Another way to ask the question is, if I want a 40% margin on a product that costs me $100.00, then how much do I need to mark that up.

People's initial answer is $140.00. No...that's not accurate. That gives me a 38.5% margin.

what would be the equation?

Usually people do this by trial and error....can't do that here.

[DanOfEarth]

OK....I figured it out. Sorry...I panicked.

Figuring the Gross Margin based on cost

It's:

Selling price = Cost/1-GM%

In other words, if my product costs $100 and the required Gross Margin is 40%, then:

Selling Price = $100/1-40% = $100/0.6 = $166.60

Sorry about the waste of forum space...I have to correct $1.2M of business about to be rolled out on Monday.

[Bob Phillips]

You want to round it as well

=ROUND(Cost/(1-GM%),2)

[DanOfEarth]

Thank you thank you. I forgot about the rounding.

Sadly to say, that little silly formula (or mathematical fluke) right there is not known to most small business men...even accountants. They want a 30% net to the COG bottom line, so they add $30 bucks to a $100 item, when they should be charging $142.20.

I just had a heated argument with the Controller of a $60,000,000 company regarding the above mark-up of items and that exact equation since the last post.

She kept arguing that determining the initial cost of an item using either "Gross Profit Margin" or "percentage Mark-up" isn't any different since it doesn't translate to the general ledger. Jesus...no wonder we're in this big financial mess.

[Bob Phillips]

Yeah, I understand. You want to earn 40% margin on your sales, not your costs.

It is the same with the purchase tax/VAT on an item, if tax is 17.5%, how much of a £100 item is tax. Clue, it ain't £17.50.

[mdmackillop]

It is the same with the purchase tax/VAT on an item, if tax is 17.5%, how much of a £100 item is tax. Clue, it ain't £17.50.

Well I go for 7/47.

[DanOfEarth]

Another chuckle about that. Another (UT MBA college graduate) challenged me with that, so I asked him...if he wanted to make a 100% profit on a $100 item, how much would he have to charge. He quipped "easy...$200"!

After informing him that that would be a massively different 50% profit margin, I didn't have the heart to tell him that:
A. The formula errors as "divided/zero", because...
B. It's impossible to make a 100% profit unless he stole it.

Mercy mercy.....

I'll bet many a pint could be won pubbing in Scotland too with that one.