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