Using Solver with Linear Constraint(s)

[stef]

Excel 2002 SP3
Win XP Pro SP2

*Follow-up to: microsoft.public.excel*

Hi,

Sorry but I have "writer's block" right now as regards to a spreadsheet
solution I need.

It's a simple problem but I am just drawing a blank at the moment....

Let me try to describe it:

I have a cell chosen as the target for solver e.g. M25 = 1% (total
market share)

And a range as the cells to change by solver e.g. J4:J23 (market share
as % at each period e.g. month or quarter, etc.)

Each cell needs to progressively grow until reaching 1%. And the rate
of growth of the cells in J4:23 needs to stay constant or linear.

The 1st value affected by J4 is a $ amount like $100,000. The ending
value is very high in comparison: 100,000 % that the original $ value.

I cannot project the 1% divided by the number of period as the 1st
period growth would be much too high.

I need the values in the range J4:J23 to change to solve the equation
but in a linear fashion, each cell value being progressively higher than
the last.

I have played around with the Linear, Automatic Scaling, etc.; options
but have not been able to get the result I want.

Actually, I do not really see a linear solution in this case but again,
I am in a "blank" right now....

Any help or suggestion?

 

[Bernard Liengme]

How about give the J's a relationship
J4 =a
J5=J4+b
J6 =J5+b

Now vary a and b to get solution
best wishes

 

[Bernard Liengme]

Please do not scatter shoot!

 

[stef]

Sorry, I'm really think in the head today....

Vary a and b in the solver you mean?

What's a and what's b?

 

[SteveM]

Excel 2002 SP3
Win XP Pro SP2

*Follow-up to: microsoft.public.excel*

Hi,

Sorry but I have "writer's block" right now as regards to a spreadsheet
solution I need.

It's a simple problem but I am just drawing a blank at the moment....

Let me try to describe it:

I have a cell chosen as the target for solver e.g. M25 = 1% (total
market share)

And a range as the cells to change by solver e.g. J4:J23 (market share
as % at each period e.g. month or quarter, etc.)

Each cell needs to progressively grow until reaching 1%. And the rate
of growth of the cells in J4:23 needs to stay constant or linear.

The 1st value affected by J4 is a $ amount like $100,000. The ending
value is very high in comparison: 100,000 % that the original $ value.

I cannot project the 1% divided by the number of period as the 1st
period growth would be much too high.

I need the values in the range J4:J23 to change to solve the equation
but in a linear fashion, each cell value being progressively higher than
the last.

I have played around with the Linear, Automatic Scaling, etc.; options
but have not been able to get the result I want.

Actually, I do not really see a linear solution in this case but again,
I am in a "blank" right now....

Any help or suggestion?

Stef,

First of all, your target cell is defined as a constraint not an
objective function. And if that is your intent, you have to move that
to the constraint block. Then the problem is to find merely a
feasible solution, not an optimal one.

I of course don't know what your real problem is or if you have
formulated it properly. It is pretty easy to get wrapped around the
axle if you really don't know what you are doing.

However, an obvious recommendation would be to define a set of
secondary constraints in which each ith ordered variable is within
some percentage of the ith - 1 variable. Something like J5 >= 1.1 *
J4, which if extended across the entire set of J variables would give
you the rank ordered solution that you're looking for. J4 and J23
would be your absolute upper and lower bounds. Of course there is
also the issue of a lower bound for every other variable. And then
you have to sum up the J's to equal 1% total market share. But you
have to be careful there to because the optimizer will stop at the
first feasible solution. When in fact there may be better solutions
if you relaxed the equality constraint.

But even that may not be good enough. If you are really maximizing
total market share, then you'd need to formulate an objective function
that reflects that and incorporate that into your model which also
includes the 1% target has a lower bound for optimality. Of course
that could lead to infeasible problems if your target is to
aggressive, which suggests a parametric analysis around the target and
probably the J bounding coefficients.

If your problem involves real money, there are people who solve these
kinds of problems for a living to help ensure you don't make a
mistake. I happen to know some myself. If you contact me directly, I
can tell you who they are.

SteveM

 

[Bernard Liengme]

Two cells that you get Solver to vary
Bernard

Sorry, I'm really think in the head today....

Vary a and b in the solver you mean?

What's a and what's b?

 

[SteveM]

Two cells that you getSolverto vary
Bernard

That's not how LP works! For one thing that assumes constant growth
rate over time. If there are fixed costs for financing or other
factors, that won't be the case. What the poster Bernard is sorting
of a suggesting without realizing it is what I suggested. Your
decision variables are the Ji's, a and b in Bernard's mind are really
the actual values of each Ji that the Solver would spit out. And
except for your entering Ji, all other Ji's are decision variables.
Not just "a" and "b" What you need to do is also set an upper and
lower bound for each Ji so that that your solution is operationally
feasible.

Versteh?

SteveM

P.S. A little knowledge is a dangerous thing