Excel formula for Calculating Percentages for Overhead-Profit

[electrichi01]

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

[jim may]

Sheet1

	A	B	C	D	E
1	0.0794	Overhead %
2	0.0794	Profit %
3	0.1588	Combined OH & Pft %	400.00	$2,518.89	Projected Sales Price
4
5	0.8412	Material & Labor %		0.8412	Cost Labor & Materials - %
6
7				2,118.89	Cost Labor & Materials - $
8
9				$400.00	Gross Margin
10
11
12			0.0794	$200.00	Overhead
13
14			0.0794	$200.00	Profit

Excel 2003

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

[electrichi01]

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

[jim may]

Cells A1 and C1 are Changable:


Sheet1

	A	B	C	D	E
1	10%	Profit % Desired	$ 200.00	Profit Dollars Desired
2
3	0.1	Overhead %
4	0.8	Material Labor & OH %
5
6				$2,000.00	Projected Sales Price
7
8				80%	Cost Labor & Materials - %
9
10			80%	1,600.00	Cost Labor & Materials - $
11			10%	200.00	Overhead $$'s
12			90%	1,800.00	Total Costs
13
14
15			10%	$200.00	Profit
16

Excel 2003

Worksheet Formulas Cell Formula 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