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.
Overview
This article will focus on Excel add-ins for operations research. We'll start with a discussion of the spreadsheet for O.R. modeling. Next, we'll summarize the available O.R. add-ins. It is helpful to distinguish three classes of techniques. The first class is "model-driven analytics," where the model and algorithm are the focus of attention. The second class is "datadriven analytics" where you have a large data set and want to discern the story it has to tell you. The third class is "business function analytics," where techniques have been devised to address certain well-defined business issue. We'll then touch on spreadsheet tools for essential pre- and post-analytic activities, and new tools for deploying spreadsheet applications to multiple users. We'll conclude with a summary and some speculation about Google Spreadsheet.
The Spreadsheet for O.R. Modeling
Spreadsheets have many advantages for the operations research professional. Spreadsheets are the dominant platform for building business models. Spreadsheets are ubiquitous and enjoy wide acceptance. For many people, spreadsheets have a transparency that makes them easier to understand than traditional OR software.
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 Add-Ins for Model-Driven Analytics
Model-driven analytics is when you have a model and need to extract insight or decision guidance. Model-driven analytics are at the heart of operations research and are the dominant content of business school quantitative courses. I will address several model-driven analytic techniques, starting with optimization.
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.
Other Techniques
Our survey indicates that the spreadsheet decision tree space is growing with new products to compete with the business school standard TreePlan. Both Palisade and Frontline have decision tree products integrated with their other tools.
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.
Business Function Analytics
Business function analytics is about tools designed for specific functional areas of business. The survey found a half-dozen spreadsheet finance tools for pricing various types of derivatives, to support trading decisions (with integration to brokers) and even a tool for automatic data feeds. In the spreadsheet marketing space, we found a tool for spreadsheet marketing engineering, templates for spreadsheet marketing plans and a spreadsheet advertising keyword pricing tool.
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.
Before and After Analysis
Most O.R. analysts spend a fair amount of time on analytical "blocking-and-tackling" activities that have to be done well but are not particularly gratifying to do. These include processing your data before analysis and doing reporting afterwards. A half-dozen spreadsheet data management and cleaning tools made the cut for our survey, including a couple that are built into Excel. Several handy spreadsheet data consolidation tools, including Excel's often-overlooked consolidate feature, are also available. On the back end, users will find products to automate and simplify the transfer of analytic outputs from spreadsheets into reports and from spreadsheets into dashboards that graphically summarize results.
Deploying Spreadsheet Applications
By "spreadsheet application" I mean software written by one person(s) for use by others. Many O.R. projects conclude by deploying an O.R. application to less-sophisticated users. Spreadsheets can be tricky for deployment because users sometimes mess about in the cell formulas. One solution is to upload the spreadsheet app to a server and provide a simple interface. Users anywhere in the world can enter their data and then, after the spreadsheet runs invisibly, see the outputs from the spreadsheet computations. This "software as a service" (SAAS) approach is the way of the future. Our survey found that several vendors as well as Microsoft are providing variations on this service. In addition, some vendors offer a "spreadsheet compiler" that locks away the code inside an EXE or DLL so the users can benefit from the spreadsheet but can't see or change the formulas.
Conclusions
THE CAPABILITY of model-driven spreadsheet analytic tools is now very high. The new spreadsheet solvers have extraordinary capabilities to handle very large models, including spreadsheet models programmed in a way that is non-continuous or non-smooth, and even a capability to plug in thirdparty solvers. The spreadsheet marketplace offers a wide selection and growing competition in high-quality spreadsheet tools for other O.R. techniques.
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.
Resources for Spreadsheet Analysts
www.usfca.edu/bps/spreadsheet-analytics
By Thomas A. Grossman
Just what are the tools and resources available to the O.R. analyst who wants to be more effective using spreadsheets? It turns out that it is a tricky question. An overwhelming amount of material on the Web relates to spreadsheets, including add-ins, templates, stand-alone software, server-based software, checkers, compilers, Web-based spreadsheet deployment tools, methodologies, books, courses, conferences, Web sites and bagel slicers (well maybe not bagel slicers). These resources address a dizzying array of problems and opportunities. There is wide range in quality, from commercial software with full tech support and detailed user manuals to academic tools that provide basic functionality but little support, and everything in between.
We launched a Web site called "Spreadsheet Analytics: Resources for Spreadsheet Analysts" to help make sense of it all. Because of the complexity of the spreadsheet analytic space, we organized the available resources into eight top-level categories and more than 30 detailed categories. This makes it much easier for an analyst to find what she is looking for. It can be an eye-opening experience to see the diversity of spreadsheet analytic issues. Here are the three top-level categories that contain standard O.R. techniques:
- Model-Driven Analytics
- Data-Driven Analytics / Business Intelligence
- Business Function Analytics
Traditional-minded O.R. professionals will want to focus on these three categories. "Model-Driven Analytics" contains favorite techniques such as sensitivity analysis, solvers, Monte Carlo simulation and decision trees. "Data-Driven Analytics / Business Intelligence" includes statistics, data mining and business intelligence. "Business Function Analytics" is where you will find topics such as queueing, Six Sigma & quality and time-series forecasting.
O.R. professionals who engage with spreadsheet-loving customers, or who seek to make their O.R. work more impactful in client organizations, will want to browse the whole site. Here are the other five top-level categories:
- Data Management
- Reporting
- Development
- Control & Compliance
- Information Sources
O.R. professionals who write spreadsheet application software for deployment to users should look at the "Development" category, especially deployment & compilers and development practices. Anybody whose job requires regular engagement with spreadsheet information systems will find something of interest in all five of the above categories.
Note that the Spreadsheet Analytics Web site focuses on commercial-quality products that are backed up by a company that is likely to be around for a while, with someone to talk to if you need help. We made judgments to exclude products that didn't look fully developed or were thinly supported. We filtered out the freeware and academic products, with exceptions for areas where there are no commercial products, or where the product has something special going on. We included an open-source product if we felt there was a community standing behind it.
The Spreadsheet Analytics Web site is non-commercial with no advertising and no selling commissions. We didn't test these products and don't endorse any of them. Remember, your mileage will vary. The Spreadsheet Analytics Web site is provided as a public service by the Business Analytics Program of the University of San Francisco's School of Business and Professional Studies. Take a look at: www.usfca.edu/bps/spreadsheet-analytics
WHAT'S THAT WEB SITE AGAIN?
For more details on the categories, products, information sources, and "beyond the spreadsheet," visit http://www.usfca.edu/bps/spreadsheet-analytics.
Thomas A. Grossman ([email protected]) 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/
References
- Grossman, T. A., V. Mehrotra, Ö. Özlük (2007), "Lessons from Mission-Critical Spreadsheets", Communications of the Association for Information Systems, 20(60): 1009-1042, December.