ORMS Today
February 1997 • Volume 24 • Number 1

Weighing the Pros and Cons of Decision Technology in Spreadsheets
In spite of drawbacks, 30 million users tip the scales in favor of using the analytical vernacular of management

By Sam Savage

A 1986 review of the spreadsheet optimizer What'sBest! in OR/MS Today described the software as a "breakthrough" product. However, the reviewer went on to express concerns that had "more to do with the spreadsheet environment than this particular software package ... the difficulty here again springs from the flexible, 'anything goes' nature of the calculations."

In a subsequent letter to the editor I expressed appreciation for the mostly positive review, but suggested that criticism of the spreadsheet, based on its lack of constraints, was counter to the OR/MS philosophy.

Today, spreadsheets are almost ubiquitous in the teaching of management science; most new textbooks are spreadsheet based. Spreadsheets are also widely used in many applications. There are, indeed, serious drawbacks to this environment, but I don't list flexibility among them. Below are my views on the pros and cons of this still relatively new medium for mathematical modeling.

Pros and Cons

First the Cons:

Documentability. The OR/MS Today review asked, "How do you document a spreadsheet model for ongoing maintenance and use by others?" A good question. I have heard it said that:
  • About 90% of spreadsheet models used in business contain errors; and

  • An equally high percentage of spreadsheet users are 95 percent confident that their models do not contain errors.
In [6] I suggested that the visible aspect of a worksheet which appears on the screen, and the underlying equations, may be viewed as the body and soul of the model, respectively. In an effort to expose the soul I recommended the use of "Worksheet Flow Diagrams" which had to be rendered by hand on printed copy as indicated in Figure 1.

Figure 1: The Body and Soul of Worksheet-Flow Diagrams

Since then, Microsoft Excel has added an "Auditing Tool" to automate this procedure as shown in Figure 2.

Figure 2: Auditing Tools Automate teh Renderign of Worksheet Flow Diagrams

Few people are aware of the auditing tools, which are particularly useful in deciphering models developed by others.

The bottom line? Even with the auditing tools, spreadsheets are still hard to document. If I learned that the FAA's air traffic control programs were spreadsheet based I would consider taking the train.

Scalability. By scalability I mean the ability to change the cardinality of a set of items within the model. Suppose, for example, you wanted to add a new product to the production model shown in Figures 2? Just insert another column to hold the new information, right? Well, maybe. If your cursor was in column C when you inserted the new column you're OK. If it was inserted at column B, then the SUMPRODUCT formula in cell F6 will ignore the new data! This of course assumes you were sophisticated enough to use a SUMPRODUCT formula to calculate the inner product in the first place. More naïve formulations of this model give wrong answers regardless of where the column is inserted, unless formulas are copied or edited.

The bottom line? Spreadsheet models rarely scale well. Something as simple as changing a dimension statement in a FORTRAN program could turn into hours of work in a poorly formulated worksheet.

Hyper-scalability. By hyper-scalability I mean the ability to change the cardinality of the sets themselves, that is, to add and remove dimensions. For example, suppose what you really needed was a multi-period production model covering four time periods. You might copy the model down or across the worksheet three times, or perhaps onto a new worksheet altogether. Now suppose you want to increase the complexity again to model plants at three locations. You must copy everything you just created two more times. Now how about scaling the model by adding a new product and a few new raw materials?

The bottom line? Forget it.

Now the pros:

Thirty million users.

The bottom line? In spite of their drawbacks, spreadsheets have overwhelmingly become the analytical vernacular of management.

A new level of technology
What long-term impact will spreadsheets have on OR/MS practice and education? To frame this discussion in an historical context, consider the following brief history of civilization

Step C represents a revolutionary rather than an evolutionary technological shift of which spreadsheets are a but a small part. When I started teaching OR/MS concepts with spreadsheets, I missed the significance of this, and simply kept doing what I had done before, only with spreadsheets. I quickly discovered that this approach was analogous to laying a wheel on its side, loading it with luggage, and dragging it across the ground behind a yak.
Decision Technology
I believe that step C opens the door to a much wider use of OR/MS concepts by decision makers outside the OR/MS community. But what will the field be called? After decades, neither "operations research" (except in the military) nor "management science" has caught on with the population at large, and "OR/MS" sounds like some kind of disease. I have chosen "Decision Technology" to describe a new field, which deals with the widespread application of the science of OR/MS to the everyday workplace.

From my perspective, here are the three most salient features distinguishing "decision technology" from the OR/MS concepts upon which it is based: 1. magic, 2. objects, and 3. free computing.

Magic. According to Arthur C. Clarke, "Any sufficiently advanced technology is indistinguishable from magic." It was with this in mind that What'sBest! was designed to reduce the simplex algorithm to magic in the way the automobile and television reduce the internal combustion engine and Maxwell's equations to magic. Other products bringing magic to the spreadsheet now include the Solver bundled with millions of copies of Excel, @RISK and Crystal Ball for Monte Carlo simulation, and numerous other products for implementing everything from decision trees and neural nets to genetic algorithms. For a steamy analytical time, check out [1,3,4 & 8].

Objects. Whereas OR/MS concepts are based largely on algebra, I see decision technology as object oriented. I now teach OR/MS applications with small spreadsheet models containing what I call the developmental necessities of the application (DNA for short). Unlike the algebraic representations of the past, DNA is a "live" decision object. It contains just enough formulas and representative data to allow a user to interactively infer its behavior. Within limits, it can be scaled up and instantiated with the user's data to yield real answers. It is a "seed" of knowledge from which understanding, and possibly, actual applications may grow.

Consider a dense transportation LP. Below is the algebraic description of a dense transportation model to ship goods from three plants to five warehouses.

Figure 3 is a view of the transportation decision object shipped with roughly a million copies of Excel per year. Add rows and columns if necessary, fill in your own data, then invoke the Solve command. A similar object is also shipped with What'sBest!.

Figure 3: View of the Transportation Decision Object Shipped with Excel

Now suppose that instead of shipping goods from three plants to five warehouses, we are shipping raw materials from three sources to five production facilities. It is elementary to combine the shipping object (Figure 3), with five copies of the product mix object (Figure 2) to create a vertically integrated model (dare I call this recombinant DNA?).

Of course, as discussed earlier, the spreadsheet will eventually be brought to its knees as the complexity of the model increases. Thus, the initial goal of the worksheet should be to convince management of the validity of the approach, so they will fund a more robust model.

There has been much discussion over the years concerning the difficulty of teaching mathematical modeling. I say it's hopeless. Instead, students can be taught how to assemble applications from decision objects, building-block style, in the log of the time it would take them to start from scratch. This raises a nice research question for the OR/MS community: What is the optimal set of building blocks for decision technology?

A second benefit of decision objects in spreadsheet format is that they replicate extremely well, and can be disseminated to a vast worldwide audience on the Internet in seconds (see for example [5, 7]). This gives them a survival edge in the jungle of technological evolution. Excel itself has been replicated to the extent that if only 1 percent of its users accessed its mathematical solver, their numbers would dwarf the entire membership of INFORMS.

Free Computing. As a graduate student in computer science in 1970 (the age of free love and expensive computing), I was taught it was a sin to waste CPU hours (@ $1,000 each) through inefficient algorithms. Today, there are 100 million computers in the United States, each of which is idle an average of at least 10 hours per day, wasting 1 billion CPU hours. From a 1970 perspective we are pouring the gross national product down the drain every night, and it has become a sin not to use as many CPU hours as possible. I prefer the age of free computing to the age of free love for two reasons. First, the viruses aren't fatal, and second, the love never turned out to be free. In any event, here are some suggestions for keeping your CPU active tonight:

  • Complete enumeration. Complete enumeration, the former laughing stock of algorithms, isn't so funny any more. Why waste an hour of your time trying to formulate an optimization model as an LP when you can just let your 100 mhz Pentium run through 2,880,000,000,000 cycles while you get a good night's sleep?

  • Instantaneous iterations -- the blitzogram. All sorts of computations we used to think of as iterative have become essentially instantaneous. For example, the FREQUENCY(data_array,bins_array) formula in Excel creates a live frequency count of the data in data_array categorized by the bins in bins_array. If data_array is filled with a data table based on a random input, it will contain a set of live Monte Carlo samples. Now hook up a graph, and you get a live histogram (I call it a blitzogram) that instantly reflects the distribution of a function of a random variable typed into a cell in the spreadsheet. Examples are shown in Figures 4 and 5. A working copy of the blitzogram may be downloaded from my web site [7].

    Figure 4: "Blitzograms" Instantly Reflect the Distribution of a Function of a Random Variable Typed into a Cell in the Spreadsheet

    Figure 4: "Blitzograms" Instantly Reflect the Distribution of a Function of a Random Variable Typed into a Cell in the Spreadsheet

    What's next?

    Given the shortcomings of the spreadsheet, surely something better will be developed.

  • Back to the future: Actually, in the not so distant past, something better had been developed. It was a documentable, scalable, hyperscalable, user-friendly, popular modeling tool from Lotus called IMPROV. After selling 200,000 copies, Lotus, for reasons that are still unclear, took it out behind the barn and shot it in the head. So now we are back to the future again waiting for a spreadsheet replacement.

    A new product called Analytica, distributed by Decisioneering [2], has the potential to fill the gap left by IMPROV, and may become a useful tool for the OR/MS community.

  • Client/server applications: The paradigm followed by IMPROV is a poor man's version of something known in the world of information systems as OLAP (online analytical processing). There are also heavy duty client/server OLAP systems such as ESSBASE from Arbor Software [3]. These products allow the user to view arbitrary slices of a multi-dimensional data base stored on the server, from within their spreadsheet. This takes advantage of the spreadsheet's best features while safely storing and manipulating the actual data in an industrial duty gigabyte container.

  • Other analytical engines: It is now also possible to have such products as Mathematica and Matlab perform calculations on your spreadsheet data and instantly return the results. Although still at an early stage, I believe that interesting synergies may result from such combinations of software.

    The fall of the algebraic curtain

    When I started teaching Management Science to MBA students 20 years ago, I felt lucky if 10 percent of my class understood what I was talking about, and luckier still, if 10 percent of those who understood, actually went out and applied the stuff. An obvious interpretation is that I am a lousy teacher. However, after subsequent interaction through my seminars with hundreds of MBAs taught by others, I favor an alternative explanation. I am convinced that there was an algebraic curtain separating most managers from management science, that spreadsheets are beginning to bring down.

    It is paradoxical that while the spreadsheet and its analytical add-ins are supplying OR/MS concepts to an ever-widening audience, business schools have been dropping their requirements for management science courses. Is management science alive or dead? When Gutenberg's printing press was introduced 500 years ago, required courses in gothic illumination also began to drop. And I'm sure there were those who pointed to this as the death of literature. As it turned out, literature survived the introduction of the printing press quite nicely, just as the concepts of OR/MS will survive the introduction of the spreadsheet.

    The name "OR/MS"? Maybe not.

    1. @RISK, Palisade Corp., Newfield, N.Y.; http://www.palisade.com

    2. Crystal Ball, Decisioneering Inc., Boulder, Colo.; http://www.decisioneering.com

    3. Essbase, Arbor Software, http://www.arborsoft.com

    4. Excel Solver, Frontline Systems, http://www.solver.com

    5. Neuwirth Erich, web page on Spreadsheets in Education, http://sunsite.univie.ac.at/Spreadsite/

    6. Savage, Sam L., 1993, "Fundamental Analytic Spreadsheet Tools for Quantitative Management," McGraw-Hill, New York.

    7. Savage, Sam L., web home page with down-loadable software, http://www.stanford.edu/~savage

    8. What'sBest!, Lindo Systems, http://www.lindo.com

    Sam L. Savage received his Ph.D. in computer science from Yale University in 1973, specializing in combinatorial optimization. He joined the faculty of the University of Chicago Graduate School of Business in 1974, an affiliation that continues today. In 1985 he co-developed What'sBest! with Linus Schrage and began his quest to bring down the algebraic curtain. In 1990, he launched his seminar series on Management Science in Spreadsheets through the University of Chicago, and moved to Stanford University where he is a senior research associate and director of the industrial affiliates program in the Department of Engineering Economic Systems and Operations Research. His latest set of business analysis tools for Microsoft Excel will be published shortly by Duxbury. Trial versions may be downloaded from his home page: http://www.stanford.edu/~savage

    Click here to go to the Spreadsheet Add-ins Survey

    E-mail to the Editorial Department of OR/MS Today: orms@lionhrtpub.com

    OR/MS Today copyright 1997, 1998 by the Institute for Operations Research and the Management Sciences. All rights reserved.

    Lionheart Publishing, Inc.
    2555 Cumberland Parkway, Suite 299, Atlanta, GA 30339 USA
    Phone: 770-431-0867 | Fax: 770-432-6969
    E-mail: lpi@lionhrtpub.com

    Web Site Copyright 1997, 1998 by Lionheart Publishing, Inc. All rights reserved.