Optimizing Price with Weightings to get to Best Price Mix - Use Solver?

[Sundance_Kid]

Hi, I hope I can explain this clearly. As part of a tender offering out consultants there is weightings applied to each of the levels to get an overall weighted average price.

Without changing the weightings assigned to each level and only changing the Fee element for each level (except for Senior Consultant), I want to optimize the fee we should charge for all the levels so as to bring down the Weighted Fee but at the same time increase the overall margin %. From looking at this basic example, I could just change the Junior Consultant fee to 200 and it would bring down the overall weighted fee and increase margin. Note: Costs are a percentage of the revenue.

While this is a basic example, I would work with more complex examples that are not so obvious.

Is there a way using some sort of modelling technique that by just changing the fee element of 4 consultant levels excluding Senior Consultant that you can ask it to reduce the overall weighted fee but at the same time increase the margin?
Something like goal seek but goal seek only works with one variable. Someone briefly mentioned solver to me, but not sure can this do something similar?

Book1
	D	E	F	G	H	I
2	Consultant	Weighting	Fee	Costs	Profit	Margin
3	Senior Consultant	50%	500	300	200	40%
4	Middle Consultant	20%	400	248	152	38%
5	Junior Consultant	10%	300	219	81	27%
6	Part Qualified Consultant	10%	200	134	66	33%
7	Novice Consultant	10%	100	60	40	40%
8
9	Total		1500	961	539	36%
10
11	Weighted Fee		390
12	Overall Margin		36%
Sheet1

Cell Formulas
Range	Formula
G3,G7	G3	=F3*60%
H3:H7	H3	=F3-G3
I3:I7,I9	I3	=H3/F3
G4	G4	=F4*62%
G5	G5	=F5*73%
G6	G6	=F6*67%
F9:H9	F9	=SUM(F3:F7)
F11	F11	=SUMPRODUCT(E3:E7,F3:F7)
F12	F12	=I9

 

[StephenCrump]

Yes, you can use Solver for this type of problem.

But in this simple example, it's not necessary. As Senior Consultant and Novice Consultant have the greatest margins, at 40%, your "optimum" solution is to charge non-zero fees only for these two tiers, so that the overall margin is maximised at 40%. Furthermore, you could reduce these two fees towards nil to minimise the weighted fee whilst maintaining the margin at 40%

This suggests you need more constraints (which Solver can accommodate).