Results 1 to 9 of 9

Math Help - Formula to determine appropriate sales price to meet specific profit margin

  1. #1
    Newbie
    Joined
    Sep 2010
    Posts
    8

    Formula to determine appropriate sales price to meet specific profit margin

    I hope this is the correct forum.

    I have a large inventory. Each item has a different amount for my cost. Currently I use complex spreadsheets to determine pricing and profit margin per. I am trying to come up with a formula that can be applied across the board and nix the spreadsheets. Here are the variables.

    Each product has a cost: product cost
    When I sell the product I have a fee based on the total sales price: seller fee
    I want a specific profit margin: Profit margin

    The only constant in this equation is the product cost. For this exercise lets put the seller's fee at 15% of total sales price, and the desired profit margin at 20%.

    What I would like to do is apply some sort of variable to all products that will determine the appropriate sales price to always return 20% profit margin. The problem is that as the cost of different items gets larger a variable applied doesn't return enough profit. Also, as the sales price rises the fee becomes a larger number. As I said the only constant is the product cost. I am guessing you have to work from that point.

    As an example lets use these numbers:
    product A: product cost $10
    product b: product cost $30
    product c: product cost $100
    product d: product cost $225

    I'm not incredibly smart, but if you can figure it out I think you are.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Junior Member RHandford's Avatar
    Joined
    Sep 2010
    From
    N.E. Lincolnshire
    Posts
    70
    Hi

    Let me get this straight:

    You have a product that had a cost of c.
    Do you add the sales fee and then add 20% profit or do you add the profit and then deduct the sellers fee.
    Perhaps you could give a worked example or email me the spreadsheet
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Sep 2010
    Posts
    8
    Quote Originally Posted by RHandford View Post
    Hi

    Let me get this straight:

    You have a product that had a cost of c.
    Do you add the sales fee and then add 20% profit or do you add the profit and then deduct the sellers fee.
    Perhaps you could give a worked example or email me the spreadsheet

    The seller's fee is deducted as a % of the total sales price. I calculate profit margin as a percentage of total sales price. I kind of work backwards adjusting total sales price to meet the appropriate profit margin. I have a spreadsheet that quickly shows me what each price adjustment returns. Then I have another spreadsheet that I use to analyze all inventory on a weekly basis as my cost per item changes. Unfortunately, they are huge and I can't send. However, example below.

    ex:
    item cost: $15
    seller fee: 15% of total sale price
    The sales price would have to be $22.99.

    $22.99*.85 (15% fee) = $19.54 gross profit
    $19.54-$15 (item cost) = $4.54 Net profit
    $4.54/$22.99 = .197 or 20% profit margin on the total sale
    Follow Math Help Forum on Facebook and Google+

  4. #4
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,069
    Thanks
    65
    Quote Originally Posted by onestop View Post
    item cost: $15
    seller fee: 15% of total sale price
    The sales price would have to be $22.99.

    $22.99*.85 (15% fee) = $19.54 gross profit
    $19.54-$15 (item cost) = $4.54 Net profit
    $4.54/$22.99 = .197 or 20% profit margin on the total sale
    Hmmm....are you sure? Shouldn't it be this way:
    Code:
    Item cost     : 15.00
    Seller fee   :   3.18 (15% of 21.18)
    Profit margin:   3.00 (20% of 15.00)
                    =====
    Selling price : 21.18
    Now you pay the salesman his $3.18 fee, and you're left with $18.00,
    so a $3.00 profit since item cost is $15.00, or 20%.
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    Sep 2010
    Posts
    8
    That would be a 20% ROI. I'm looking for a 20% profit margin on sale.

    Basically (net profit/Total Revenue)*100=profit margin. I need a way to use ROI to reach the 20% PM since my cost is the only constant.

    Profit margin - Wikipedia, the free encyclopedia
    Follow Math Help Forum on Facebook and Google+

  6. #6
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,069
    Thanks
    65
    HOKAY...now that we got that straight!
    Code:
    Item cost     : 15.00
    Seller fee    :  3.46 (15% of 23.08)
    Profit margin :  4.62 (20% of 23.08)
                    =====
    Selling price : 23.08
    That kinda agrees with your example; more precise : 4.62 / 23.08 = .20017

    Seems straightforward enough:
    c = item cost (given)
    f = seller fee % (given)
    m = profit margin % (given)
    p = selling price (calculated)
    General case formula:
    p = c / (1 - f - m)

    With above example: p = 15 / (1 - .15 - .20) = 15 / .65 = 23.08

    So, for each sale, this would happen:
    get c,f,m
    p = c / (1 - f - m)
    PRINT c : 15.00
    PRINT p*f : 3.46
    PRINT p*m :4.62
    PRINT p : 23.08

    And, of course, c,f and m can be ANY reasonable amounts.
    Last edited by Wilmer; September 23rd 2010 at 03:48 PM.
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Newbie
    Joined
    Sep 2010
    Posts
    8
    Perfect. Thank you.

    It has been a long time since 8th grade. Any suggestions on resources for brushing up my algebra?
    Follow Math Help Forum on Facebook and Google+

  8. #8
    MHF Contributor
    Joined
    Dec 2007
    From
    Ottawa, Canada
    Posts
    3,069
    Thanks
    65
    If you stopped after grade 8, I don't know what to tell you...
    Follow Math Help Forum on Facebook and Google+

  9. #9
    Newbie
    Joined
    Sep 2010
    Posts
    8
    Quote Originally Posted by Wilmer View Post
    If you stopped after grade 8, I don't know what to tell you...
    Didn't stop after 8th grade. I stopped after Cal II in 12th. They don't require math in college if you have already gone that far with AP credit. I has just been a long time since I have needed to use any algebra. Looking for a good refresher resource.
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Replies: 1
    Last Post: May 14th 2011, 10:07 AM
  2. Replies: 1
    Last Post: May 14th 2011, 08:16 AM
  3. A circular problem with resolving a sales price
    Posted in the Business Math Forum
    Replies: 3
    Last Post: May 21st 2010, 05:06 PM
  4. Gross Profit Margin Problem
    Posted in the Business Math Forum
    Replies: 2
    Last Post: April 29th 2010, 07:42 AM
  5. sales margin question
    Posted in the Algebra Forum
    Replies: 2
    Last Post: June 9th 2009, 11:31 PM

Search Tags


/mathhelpforum @mathhelpforum