Basic Solver Problem

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?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1138901743.651395.311010@f14g2000cwb.googlegroups.com...
> 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
>

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

In article <1138904625.034578.176820@g44g2000cwa.googlegroups.com>,
SIGE_GOEVAERTS@HOTMAIL.COM says...
> 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
>
>

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

In article <1138905998.631449.262760@g44g2000cwa.googlegroups.com>,
SIGE_GOEVAERTS@HOTMAIL.COM says...
> 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

Ok Tushar.

Thanks for the clarification.

Best Regards, SIge

Tushar Mehta wrote:
> In article <1138905998.631449.262760@g44g2000cwa.googlegroups.com>,
> SIGE_GOEVAERTS@HOTMAIL.COM says...
> > 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

> 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.

--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Sige" <SIGE_GOEVAERTS@HOTMAIL.COM> wrote in message
news:1138956346.644336.161670@o13g2000cwo.googlegroups.com...
> Ok Tushar.
>
> Thanks for the clarification.
>
> Best Regards, SIge
>
> Tushar Mehta wrote:
>> In article <1138905998.631449.262760@g44g2000cwa.googlegroups.com>,
>> SIGE_GOEVAERTS@HOTMAIL.COM says...
>> > 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
>

Thank you Dana!
Sige