PDA

View Full Version : Solved: Emergency...gross margin algorithm!

DanOfEarth
02-27-2009, 02:43 PM
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:

MaximS
02-27-2009, 02:48 PM

DanOfEarth
02-27-2009, 02:58 PM
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
02-27-2009, 03:14 PM
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
02-27-2009, 03:27 PM
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.

xld
02-27-2009, 03:44 PM
You want to round it as well

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

DanOfEarth
02-27-2009, 05:10 PM
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.

xld
02-27-2009, 05:44 PM
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
03-01-2009, 07:33 AM
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

DanOfEarth
03-01-2009, 12:17 PM
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: