Solver question

[prspilot]

I have built a worksheet that calulates the theoretical return on investment
during a one year period. I have 300 days of the price of the stock in
column A. Column B is the daily % increase. The next several columns
contain formulas with lots of "If..Then..Else" statements based on a set of 5
variables. The last column uses all of the previous columns to calculate the
balance at the end of that day. The balance in the last column also of
course depends on the balance from the previous day. When I change variables
manually, the sheet works fine.

I want to solve for the maximum value of the balance on the 300th day by
playing with my 5 variables. Obviously, there are a lot of calulations
happening on the sheet every time I change a variable (300 rows times 5
columns worth of formulas), but I can do this manually, and get the result
very quickly.

This seems like a perfect problem for Solver, but when I set the balance
cell on the 300th day as my target, and tell it where my 5 variable cells
are, it acts like it solves the problem, but the variables never change, and
neiher does the answer. I have a less than optimum solution that I arrived
at by just playing with one variable at a time, but I would really like to
find the optimal solution. Any ideas?

 

[Mike Middleton]

prspilot -

The standard Solver that ships with Excel cannot usually handle
discontinuous functions like IF, CHOOSE, etc.

In some models you can replace the discontinuous functions with
relationships using integer variables, and then use the standard Solver to
get a solution.

Frontline Systems, the developer of Excel's standard Solver, also has
advanced versions that perform better with discontinuous functions. Many
quantitative methods textbooks include their Premium Solver. (Microsoft has
chosen to continue distributing only the original standard Solver with
Excel.) You can get information about Premium Solver at
http://www.solver.com/xlspremsolv.htm

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

 

[prspilot]

Thanks Mike,

I was thinking about writing a macro in VB that is just 5 nested FOR loops
that run through the ranges of my 5 variables and looks for a maximum at my
target. Will that work? I don't really care how efficiently I get the
answer.

I haven't used VB to write macros, and am unfamiliar with exectly how to do
it. Is there an example VB macro that would be similar enough to my problem
to give me a jump start?

Barry

 

[Mike Middleton]

prspilot -

I was thinking about writing a macro in VB ... <

Or, download the trial version of Premium Solver.

I'd opt for the latter.

- Mike