Solved: Emergency...gross margin algorithm!

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.:doh:

sample workbook is always make us to help you faster :)

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.

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.

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.

You want to round it as well

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

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.

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.

Quote:

---

Originally Posted by xld

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. :thumb

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.:beerchug: