OR/MS Today - August 2010|
Add - Ins & Analytics
Spreadsheet O.R. Comes Of Age
Current generation of commercial spreadsheet solvers provides extraordinary speed and power for serious work.
By Thomas A. Grossman
The spreadsheet has come of age as an operations research tool. Microsoft Excel is clearly an appropriate platform for serious O.R. work. The efficient O.R professional will find that the spreadsheet can afford opportunities to accomplish O.R. analyses in less time, at lower cost and with greater client acceptance than with traditional standalone O.R. software.
If you have not kept current with recent developments, the spreadsheet can seem like an afterthought, a handy place to store data before sucking it into custom software, or a convenient reporting tool but not a vehicle for serious O.R.work. This perception is out of date. The current generation of commercial spreadsheet solvers provides extraordinary speed and power and can handle problem sizes limited only by hardware memory. In addition, high-quality spreadsheet add-ins for simulation, decision trees, forecasting, queueing and other O.R. techniques are readily available.
Since 1997 I've been tracking the ecosystem of "spreadsheet analytics." By this I mean tools and techniques related to using spreadsheets for business analysis, which comprises O.R. and much more. Back in 1997, only a limited number of O.R. tools existed. Today, the ecosystem of spreadsheet analytics has expanded dramatically.
To better understand what's available, a team of researchers at the University of San Francisco recently surveyed hundreds of spreadsheet analytic products in order to ascertain the current state of the art. We uncovered more than 180 commercial products from 150-plus vendors, as well as a handful of notable academic tools that we have compiled as an open, online resource for any interested analyst. To get more details, visit "Spreadsheet Analytics: Resources for Spreadsheet Analysts" at www.usfca.edu/bps/spreadsheet-analytics and see the accompanying story "Resources for spreadsheet analysts." This article highlights those resources of greatest interest to the O.R. professional.
Happily, spreadsheets have many attractive features for the programmer. The spreadsheet is in the class of "rapid development languages," it is a "fourth-generation language," and it functions as an "integrated development environment" [Grossman, Mehrotra and Özlük 2007]. This means that spreadsheets work well when time is short and provide many easy-to-use features that make the analyst more productive.
However, spreadsheets have important limitations. They do not scale up well (although careful design makes a big difference) and can be tricky to maintain over time (unless you carefully provide for future maintenance). And, of course, spreadsheets suffer from "dimensional arthritis" because it is challenging to program more than two or perhaps three subscripts. The spreadsheet's well known ease of programming causes a subtle problem: Because it is easy to get away with poor programming practices, many developers acquire bad habits that would not have formed if they were working in a procedural programming language.
In my view, implementing a model in a spreadsheet should be approached with the same seriousness and discipline as implementing a model in C++ or an algebraic language. Remember,when you are writing Excel formulas you are writing software. As with any computer programming language, a disciplined approach to development is required when programming with a spreadsheet. Well-known software development principles apply: use a top-down modular structure, experiment with different designs before starting to write formulas, have a clear program flow, keep data separate from computation,write for the reader, plan on future maintenance, etc. When coding in a spreadsheet computer programming language it is especially important not to let the spreadsheet's ease of prototyping and exploration distract you from disciplined development.
Spreadsheet optimization add-ins. The current crop of commercial spreadsheet optimization add-ins offers top-flight performance. I recommend that O.R. professionals avoid the free "Excel Solver" that comes with Excel. It is great for its intended purpose of giving a mass audience a taste of optimization technology. However, the free Excel Solver wasn't intended for use by O.R. professionals, and the modest investment in a commercial optimization add-in provides much greater functionality.
Commercial spreadsheet optimization add-ins can handle problems of any size, limited only by physical computer memory. Real-world spreadsheet models with tens of thousands or even hundreds of thousands of decision variables are routinely optimized. The vendors have invested heavily to incorporate ongoing advances in algorithms. Multiple algorithms for linear and non-linear models are readily available, including specialized algorithms for specialized problem types. Spreadsheet solver products have the ability to detect model properties such as linearity and convexity and automatically choose the right algorithm, while also offering a growing capability to automatically detect and fix discontinuous and non-smooth spreadsheet model implementations.
Lindo Systems'What's Best! offers four options for different size problems. They provide several algorithms including LP, barrier, non-linear, global and stochastic. Frontline Systems Premium Solver product line offers three options for different size problems. They provide several algorithms including LP, barrier, non-linear,MIP, evolutionary and interval global algorithms, and also support the use of third-party plug-in solver engines such as GUROBI and XPRESS. Both vendors have decades of experience in spreadsheet optimization, backed up by strong scientific and software talent.
Spreadsheet meta-heuristic add-ins. Our survey shows several new spreadsheet genetic algorithms and other spreadsheet meta-heuristics at a variety of price points. Frontline Systems provides an evolutionary algorithm in its Solver family of products. A meta-heuristic is bundled with Crystal Ball and @Risk for simulation optimization. Several add-ins provide just a meta-heuristic.
Spreadsheet simulation. For spreadsheet Monte Carlo simulation, the old duopoly of Oracle's Crystal Ball and Palisade's @Risk is being challenged at the high and low end. For users with limited budgets, our survey found several companies that provide low-cost Monte Carlo simulation capability.
On the high end, long-time leaders Crystal Ball and @Risk have undergone steady and impressive improvement and remain formidable products with a sizable international user base. They face competition from two notable new products, Vose Software's ModelRisk and Frontline System's RiskSolver.
Both Crystal Ball and RiskSolver use Frontline's Psi technology to greatly speed up simulation runs. RiskSolver has innovative "simulation optimization" capability that richly integrates simulation with Frontline's optimization capability. The result is a truly impressive product that may be technically superior to non-spreadsheet software for certain types of problems.
For the everyday analyst, spreadsheet sensitivity analysis is perhaps the most widely used technique. Our survey found tools for one- and two-parameter sensitivity and summarizing sensitivity results with a tornado chart and spider chart, as well as a programming approach for running scenarios through your model.
Spreadsheet goal-seeking is improving and the clunky Excel Goal Seek tool is no longer the only option. Our survey found new goal-seeking tools that handle multiple goal seeks at once, and can remember and repeat them.
Add-Ins for Data-Driven Analytics DATA-DRIVENANALYTICS refers to techniques for learning from a dataset; the emphasis is on discovering the insight hidden in the data. In contrast, with model-driven analytics the emphasis is on algorithmic manipulation of selected model inputs, with the data playing a supporting role.
Our survey found plenty of spreadsheet statistics add-ins at a variety of price points. We found three commercial tools for spreadsheet data mining. Our survey found no true spreadsheet business intelligence tools beyond the existing Excel features of sort, filter, pivot table and pivot chart. Our survey discovered five general-purpose products for spreadsheet neural networks.
Our survey found several spreadsheet Six Sigma and quality tools. Almost a dozen spreadsheet time-series forecasting products are out there that generate time-series forecasts using smoothing, regression and neural network approaches. A marvelous spreadsheet queueing add-in gives users custom functions to program queueing theory formulas right in their spreadsheet, plus a couple of other academic tools. We also found a set of spreadsheet project management templates, and an add-in for spreadsheet data envelopment analysis.
The big open question in the spreadsheet analytics space is Google. Where is Google going with Google Spreadsheet? Although wonderful for collaboration, Google Spreadsheet is currently an analytic wimp. Google has not released a product roadmap so we don't know their intentions. A small eco-system of add-in providers is emerging. It will fascinating to see what Google does and whether any existing operations research add-in vendors adapt their software to work with Google Spreadsheet a challenge whose difficulty and expense is unknown to this author. In addition, the open source OpenOffice.org products might yet take off.
Thomas A. Grossman (email@example.com) is an associate professor of business analytics at the University of San Francisco. He teaches courses in Spreadsheet Analytics, Business Analytics and Spreadsheet Engineering & Management. He is developing a new MBA major in Business Analytics. He does research on how spreadsheets are used in organizations and how they can be used better. Web site: http://web.usfca.edu/bps/faculty/Thomas_Grossman/
OR/MS Today copyright © 2010 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
Web Site © Copyright 2010 by Lionheart Publishing, Inc. All rights reserved.