August 2006

From mortgage portfolios to credit assets, property appraisal to energy markets, @RISK is being rolled out at every turn in the world of finance. Recently in London, financial managers from across the spectrum of industry gathered to share ideas, swap applications, and learn new insights.

continue below

Seminar Schedule
:: Live Web Training
Risk and Decision Assessment Training using @RISK, Part I
September 11-12
October 30-31

Risk and Decision Assessment Training using @RISK, Part II
September 14-15
November 2-3

Project Risk Assessment using @RISK for Project
October 5-6

:: Regional Seminars
Risk and Decision Assessment Training using @RISK and the DecisionTools Suite
September 18-19, Toronto

September 25-26, Boston

October 2-3, Ithaca

October 9-10, Baltimore
October 23-24, Columbus

full schedule and registration

Ask Amy
Expert Answers to
Technical Questions

Dear Amy,

Can @RISK use Excel's Solver to run an optimization between iterations during simulation?

— R.R.

Dear R.R.,

If you are combining Monte Carlo simulation with optimization, you should consider Palisade's RISKOptimizer tool. Available by itself or with @RISK or DecisionTools Suite Industrial editions, RISKOptimizer uses innovative genetic algorithms to come up with trial solutions, then automatically performs a Monte Carlo simulation on each trial solution. You can optimize your @RISK models “as-is” without changing them. With RISKOptimizer, you can solve problems no other solver can.

continue below

Ask Amy continued from above

If you want to use Excel’s Solver, you can do so via macros.  @RISK has the ability to call macros between iterations.  This is especially useful when a model needs to perform some type of iterative calculation between generating the random samples and storing the output result.

To run a macro during simulation, the @RISK functions must be "locked" during the extra worksheet recalculations.  Otherwise the @RISK functions will resample and the model will not remain static during the processing of the macro.  This is especially problematic if the cells targeted by the macro are dependent on the cells with @RISK functions.

To begin, design the macro to first make static copies of the random samples for the optimization process to act on.  That is, the input samples are placed as values into the model via the macro.  It must be this way (disconnected from the model) so the Solver routine can freely recalculate the model without the @RISK functions re-sampling new values with each recalculation, thus allowing the Solver routine to solve based on the static model.  

Click on the “@RISK Solver Example” link below for a sample spreadsheet model.  In the example you will note a macro called "PlaceSampleAndRunSolver" which first places the static copy of the @RISK function sample into the model and then starts the Solver optimization.  The series of events that occur with each iteration are as follows:

  1. @RISK generates random sample for the @RISK function in the cells labeled ‘Generate Sample Here’.
  2. @RISK calls the "PlaceSampleAndRunSolver" macro which performs the following operations
    1. Copy the value of the samples from the cells labeled ‘Generate Sample Here’ to the cells labeled ‘Copy Sampled Values Here’ in the corresponding row.
    2. Start the Solver routine to find the optima.
  3. @RISK saves the output results after running the optimization in the cells tagged with RiskOutput().

The result population will be a distribution of optimal results. Try it out. 

Note: Before you run a simulation on the example, you’ll need to check that the reference to the Solver add-in from within your VBA application is correct. To do this, first launch @RISK.  From your Excel menu select Tools > Macros > Visual Basic Editor.  Within the VBA module, select Tools > References.  This will bring up a list of all available references. Make sure SOLVER is checked.  If Solver is not listed, or if it’s listed but is labeled MISSING, you’ll need to locate the Solver.xla file on your computer using Windows Search. Once you’ve located the file, click on the ‘Browse’ button to the right of the References list.  A window will appear with the title ‘Add Reference’.  At the bottom of this window, in the field labeled ‘Files of type:’, click on the drop-down arrow and select ‘Microsoft Excel Files (*.xls,*.xla)’.  Browse to the location of the Solver.xla file on your computer and click ‘Open’.  Click ‘OK’ in the ‘References’ window.  You should now be ready to run a simulation on the example.

learn more about RISKOptimizer
@RISK Solver example
KnowledgeBase articles about Palisade Software

E-mail comments or suggestions to:
2006 Palisade Corporation, 798 Cascadilla Street, Ithaca, NY 14850 USA