Calculate Margins

Discussion in 'Microsoft Excel Misc' started by SeeStation, Sep 19, 2008.

  1. SeeStation

    SeeStation Guest

    I need to calculate gross margins and prices at different levels of GM:

    Non-Excel Formula to calculate Selling price using gross margin
    Selling Price = Cost / (1-GM%)

    For example, if your product costs $100 and the required gross margin is
    40%, then your Selling Price = $100/(1-0.4) = $100/0.6 = $166.6


    So in my worksheet E16=cost E17=Current Selling Price
    I want to put my current GM in F17,
    I want to get a selling price for GM at:
    15% in G16
    20% in H16
    25% in I16
    30% in J16
    35% in K16

    Any ideas?
     
    SeeStation, Sep 19, 2008
    #1
    1. Advertisements

  2. SeeStation

    FSt1 Guest

    hi,
    you already have the formula
    if cost = e16 and gm = f17 then formula in e17 would be
    =e16/(1-f17)

    the other formula would be much the same except using the % instead of a
    cell reference.
    in g17, =e16/(1-.15)
    in h17, =e16/(1-.2)
    in i17, =e16.(1-.25)
    ect, ect

    regards
    FSt1

    "SeeStation" wrote:

    > I need to calculate gross margins and prices at different levels of GM:
    >
    > Non-Excel Formula to calculate Selling price using gross margin
    > Selling Price = Cost / (1-GM%)
    >
    > For example, if your product costs $100 and the required gross margin is
    > 40%, then your Selling Price = $100/(1-0.4) = $100/0.6 = $166.6
    >
    >
    > So in my worksheet E16=cost E17=Current Selling Price
    > I want to put my current GM in F17,
    > I want to get a selling price for GM at:
    > 15% in G16
    > 20% in H16
    > 25% in I16
    > 30% in J16
    > 35% in K16
    >
    > Any ideas?
     
    FSt1, Sep 19, 2008
    #2
    1. Advertisements

  3. SeeStation

    SeeStation Guest

    Thanks for your quick reply. I had figured that one out before I was able to
    get back here and mark it as solved.
    Sometimes you just have to go back to the old pencil and paper and map it
    all out. (or at least NotePad)

    "FSt1" wrote:

    > hi,
    > you already have the formula
    > if cost = e16 and gm = f17 then formula in e17 would be
    > =e16/(1-f17)
    >
    > the other formula would be much the same except using the % instead of a
    > cell reference.
    > in g17, =e16/(1-.15)
    > in h17, =e16/(1-.2)
    > in i17, =e16.(1-.25)
    > ect, ect
    >
    > regards
    > FSt1
    >
    > "SeeStation" wrote:
    >
    > > I need to calculate gross margins and prices at different levels of GM:
    > >
    > > Non-Excel Formula to calculate Selling price using gross margin
    > > Selling Price = Cost / (1-GM%)
    > >
    > > For example, if your product costs $100 and the required gross margin is
    > > 40%, then your Selling Price = $100/(1-0.4) = $100/0.6 = $166.6
    > >
    > >
    > > So in my worksheet E16=cost E17=Current Selling Price
    > > I want to put my current GM in F17,
    > > I want to get a selling price for GM at:
    > > 15% in G16
    > > 20% in H16
    > > 25% in I16
    > > 30% in J16
    > > 35% in K16
    > >
    > > Any ideas?
     
    SeeStation, Sep 19, 2008
    #3
  4. SeeStation

    FSt1 Guest

    hi
    i thought that was a tab simple. but glad to try and help anyway.
    thanks for the feedback.
    regards
    FSt1

    "SeeStation" wrote:

    > Thanks for your quick reply. I had figured that one out before I was able to
    > get back here and mark it as solved.
    > Sometimes you just have to go back to the old pencil and paper and map it
    > all out. (or at least NotePad)
    >
    > "FSt1" wrote:
    >
    > > hi,
    > > you already have the formula
    > > if cost = e16 and gm = f17 then formula in e17 would be
    > > =e16/(1-f17)
    > >
    > > the other formula would be much the same except using the % instead of a
    > > cell reference.
    > > in g17, =e16/(1-.15)
    > > in h17, =e16/(1-.2)
    > > in i17, =e16.(1-.25)
    > > ect, ect
    > >
    > > regards
    > > FSt1
    > >
    > > "SeeStation" wrote:
    > >
    > > > I need to calculate gross margins and prices at different levels of GM:
    > > >
    > > > Non-Excel Formula to calculate Selling price using gross margin
    > > > Selling Price = Cost / (1-GM%)
    > > >
    > > > For example, if your product costs $100 and the required gross margin is
    > > > 40%, then your Selling Price = $100/(1-0.4) = $100/0.6 = $166.6
    > > >
    > > >
    > > > So in my worksheet E16=cost E17=Current Selling Price
    > > > I want to put my current GM in F17,
    > > > I want to get a selling price for GM at:
    > > > 15% in G16
    > > > 20% in H16
    > > > 25% in I16
    > > > 30% in J16
    > > > 35% in K16
    > > >
    > > > Any ideas?
     
    FSt1, Sep 19, 2008
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marilyn

    Cell Margins

    Marilyn, Jul 11, 2003, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    11,379
    Tim C
    Jul 11, 2003
  2. Craig

    Excel 97 Margins do not fit page size

    Craig, Jul 30, 2003, in forum: Microsoft Excel Misc
    Replies:
    0
    Views:
    250
    Craig
    Jul 30, 2003
  3. Justin

    Margins do not fit page size! ERROR

    Justin, Aug 12, 2003, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    470
    Geoff
    Aug 12, 2003
  4. Bernie Deitrick

    Re: Calculate once - "lookup" value once and never calculate again

    Bernie Deitrick, Jul 15, 2004, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    425
  5. Wanna Learn

    calculate time does not calculate

    Wanna Learn, Aug 18, 2008, in forum: Microsoft Excel Misc
    Replies:
    2
    Views:
    302
    MillyD
    Aug 18, 2008
Loading...

Share This Page