Volume 4, Number 3, May 2004

 

A New and Different Spreadsheet Text
The Art of Modeling with Spreadsheets: Management Science, Spreadsheet Engineering and Modeling Craft by Stephen G. Powell and Kenneth R. Baker
 
 
David Tufte
School of Business
Southern Utah University
351 West Center St.
Cedar City, UT 84720
 

Business programs teach many different quantitative methods courses: business statistics, operations research, and management science are the common names and topics; all business schools offer some combination of these to undergraduate or M.B.A. students. Increasingly, the unifying theme of these courses is spreadsheet engineering, the idea that the design and construction of spreadsheets should be geared towards their use, abuse, and generalization by future users. In fact, in many situations these future users are people who originally wrote the spreadsheets and are now completely befuddled by their earlier work.

The text entitled "The Art of Modeling with Spreadsheets: Management Science, Spreadsheet Engineering and Modeling Craft" by Stephen G. Powell and Kenneth R. Baker is clearly set out for the advanced student with some quantitative skills. It is certainly appropriate for M.B.A. or M.Acc. programs, and possibly for advanced undergraduates as well. The book became available for Spring 2004.

I offer a review based on one class−an elective without pre-requisites for M.B.A. and M.Acc. students. My view is that the choice of text worked well for this course, and that it may also do well for required or lower-level courses. My background is economics, and econometrics specifically, which may color some of what I say below.

This text extends the typical student's spreadsheet skills by focusing on the design and production of sophisticated spreadsheets. The authors strongly make the case that there is an art to this process, and that students should regard the production of useful spreadsheets as a craft that can and should be done well.

This text is a new addition to the line of publications at John Wiley and Sons (and for a first edition, it is remarkably free of typographical errors). Consistent with the mission of that publisher, this is a solidly executed and technically oriented book. Further, given the author driven orientation of that publisher, the text is idiosyncratic: it does not have the look and feel of a book guided by a risk averse editor focused on the competition's product.

Instead, the text appears to be the vision of two authors who want to take the profession in a new and better direction. Powell and Baker are professors in the Tuck School at Dartmouth College, home of an M.B.A. program routinely ranking in the top five in the world. Their vision is broadly consistent with the evolution of A.A.C.S.B. guidelines for business schools over the last ten years. They believe that communication is the key to good business education. As such, spreadsheets are how business people communicate numerical ideas, and a facility with the construction of easily understandable and scalable spreadsheets is a key to good business communication.

The text is broadly divided into two sections. The first six chapters focus on the art and craft of modeling, while presenting some technical issues. The last three chapters focus on technical issues. This book does not follow the recipe of one chapter for each week of class during the typical semester. Rather, the first six chapters are quite short, accounting for about one third of the text. The three technical chapters account for about 60% of the text. Cases and appendices cover the rest. The vast majority of the instructor's time will be spent on Chapters 7 through 9, with reference to the earlier material in Chapters 1 through 6. How much time to spend on those earlier chapters, and when to spend that time is problematic. In some sense the book is no help here, but in another it has helped immensely by including this material at all (and placing it early in the text).

In my single use of the text, I covered Chapters 1 through 6 linearly in about 4 weeks (skipping the second half of Chapter 4). This gave me a little less than 4 weeks to spend on each additional chapter. Experience indicates that Chapters 7 and 9 can be done more quickly than Chapter 8. While covering Chapters 7 and 8, I went back and picked up the material from the latter half of Chapter 4 that was relevant to those chapters. Throughout my coverage of the last three chapters, I re-emphasized the material in Chapters 1 through 6. I recommend stressing this early material quite a lot: the early chapters are the proverbial forest that you do not want students to miss for the trees in Chapters 7 through 9.

Chapter 1 is introductory. It discusses the need for modeling on the job, and how spreadsheets have changed modeling from a job for specialists to a job that all business people perform. A nice addition is a section on the overconfidence of spreadsheet users and designers, and the poor processes they use to create spreadsheets and derive answers from them.

Chapter 2 focuses on modeling as part of problem-solving. Several heuristics are discussed: divergent and convergent thinking, a six step linear creative process, a four part description of the relationship between models and the real world, and a five step non-linear process. These are not presented as requirements, but rather as methods that can be learned. The authors take great pains to point out that problem-solving requires creativity, that many people do not think that they are or can be creative, and that they are probably wrong. The goal here is to push the student to creatively analyze a problem, within the context of some suggested methods. Four of the cases from the book that are intentionally loose and disorganized (see below for a discussion of this feature) are used in the problem set to illustrate these techniques.

Chapter 3 discusses modeling as a craft that can be learned through training. Eight techniques are treated more or less equally. On the surface, these are fairly obvious suggestions. The advantage of this text is that it actually does state and discuss them. All of them are techniques that I have used in solving research problems, but nonetheless they are not things that I apply as diligently as I should. The key to students getting a lot out of this chapter is to return to these ideas over and over. Perhaps even to have the students detail in their solutions to problems from later chapters which of these they have used. The problem set continues with the four rough cases that were used in Chapter 2.

Chapter 4 introduces visual modeling. This is where I have the biggest organizational problem with the text. Four techniques are introduced. Influence (flow) charts are introduced first, as a way of isolating important features of messy problems. Outlines follow as a way to capture that in writing. Both of these are important for managers to learn, but I am not sure that they are appropriate for the level at which this book is targeted. This is followed by decision trees. The difficulty here is that this requires minimal statistics, which really isn't used again until Chapter 7. I saved my discussion of this topic until then. The last item is network diagrams. Here again, the related material appears in Chapter 8, and I saved my discussion until then. This chapter contains true self-contained problems in the problem set, as well as introducing four new cases more appropriate to the material at hand.

Chapter 5 is the core spreadsheet engineering chapter. It is divided into four broad sections: designing a spreadsheet, designing a workbook, building a spreadsheet, and testing a spreadsheet. Each is subdivided into three to eight rules, which are introduced sequentially, and discussed with examples. Most textbooks in this area contain a similar discussion. This one is particularly strong on motivating a small beginning to your work, password protection, using dummy data to understand the arithmetic, and using names for ranges in the spreadsheet to make the final product more understandable for end users. The problem set contains no basic problems, but rather focuses on extending the eight cases that formed the basis for the problems sets in Chapters 2 through 4.

Chapter 6 introduces broad tasks that spreadsheets can manage. Short discussions of base-case, what-if, and break-even analysis serve as an introduction to the last three chapters. Sections on optimization analysis and simulation introduce Chapters 8 and 9 more directly. The problem sets continue to extend the eight cases from the previous chapter. My sense was that these problems were not specific enough to introduce the ideas discussed in the chapter, but rather assume a level of generality more consistent with Chapters 7 through 9.

Chapter 7 covers data analysis. Unlike many spreadsheet texts, this includes a section on using Excel as a database program (rather than something like Access). I found that this was new to most of my students and very helpful to them−most were aware of employers who maintained databases in spreadsheets because they did not have command of the relational underpinnings of Access. This is followed by short, but standard, sections on estimating parameters, estimating relationships, and forecasting. The text follows that standard approach in business of focusing on parameter estimates, rather than on hypothesis tests. I don't agree with this, but I am used to seeing it in texts, so it is not a drawback. All in all, I felt this chapter was somewhat weak, but that may reflect my background in econometrics rather than management science.

Chapter 8 is the most difficult in the text. It covers the generic idea of optimization problems, how to use Solver to get solutions for them within Excel, and programming problems. The focus is on linear programming problems. The section of Chapter 4 on network diagrams relates most closely to this material. I wouldn't claim that there is a lot of new stuff in this chapter for the teacher. But what is here is very well done, and very sophisticated for students. The problem set for this chapter is the best I have ever seen for linear programming. The one thing that it does lack is shorter introductory problems. Most of them are quite extensive, often taking students several hours to do correctly. But they are wonderfully detailed, frequently involving multiple levels, which must be carefully related through network diagrams. One difficulty I had was that the high level of detail within the problems often made for difficult grading, since students were often coming up with reasonable yet outlandish-at-first-glance alternatives for designing their spreadsheets.

Chapter 9 covers Monte Carlo simulation, as done with Crystal Ball in Excel. The treatment here is less advanced than Chapter 8, and more easily digestible by students. The discussion is standard although it does extend, through using the OptQuest package, to difficult problems. This is a package, included with the text, that is typically available only with the more expensive versions of Crystal Ball, and it embeds optimization execution within a Crystal Ball run. My personal feeling was that the problem set for this chapter was somewhat weaker than for the other two major chapters. However, one weakness I found was that again the chapter seemed to lack smaller, less serious, problems. In Chapter 8 this was an issue of building up the students to the later work, but here I fear that interested readers may run into capability limitations with older or student versions of Crystal Ball which do not have the capacity to simulate the number of variables required by the problems (note that the versions that ship with the book are OK, but the versions licensed in some of our labs on campus were not). Lastly, two appendices to this chapter detail more arcane features of Crystal Ball that are not necessary for the first time user. Finally, I have not attempted this, but I think that teachers at universities, which use @Risk as opposed to Crystal Ball, should be able to get some understanding of simulation into students in spite of the Crystal Ball focus of the text.

The set of cases included with the text are excellent, although intentionally bothersome. The latter idea requires some up-front explanation. The authors stress heavily the idea that in real world projects the information is often lousy. Thus, they intentionally include cases with lousy information. This can take a number of forms. First, all the cases are reported in prose format, with some tables. The organization of the prose is often non-linear, simulating the way projects are often explained in the real world. The tables include some of the data that is obviously best presented in that form, but not all of it. Also, the tables routinely present data that might be better put in prose or in a chart. The cases often contain extraneous information. They also occasionally lack information they should include. Since the missing information never compromises the ability to present some solution to the cases ( i.e., only a complete solution is problematic), I am of the opinion that these are not oversights, but rather an intentional educational ploy on the part of the authors. Let me explain with an example. An extensive case discusses the industrial production of chocolate and by-products from raw beans. In the text some prices are not provided. These values are included in the solution material (there for the instructor to find and use in a pinch). This provides the opportunity for students to identify necessary but missing information that can be estimated or made available to them if they inquire.

The text contains two appendices. One reviews basic probability concepts: distributions, moments, and sampling theory, effectively the tough middle part of a first semester course in statistics. The second appendix covers the basics of Excel. One important feature here is the subsection on naming ranges. The authors make a lot of this feature in the text, and while I knew of this feature, I learned to make better use of it too.

It seems that no text is complete without a suite of software loaded onto a CD-ROM tucked into a pocket inside the cover. This text is no exception. It includes Premium Solver for Education, Crystal Ball 2000.2 Student Edition, and a Sensitivity Toolkit . Excel comes with numerous add-in programs. These are packages for addressing specific problems, which are not normally loaded when Excel is installed. They must be added to Excel through the "Tools" menu. When Excel is installed on a single machine, or when the add-in is added from a network server, this process is permanent. However, most network installations of Excel will permit the use of add-in programs on a single use basis. Premium Solver for Education is an upgraded version of the Solver add-in that ships with Excel. Crystal Ball is another add-in for Excel. The student edition disables some functions.

The Sensitivity Toolkit is a program that is unique to this text. It works conditional on the prior installation of Premium Solver for Education, and Crystal Ball 2000.2 Student Edition. The Sensitivity Toolkit contains four features, which allow the student to perform a layer of sensitivity analysis over the typical results produced by other tools. The Data Sensitivity tool can quickly create tables of possibilities based on previously completed work. If given a spreadsheet containing relationships between cells, this tool allows the user to systematically change one or two cells, and capture in tabular form the response of some other cell in the spreadsheet. The Tornado Chart tool is similar, although it captures in a chart the response of a target cell to changes in many underlying inputs. The Solver Sensitivity Tool does the same with output from Solver. It allows the user to designate parts of a completed spreadsheet to vary, and then captures how this changes some other cell. The Crystal Ball Sensitivity tool captures in a table the correlation of input cells with output cells.

Personally, I am somewhat biased against the use of software that ships with textbooks. I don't believe students will keep the software, and thus I am unwilling to push them to use it. In this particular case, my university had student site licenses for (plain as opposed to premium) Solver and a basic version of Crystal Ball. These were available in the networked lab where the class was taught. While more advanced site licenses were available for faculty, this did not serve me well in interacting with students using a lab. Since the versions that ship with the text may interfere with these prior installations, I chose not to pursue them. However, students indicated that all three programs worked well on their home computers. Note that there is also a licensing issue involved. The software on the disk is licensed to the purchaser of the book for use on a PC that they own. This will conflict with how most universities offer computers and textbooks.

A website is available for the text and includes a section for instructor material. This contains four things. The set of PowerPoint lectures for the text is good−I used them a bit, and liked them, although I rarely go in for that sort of thing. There are also spreadsheets which match the Excel figures in the text. These were very useful, although they were not always organized in the easiest way. There are also answer keys for most of the problems in the text. These are very well done, but they could stand additional comments by the authors. Lastly, the data files for the text are included too.

Should you choose this book? The book has a lot of pros: it is concise, completely integrated with Excel from the bottom up, and has few errors for a first edition. It has some cons as well: users may not agree with the emphasis on the craft aspects of spreadsheet engineering, and it is not suitable for inexperienced users. My students' main complaint was that the problems were too long, and I would concur in that a long spreadsheet problem can be very difficult to grade because there is so much room for error. But I think overall, for teachers who enjoy this subject matter and want to see their students do well, this is a good text to choose.

 


To download a printable version (pdf) of this paper, click here. To download the Adobe Acrobat reader for viewing and printing pdf files, click here.
To reference this paper, please use: 
Tufte D., (2004), "The Art of Modeling with Spreadsheets: Management Science, Spreadsheet Engineering and Modeling Craft by Stephen G. Powell and Kenneth R. Baker," INFORMS Transactions on Education, Vol. 4, No 3,  http://ite.pubs.informs.org/Vol4No3/Tufte/