+ Reply to Thread
Results 1 to 14 of 14

Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

  1. #1
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    I really need help,

    I am having trouble figuring it out how to add the formula for sales price column to calculate price get Net margin 10% if cost goes up or down want sales prices to calculate as price 10% net margin

    in Net Margin column formula I have

    Sales Price -(Cost+Comm)/ Sales = Net Margin

    up to now I add sales price manually, now I would to add formula to calculate sales price from net margin

    my brain is not working , can some please help me with this, I am sure it must be really easy,

    I have to add this to the file today, I really appreciate all the help in this matter.

    hope my question is not confusing, please let me know if its does not make sense,

    I just want Net margin to stay 10% no matter what the cost is and calculate the sales price from 10% net margin.

    thanks in advance
    Last edited by Bitto; 10-21-2013 at 02:12 PM.

  2. #2
    Forum Contributor
    Join Date
    07-02-2013
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2003 and 2007
    Posts
    197

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    Try this:

    =(Cost+Comm)/(1-Net margin%)

    That will give the sales price

  3. #3
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    thanks for quick reply, I tried using the formula as per your reply, some how it did not work please see attached the work sheet

    on the work sheet I tried to show what I am trying to accomplish, please help.

    from G Column to
    B

  4. #4
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    Sorry did not finish the question
    Sales Cost Commm Net Margin
    B D F G
    10900 6996.52 3161 6.81%

    what I want G Net margin to stay 10%

    new cost

    ? 7500.00 3161 10.00%


    what the sales price will be how can I add this formula using these Net margin

    appreciate all the help

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    Personally, I would solve the problem algebraically, then put it into Excel. Take the formula you gave (Sales Price -(Cost+Comm)/ Sales = Net Margin) and solve for Salesprice. If your algebra is rusty, here's a tutorial for solving basic equations http://www.purplemath.com/modules/solvelin.htm

    If I did the algebra right, it looks like salesprice=netmargin+(cost+comm)/sales
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    A
    B
    C
    D
    E
    1
    Sales
    Cost
    Comm
    Gross Margin
    2
    $ 11,286.13
    $ 6,996.52
    $ 3,161.00
    10%
    A2: =(B2+C2)/(1-D2)
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    Thanks Shg
    for this reply, some how this did help either , when I enter this formula, did not get any amount in sales column, I really donot know what I am doing wrong. I tried everything , I can really use some help.

    I want to add the formula in A column to get the sales price from Net margin as 10%
    what can I Do. C column comm changes according to sales price, that's what I did not add any amount.
    A B C D
    29%
    Sales Cost Comm NetMargin

    10900 6996.52 3161 6.81%

    ? 7,500.00 =(A8-(B8+($C$3*A8)))/A8

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    I'm sorry, I can't follow your question at all.

    If commission varies with sell price, then you need to explain how it is calculated.

  9. #9
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    I am not talking about comm



    A B C D
    29%
    Sales Cost Comm NetMargin

    10900 6996.52 3161 6 .81%

    ? 7,500.00 # =(A8-(B8+($C$3*A8)))/A8



    what I am want to add the formula in Column A to calculate Sales price from Net margin ( D column ) 10 %

    is this possible . how to achieve this . I am going in circle .

    want A column sales price close to 10 % net margin

    thanks again

  10. #10
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    see attached the File, sorry I should have uploaded the file earlier .
    Attached Files Attached Files

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    In B6,

    =D6/(1-F3-10%)

  12. #12
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    thank you very much, you have really saved my day


  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    Please, please, upload a workbook with your first post in future threads.

  14. #14
    Forum Contributor
    Join Date
    01-31-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    124

    Re: Calculate Sales price with Net Margin 10% Goal, if Cost goes up or down

    I will, thanks again

    I really appreciate all the help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Need to calculate retail price based on known cost and percentage
    By rayzorkat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-02-2013, 06:27 PM
  2. Margin, RRP and Cost Price Calculation assistance
    By scottmjohnson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-24-2012, 05:55 AM
  3. Calculate the correct cost when more than 1 cost price
    By CplSmudge in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2012, 01:12 PM
  4. How to calculate a price using goal seek?
    By karo3440 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-25-2011, 03:26 AM
  5. Forumula to calculate cost price ?
    By Digmen1 in forum Excel General
    Replies: 3
    Last Post: 04-16-2008, 08:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1