+ Reply to Thread
Results 1 to 4 of 4

Formula to backout percentage markup

  1. #1
    Registered User
    Join Date
    02-19-2007
    Posts
    2

    Formula to backout percentage markup

    Hi, Trying to create formula to backout markup on tools. I have one spreadsheet (Ex A) used by the techs to quote tool amounts to customers. The second worksheet is used by office to track sales (Ex B). It's very large but here are the columns I'm concerned with.

    Example A: Form to quote prices to customers (This changes every time a customer calls or comes in)

    Column A (input field that sum all tool amounts) = $2,864.77
    Column B (calculates 20% markup for each tool) = $572.95
    Column C (Subtotal and amount quoted customer) =$3,437.72

    Example B: Tool Ledger to track sales from all techs

    Column A (Amount Billed Customer)= $3,437.72
    Column B (formula needed ... I have: =A1*(1-0.2) which should take the amount billed customer and minus the 20% markup ($2,864.77 from ex) but it does not. It returns $2,750.17 Help?

  2. #2
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    That's the difference between MARKUP and PROFIT MARGIN
    Your MARKUP is 20% but your PROFIT MARGIN is only 16.6%


    Your selling price is cost plus 20% (120% of cost)
    Cost x 1.2 = Sell
    Your profit is selling price divided by 1.2
    Sell / 1.2 = Cost
    =(A1/1.2)

    If you actually wanted to make 20% PROFIT you'd have to use
    Cost / .80 = Sell

  3. #3
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Hi,

    the formula required

    =C1/1.2

    this will show your original A1 figure after the 20% (or 16.66%) 'Profit' has been removed.

    Accounting Profit is defined as the difference between Sales and costs (16.66%), dictionary defined Profit is defind as the positive return made on an investment, ie 20%

    hth
    ---
    Si fractum non sit, noli id reficere.

  4. #4
    Registered User
    Join Date
    02-19-2007
    Posts
    2
    Thanks - I appreciate your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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