+ Reply to Thread
Results 1 to 8 of 8

Calculate Retail Price based on desired profit margin

  1. #1
    Registered User
    Join Date
    01-12-2016
    Location
    Dunfermline, UK
    MS-Off Ver
    2013
    Posts
    4

    Calculate Retail Price based on desired profit margin

    Hi,

    I have an excel sheet containing products which I sell and I am trying to work out a formula to give me a retail price (selling price) based on a desired profit margin taking into account all costs and fees. My spreadsheet is set out as follows:

    Column H - Cost of goods from supplier
    Column I - Cost of delivery from supplier
    Column J - VAT (20% of column H an I)
    Column K - Total Cost (total of H,I and J)
    Column L - Postage Credit (credit received from Amazon when selling on their site)
    Column M - Fees (variable fees owed to Amazon, 0.13*column L and column N)
    Column N - Retail price i.e. price I will sell the item for
    Column O - Profit =SUM(N4+L4)-(K4+M4)
    Column P - Profit % - This is the column where I wish to enter the desired margin percentage to calculate column N

    Any help would be appreciated as this has me beat!

    Graham

  2. #2
    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 Retail Price based on desired profit margin

    Welcome to the board.

    Price = totalCost / (1 - marginPercentage)
    Last edited by shg; 01-12-2016 at 10:17 AM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-12-2016
    Location
    Dunfermline, UK
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculate Retail Price based on desired profit margin

    The issue is column M, as this is a variable (13%) of the retail price so I want the margin to be whatever is in column P taking into account both the total cost and the fees in column N...

  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: Calculate Retail Price based on desired profit margin

    Price = totalCost / (1 - priceBasedFeePercentages - marginPercentage)

  5. #5
    Registered User
    Join Date
    01-12-2016
    Location
    Dunfermline, UK
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculate Retail Price based on desired profit margin

    Thank you, when I do that =SUM(L4/(1-N4-Q4)) however I get a circular reference error as column N (fees) is calulated as 13% of the retail price

  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 Retail Price based on desired profit margin

    Row\Col
    A
    B
    C
    1
    Total Cost
    $ 100.00
    B1: Input
    2
    Amazon Fee
    13%
    B2: Input
    3
    Gross Margin
    45%
    B3: Input
    4
    Sell
    $ 238.10
    B4: =B1/(1-B2-B3)
    5
    Pay Amazon
    $ 30.95
    B5: =B2*B4

  7. #7
    Registered User
    Join Date
    01-12-2016
    Location
    Dunfermline, UK
    MS-Off Ver
    2013
    Posts
    4

    Re: Calculate Retail Price based on desired profit margin

    Thank you and apologies if I have misread but this doesn't achieve what I am looking to do which is have excel automatically calculate the sell price based on a entered gross margin but taking into account both the cost of goods and amazon fees...

  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 Retail Price based on desired profit margin

    Let's try again:

    Row\Col
    A
    B
    C
    1
    Cost from Supplier
    $ 100.00
    B1: Input
    2
    Delivery Fee
    $ 10.00
    B2: Input
    3
    VAT
    $ 22.00
    B3: =20%*(B1+B2)
    4
    Total Cost
    $ 132.00
    B4: =SUM(B1:B3)
    5
    Amazon Fee
    13%
    B5: Input
    6
    Gross Margin
    45%
    B6: Input
    7
    Sell
    $ 314.29
    B7: =B4/(1-B5-B6)
    8
    Pay Amazon
    $ 40.86
    B8: =B5*B7
    9
    Pay Me
    $ 141.43
    B9: =B6*B7


    Sell price is calculated in B7.

+ 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. Price determined by Profit Margin
    By johntyjohnson in forum Excel General
    Replies: 6
    Last Post: 08-31-2015, 02:59 PM
  2. [SOLVED] Formula to calculate supplier evaluation scores based on their profit margin %
    By The_Snook in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2015, 06:03 PM
  3. calculate retail price based on mark up on cost price
    By pvl in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 07-27-2014, 03:49 AM
  4. Need to have Excel calculate profit margin based on variable...stuck!
    By djbeatsent in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-09-2013, 11:21 AM
  5. [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
  6. Replies: 2
    Last Post: 01-09-2010, 04:06 AM
  7. I need an Excel template that will calculate Retail Gross Profit
    By Anthony in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-25-2006, 10:11 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