Basic Solver Problem

[Sige]

Hi There,

My Case:

A1:A10 contains Unit volumes for the 10 items
B1:B10 contains Unit price
C1:C10 = A*B
C11 = Total Price
D11 = Target Price

By just changing the Unit volumes I want to reach the Target Price.

My constraint:
Each Item should keep it's relative volume share. Easy enough to
calculate it without Solver but...how do you determine that constraint
with Solver???

Thanks for your insight,
Brgds Sige

 

[Bernard Liengme]

Do you mean
(1) the percentage of total volume must stay same for each item or
(2) the ranking of each item must remain unchanged?

 

[Sige]

Hi Bernard,

(1) the percentage of total volume must stay same for each item !!!

E.G.:
Item A= 25 units
Item B= 50 units
Item C= 125 Units
Total = 200Units

The relative share should remain unchanged:
Being 25/200= 12.5% for A, 25% for B, 62.5% for C

Brgds Sige

 

[Tushar Mehta]

There's really nothing to solve. If the %s have to remain the same, you
have only one variable and simple algebra will yield the answer.

In your example, B=2*A and C=5*A. So, the only variable you have to play
with is A. If the unit prices were Pa, Pb, and Pc, you would get the total
price as Pa*A + 2*Pb*A + 5*Pc*A or A(Pa +2Pb + 5Pc). Given a target of Pt,
solve for A to get A = Pt/(Pa +2Pb + 5Pc).

The generalization to 10 items is similarly straightforward.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Sige]

Hi Tushar,

Sure is true ... though this is just a simplified example!
There are other constraints on these items in my model, that I CAN
model in Solver ... though modelling the items to keep the relative
share beats me a bit.

Brgds Sige

 

[Tushar Mehta]

Hi Tushar,

Sure is true ... though this is just a simplified example!
There are other constraints on these items in my model, that I CAN
model in Solver ... though modelling the items to keep the relative
share beats me a bit.

Brgds Sige

None of the other constraints matter. The relative share requirement
trivializes the model to that of a single variable. The other constraints
can do nothing other than (a) make the solution infeasible, or (b) be non-
binding.

I'll repeat this one more time and then leave this discussion alone. By
keeping the relative shares constant, you have only one truly independent
variable. It's like writing

'by changing' variables: A1, A2, A3, ...
subject to A2=2*A1, A3=5*A1, etc.

Which means you can put in A2:An the formulas:

=2*A1
=5*A1
....

That will bring the 'by changing variables' down to A1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Sige]

Ok Tushar.

Thanks for the clarification.

Best Regards, SIge

 

[Dana DeLouis]

By just changing the Unit volumes I want to reach the Target Price.

Hi. For larger models, here is a technique that is similar as Tushar's.

A1:A3 has your small sample...25, 50,125

Make two range names:
Total, Volume

Total =your original sum (200)

Now use a helper column to calculate relative ratios.
B1 =A1/Total
B2 =A2/Total
etc...

Now, the new volumes will be in C1:C3
C1 = Volume*B1
C2 = Volume*B2
etc...

Have Solver adjust just the Volume, and the volumes in C1:C3 will adjust,
with the ratios staying the same.

As a technique, some like to drop the C1:C10 calculations, and for Total
Price, use a formula like
=SumProduct(NewVolumes, UnitPrices)

again, it all depends..
Anyway, hope this helps. :>)

As a side note, your original "Total Price" without all the above might be:
=SUMPRODUCT(OriginalVolumes/Total*Volume,UnitPrices)

....where Solver is only adjusting the Volume.

 

[Sige]

Thank you Dana!
Sige