Dynamic Adjust the Value in a Cell

[Llewellyn]

Hi

I'm loosing my hair over this one, and it would be greatly appreciated if someone can assist me with this problem. I need excel to dynamically adjust the "Sell Price" value, so that my "Profit" value is kept at a fixed value, for example 20.

Example.xlsx
	A	B	C	D	E
1	Product	Buy Price	Profit Margin	Sell Price	Profit
2	Cheese	112	25.00%	140.00	28.00
Example

Cell Formulas
Range	Formula
D2	D2	=SUM(B2+(B2*C2))
E2	E2	=SUM(D2-B2)

 

[etaf]

if you know the fixed value of the profit, don't you just add that to the Buy Price ?
not sure i understand the question ?

or do you need to change the Profit margin percent to keep a fixed price ?

sorry , not sure i follow the example

=B2 + 20

 

[footoo]

.

 

[Llewellyn]

Hi Etaf

Thank you for the reply. You are partially correct in the second statement. I receive a product list (4000 to be exact) with a value B2 per product. Based on B2, would I calculate E2 by applying a value C2 to B2, which will also calculate F2. I require Excel to only re-adjust C2 if the value of F2 is greater than G2. This new value of C2 then needs to be populated in D2 and the new value of H2 needs to be calculated.

I have created a new sheet. Hope this explain it better.

Example.xlsx
	A	B	C	D	E	F	G	H
1	Product	Buy Price	Profit Margin (Base)	Adjusted Profit Margin	Sell Price	Profit @ 25%	Actual Profit	Final Sell Price
2	Cheese	112.00	25.00%		140.00	28.00	20.00	112
Example

Cell Formulas
Range	Formula
E2	E2	=SUM(B2+(B2*C2))
F2	F2	=SUM(E2-B2)
H2	H2	=(SUM(B2+(B2*D2)))

 

[etaf]

does this Work for you
There will be a breakeven for the £20 mark

Enter the New price and the other values are calculated

Book2
	A	B	C	D	E	F	G
1	Product	Buy Price	Profit Margin (Base)	Adjusted Profit Margin	Sell Price	Profit @ 25%	Profit target
2	Cheese 1	£ 30.00	25.0%	67%	£ 50.00	£ 7.50	20
3	Cheese 2	£ 35.00	25.0%	57%	£ 55.00	£ 8.75
4	Cheese 3	£ 40.00	25.0%	50%	£ 60.00	£ 10.00
5	Cheese 4	£ 45.00	25.0%	44%	£ 65.00	£ 11.25
6	Cheese 5	£ 50.00	25.0%	40%	£ 70.00	£ 12.50
7	Cheese 6	£ 55.00	25.0%	36%	£ 75.00	£ 13.75
8	Cheese 7	£ 60.00	25.0%	33%	£ 80.00	£ 15.00
9	Cheese 8	£ 65.00	25.0%	31%	£ 85.00	£ 16.25
10	Cheese 9	£ 70.00	25.0%	29%	£ 90.00	£ 17.50
11	Cheese 10	£ 75.00	25.0%	27%	£ 95.00	£ 18.75
12	Cheese 11	£ 76.00	25.0%	26%	£ 96.00	£ 19.00
13	Cheese 12	£ 77.00	25.0%	26%	£ 97.00	£ 19.25
14	Cheese 13	£ 78.00	25.0%	26%	£ 98.00	£ 19.50
15	Cheese 14	£ 79.00	25.0%	25%	£ 99.00	£ 19.75
16	Cheese 15	£ 80.00	25.0%	25%	£ 100.00	£ 20.00
17	Cheese 16	£ 81.00	25.0%	25%	£ 101.25	£ 20.25
18	Cheese 17	£ 82.00	25.0%	25%	£ 102.50	£ 20.50
19	Cheese 18	£ 83.00	25.0%	25%	£ 103.75	£ 20.75
20	Cheese 19	£ 84.00	25.0%	25%	£ 105.00	£ 21.00
21	Cheese 20	£ 85.00	25.0%	25%	£ 106.25	£ 21.25
22	Cheese 21	£ 86.00	25.0%	25%	£ 107.50	£ 21.50
23	Cheese 22	£ 87.00	25.0%	25%	£ 108.75	£ 21.75
24	Cheese 23	£ 88.00	25.0%	25%	£ 110.00	£ 22.00
25	Cheese 24	£ 89.00	25.0%	25%	£ 111.25	£ 22.25
Sheet2

Cell Formulas
Range	Formula
D2:D25	D2	=(E2-B2)/B2
E2:E25	E2	=IF((B2*(1+C2)-B2)<$G$2,(B2+$G$2),B2*(1+C2))
F2:F25	F2	=(B2*1.25)-B2

 

[Llewellyn]

Hi Etaf

Thank you very much. This is what I was looking for.

 

[etaf]

you are welcome