# 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. ## 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. ## Re: Excel formula for Calculating Percentages for Overhead-Profit

Sheet1
ABCDE
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
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

3. ## 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. ## 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
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 - \$
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