Decision Support Systems
Using Excel and VBA for Excel
by Michelle M.H. Şeref, Ravindra K. Ahuja, and Wayne L. Winston
Dynamic Ideas, Belmont, Massachusetts, 2007.
A decision support system (DSS) is an intelligent information system that uses data, models it, processes or analyzes it using problem-specific methodologies, and assists the user in the decision-making process through a graphical user interface (GUI). Developing Spreadsheet-Based Decision Support Systems is a comprehensive book that describes how to build decision support systems using the Excel spreadsheet framework and the VBA programming language. This book illustrates complete decision support development applications through several case studies arising in operations research, industrial engineering, management, and business administration.
The book is composed of the following three parts.
Part I – Excel Essentials:
This part presents an overview of Excel’s basic and extended functionalities. The basic functionality topics include referencing and names, functions and formulas, charts, and pivot tables. The extended functionality topics include statistical analysis, the Solver and modeling, simulation, and working with large datasets.
Part II – VBA for Excel:
This part presents an overview of programming in VBA and manipulating Excel objects. Covered topics include macros, programming structures, building user interfaces, and using VBA for optimization and simulation.
Part III – Case Studies:
This part presents several case studies of decision support systems arising in different application settings. These case studies include inventory management, retirement planning, portfolio management, and other applications in operations management and engineering.
The book is self-complete and does not require any prior background in information systems, databases, or database management systems. Each topic covered is illustrated through examples and hands-on tutorials. Each chapter contains several hands-on exercises for additional practice. This book is ideally suited as a textbook for teaching undergraduate- and graduate-level courses in any branch of science, engineering, and management but can also be used as a supplementary reference book or a self-study manual.
The ability to extract data from external sources and embed analytical decision models within larger systems are two of the most valuable skills required for entering today’s information technology dominated workplace. Such decision support systems (DSSs) may be developed in various environments that support data storage, data analysis, solution method development, and graphical user interface. Microsoft Excel spreadsheet software provides all of the necessary components to build a DSS. It enables the data to be stored in a spreadsheet, optimization and simulations models to be built, and data to be manipulated using the programming language VBA for Excel, and it provides tools to build graphical user interfaces. Microsoft Excel is also the most popular software engineers and managers use in their workplace. Thus, Excel offers an excellent environment to build a DSS, and our students can very easily acquire these skills. This book describes all the necessary techniques to build such systems.
The book is designed to meet the needs of undergraduate as well graduate students for courses in business school or operations research or industrial engineering departments. The book can be used as a textbook for full courses, or it can be used as a reference book to supplement the current material in existing courses. The book is also written in a style so that managers, engineers, and practitioners can use it for self-study. The book is self-complete and does not require any previous background. The 25 case studies developed give the instructors a wonderful selection to cover in the class depending upon the audience. The case studies not only illustrate the applications of models to real-life applications but also illustrate methodologies not often covered in courses, such as neighborhood search and genetic algorithms.
The book has the following distinguishing characteristics:
- A unified approach that describes all technologies necessary to build a complete spreadsheet-based decision support system. The book covers the material found in several books in one book and makes it highly accessible for students with little previous background.
- This is the first book that covers Excel functionalities, VBA for Excel, and a variety of case studies arising in industrial engineering, operations research, operations management, and business administration in a single book.
- All important topics are explained using easy-to-follow and interesting hands-on tutorials. The focus is on learning by doing. Numerous hands-on exercises further supplement the learning experience.
- This book can be used as a textbook for teaching courses at the undergraduate as well as graduate levels in industrial engineering, operations research, and business departments. The book can also be used as a self-study guide by practitioners wanting to learn more about operations research and/or information technology.
- The book can also be used to supplement existing courses such as spreadsheet-based modeling courses. The book material is organized in a modular fashion so that instructors can pick and choose certain modules to supplement other courses they teach.
Instructor and Student Resources
We have developed additional course material that contains valuable resources for both instructors and students. The following material is available at the website www.dssbooks.com (this site is still under construction and will be available shortly):
Case Studies: An additional 15 case studies based on different application settings such as supply-chain management, project management, financial management, and sports.
Case Files: Excel files for all the case studies described in the book or the website.
Chapter Examples: Both the finished and unfinished Excel files for all the hands-on examples in the book.
Chapter Exercises: Excel files for exercises in all chapters. Solutions available only to the instructors.
Chapter PowerPoints: PowerPoint presentations for all the book chapters.
Projects Manual: A compilation of 75 student (team) projects describing additional applications of spreadsheet-based decision support systems.
Instructor Resources: Tips for instructors and sample course schedules.
The Solutions Manual of the book is also available and will be provided on a CD-ROM to instructors offering courses using this book as the principal textbook. The website (www.dssbooks.com) provides the email addresses for requesting the Solution Manual and giving your feedback to the book authors (this site is still under construction and will be available shortly).
About The Authors
Michelle M.H. Şeref
Michelle Şeref is a researcher in the Warrington College of Business at the University of Florida, Gainesville, in the Department of Decision Information Sciences. Her research focus is in the area of Operations Management with special interests in operations/marketing interface and new product development. Michelle has taught courses in Developing Spreadsheet-Based Decision Support Systems (DSS) in the department of Industrial Engineering at the University of Florida for several semesters and received a Teaching Award for her exemplary performance. She has also presented several tutorials at workshops and conferences on the need and importance of teaching DSS material to industrial engineers and business majors. She has also taught at two instructor-training workshops on DSS development. Michelle plans to graduate with her Ph.D. in 2008 and pursue an academic career.
Ravindra K. Ahuja
Ravindra Ahuja is a professor at the University of Florida, Gainesville, in the Department of Industrial and Systems Engineering. He specializes in mathematical modeling, state-of-the-art network optimization techniques, and solving large-scale scheduling problems arising in logistics and transportation. He founded the company Innovative Scheduling, Inc. (www.InnovativeScheduling.com) to bring cutting-edge operations research and information technology techniques to the field of transportation and scheduling. He has developed innovative models and algorithms for several scheduling problems that were previously considered intractable; these models are now being packaged into interactive web-enabled decision support systems. He is winner of the 1993 Lanchester Prize for the best contribution of the year in Operations Research, the 2003 Pierskalla Award for the best paper on Operations Research in Health Applications, and the 2006 Daniel H. Wagner Award for Excellence in Operations Research Practice. He is a coauthor of the widely used text and reference book “Network Flows: Theory, Algorithms, and Applications.” He is also a coauthor of the companion book, “Developing Spreadsheet-Based Decision Support Systems,” which describes how to build decision support systems using Excel and VBA for Excel.
Wayne L. Winston
Wayne Winston is a professor at the Kelly School of Business at Indiana University, Bloomington, in the Department of Operation &Decision Technologies. He has a Masters degree from MIT in mathematics and a Ph.D. from Yale University in operations research. Professor Winston has written several widely used textbooks on spreadsheet modeling and operations research, including Introduction to Mathematical Programming, Introduction to Probability Models, and Practical Management Science. His primary research areas are spreadsheet models, applied probability, dynamic programming, quality control, and math and sports. He teaches various spreadsheet modeling courses in the MBA program at Indiana University and has been the recipient of the Lilly Award for Teaching Excellence four times. Professor Winston is also involved in several consulting projects with companies including Eli Lilly, Bristol Myers Squibb, Microsoft, Intel, Cisco, the Dallas Mavericks, and the Department of Defense and US Army. He is also a two-time Jeopardy! champion.