Powerful tool eliminates much of the "guesswork" inherent to model derivation

By Marc Sullivan

December 1999

Palisade's new software RISKOptimizer replaces stochastic factors with distribution factors to minimize the uncertainties that standard optimization programs may encounter if the values of factors used in a model are undetermined. Employing genetic algorithms, RISKOptimizer continually analyzes models until it finds optimal maximum, minimum, or specified values for unknown factors. RISKOptimizer thereby can maintain the integrity and efficacy of models, even if the actual values of some factors are unknown.

System Requirements and Installation

Operating system: Windows 95, 98, Windows NT 4.0, and more recent versions of these operating systems. RISKOptimizer, however, cannot run in Windows NT 3.5.1, Windows for Workgroups 3.1.1, and Windows 3.1 or its earlier versions. Further, OS/2, Macintosh and Unix platforms are incapable of running the RISKOptimizer software.

Spreadsheets: Microsoft Excel 7.0 or more recent versions. RISKOptimizer cannot run in Microsoft Excel 5.0 or earlier versions. RISKOptimizer is incompatible with non-Microsoft spreadsheet packages such as Lotus 1-2-3.

Memory: At least 16MB of RAM is recommended; however, 32MB or more is preferable.

Disk space: The installation of RISKOptimizer requires at least 12MB hard disk space; however, as RISKOptimizer runs numerous simulations that can generate a significant amount of temporary data, it is recommended that 50MB hard disk space be available for these temporary files.

For this review, RISKOptimizer was installed and tested on a Windows 95 PC with a 266mghz processor and 32MB of RAM. The installation process, which utilizes the standard Plug-and-Play format, only entails a series of mouse clicks and directory specifications and is quite straightforward.

User Manual and Online Assistance

Palisade furnishes users with three sources of information: a printed manual, an online tutorial and the company Website. The printed manual for RISKOptimizer, which provides background information on how the software works and its practical applications, is very thorough and complete. Specifically, one chapter provides a history and explanation of genetic algorithms, which are at the core of the RISKOptimizer software. Further, the manual includes several example applications, outlining the structure of each example and the process through which RISKOptimizer finds the optimal solution. These example applications, which are also available in the "examples" folder loaded at the time of installation, operate as a template for models the user may choose to create.

The RISKOptimizer online tutorial offers a brief overview of uncertainty and optimization. An insightful feature of the tutorial is its use of an example application to identify uncertain factors and to provide a detailed step by step analysis of how RISKOptimizer specifies values for these factors.

Another important resource is Palisade's Website, www.palisade.com. The site contains a support link that offers a selection of software examples. Some example applications, which include explanations and macros that attach to the simulation, are available in zip format and can be easily downloaded.

User Interface

RISKOptimizer essentially is an "Add-In" to Microsoft Excel; therefore, the interface looks and feels like other Microsoft Excel tools. Because most of the model set-up exists within an Excel spreadsheet and uses Excel-style function formulas, a familiarity with Excel is desirable. RISKOptimizer launches simultaneously with the Excel spreadsheet, appearing as a "toolbar" in the spreadsheet itself.

Creating a model with RISKOptimizer is similar to creating an optimization model with Excel Solver; however, instead of the Excel Solver parameter box, the user identifies the components of the model in the RISKOptimizer settings box as shown in Figure 1. This box, like the Excel Solver parameter box, is used to specify the target cell and objective, adjustable cells and constraints. To set up the adjustable cells, the user selects a solving method, that is, the genetic algorithm to be used during the simulation. The available genetic algorithms are:

Recipe - used when the set of variables that are to be adjusted can be varied independently of each other.Order - a permutation of a list of terms; used when attempting to find the best way to arrange a set of given values.

Grouping - used when the problem involves multiple variables to be grouped together in sets. The number of groups RISKOptimizer creates will be equal to the number of unique values present in the adjustable cells.

Budget - similar to 'Recipe' except all values must total to a certain number. That number is the total of the variables' values at the time an optimization is started.

Schedule - similar to 'Grouping'; it is an assignment of tasks to times. Each task is assumed to take the same amount of time, much as classes at a school are all of the same length.

Project - similar to 'Order' except certain items must precede others.

The options button on the settings box allows the user to adjust the optimization's population and/or its stopping conditions. Stopping conditions are measured either by the number of simulations or by the amount of time expended. As long as the user writes, saves and attaches the macros to the RISKOptimizer settings at the selected interval, users can have these macros run before, after, or at varying intervals during the optimization as seen in Figure 2. Users can also choose the type of sampling to be used, either Monte Carlo or Latin Hypercube.

The RISKOptimizer Watcher, shown in Figure 3, enables users to view graphs that depict the progress of the optimization, as well to view the results of the optimization as such data becomes available. In addition, a detailed log records each input and output values as RISKOptimizer applies them to the model. The log also displays whether the optimization has met the constraints during each simulation.

Example: Transportation Model

Consider the simple transportation model depicted in Figure 4. Suppose a company has three distribution centers (St. Louis, Philadelphia, New Orleans) with variable per unit shipping costs to each demand location (Midwest, Northeast, Southeast, West) as shown in cells C3-F5. The quantities on hand at each distribution center are given in column H, with a holding cost of $1.50 per unit. The objective is to determine a shipping plan (in C10:F12) that fulfills the demand with the lowest possible total shipping cost. If demand is assumed to be deterministic with values of 6,000, 4,000, 2,000 and 1,500 units, respectively, then the Excel Solver can easily solve this linear program. From St. Louis, ship 3,500 units to the Midwest and 1,500 units to the Northeast; from Philadelphia, ship 2,500 2,000 and 1,500 units to the Northeast, Southeast, and West, respectively; ship 2,500 units from New Orleans to the Midwest. The total cost of the plan is $59,750.

Now suppose that the company would like to determine the optimal supply amounts (column H) to make available at each distribution center, as suggested in an example by Winston [1999]. Also suppose that the sales force was unsure of the demand for each region, but could come up with the estimates of minimum, most likely, and maximum values shown in the lower left-hand portion of Figure 4.

Solver is unable to solve this model because it represents a situation where not every values is known. RISKOptimizer has the capability to solve these kinds of models by replacing uncertain values with one of more than 40 probability distribution functions. For this problem, the initial demand values in cells C14:F14 have been replaced with the @RISK functions for the corresponding triangular distributions. As an example, the function RISKTRIANG(C18,C19,C20) is used in cell C22 for the Midwest region's demand. The relevant RISKOptimizer settings for this problem are illustrated in Figure 1 (above), where the goal has been specified as minimizing average total cost.

The user would then write and attach the following macro to allow for the calculation of the demand values using the @RISK Functions. RISKOptimizer would generate a set of demand values from the triangular function. Once done, RISKOptimizer would run the values through the Excel Solver routine via the macro. (Note - most models will not require a macro, but for this example model one was needed in order to retrieve values from the Solver tool.)

Sub TranMacro()

TranMacro Macro

Macro recorded 9/03/99 by Marc L. Sullivan

Range("C22:F22").Select

Selection Copy

Range("C14").Select

Selection PasteSpecial Paste: =x1Values, Operation: =x1None,

SkipBlanks:=_

False, Transpose;=False

SolverOk SetCell: = "$I$18", MaxMin Val: =2, ValueOf:="0",

ByChange:="$C$10:$F$12"

SolverSolve(True)

EndSub

This macro would be attached in the RISKOptimizer model setup and would be set to run before each simulation begins (see Figure 2). Each "iteration" would result in a new shipping plan. All of the shipping plans that meet the constraints of the model are summarized at the end of the run. The result would be a new set of calculated values for the quantities to hold, which takes the uncertainty into consideration. RISKOptimizer selected the supply amounts of 2,781, 5,360 and 4,700, respectively, at the three distribution centers, for a minimum total cost of $59,591.

Summary

Palisade's RISKOptimizer, a powerful tool that limits much of the "guesswork" inherent to model derivation, is a key resource in the operations research field where accurate forecasts are imperative. The software, which replaces unknown values with probability distributions, has the capability to combat uncertainty when assigning optimal values. As RISKOptimizer is an "Add-In" to Microsoft Excel, those familiar with Excel can easily implement the software to set up realistic models.

RISKOptimizer allows users to add macros and utilize them during simulations; therefore, the potential for setting up complex models and deriving solutions is limitless. The accompanying manual, online tutorial and Website information evinces Palisade's commitment to assist users in understanding the software and its various capabilities. Their recent publication of Wayne Winston's book entitled "Decision Making Under Uncertainty with RISKOptimizer," which contains numerous examples of operations management and financial planning models, is further evidence of this commitment.

RISKOptimizer, expanding upon what basic Solvers already offer, can make any model more valid by factoring out risk and uncertainty.

Marc Sullivan is a graduate of Drexel University with a B.S. degree in Commerce and Engineering Sciences. He is currently working at QAD as a software analyst.

References

1. Winston, W. , "Decision Making Under Uncertainty with RISKOptimizer," Palisade Corporation, 1999.

Vendor Comments

Editor's note: It is the policy of OR/MS Today to allow developers of reviewed software an opportunity to clarify or comment on the review article. Following are comments from Randy Heffernan, sales manager of Palisade Corporation.

"We appreciate ORMS/Today taking the time to review RISKOptimizer. RISKOptimizer is used by many companies to solve a wide range of optimization problems that include uncertain factors. Applications include yield management for airline seats and hotel rooms with uncertain demand; ordering policies for retailers; product mix decisions; retirement planning; and financial decision making including hedging strategies, calculating and controlling value at risk (VAR) of a portfolio, and portfolio investment mix. Furthermore, the publication of Decision Making Under Uncertainty with RISKOptimizer by Wayne Winston has spurred adoption of RISKOptimizer by many top level MBA programs worldwide, including the London Business School, Dartmouth College and Johns Hopkins University. RISKOptimizer is also available bundled with the latest version of @RISK in @RISK 4.0 Industrial version. Product information, tutorials and a free trial version of RISKOptimizer are available on Palisade's Website www.palisade.com.

For more information, contact:

Randy HeffernanTel: 607-277-8000

Fax: 607-277-8001

rheffernan@palisade.com

798 Cascadilla Street

Ithaca, NY 14850-3239

800 432 RISK (US/Can)

+1 607 277 8000

+1 607 277 8001 fax

sales@palisade.com

+1 607 277 8000 x318

+54-1152528795 Argentina

+56-25813492 Chile

+507-8365675 Panamá

+52 55 5350 2852 México

+511-7086781 Perú

+57-15085187 Colombia

servicioalcliente@palisade.com

ventas@palisade.com

www.palisade-lta.com