Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Formulas: Profit Margin

  1. #1
    Join Date
    8th July 2003
    Posts
    5
    I need a formula to calculate profit magin, can anyone help?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,352
    Welcome to the Forum!

    Can you give a few more details about what you want? Or what data you have to start with? In its simplest form, if c is cost and p is profit then c+p is revenue and the profit margin is p/(c+p), markup is p/c.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    Profit Margin Percentage = (Sell Price - Cost Price) / Sell Price.

    Watch that Sell Price <> 0 in your formula or you&#039;ll get an error.

    =IF(Sell<>0, (Sell-Cost)/Sell, 0) should work.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th July 2003
    Posts
    5
    Im estimating material cost and labor. At the base of the form there is a box for magin that is adjusted. I can calculate markup with a simple formula but margin becomes a circular reference.

    I.E. Cost + Profit, what percentage of the sum total represents margin and that margin amount can be adjusted. The margin calculation I use now just adds a profit to the sub-total but does not reflect the overall margin for the job.


    Sub-Total $33,985
    TOTAL COST $33,985
    MARGIN 35% $11,894.75
    TOTAL SALE $45,880

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th July 2003
    Posts
    5

  6. #6
    Join Date
    8th July 2003
    Posts
    5

  7. #7
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    In your example:

    Cost = $33,985

    Markup (not margin) = 35%

    So Sell = Cost * 1.35 = $45,879.75

    Total Margin = (Sell - Cost) / Sell = 25.9%

    You should not have a circular reference since the markup and margin are 2 different numbers.

    ....Ralph

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    8th July 2003
    Posts
    5
    now how do i change the margin percentage and have it reflect the revised dollar amount?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th June 2003
    Location
    Maryland, USA
    Posts
    368
    I&#039;m not 100% sure I understand what you are trying to do. . . so I&#039;ll put down a few formulas to see if I&#039;m close.

    1. If you know what the total cost is (say $3,000) and you want to have a variable markup percentage that you can change (start with 35% like your example), then you need the formula for calculating the selling price. That would be Sell = Cost * (1 + markup) or Sell = 3000 * (1 + .35). Sell would calculate to $4,050.

    2. If you also need to calculate your Profit Margin on the transaction, then use the second formula. Margin = (Sell - Cost) / Sell. This would be (4050 - 3000)/4050 = .2593 or 25.93%

    3. If you want to make the Profit Margin a variable and calculate a new Selling Price then (using the numbers from #2 above) Sell = Cost / (1 - Profit Margin) or Sell = 3000 / (1 - .2593) = 3000 / 0.7407 = $4,050

    If you want a 35% Profit Margin it would equal a selling price of 3000 / (1 - .35) = $4,615 . . . . 35% Profit Margin and 35% Markup are not the same thing.

    Hope I got close to what you need... . . . Ralph

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Gross Margin Calculation Subtracting Many Costs From One Revenue
    By cparaske in forum Excel and/or Access Help
    Replies: 4
    Last Post: May 24th, 2007, 21:32
  2. Margin between 1st and 2nd
    By pytelium in forum EXCEL HELP
    Replies: 21
    Last Post: August 18th, 2006, 16:55
  3. Data Validation to Calculate Profit Margin
    By unbridled4 in forum EXCEL HELP
    Replies: 7
    Last Post: August 8th, 2006, 20:21
  4. Footer Left/Right Margin Changes
    By excelboy in forum EXCEL HELP
    Replies: 4
    Last Post: April 22nd, 2003, 07:02

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
  •  
porno