RISKOptimizer and Evolver Constraint Solver
This new feature greatly enhances these optimizers’ ability to handle complex sets of model constraints.
RISKOptimizer and Evolver XDKs
More thorough documentation for these Excel Developer Kits has been added. Also, a backwards compatibility layer was made available so that macros written using the previous macro control language will continue to function.
Simulation with Optimization
Available with @RISK Industrial or the DecisionTools Suite Industrial, RISKOptimizer is the simulation optimization add-in for Microsoft Excel®. RISKOptimizer combines the Monte Carlo simulation technology of @RISK, Palisade's risk analysis add-in, with genetic algorithm optimization technology to allow the optimization of Excel spreadsheet models that contain uncertain values. Take any optimization problem and replace uncertain values with @RISK probability distribution functions that represent a range of possible values. For each trial solution RISKOptimizer tries during optimization, it runs a Monte Carlo simulation, finding the combination of adjustable cells that provides the best simulation results.
Excel Ease of Use
RISKOptimizer is a true add-in to Microsoft Excel, integrating completely with your spreadsheet. Define your models, adjust your settings, run optimizations, monitor progress, and generate reports – while never leaving Excel. Streamlined dialog boxes mean fewer open windows to navigate.
Standard optimization programs are good at finding the best combination of values to maximize or minimize the outcome of a spreadsheet model given certain constraints. However, these programs are not set up to handle “uncontrolled” uncertainty, and require static values for any factor that is not being adjusted by the optimization. This forces modelers into making decisions based on overly simplistic or inaccurate results.
Add Simulation to Optimization
Suppose you have several factories and want to find the best locations to manufacture different products to meet demand in nearby cities. You want to maximize profits and minimize shipping costs. This is a straightforward optimization problem where you want to assign manufacturing volume, by product, to different factories. But key factors out of your control are uncertain: shipping costs, demand, etc. Traditionally you would have had to guess at the uncertain factors and hope for the best. With RISKOptimizer, those uncertain factors are represented with probability distribution functions (like Normal, Triang, etc.) so that a Monte Carlo simulation can be run for each trial allocation of manufacturing volume. In this way, you can maximize the mean of the simulated output – say profits – an account for risk during optimization.
Add Optimization to Simulation
@RISK uses Monte Carlo simulation to account for the uncertainty in models and determine the probability of various outcomes occurring. But Monte Carlo simulation does not deal with decision variables whose values you can control. It handles random, uncertain values at a single state of those decision variables.
Suppose you are developing a new product and want to determine whether or not this venture will pay off in the long run. You build a standard spreadsheet model to calculate the profit, replacing uncertain factors like demand and material costs with @RISK functions. Then you realize that some of your assumptions are based on using specific vendors and production methods to construct your product. There may be other vendors and methods available to you that could save money. It's also possible that some production methods may make shipping costs unattractive. With @RISK alone, you could run multiple simulations and compare results - but did you try every possible combination of inputs? With RISKOptimizer, you can try different combinations of vendors and methods to maximize your profits
Using RISKOptimizer involves three simple steps:
1. Set Up Your Model.
The RISKOptimizer Model window provides one-stop setup for all optimization problems. Here you specify the target cell and statistic, identify cells to adjust, and define constraints. Adjustable cells and constraints support cell ranges for easy setup and changes, while target cells can be maximized, minimized, or approach a specific goal.
More on Setting Up Your Model
Defining Ranges and Stopping Conditions
When defining adjustable cells, you can specify the maximum and minimum boundaries of ranges of cells directly in Excel, greatly simplifying setup and making changes easy. For example, you can tell RISKOptimizer to adjust cells B1:B5, with a minimum value for each in A1:A5, and a maximum value for each in C1:C5. Multiple groups of cells may be specified, with multiple ranges in each group.
You must also define constraints in your model. For example, there may be limited resources which must be modeled. When defining constraints (hard or soft), you can also specify minimums and maximums with cell ranges.
Finally, set stopping conditions for your optimization, telling RISKOptimizer when to stop each simulation and when to halt the optimization as a whole.
RISKOptimizer uses six different solving methods that you can specify to find the optimal combination of adjustable cells. Different methods are used to solve different types of problems. The six methods are:
Recipe - a set of variables which can change independently.
Grouping - a collection of elements to be placed into groups.
Order - an ordered list of elements.
Budget - recipe algorithm, but total is kept constant.
Project - order algorithm, but some elements precede others.
Schedule - group algorithm, but assign elements to blocks of time while meeting constraints.
In your spreadsheet itself, you need to add probability distribution functions to describe uncertain factors beyond your control. For more on probability distribution functions, see @RISK.
RISKOptimizer also allows a great degree of control over how it performs the optimization itself. You can set optimization and simulation parameters, runtime settings, control macros, and more in the RISKOptimizer Settings dialog.
2. Run the Optimization.
Click the Start icon to start the optimization. RISKOptimizer will start generating trial solutions, and running Monte Carlo simulations on each one, in an effort to achieve the target set in Step 1. The summary RISKOptimizer Progress window appears, showing simulation status and best answer achieved thus far. This window lets you pause, stop, and run the optimization using playback controls. You can also monitor progress in detail with the RISKOptimizer Watcher. Tabbed reports show real-time updates on best answers achieved, all solutions tried, the diversity of solutions being tried, and more.
More on Running the Optimization
How RISKOptimizer Works
During an optimization, RISKOptimizer generates a number of trial solutions and uses genetic algorithms to continually improve results of each trial. For each trial solution, a Monte Carlo simulation is run, sampling probability distribution functions and generating a new value for the target cell - over and over again. The result for each trial solution is the statistic that you wish to minimize or maximize for the output distribution of the target cell (mean, standard deviation, etc.). For each new trial solution, another simulation is run and another value for the target statistic is generated.
Get Results Fast
RISKOptimizer uses two advanced techniques to minimize runtimes and generate optimal solutions as quickly as possible. First, RISKOptimizer uses convergence monitoring to determine when a sufficient number of iterations have been run (but not too many). This insures that the resulting statistic from the target cell's probability distribution is stable, and that any statistics from output distributions referenced in constraints are stable. RISKOptimizer can also "project" convergence based on prior simulations, saving time during an optimization. Secondly, RISKOptimizer uses genetic operators to generate trial solutions that move toward an optimal solution as quickly as possible. Genetic algorithms search the entire solution space, finding the global solution and zeroing in on it.
3. View Optimization Results.
After optimization, RISKOptimizer can display the results of the original, best, and last solution on your entire model, updating it with each scenario in a single click. This makes it easy to decide the best course of action. You can also generate reports directly in Excel for an optimization summary, log of all simulations, and log of progress steps.
RISKOptimizer optimization and simulations are calculated 100% within Excel, supported by Palisade sampling and statistics proven in over twenty years of use. Palisade does not attempt to rewrite Excel in an external recalculator to gain speed. A single recalculation from an unsupported or poorly reproduced macro or function can dramatically change your results. Where will it occur, and when? Correct results—and fast—using RISKOptimizer!
Compatibility: RISKOptimizer is compatible with Excel versions 2000 through 2007
NOTE: For more information about RISKOptimizer, please take a look to the Tech Corner: Using RISKOptimizer. Click here to view the RISKOptimizer tutorial.