Simulation Optimization

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 Premium Solver or Premium Solver Platform -- the leading optimization software for Microsoft Excel models -- works closely in concert with Risk Solver to find solutions to simulation optimization problems -- at speeds up to 100 times faster than other software!

• How Simulation Optimization Works
  
• Defining a Simulation Optimization Model
  
• Solving a Simulation Optimization Model

How Simulation Optimization Works

The overall process works like this:  Your simulation model includes uncertain variables whose values are defined by probability distributions. You can use the cells containing these uncertain variables in any formula in your model. Risk Solver performs a simulation with thousands of trials, where a different value is sampled for each uncertain variable on each trial, and your model is recalculated with these values. Statistics across all the trials are accumulated for any formula cell you designate. You can access these statistics in regular Excel formulas. And with Premium Solver or Premium Solver Platform, you can use these formulas in the objective and constraints of your optimization model.  For example, you could make average delivery time (computed by your model, based on decisions and uncertainties) an objective to be minimized, subject to a constraint that the cost of inventory should, with 95% probability, be less than a dollar threshold you specify.

Defining a Simulation Optimization Model

Creating a simulation optimization model using Premium Solver and Risk Solver 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 Premium Solver and Risk Solver is also straightforward. Follow these steps:

1. Activate Interactive Simulation by clicking the light bulb button on the Risk Solver Ribbon or toolbar.
   
2. Select Tools Premium Solver to display the Solver Parameters dialog, and click the 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.  Frontline Systems is already delivering the highest performance software for simulation optimization in Excel available today -- but we're taking this capability much further in the future!  Contact us if you'd like to learn more.

Simulation Models >

Simulation Analysis >

Risk Analysis Tutorial >

< Back to Simulation Introduction

 

To Learn More: For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.

User type Please Select Commercial User Consultant Corp Developer Govt/Military Professor Student VAR/ISV Firm Email address Name First Last Company University Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.