April 1999 Why Spreadsheets Should Be in OR/MS Practitioners' Tool Kits By Thomas A. Grossman Jr. Spreadsheets have the potential to revolutionize management science. They are certainly revolutionizing management science as experienced by tens of thousands of business school students every year, who as end-user modelers now get to actually do management science rather than study algorithms they'll never be able to access, or learn how to become "informed consumers" of a micro-niche of the management consulting industry. However, spreadsheets don't seem to be getting much attention in the practice community. In this article we argue that spreadsheets have an important place in the practitioner's tool kit. Science is at the center of OR/MS, but application of OR/MS is as much art and craft as it is science. Skilled craftsmen know it's important to choose the right tool for the job, and smart OR practitioners recognize the value of selecting the best software tool for each activity. Spreadsheets provide real benefits to OR practitioners. Here's why: 1. Spreadsheets are more powerful for "real" OR modeling than is generally realized, and getting more so rapidly. 2. Spreadsheets provide an integrated platform that meet many practitioner needs better than specialized OR software. 3. Spreadsheets are useful for quick prototyping, even of models that will ultimately be written in special-purpose software. 4. Spreadsheets provide an excellent communication tool for non-technical clients. When not to use spreadsheets Spreadsheets are a source of worry to some OR professionals, and the notion of using spreadsheets for OR/MS — particularly by naive end user modelers — can induce gnashing of teeth and darkening of countenances. Criticisms of spreadsheets for OR/MS are often valid, since (like any software) spreadsheets have definite limitations. So before we discuss using spreadsheets for management science, let's talk about not using spreadsheets for management science. There will always be models and applications where spreadsheets just plain won't work, or where spreadsheets could be made to work but are not appropriate. It is a universal truth of programming that special-purpose software has big advantages over general-purpose software whenever size, scope, complexity or requisite technical expertise are sufficiently large. Spreadsheets are slower, dumber, less efficient and less scaleable than stand-alone management science software, and always will be. If your model is big and complex and hard, then DON'T USE SPREADSHEETS. Examples where spreadsheets generally don't make sense include models where the algebraic representation has more than two or three indices; complex queueing simulations; models where "standard" LP/QP/IP techniques are insufficient and custom algorithms are required; and models with highly complex data pre-processing. Also, spreadsheet models probably don't make sense in production IT environments. However, if your model is small (don't forget that "small" gets bigger every day!) and more-or-less straightforward, you're crazy not to use spreadsheets. Why would anyone want to implement, say, a 200-variable transportation problem in anything other than a spreadsheet? Spreadsheets are useful for big problems, too. Industry users routinely optimize spreadsheet LP models with 15,000 decision variables (although at that size model management and debugging issues would argue for a transition to a modeling language such as AMPL) and quadratic programs with 500 decision variables. Spreadsheets are a great integrated platform for the unsung but vital activities of OR practice: data management, reporting and graphing. These issues don't get much attention in the research literature, but they are key to the day-to-day business of practitioners. Due to their flexibility, ubiquity and ease of use, spreadsheets can be a good choice for data management and report generation, and are often the best choice for graphing. (Again, up to a point: When the datasets are too large, swap the spreadsheet for a database. If the graph is too complicated, use Mathematica. Your mileage will vary.) If you're an OR/MS project leader stuck in O'Hare Airport, why not use the Excel Solver that's already sitting on your laptop to whip up a prototype instead of waiting for a young OR associate back home to program the matrix generator for you? With an easy interface, built-in linear and non-linear optimizers, and available add-in packages for simulation and meta-heuristics, spreadsheets are a very pleasant environment for prototyping and general hacking. (Also, Solver provides a quick method for solving a system of linear equations: Feed the equations to Solver as constraints, create a dummy OBJ, and go. No matrix manipulations nor programming required.) Non-technical communication tool Spreadsheets are tremendously valuable as a tool for communicating model functionality to non-technical clients. It is well-known that getting clients to "buy in" to OR/MS models is a difficult challenge. Who among us with practice experience has not witnessed eyes glazing over, women fainting, and strong men weeping in the face of an algebraic LP formulation? Like most normal human beings, our clients and potential clients don't understand our algebraic formulations. But clients LIKE spreadsheet models! They provide a concrete representation of a model that ordinary people often find easier to understand than the more powerful abstract algebraic representation that OR/MS professionals use. Unlike specialized OR software, spreadsheet models automatically provide "what-if" tools without additional programming. You can hand someone a laptop with Excel and have them change a number to "see for themselves." If you can implement your model (even a scaled-down version) so the client actually understands it, why not do it? I believe that OR practitioners owe it to themselves to learn at least something about doing management science in spreadsheets. Yes, the latest interior point algorithm implemented in assembler and running on networked workstations will be a zillion times faster than the Excel Solver. But in the blink of an eye, Solver on a cheap Pentium can optimize problems that we could barely solve 10 or 20 years ago. There are plenty of profitable consulting engagements that use run-of- the-mill 500-variable LPs or 100-variable binary IPs that spreadsheets can generally handle without difficulty. Why not program them in an easy computing environment that the client might just be able to maintain on their own? Spreadsheets offer OR/MS practitioners many benefits in terms of number-crunching, implementing, prototyping, and communicating. They can enhance client acceptance and provide convenient data management. Spreadsheets are a cheap and useful addition to the practitioner's tool kit. If you are a practitioner with a varied practice, you might well find that a day spent learning how to do OR in spreadsheets will pay off for you in some pleasing ways. The accompanying story suggests where to get started finding resources for spreadsheet OR. Further reading Leon, Przasnyski and Seal [1996] provide examples and survey results on the use of spreadsheets by OR practitioners. See Decisioneering [1999] for several spreadsheet simulation modeling success stories; TRW and Bankers Trust are particularly interesting. Fylstra et al. [1998] discuss the technical details of the Solver. Conway and Ragsdale [1997] provide useful guidelines for formulating optimization models in spreadsheets. Savage [1997] discusses the weaknesses and benefits of spreadsheets. Cragg and King [1993] discuss problems with spreadsheet models in general, and identify opportunities for OR practice. Powell [1995] discusses the craft skills of modeling. Plane [1994] makes the case for end user modeling. Acknowledgements Thanks to Steve Powell and Peter Horner for comments on an early draft. Sources Conway, D. G., and C. T. Ragsdale, 1997, "Modeling optimization problems in the unstructured world of spreadsheets," Omega, International Journal of Management Science, Vol. 25, No. 3, pp. 313-22. Cragg, P. B., and M. King, 1993, "Spreadsheet modelling abuse: An opportunity for OR?" Journal of the Operational Research Society, Vol. 44, No. 8, pp. 743-52. Decisioneering, 1999, "Application Reports" Web page, [accessed March 1999]. Available at http://www.decisioneering.com/crystal_ball/stories_index.html. Fylstra, D., L. Lasdon, J. Watson, and A. Waren, 1998, "Design and use of the Microsoft Excel Solver," Interfaces, Vol. 28, No. 5, pp. 29-55. Leon, L., Z. Przasnyski, and K. C. Seal, 1996, "Spreadsheets and OR/MS models: An end-user perspective," Interfaces, Vol. 26, No. 2, pp. 92-104. Plane, D. R., 1994, "Spreadsheet power," OR/MS Today, Vol. 21, No. 6 (December), pp. 32-38. Powell, S. G., 1995, "The teachers' forum: Six key modeling heuristics," Interfaces, Vol. 25, No. 4, pp. 114-25. Savage, S. L., 1997, "Weighing the pros and cons of decision technology in spreadsheets," OR/MS Today, Vol. 24. No. 1. (February), pp. 42-45. Getting started with management science in spreadsheets Management science in spreadsheets is different than traditional management science. The spreadsheet model representation is fundamentally different than the traditional algebraic representation. Spreadsheet management science tools (usually in the form of Excel "add-ins") have to be sought out. Training resources need to be acquired. Where should a practitioner who wants to use spreadsheets for management science start to look? Likewise, where should a spreadsheet user who lacks advanced management science training go to deepen his knowledge of management science, or to find ways to extend the reach of their spreadsheet's tools? We suggest that people interested in spreadsheet management science start on the World Wide Web, by visiting the "Resources for Management Science in Spreadsheets" homepage, at http://www.ucalgary.ca/~grossman/resources.htm. (The INFORMS Education Forum, http://www.informs.org/informed/ will maintain this link should it ever move.) The resources page gathers in one place a wealth of information on spreadsheet management science. The page includes a list of spreadsheet management science survey textbooks, specialized spreadsheet management science books, carefully selected readings, and courses/training for working professionals. For those seeking software solutions, the page lists all known commercial add-in software for optimization, including bigger versions of the Excel Solver, competitors to the Solver, and genetic algorithms. There are lists of commercial software for simulation, decision trees and forecasting. There is even a list of mail order book providers to simplify acquiring resources. Web links, mail addresses, telephone numbers and ISBN's are all provided. The Resources web page will be updated and maintained regularly. Stop by often to see what's new! Additions and suggestions to improve the page are always welcome. Send them to the author at grossman@ucalgary.ca. Links 1. Resources for Management Science in Spreadsheets: http://www.ucalgary.ca/~grossman/resources.htm 2. INFORMS Education Forum: http://www.informs.org/informed/ — T.G. Tom Grossman of the University of Calgary is president of the INFORMS Education Forum and a former practitioner with Decision Focus (now Talus Solutions). He can be reached via http://www.ucalgary.ca/~grossman Table of Contents OR/MS Today Home Page OR/MS Today copyright © 1999 by the Institute for Operations Research and the Management Sciences. All rights reserved. Lionheart Publishing, Inc. 506 Roswell Street, 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 1999 by Lionheart Publishing, Inc. All rights reserved.