Easy-to-learn, low-cost business analysis software makes a good choice for short courses and quick-and-dirty industrial applications.
By Jeffrey D. Camm
As a senior undergraduate mathematics major in 1980, I stumbled across a package called LINDO written by Linus Schrage. I was stunned that someone could write a computer package that allowed me to type a linear program into the computer just as I would write it down on paper, solve it, and generate nice reports. LINDO became a major influence in educating students in optimization and indeed in solving real-world problems in the 1980s.
In a similar experience a little over a decade later, I stumbled onto Excel solver. Wow! Someone at Microsoft knew about mathematical programming and cared enough to put it right in their spreadsheet product! I had no doubt that this was going to cause a real change in how I was teaching management science. It has.
Without question, the leader of the management science in spreadsheets movement is Sam Savage. In 1985, Savage developed What'sBest!, the first widely marketed spreadsheet optimization package (which incidentally is now a product offered by LINDO Systems). Since that time, through industry and academic seminars and new software development, Savage has been "trying to do for management science, what Walt Disney did for the rodent." His newest endeavor in this regard is Insight.xla.
Insight.xla is a series of Excel add-ins for simulation, queueing, markov chains, forecasting, decision trees and optimization. The software comes with a book (294 pages) that is essentially a tutorial on quantitative business analysis. Sitting down with the book and working through a few examples on your computer will allow you to easily learn this software.
Installation and Documentation
Working through the book that accompanies Insight.xla is like being in one of Sam's presentations. It is filled with outstanding examples, wit and insights. The advantage is that you can go at your own pace, so you don't miss anything he says! Figures 1 and 2 are brief examples of Savage's approach. Figure 1 shows the meaning of the central limit theorem better than any single picture I've seen. Figure 2 illustrates the difference between nonconvex and convex functions. Examples like these abound and are not limited to figures. For example, Savage writes: "People facing uncertainty have generally achieved one of the following levels of enlightenment:
Level 0: Dumb. When the boss asks what the output of the model will be, this person throws up his or her hands and says, "I don't know because I don't know what the inputs will be."
Level Minus 1: Dumber. One step down from dumb, these people plug in best guesses into the model and confidently proclaim that they have the best guess for the output.
Level 1: Smart. A big step up from dumb, these people run a simulation to find the output's range of uncertainty. In the process, they are forced to learn something about the nature of the uncertain inputs of their model, which makes them smarter still.
Level 2: Proactive. Even at level 1, you are simply reporting what is likely to happen. Reaching level 2 requires a commitment to action given the uncertainty observed at level 1." [Savage, p.66]
You get the picture. The book is educational and fun. It provides solid documentation for the software.
Minimum requirements for Insight.xla are IBM or compatible PC with Microsoftâ Excel 5.0 or later. Excel running under Windows 95 or later is recommended. (A Macintosh version of Insight.xla is also available. Although What'sBest! is supported only on PC, all optimization examples are done in the Solver format as well, and work fine on the Mac.)
I installed it on my laptop under Windows 95. I consider software easy to install if I don't have to call Henry (our IT support guy). Insight.xla passed the "no call to Henry test." It is easy to install and even easier to use. The add-ins can be set to load automatically when Excel is invoked (by going to Tools, Add-Ins, and clicking on the appropriate add-ins) or can be run for a given session by opening the .xla file. I found it easier to control these by the former rather than the latter, since the .xla are buried under several layers of subdirectories. Once loaded, the add-ins appear on the menu bar. The add-ins are driven by dialog boxes which makes them extremely user friendly. Each of the add-ins is discussed below.
What is it?
SIM.xla. This add-in is for simulation. Uncertain cells are described through the function wizard using 13 available gen_* functions. These are most of the commonly used distributions. Options to resample from collected data (gen_Resample and gen_ResampleSync) are very useful. Figure 3 shows the dialog box from the Run Simulation option. Up to five output cells can be specified along with titles, a seed and the number of trials. A variety of statistics and graphs are generated for each output cell. In Figure 4 we show a spreadsheet for options pricing (Option.xls). The price change from week to week is modeled as a random walk. In cell I6, the net present value of payoff is calculated for the 12-week period. As shown in Figure 3, cell I6 is our output cell and we would like to try 500 trials using 39 as a seed. The output is sent to a separate worksheet called Simstats.xls. Figure 5 shows the default output, which includes average, standard deviation, standard error, max, min and percentile information. From the default output, a histogram (number of bins may be specified) and a cumulative probability graph can be constructed as shown in Figures 6 and 7.
The advantages of using SIM.xla rather than Excel's built-in random number functions are twofold. First, using SIM.xla gets you the automatically generated output as discussed above. Second, the gen_* functions are based on a well-tested random number generator [see Law and Kelton, p. 430]. They are therefore likely to be more accurate than the default functions from Excel (I have seen a study where the Excel random generator failed statistical tests for randomness).
There are certainly add-in simulation packages available that are more extensive than SIM.xla. For example, Crystal Ball and @RISK have options to correlate random variables, real-time distribution graphing, and the ability to terminate a simulation run according to statistical measures. However, these packages are much more expensive and are not bundled with all of the add-ins described below.
QUEUE.xla. This add-in simulates a simple M/M/1 queue. The user specifies the mean interarrival and service times and the maximum run time. Initial conditions may also be specified through the Initial worksheet in QUEUE.xls. Output statistics are given including the option of a graph of queue length over time. The add-in can be run from a pulldown menu or from visual basic buttons in the spreadsheet QUEUE.xls. This add-in is useful and interesting for experimenting to better understand queuing in general and the M/M/1 in particular, but probably not much else.
Q_NET.xla. Q_Net.xla is an add-in for simulating a queuing network. The type of network is specified by entries in a transition probability matrix in the Transition Matrix worksheet of Q_NET.xls. Mean interarrival to system and service times for each station must be specified. Buttons allow the user to add or delete stations in the network. Output from this is similar to QUEUE.xls with a graph of average throughput of the system.
FORECAST.xla. FORECAST.xla is an add-in that performs two-parameter exponential smoothing on time series data. The exponential smoothing option of the standard data analysis tool in Excel only offers simple (single parameter) exponential smoothing. In FORECAST.xla, data location and desired options are easily communicated through the Forecasting dialog box as shown in Figure 8. Output is directed to three sheets, Deseason, Smooth and Results. These contain deseasoned data and seasonality factors, parameter values and model statistics, and forecasts (including a graph) respectively.
TREE.xla. TREE.xla is an add-in for decision analysis and decision trees. Decision forks and uncertainty forks are added by option selection, and the tree is constructed in the spreadsheet with positions for all necessary information (labels, values or utilities, and probabilities). Entering all of the information for the decision tree is a bit painful, especially compared to the other add-ins which are dialog box driven.
What'sBest! What'sBest! is Savage's award-winning spreadsheet optimization package. The version included in Insight.xla will solve linear, nonlinear and integer programming problems. A comparison of Excel Solver and What'sBest! is included in the documentation. Both options are good and both are well documented in the manual.
In addition to the six add-ins, MARKOV.xls is included in Insight.xla. It is not really an add-in, but is simply a spreadsheet that calculates equilibrium for an example markov chain. Another spreadsheet, 3dgraph.xls, allows the user to graph any two-variable function on the intervals -1 to 1 using a data table. Savage's famous Spinner.xls spreadsheet is also included. This allows the user to "spin" to get a random number between zero and one. These last three items are ideal for real-time classroom demos.
A variety of example data sets and models are included with the software. The optimization models tend to be small textbook models, but the data sets for forecasting (contained in Series.xls) are real. All of these will prove useful in the classroom.
Finally, the original version of SIM.xla contained a bug with regard to multiple histograms in Excel' 97. An update is available for free from http://www.AnalyCorp.com.
Much of Insight.xla and certainly much of the material in the accompanying book were developed by Savage for his highly successful "Beyond What If" and "Financial Engineering" seminars for industry. Insight.xla is ideal for these types of short courses. Likewise, because of Savage's down-to-earth style, it is also ideal for folks in industry who want to independently get up to speed in the spreadsheet environment. What I see as the fundamental pieces of Insight.xla, namely SIM.xla, FORECAST.xla and What'sBest! are powerful enough to be used in some real applications.
Many academics will also find Insight.xla useful. If you are already using a text with Excel Solver and either Crystal Ball or @RISK in your classes (and the students have these available over a network), it might be difficult to justify having your students purchase Insight.xla. I think most academics would not be comfortable using the Insight.xla book as the sole text for a required management science course. On the other hand, if the spreadsheet simulation packages are not an option, Insight.xla is definitely something to consider. In this situation I would recommend Insight.xla as a text supplement. For a low price, your students will walk away with some good software, and much more. The "insight" in Insight.xla more aptly describes Savage's approach to conveying quantitative business analysis than the software itself. Savage's well-chosen examples and pragmatic approach will be a real help to many students.
Jeffrey D. Camm is professor of quantitative analysis and head of the Department of Quantitative Analysis and Operations Management at the University of Cincinnati. He has been actively involved in the movement towards teaching spreadsheet-based management science.
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
Web Site © Copyright 1999 by Lionheart Publishing, Inc. All rights reserved.