OR/MS Today - February 2004|
New edition of Sam Savage's Excel add-ins for uncertainty modeling and accompanying book earn high marks for teaching management science.
By Stefan Scholtes
Sam Savage is known as one of the fathers of the spreadsheet movement in management science. He developed the prize-winning spreadsheet optimization software What'sBest! back in the 1980s when most OR practitioners still solved their optimization problems on mainframe computers, and students like me, even if they loved the subject, didn't have the faintest idea how the wonderful concepts and techniques could be applied to real-world problems. Of course, the subject has moved on over the past 20 years. Industrial-strength optimization solvers and simulation packages are now readily available, and their use is facilitated by high-level, easy-to-use modeling environments such as AMPL, GAMS, Mathematica and Matlab. The vendors of many of these packages offer inexpensive student versions, which are used throughout the world for OR teaching in engineering departments, including financial engineering.
Interestingly, these advances have not had a significant impact on generic management science teaching in business schools. Why is this? The prime reason is that, in comparison to their engineering pendants, management applications are typically an order of magnitude messier. They are highly complex with lots of independent actors and lots of uncertainty in the data. It seems unrealistic to assume that one can build a valid computer model of a real management problem, let alone find the "right" solution. But what is management science good for then? I had long discussions about this issue with Savage two years ago when I was visiting Stanford and happened to have an office next to his. These discussions came in handy at a time when my teaching audience changed from engineering to MBA students.
Savage convinced me that the virtue of OR/MS for managers is not that it allows them to produce answers, but rather that it forces them to ask the right questions. The emphasis shifts from model solution to model communication and exploration. The aim is not to build one complex model that comes close to reality, but to build many small models that are easy to communicate and modify, and that allow the illustration and understanding of the many facets of a management problem. This is where Insight 2.0 is immensely helpful.
In his book, Savage offers a neat categorization of how managers deal with uncertainty and complexity. For example, faced with the question, What will the future returns on a particular investment be?, the "dumb" managers admit that they don't know because they don't know what the future circumstances will be. That's honest but not very helpful. Even worse, though, are the "dumber" managers who will plug best guesses of the future circumstances into, say, a net present value model and claim that their model produces the best guess for the future returns. The "smart" managers play out the futures and produce a range of outcomes for a range of possible future circumstances as well as future actions. OR/MS is uniquely placed to provide the playground for the smart manager and Insight 2.0 contains some toys for this playground.
All software is well documented in the accompanying book "Decision Making with Insight."
In what follows, I will confine myself to a description of the simulation and decision tree software since they are most useful for my teaching purposes. Extend, What'sBest! and Frontline System's Evolutionary solver are stand-alone packages and deserve dedicated reviews.
XLSim supports the usual Monte Carlo set-up. One starts from a deterministic spreadsheet model that transforms unknown inputs, e.g. production, demand, costs, etc., into outputs, e.g. inventory levels or cash flows. The uncertain input cells are then randomized by inserting suitable random number formulas, which sample either from a host of theoretical distributions or re-sample from historical data every time the spreadsheet is re-calculated, e.g. by pressing the F9 key. Finally, the model is automatically re-calculated many times and the values of the output measures are recorded for each of these trials. The recorded values provide an approximation of the distribution of the output measures and can be visualized, e.g. as a histogram or cumulative distribution function, and further analyzed.
If the original spreadsheet model has control parameters, e.g. an order quantity or a re-ordering point in an inventory model, the user may wish to compare the distributions of output measures for various parameter settings. XLSim also supports such parameterized simulations.
A useful feature that is provided by @Risk but not automated in XLSim is a correlation analysis between random inputs and random outputs. This allows a ranking of the uncertainties according to their impact on an output measure. In XLSim such an analysis can be performed manually by designating the relevant inputs as outputs and calculating the correlations between simulation outputs directly in Excel.
One problem with the predecessor version, Sim.xla, was that it would occasionally be difficult to copy models from one computer to another if the software wasn't installed in the same directories on both machines. This problem is overcome in XLSim through the new freeze/thaw commands.
Decision Tree Analysis with XLTree
XLTree has some interesting new features. One cosmetic disadvantage of the earlier version, Tree.xla, was that the set-up of the trees was rather unattractive for graphical output. XLTree now produces nice graphical trees with several formatting options.
An interesting new technical feature is the option to change the orders of symmetric nodes within a tree with an automatic update of probabilities. Savage calls this "tree flipping." It is useful for calculating the value of information, as explained in the accompanying book, "Decision Making with Insight."
XLTree allows you to keep track of several state variables, such as cash flows, sales quantities, costs, etc., as you pass through the tree. These quantities can be used at the leaves to calculate the respective payoffs, do sensitivity analyses in a data table, or, in conjunction with XLSim, perform Monte Carlo simulations.
XLTree is very easy to use and, as mentioned above, has the advantage over some competing products because it is very close to vanilla Excel. I have not come across any disadvantages except for the limitations in the standard version explained below.
The commercial version of XLTree is limited only by the size of Excel. Unfortunately, and in contrast to its predecessor Tree.xla, the standard version of XLTree has more severe limitations: it admits no more than three branches per node and no trees with a depth of more than four levels. These limitations could hamper effective teaching as well as the usefulness of XLTree for student projects. Savage is aware of this issue and offers a free student upgrade if instructors adopt the software for classroom use. The upgrade admits five branches per node and trees up to nine levels, which is sufficient for most teaching purposes (instructors can contact Savage at firstname.lastname@example.org for the update).
The Book: "Decision Making with Insight"
Most of my former students perform quantitative analyses at least occasionally, some on a regular basis. I am not concerned about the latter constituency; I would be a terrible teacher if they weren't using OR/MS techniques. Unfortunately, they are in the minority. The vast majority use Excel on an ad-hoc basis, often for a quick and dirty back-of-the-envelope calculation or to perform a graphical visualization of data. I want to make sure that these people feel as comfortable with Monte Carlo simulation, decision trees, regression or optimization as they are with the Excel graphics capabilities. It is here where Insight 2.0 is of invaluable help. It is easy to use, close to vanilla Excel, comes with a brief and no-nonsense primer and, most importantly, is affordable for students!
I am quite protective of my students' budgets and would not ask them to buy an expensive book/software package. Insight 2.0 comes with a very useful book for $50, which I can justify to myself and to my students. Those students who will end up in highly quantitative environments can upgrade for $125 to the commercial version, which is for most purposes just as good as the more expensive competition. But, more importantly, those students who use Excel only occasionally can continue to use the standard version, without having to buy an expensive commercial package, and thereby apply what I taught them. That's what makes teaching with Insight worth my while.
Stefan Scholtes holds a readership in management science jointly in the Engineering Department and the Judge Institute of Management at Cambridge University.
OR/MS Today copyright © 2004 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 2004 by Lionheart Publishing, Inc. All rights reserved.