An even more powerful method for simulation analysis, beyond parameterized simulation, is to use simulation optimization to automatically find the best value of one or more variables that we can control. We can put the computer to work, in effect performing parameterized simulations for many different combinations of values for our decision variables, and seeking the best combination of values for criteria that we specify.

Frontline's Risk Solver Pro and Risk Solver Platform can both handle simulation optimization problems -- at speeds up to 100 times faster than other software!

How Simulation Optimization Works

Optimization helps you make better choices when you have all the data, and simulation helps you understand the possible outcomes when you don’t. Frontline Solvers enable you to combine these analytic methods, so you can make better choices for decisions you do control, taking into account the range of potential outcomes for factors you don’t control.

This method, called simulation optimization, helps you make better resource allocation choices “here and now,” in situations with uncertainty. You simply create a model that includes:

  1. Decision variables for resources you do control, just as in a conventional optimization model
  2. Uncertain variables for factors you don’t control, just as in a Monte Carlo simulation model
  3. An objective to maximize or minimize, that may depend on decision and uncertain variables
  4. Constraints to satisfy, that may also depend on decision and uncertain variables

A Solver using simulation optimization can search through thousands of ways to allocate resources and, for each one, through thousands of possible future outcomes, and seek the best set of choices. The solution gives you a much better picture of the decisions you should make, given what you know for sure and the range of outcomes for factors that you don’t know for sure.

Defining a Simulation Optimization Model

Creating a simulation optimization model using Risk Solver Pro or Risk Solver Platform is straightforward. You follow these steps:

  1. Define decision variable cells (such as A1), using either the Solver Parameters dialog or the PsiVar() function. These are factors that are under your control – you (or the Solver) will decide what values they should have. 
  2. Define uncertain variable cells (such as A2), that contain formulas calling the PSI Distribution functions supplied by Risk Solver – for example PsiUniform() and PsiNormal(). These are factors that are not under your control.
  3. Build your model, using cell formulas that may depend on the decision variables, uncertain variables, or both.
  4. Each cell (such as B1) containing a formula that depends on uncertain variables (say =A1+2*A2) represents thousands of trial values, generated during each Monte Carlo simulation by sampling different values for A2 and computing =A1+2*A2.
  5. In other cells (such as C1), define the summary statistics you want, using functions such as PsiMean(B1) or PsiStdDev(B1). You may use formulas to compute further values based on these summary statistics.
  6. Define your objective and constraints for optimization. These may be cell formulas that depend only on the decision variables, depend on the uncertain variables through PSI Statistics functions, or depend on both.

Solving a Simulation Optimization Model

Solving a simulation optimization model using Risk Solver Pro and Risk Solver Platform is also straightforward. Follow these steps:

  1. Activate Interactive Simulation by clicking the light bulb button on the Risk Solver Ribbon.
  2. On the Output tab of the Task Pane to the right of the Excel window click on the green arrow Solve button.

Simulation optimization is a powerful, general framework for finding "best solutions," but it is computationally very expensive, and there are limits on the size and complexity of your model (number of decision variables, uncertain variables, constraints, and total formulas) if you want to find a solution in a reasonable amount of time. That said, Frontline Systems is already delivering the highest performance software for simulation optimization in Excel available today -- and we're continuing to take this capability much further to make it easier for you to make the best decisions in the least amount of time. Contact us if you'd like to learn more.

< Risk Analysis Tutorials Overview Page