Results 1 to 4 of 4

Excel formula for Calculating Percentages for Overhead-Profit

This is a discussion on Excel formula for Calculating Percentages for Overhead-Profit within the Excel Questions forums, part of the Question Forums category; Hi In this example, I want to be able to change in a spreadsheet the Overhead and Profit percentages to ...

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    38

    Question Excel formula for Calculating Percentages for Overhead-Profit

    Hi
    In this example, I want to be able to change in a spreadsheet the Overhead and Profit percentages to any number in the boxes.
    As you look at the simple math, using 10% for example only gives you 7.94% real, as you need to divide to come up with the true 10%.

    So, how would you write this algebraically?

    Meaning, on another sheet, will be the data entry point for adjusting OH & Profit, so I would like it to always compute this correctly. When I have done it in the past, it comes up with a circular reference.

    Example- open excel

    Labor $1000 or 39.68% of total
    Materials $1000 or 39.68% of total
    Subtotal $2000

    OH 10% $200 or 7.94% of total
    Profit 10% $200 or 7.94% of total

    Subtotal $2400 or 95.24% of total
    Tax 5% or 4.76% of total

    Grand Total $2250 or 100% of total

    I don't know how to make attachments here, or if it is allowed? I can send the files to those that would like to assist.

    Thanks

    Keith

  2. #2
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,329

    Default Re: Excel formula for Calculating Percentages for Overhead-Profit

    Sheet1
    ABCDE
    10.0794 Overhead %
    20.0794 Profit %
    30.1588 Combined OH & Pft % 400.00 $2,518.89 Projected Sales Price
    4
    50.8412 Material & Labor % 0.8412 Cost Labor & Materials - %
    6
    72,118.89 Cost Labor & Materials - $
    8
    9$400.00 Gross Margin
    10
    11
    120.0794$200.00 Overhead
    13
    140.0794$200.00 Profit
    Excel 2003

    Worksheet Formulas
    CellFormula
    A3=SUM(A1:A2)
    D3=C3/A3
    A5=1-A3
    D5=A5
    D7=D3*D5
    D9=D3-D7
    D12=D$3*C12

    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  3. #3
    New Member
    Join Date
    Aug 2009
    Posts
    38

    Default Re: Excel formula for Calculating Percentages for Overhead-Profit

    Hi Jim

    Thanks for the Formula.

    Perhaps I didn't explain myself very well. As you see in the calculation, 10% isn't actually 10%, but 7.94%.

    So, how can I get the actual 10% calc?
    Then, how can I have a cell where a user can change the percentage to whatever they like and it will accurately calc the true %?

    Does this make sense?

    Thanks

  4. #4
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    6,329

    Default Re: Excel formula for Calculating Percentages for Overhead-Profit

    Cells A1 and C1 are Changable:


    Sheet1
    ABCDE
    110% Profit % Desired $ 200.00 Profit Dollars Desired
    2
    30.1Overhead %
    40.8 Material Labor & OH %
    5
    6$2,000.00 Projected Sales Price
    7
    880% Cost Labor & Materials - %
    9
    1080%1,600.00 Cost Labor & Materials - $
    1110%200.00Overhead $$'s
    1290%1,800.00Total Costs
    13
    14
    1510%$200.00 Profit
    16
    Excel 2003

    Worksheet Formulas
    CellFormula
    A4=1-A1-A3
    D6=C1/A1
    D8=A4
    C10=D10/$D$6
    D10=D6*D8
    C11=A3
    D11=D6*C11
    C12=C10+C11
    D12=D10+D11
    C15=1-C12
    D15=D6-D12

    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com