+ Reply to Thread
Results 1 to 8 of 8

Formula that determines selling price

  1. #1
    Registered User
    Join Date
    05-18-2017
    Location
    Salt Lake City, Utah
    MS-Off Ver
    Excel 2016
    Posts
    16

    Formula that determines selling price

    Hello everyone,

    I was hoping someone could help me out by finding a formula that will determine what I need to set my selling price to on Amazon.com. My desired margin is to make at least 25.00%, my cost of the item is $5.60 and I have a 16.85% fee on the item selling price.

    Any help would be greatly appreciated!

    Thanks,
    Courtney

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Formula that determines selling price

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula that determines selling price

    I think the formula that you want is (with your cost in B2):

    =ROUNDUP(ROUNDUP(B2*(1+25%), 2) / (1-16.85%), 2)

    Thus:

    Please Login or Register  to view this content.
    PS.... The formula is based on the following algebra:

    price - fee = cost + profit
    fee = price*16.85%
    profit = cost*25%

    So:
    price*(1-16.85%) = cost*(1+25%)
    price = cost*(1+25%) / (1-16.85%)
    Last edited by joeu2004; 07-24-2017 at 04:41 PM. Reason: PS

  4. #4
    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: Formula that determines selling price

    Assuming you measure margin on gross sales,

    A
    B
    C
    1
    Amazon Fee
    16.85%
    B1: Input
    2
    Margin
    25.0%
    B2: Input
    3
    Cost
    $ 5.60
    B3: Input
    4
    5
    Price
    $ 9.63
    B5: =B3/(1 - B1 - B2)
    6
    Check:
    7
    Amazon keeps
    $ 1.62
    B7: =B1*B5
    8
    I get
    $ 8.01
    B8: =B5-B7
    9
    Margin Amt
    $ 2.41
    B9: =B8-B3
    10
    Margin %
    25.0%
    B10: =B9/B5
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Formula that determines selling price

    I concur with shg. The other calculations are showing a 25% mark-up on cost, not a 25% margin on sales.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula that determines selling price

    I thought we would want 25% of the original base cost. Not 25% of the eventual selling price.

    For 25% of the base cost, B5 would be
    =B3/(1-B1)*(1+B2)

  7. #7
    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: Formula that determines selling price

    In my world, margin is measured on price. Markup, or uplift, is measured on cost.

    "I sold $1M on Amazon this year at 25% GM." That means I made $250K gross profit.

  8. #8
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Formula that determines selling price

    Quote Originally Posted by joeu2004 View Post
    I think the formula that you want is (with your cost in B2):
    =ROUNDUP(ROUNDUP(B2*(1+25%), 2) / (1-16.85%), 2)
    [....]
    PS.... The formula is based on the following algebra:
    price - fee = cost + profit
    fee = price*16.85%
    profit = cost*25%
    Quote Originally Posted by gak67 View Post
    I concur with shg. The other calculations are showing a 25% mark-up on cost, not a 25% margin on sales.
    I agree that I miscalculated profit.

    However, the issue of what profit is based on depends on whether Courtney wants the gross profit margin or the net profit margin to be 25%.

    GPM does not include operating expenses, which I consider the Amazon fee to be, whereas NPM does.

    [EDIT] The idea is for the customer to cover the Amazon fee, but we have no interest in making profit on it, any more than we might want to make profit on shipping and taxes. The only difference is: shipping and taxes are line items, whereas the Amazon fee is not (I presume; I really don't know).

    I believe the GPM should be 25%. So:

    price - fee = revenue
    fee = price*16.85%
    revenue = cost + profit
    profit = revenue*25%

    revenue - revenue*25% = cost
    revenue*(1-25%) = cost
    revenue = cost/(1-25%)

    price*(1-16.85%) = cost/(1-25%)
    price = cost/(1-25%)/(1-16.85%)

    So I think the formula should be (with cost in B2):

    =ROUNDUP(ROUNDUP(B2/(1-25%), 2) / (1-16.85%), 2)

    Please Login or Register  to view this content.
    Last edited by joeu2004; 07-24-2017 at 10:39 PM. Reason: critical typo in 2nd-to-last formula for B2

+ 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. Expressing Selling Price as a percentage of List Price
    By Antman452 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-02-2016, 08:21 AM
  2. [SOLVED] Setting a selling price based on cost price and order quantity
    By Steven811 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2014, 08:07 AM
  3. Replies: 3
    Last Post: 03-19-2014, 12:05 PM
  4. Calculating Selling Price Formula in Excel
    By hitari in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2013, 10:07 AM
  5. Replies: 2
    Last Post: 04-13-2012, 08:22 AM
  6. Replies: 5
    Last Post: 02-06-2012, 06:21 AM
  7. calculating selling price
    By cbs in forum Excel General
    Replies: 2
    Last Post: 12-05-2010, 09:05 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