Decision Support Systems
Using Excel and VBA for Excel, 2nd Edition
by Sandra D. Ekşioğlu, Michelle M.H. Şeref, Ravindra K. Ahuja, and Wayne L. Winston
Dynamic Ideas, Belmont, Massachusetts, 2011.
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, 2nd Edition, is a comprehensive book that describes how to build decision support systems using the Excel spreadsheet framework and the VBA programming language. Risk Solver Platform for Education, an Excel Add-In, is used for optimization and simulation. 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, Risk Solver Platform for Education for modeling and solving optimization and simulation problems, 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.
Changes Made in the 2nd edition
Part I - This book features Excel 2010. In Chapter 2 we give an overview of the Ribbon, Quick Access Toolbar and File Menu tab which is a new addition to Excel's interface. In this Chapter we also discuss the new conditional formatting features of Excel. In Chapter 3 we discuss the new and improved charting interface of Excel and introduce Sparklines. In Chapter 4 we discuss some of the improvements made on Excel's function library. In Chapter 6 we present some of the improved capabilities for working with pivot tables and introduce Slicers. In Chapters 8 and 9 we use Risk Solver Platform for Education to model and solve optimization and simulation problems. In Chapter 10 we present the new Official Excel Table tools and discuss its capabilities to organize and manipulate data.
Part II - In Chapters 19 and 20 we discuss how to use Object Oriented API with Risk Solver Platform to modify and solve optimization and simulation models using VBA commands. For instructions on how to access Risk Solver Platform for Education, please visit this page www.solver.com/professor-and-student-users.htm.
Part III - A number of the case studies (such as, Birthday Simulation, Portfolio Management and Optimization, Single and Multi-Server Queuing Simulation, etc.), are updated to use Risk Solver Platform for optimization and simulation. The use of Risk Solver Platform has especially improved the coding efficiency and performance of these case
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 Add-Ins, such as, Risk Solver Platform for Education, are accessory software that extend the capabilities of existing Excel applications to perform optimization, simulation, risk analyses, etc. Microsoft Excel is 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/excel2:
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/excel2) provides the email addresses for requesting the Solution Manual and giving your feedback to the book authors.
About The Authors
Sandra D. Ekşioğlu
Sandra D. Ekşioğlu is an Assistant Professor at Mississippi State University (MSU), in the Department of Industrial and Systems Engineering. Her research is focused in the area of Operations Research (OR). She uses OR tools to model and solve large scale supply chain design and management, logistics management and transportation-related problems. Sandra’s research on biomass-to-biofuel supply chain has resulted on a number of innovative models and solution algorithms that improve the performance of this supply chain. She received the NSF CAREER Award in support of this research. Sandra has taught the Information Systems for Industrial Engineers class at MSU for several semesters. This class teaches students how to build Spreadsheet-Based Decision Support Systems. Sandra used the feedback and comments from students to make valuable additions and modifications to this new edition of the book.
Michelle M.H. Şeref
Michelle Şeref has a Ph.D. in Operations Management from the University of Florida and a Master’s and Bachelor’s degree in Industrial and Systems Engineering from the same university. Michelle wrote her Master’s thesis on decision support system development and design while simultaneously working on the first edition of this book and the creation of educational materials for a course in DSS. Michelle’s Ph.D. dissertation focused on supply chain management decisions under various marketing strategies, including advance sales inventory management and new product development pricing and timing decisions. Using her advanced education and research experience in optimizing operations decisions, Michelle is now pursuing a second Ph.D. in rhetoric and communication to study the relationship between social behavior and language. Michelle’s new dissertation focus is on causes and remedies of miscommunication in intercultural and interdisciplinary communication. She plans to complete her new Ph.D. at Virginia Tech and continue her career in academia.
Ravindra K. Ahuja
Ravindra Ahuja is a Professor in Industrial and Systems Engineering at the University of Florida, Gainesville, and also the founding President & CEO of Innovative Scheduling, Inc., a company focused on developing business intelligence solutions for large-scale and complex problems arising in logistics and transportation. Professor Ahuja has contributed both to the theory and applications of Operations Research and his contributions have received highly competitive awards from INFORMS, including 1993 Lanchester Prize, 2003 Pierskalla Award, 2006 Daniel H. Wagner Award, 2007 Koopman Prize, and INFORMS Fellowship in 2008. 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 Web-Enabled Decision Support Systems,” which describes how to build web applications using VB .NET and ASP .NET. Professor Ahuja consults for several Fortune 500 companies.
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.