Chapter Files Web Chapters Project Manual Look Inside Authors
 
Table of Contents
Developing Spreadsheet-Based Decision Support Systems
Using Excel and VBA for Excel
by Sandra D. Ekşioğlu, Michelle M.H. Şeref, Ravindra K. Ahuja, and Wayne L. Winston
Dynamic Ideas, Belmont, Massachusetts, 2011.
ISBN:  978-0-9759146-8-7
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 main contribution of the book is to present all the techniques needed to build complete Excel-based decision support systems in a highly accessible manner through numerous hands-on examples. This book is ideally suited as a textbook for teaching undergraduate- and graduate-level courses in any branch of science, engineering, and management and can also be used as a supplementary reference book or a self-study manual.
Buy the Book

Book Outline

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: TThis part presents an overview of programming in VBA and manipulating Excel objects. Covered topics include macros, programming structures, building user interfaces, and using Object Oriented API in Risk Solver Platform 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.

To view the Table of Contents of the book, click here.


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.

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 studies.

Educational Philosophy

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.

Distinguishing Characteristics

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.