Basic Solver Problem

  • Thread starter Thread starter Sige
  • Start date Start date
S

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
 
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?
 
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
 
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
 
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
 
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
Click to expand...
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
 
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.
 
Back
Top