Software Survey
Spreadsheet Add-Ins for OR/MS
Once considered little more than a curiosity, spreadsheet add-ins evolve into powerful tools for analytical professionals
By Thomas A. Grossman
Like Dr. Johnson's dog walking on its hind legs — remarkable for being done at all, rather than for being done well — spreadsheet add-ins for management science were once perceived as a curiosity rather than as a serious analytical tool. This perception was not necessarily unfounded, in part because of the sluggish performance of early PCs (remember when a 386 was a hot piece of hardware?). Some older releases of popular add-ins suffered from poorly optimized code, cranky user interfaces, and unstable implementations that limited the effectiveness and relevance of spreadsheet add-ins.
That was then, this is now. Spreadsheet add-ins have evolved into powerful and flexible analytical tools for OR/MS professionals and end-user modelers alike. Spreadsheets are clearly a solid platform for OR/MS. Steady investment by multiple vendors has yielded efficient code, good user interfaces and excellent documentation. Although not bug-free, they are no more buggy than stand-alone OR/MS software, and running on modern PCs that are fast as a 1980s supercomputer, they can handle sizable problems with ease.
Spreadsheet add-ins are regularly used by end-user modelers to quickly analyze problems that once would have required the expensive intervention of OR/MS specialists using stand-alone software. In fact, some end-user modelers routinely use spreadsheet add-ins for linear optimization problems with 15,000+ decision variables. OR/MS specialists use spreadsheet add-ins for the convenience of an easy interface, widespread client acceptance, strong what-if capabilities, ability to manage input data, and excellent report and chart outputs.
Add-Ins in This Survey
The last time OR/MS Today addressed spreadsheet add-ins was a 1997 survey of 30 add-ins. In 2002, we doubt we can even identify all of the add-ins that contain OR/MS content, particularly because of the vast increase of finance add-ins. This year, we consider only add-ins that implement the core OR/MS tools of optimization, simulation, decision analysis, forecasting and queueing analysis. With two exceptions, we limit the survey to established products with a strong track record over a large customer base and established procedures for customer support. The exceptions are queueing analysis and parametric optimization, where the only available add-ins are provided by academics with limited resources for customer support. Many other fine add-ins can be found at the Spreadsheet Analytics Website: www.ucalgary.ca/mg/grossman/spreadsheetanalytics. The add-in vendors concentrate overwhelmingly on Excel, and add-ins for Lotus 1-2-3 and Quattro Pro are virtually non-existent.
Steady Improvement in Established Add-Ins
The OR/MS spreadsheet add-in market is dominated by the "Big 4" vendors with sizable product lines: Decisioneering and Palisade for simulation and stochastic analysis, and Frontline Systems and LINDO Systems for optimization. Frontline Systems has a distribution arrangement where their Solver add-in ships with every copy of Excel, Lotus 1-2-3 and Quattro Pro. In addition there are several niche players. The Big 4 all report significant use by a wide variety of industries.
Since the last survey, the dominant trend has been steady improvement in the Big 4's product offerings. Their products have improved stability, increased accuracy, faster implementations, better user interfaces and the ability to handle larger problem sizes. In addition, many of the vendors offer their products in the form of a "dynamic link library" (DLL) that can be called from procedural programming languages such as C++.
Palisade's RiskOptimizer product and the OptQuest feature within Decisioneering's Crystal Ball provide a capability to automatically perform a series of simulations that search over decision variables to improve or optimize an objective function.
Frontline Systems' ongoing R&D program is bearing fruit, with innovative features that may be of interest to OR/MS specialists who do not normally use spreadsheets. (Disclaimer: I report the company's statements, and have not tested the software myself.) Frontline Systems claims to be a world leader in global and non-smooth optimization (www.solver.com/pressback2.htm). They indicate that their Premium Solver Platform v5.0, currently in beta test, has a new solver engine based on interval methods for equation-solving and global optimization that can find all real solutions to a system of nonlinear equations, or the globally optimal solution to a nonlinear optimization problem. They say that v5.0 can automatically determine whether a model is linear, quadratic, smooth nonlinear or nonsmooth, and automatically select the valid or "best" Solver engines for use with the model, and that this product can pinpoint the exact cell formulas that make a model nonlinear or nonsmooth.
What is particularly important for the OR/MS profession is that the Big 4 vendors focus their attention on industry users, not on OR/MS specialists. The vendors tell me that they sell primarily to end-user modelers with sophisticated applications who have never heard of INFORMS. Stories about OR/MS applications using spreadsheet add-ins are being disseminated on the vendors' Websites, not in Interfaces and other journals more visible to OR/MS professionals. End-user modelers using spreadsheet add-ins are an important — perhaps dominant — mode of OR/MS application but are largely invisible to the OR/MS profession.
New Add-Ins
There are now two add-ins for time-series forecasting, one from Decisioneering and the other from John Galt Solutions.
There are three add-ins for queueing analysis. The Queueing ToolPak by Armann Ingolfsson provides Excel functions for common queueing theory formulas that make queueing theory computations effortless and facilitate what-if analysis. This product is vastly superior to the queueing theory template spreadsheets previously available. There is a set of Graphical Spreadsheet Queueing Simulation templates that allow easy process-driven simulation of queues that highlight the full details of the evolution of a queue over time and facilitate intuitive understanding of queueing processes. Sam Savage's Insight.xla provides an event-driven queueing simulation.
The most important new add-in performs parametric optimization in spreadsheets. It allows the user to specify any cell (or pair of cells) in a spreadsheet, and automatically performs a series of optimization runs using different values of that cell. (With fast PCs, performing multiple optimization runs for most models is perfectly acceptable.)
If the cell in question is a right-hand side of a canonical LP, the parametric optimization tool manages a series of optimization runs that demonstrate visibly the change in the optimal objective function value and decision variables as a function of the right-hand side value. This provides a richer and more accessible sensitivity analysis then the limited information provided by traditional shadow prices. (Shadow prices are a special case of the more general approach of parametric optimization, with enhanced "efficiency" for analysis of a narrow set of issues.) More important, parametric optimization allows any datum in the spreadsheet to be used, including a data value that feeds into multiple coefficients in the optimization model.
The Solvertable by Chris Albright at Indiana University (generously distributed without charge) is the only spreadsheet parametric optimization product with portable code, good documentation and a satisfactory user interface. Albright's Solvertable is well-documented, and runs on most (but not quite all) hardware/software configurations. It is widely used in MBA programs. Although many people have written a spreadsheet parametric optimizer (some VBA instructors assign this challenge to students), creating a reliable and portable product requires substantial investment. A parametric optimization product with commercial-quality customer support, professionally designed user interface, and bulletproof code would be a valuable contribution to the profession.
Further Reading
- 1. Savage, Sam, 1997, "Spreadsheet Add-Ins Survey," OR/MS Today, Vol. 24, No. 1, February 1997, pp. 46-48, condensed version available at www.lionhrtpub.com/orms/surveys/SSA/SSA97.html
- 2. Grossman, T. A., 1999, "Why Spreadsheets Should Be in OR/MS Practitioners' Tool Kits," OR/MS Today, Vol. 26, No. 2, April 1999, pp. 20-21; www.lionhrtpub.com/orms/orms-4-99/forum.html
- 3. Maxwell, D. T., 2002, "Software Survey: Decision Analysis: Aiding Insight VI," OR/MS Today, Vol. 29, No. 3, June 2002, pp. 44-51.
- 4. The Spreadsheet Analytics Website attempts to provide a comprehensive list of resources for OR/MS in spreadsheets: www.ucalgary.ca/mg/grossman/spreadsheetanalytics.
Thomas A. Grossman (thomas.grossman@haskayne.ucalgary.ca) is an associate professor at the Haskayne School of Business, University of Calgary, Calgary, Canada. He is president of the Spreadsheet Productivity Research Interest Group, http://www.ucalgary.ca/mg/grossman/spreadsheetresearch.
OR/MS Today copyright © 2002 by the Institute for Operations Research and the Management Sciences. All rights reserved.
Lionheart Publishing, Inc.
506 Roswell Rd., Suite 220, Marietta, GA 30060 USA
Phone: 770-431-0867 | Fax: 770-432-6969
E-mail: lpi@lionhrtpub.com
URL: http://www.lionhrtpub.com
Web Site © Copyright 2002 by Lionheart Publishing, Inc. All rights reserved.