How do I force a value to increase in one cell if another cell drops below a certain value?

[Konecranes]

I'm trying to force a value to increase in one cell if another cell drops below a specific value.

Example: If D18 drops below of 10% of D6 then D6 must be a minimum of 90% of D18.

D18 = net profit (must stay at 10%)
D12 = gross profit
D10 = fixed costs
D6 = total sales (must stay at 90% or better of profit)

Sales - service = gross profit, gross profit - fixed costs = net profit

 

[Caribeiro77]

Welcome is D18 a % cell? can you post some sample data?

PS .
Sales - service - COGS = gross profit, gross profit - fixed costs = net profit

 

[Konecranes]

D18 is a $ cell.

Sometimes my offices tell me they have to sell work at a lower margin (gross profit). So I set up my gross profit cell (D12) with a drop down box to change the margin of gross profit. Each group has a fixed costs that doesn't change so that cell (D10) does not change unless I change it when we hire someone or add an asset. The net profit (cell D18) is what our investors want in return - no less than 10% of sales, but it can be higher.

The drop down box in the gross margin cell allows me to go from 25% to 40% gross profit. So, if my fixed costs (D10) is -$150,000, and my sales (D6) are at $750,000, and I choose 30% gross profit from the drop down in cell D12 then my net profit would equal 10%. If I change the gross profit to 25% then my new profit will drop under 10% as my fixed costs and sales do not change. What I would like is: If I change the gross profit and it changes my new profit to show anything less than 10% of sales, than sales would automatically increase to a value to show the new profit at a minimum of 10%.

In essence, if one of my managers says to me that he can only get 25% gross profit in his market then he needs to get his sales up to cover his fixed costs and show a net profit of a minimum of 10%.

 

[Caribeiro77]

Can you post an example of what you need ? You can use Mr Exce lHTML maker (see on my signature how to do it)

 

[Konecranes]

Excel 2012
	D
6	750,000
7
8
9	750,000
10	225,000
11
12
13	225,000
14	(150,000)
15
16	75,000
17
18	10.0%
19
20
21	30.0%
Sheet1

 

[Konecranes]

Excel 2012
	C	D
6	SALES	750,000
7
8
9	SALES	750,000
10	CMII $	225,000
11
12
13	CMII $	225,000
14	F/C	(150,000)
15
16	ROS $	75,000
17
18	ROS %	10.0%
19
20
21	CMII %	30.0%
Sheet1

 

[Caribeiro77]

Posting with formulas would help... You have that option when copying the code in excel... select "all formulas" on the HTML maker..

 

[Konecranes]

Excel 2012
	C	D
6	SALES	750,000
7
8
9	SALES	750,000
10	CMII $	225,000
11
12
13	CMII $	225,000
14	F/C	(150,000)
15
16	ROS $	75,000
17
18	ROS %	10.0%
19
20
21	CMII %	30.0%
Sheet1

Cell Formulas
Range	Formula
D6	=-D14/0.2
D9	=SUM(D6:D7)
D13	=SUM(D10:D11)
D16	=SUM(D13:D14)
D18	=+D16/D9
D21	=+D13/D9

 

[Caribeiro77]

Two of your formulas are refering to cells that in your post don't have any value:

D7 and D11

will you type any values on this 2 cells?

 

[Konecranes]

No, no values for those cells.