Solver problem

[prabhat]

I want to maximize one value (expected rate of return) and minimize another
value (variance) at the same time. Is there a way to find an optimum value
rather than just finding either the maximum value of expected return, or
finding the minimum value of variance.

Is it possible to generate a table for a range of values ? Is it possible to
generate a graph of expected return vs variance.

Thanks,
Prabhat

 

[Gary''s Student]

Consider combining the variables:

If the expected rate of return in is B9
and
the variance is in B10
then in another cell enter:
=B9/(1+B10)

and use Solver to maximize this cell.

 

[prabhat]

Why is B9/(1 + B10) .. and not B9/B10 .. also, is there a way to plot a graph
of expected value vs variance.

 

[Gary''s Student]

I picked (1+B9) to avoid he denominator coming close to zero if the variance
gets close to zero.

 

[ShaneDevenshire]

Hi,

Regarding the graphing - if you are doing this for only one value than it
wouldn't be a very exciting chart. If you're doing it for a bunch of values,
then put the values in one column and the variances in another. Use an XY
Scatter chart.

 

[prabhat]

Hi,

I want to do it for a bunch of values. However, I am not sure how can I
generate these values automatically. I have an equation which gives two
outputs - variance and expected return. I can maximize/minimize/set to a
particular value these two outputs, but how to I generate a set of these two
outputs .. The only option I can think of right now is manually. For example,
I set the solver to solve return = 5%, and get corresponding value of
variance. Then I set return = 6%, and find corresponding value of variance.
Is there a way I can do this automatically for me and generate a bunch of
values ?

Thanks,
Prabhat

 

[Mike Middleton]

Prabhat -

If you want the efficient frontier, maybe you could use the VBA solution at
http://www.solver.com/invefficientfrontier.htm
or perhaps you could adapt it for your specific model and situation.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel