Consulting

Results 1 to 10 of 10

Thread: Solved: Emergency...gross margin algorithm!

  1. #1

    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.

  2. #2
    VBAX Mentor MaximS's Avatar
    Joined
    Sep 2008
    Location
    Stoke-On-Trent
    Posts
    360
    Location
    sample workbook is always make us to help you faster

  3. #3
    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.

  4. #4
    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.

  5. #5
    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.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    You want to round it as well

    =ROUND(Cost/(1-GM%),2)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    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.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  10. #10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •