Excel formula for Calculating Percentages for Overhead-Profit

electrichi01

New Member
Joined
Aug 10, 2009
Messages
44
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Excel Workbook
ABCDE
10.0794Overhead %
20.0794Profit %
30.1588Combined OH & Pft %400.00$2,518.89Projected Sales Price
4
50.8412Material & Labor %0.8412Cost Labor & Materials - %
6
72,118.89Cost Labor & Materials - $
8
9$400.00Gross Margin
10
11
120.0794$200.00Overhead
13
140.0794$200.00Profit
Sheet1
Excel 2003
Cell Formulas
RangeFormula
A3=SUM(A1:A2)
A5=1-A3
D3=C3/A3
D5=A5
D7=D3*D5
D9=D3-D7
D12=D$3*C12
 
Upvote 0
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
 
Upvote 0
Cells A1 and C1 are Changable:
Excel Workbook
ABCDE
110%Profit % Desired$ 200.00Profit Dollars Desired
2
30.1Overhead %
40.8Material Labor & OH %
5
6$2,000.00Projected Sales Price
7
880%Cost Labor & Materials - %
9
1080%1,600.00Cost Labor & Materials - $
1110%200.00Overhead $$'s
1290%1,800.00Total Costs
13
14
1510%$200.00Profit
16
Sheet1
Excel 2003
Cell Formulas
RangeFormula
A4=1-A1-A3
D6=C1/A1
D8=A4
D10=D6*D8
D11=D6*C11
D12=D10+D11
D15=D6-D12
C10=D10/$D$6
C11=A3
C12=C10+C11
C15=1-C12
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,760
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top