Recent Orders

Our Reviews

Sample Papers

How It Works

Get First 2 Pages Of Your Homework Absolutely Free!

Messages

Welcome to TutorsOnSpot.Com!

World's No. 1 Assignment Writing Market

Post Your Homework

Proposals

Post your homework and get free proposals here!

Post Your Homework

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Get Free Quotes Post Your Requirements

Westhouser paper company case solution

03/12/2021 Client: muhammad11 Deadline: 2 Day

cengage.com/mindtap

Fit your coursework into your hectic life. Make the most of your time by learning your way. Access the resources you need to succeed wherever, whenever.

• �Get more from your time online with an easy-to-follow five-step�learning�path.

• �Stay�focused�with�an�all-in-one-place,�integrated� presentation�of�course�content.

• �Get�the�free�MindTap�Mobile�App�and�learn� wherever you are.

Break limitations. Create your own�potential,�and�be�unstoppable� with�MindTap.

MINDTAP. POWERED BY YOU.

Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

Practical Management Science

Wayne L. Winston Kelley School of Business, Indiana University

S. Christian Albright Kelley School of Business, Indiana University

6th Edition

Australia ● Brazil ● Mexico ● Singapore ● United Kingdom ● United States

This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword for materials in your areas of interest.

Important Notice: Media content referenced within the product description or the product text may not be available in the eBook version.

Practical Management Science, Sixth Edition

Wayne L. Winston, S. Christian Albright

Senior Vice President: Erin Joyner

Vice President, Business and Economics: Mike Schenk

Product Manager: Aaron Arnsparger

Project Manager: Julie Dierig

Content Developer: Tara Slagle, MPS

Product Assistant: Renee Schnee

Marketing Manager: Nate Anderson

Digital Content Specialist: Jennifer Chinn

Manufacturing Planner: Ron Montgomery

Sr. Art Director: Michelle Kunkler

Cover Designer: Ramsdell Design, LLC

Cover Image: agsandrew/ Shutterstock.com

Intellectual Property

Analyst: Reba Frederics

Project Manager: Jillian Shafer

Production Service: Lumina Datamatics, Inc

Unless otherwise noted, all content is © Cengage

ALL RIGHTS RESERVED. No part of this work covered by the copyright herein may be reproduced or distributed in any form or by any means, except as permitted by U.S. copyright law, without the prior written permission of the copyright owner.

Library of Congress Control Number: 2017947975 ISBN: 978-1-337-40665-9

Cengage 20 Channel Center Street Boston, MA 02210 USA

Cengage is a leading provider of customized learning solutions with employees residing in nearly 40 different countries and sales in more than 125 countries around the world. Find your local representative at www.cengage.com.

Cengage products are represented in Canada by Nelson Education, Ltd.

To learn more about Cengage Learning Solutions, visit www.cengage.com

Purchase any of our products at your local college store or at our preferred online store www.cengagebrain.com

For product information and technology assistance, contact us at Cengage Customer & Sales Support, 1-800-354-9706

For permission to use material from this text or product, submit all requests online at www.cengage.com/permissions

Further permissions questions can be emailed to permissionrequest@cengage.com

Printed in the United States of America Print Number: 01 Print Year: 2017

To Mary, my wonderful wife, best friend, and constant companion And to our Welsh Corgi, Bryn, who still just wants to play ball S.C.A.

To my wonderful family Vivian, Jennifer, and Gregory W.L.W.

S. Christian Albright got his B.S. degree in Mathematics from Stanford in 1968 and his Ph.D. degree in Operations Research from Stanford in 1972. Until his retirement in 2011, he taught in the Operations & Decision Technologies Department in the Kelley School of Business at Indiana University. His teaching included courses in management science, computer simulation, and statis- tics to all levels of business students: undergraduates, MBAs, and doctoral students. He has published over 20 articles in leading operations research journals in the area of applied probability, and he has authored several books, including Practical Manage-

ment Science, Data Analysis and Decision Making, Data Analysis for Managers, Spread- sheet Modeling and Applications, and VBA for Modelers. He jointly developed StatTools, a statistical add-in for Excel, with the Palisade Corporation. In “retirement,” he continues to revise his books, and he has developed a commercial product, ExcelNow!, an extension of the Excel tutorial that accompanies this book. On the personal side, Chris has been married to his wonderful wife Mary for 46 years. They have a special family in Philadelphia: their son Sam, his wife Lindsay, and their two sons, Teddy and Archer. Chris has many interests outside the academic area. They include activities with his family (especially traveling with Mary), going to cultural events, power walking, and reading. And although he earns his livelihood from statistics and management science, his real passion is for playing classical music on the piano.

Wayne L. Winston is Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University and is now a Professor of Decision and Information Sciences at the Bauer College at the University of Houston. Winston received his B.S. degree in Mathematics from MIT and his Ph.D. degree in Operations Research from Yale. He has written the successful textbooks Operations Research: Applications and Algorithms, Mathematical Programming: Applications and Algorithms, Simulation Modeling with @RISK, Practical Management Science, Data Analysis for Managers, Spreadsheet

Modeling and Applications, Mathletics, Data Analysis and Business Modeling with Excel 2013, Marketing Analytics, and Financial Models Using Simulation and Optimization. Winston has published over 20 articles in leading journals and has won more than 45 teaching awards, including the school-wide MBA award six times. His current interest is in showing how spreadsheet models can be used to solve business problems in all disciplines, particularly in finance, sports, and marketing. Wayne enjoys swimming and basketball, and his passion for trivia won him an appearance several years ago on the television game show Jeopardy, where he won two games. He is married to the lovely and talented Vivian. They have two children, Gregory and Jennifer.

About the Authors

vii

Preface xiii 1 Introduction to Modeling 1 2 Introduction to Spreadsheet Modeling 19 3 Introduction to Optimization Modeling 71 4 Linear Programming Models 135 5 Network Models 219 6 Optimization Models with Integer Variables 277 7 Nonlinear Optimization Models 339 8 Evolutionary Solver: An Alternative Optimization Procedure 407 9 Decision Making under Uncertainty 457 10 Introduction to Simulation Modeling 515 11 Simulation Models 589 12 Queueing Models 667 13 Regression and Forecasting Models 715 14 Data Mining 771 References 809 Index 815

MindTap Chapters 15 Project Management 15-1 16 Multiobjective Decision Making 16-1 17 Inventory and Supply Chain Models 17-1

Brief Contents

Preface xiii

CHAPTER 1 Introduction to Modeling 1 1.1 Introduction 3 1.2 A Capital Budgeting Example 3 1.3 Modeling versus Models 6 1.4 A Seven-Step Modeling Process 7 1.5 A Great Source for Management Science

Applications: Interfaces 13 1.6 Why Study Management Science? 13 1.7 Software Included with This Book 15 1.8 Conclusion 17

CHAPTER 2 Introduction to Spreadsheet Modeling 19

2.1 Introduction 20 2.2 Basic Spreadsheet Modeling:

Concepts and Best Practices 21 2.3 Cost Projections 25 2.4 Breakeven Analysis 31 2.5 Ordering with Quantity Discounts

and Demand Uncertainty 39 2.6 Estimating the Relationship between

Price and Demand 44 2.7 Decisions Involving the Time Value of

Money 54 2.8 Conclusion 59 Appendix Tips for Editing and

Documenting Spreadsheets 64 CASE 2.1 Project Selection at Ewing Natural

Gas 66 CASE 2.2 New Product Introduction at eTech 68

CHAPTER 3 Introduction to Optimization Modeling 71

3.1 Introduction 72 3.2 Introduction to Optimization 73 3.3 A Two-Variable Product Mix Model 75

Contents

3.4 Sensitivity Analysis 87 3.5 Properties of Linear Models 97 3.6 Infeasibility and Unboundedness 100 3.7 A Larger Product Mix Model 103 3.8 A Multiperiod Production Model 111 3.9 A Comparison of Algebraic

and Spreadsheet Models 120 3.10 A Decision Support System 121 3.11 Conclusion 123 Appendix Information on Optimization Software 130 CASE 3.1 Shelby Shelving 132

CHAPTER 4 Linear Programming Models 135 4.1 Introduction 136 4.2 Advertising Models 137 4.3 Employee Scheduling Models 147 4.4 Aggregate Planning Models 155 4.5 Blending Models 166 4.6 Production Process Models 174 4.7 Financial Models 179 4.8 Data Envelopment Analysis (DEA) 191 4.9 Conclusion 198 CASE 4.1 Blending Aviation Gasoline at Jansen

Gas 214 CASE 4.2 Delinquent Accounts at GE Capital 216 CASE 4.3 Foreign Currency Trading 217

CHAPTER 5 Network Models 219 5.1 Introduction 220 5.2 Transportation Models 221 5.3 Assignment Models 233 5.4 Other Logistics Models 240 5.5 Shortest Path Models 249 5.6 Network Models in the Airline Industry 258 5.7 Conclusion 267 CASE 5.1 Optimized Motor Carrier Selection at

Westvaco 274

CHAPTER 9 Decision Making under Uncertainty 457

9.1 Introduction 458 9.2 Elements of Decision Analysis 460 9.3 Single-Stage Decision Problems 467 9.4 The PrecisionTree Add-In 471 9.5 Multistage Decision Problems 474 9.6 The Role of Risk Aversion 492 9.7 Conclusion 499 CASE 9.1 Jogger Shoe Company 510 CASE 9.2 Westhouser Paper Company 511 CASE 9.3 Electronic Timing System for

Olympics 512 CASE 9.4 Developing a Helicopter Component

for the Army 513

CHAPTER 10 Introduction to Simulation Modeling 515

10.1 Introduction 516 10.2 Probability Distributions for Input

Variables 518 10.3 Simulation and the Flaw of Averages 537 10.4 Simulation with Built-in Excel Tools 540 10.5 Introduction to @RISK 551 10.6 The Effects of Input Distributions on

Results 568 10.7 Conclusion 577 Appendix Learning More About @RISK 583 CASE 10.1 Ski Iacket Production 584 CASE 10.2 Ebony Bath Soap 585 CASE 10.3 Advertising Effectiveness 586 CASE 10.4 New Project Introduction at eTech 588

CHAPTER 11 Simulation Models 589 11.1 Introduction 591 11.2 Operations Models 591 11.3 Financial Models 607 11.4 Marketing Models 631 11.5 Simulating Games of Chance 646 11.6 Conclusion 652 Appendix Other Palisade Tools for Simulation 662

x Contents

CHAPTER 6 Optimization Models with Integer Variables 277

6.1 Introduction 278 6.2 Overview of Optimization with Integer

Variables 279 6.3 Capital Budgeting Models 283 6.4 Fixed-Cost Models 290 6.5 Set-Covering and Location-Assignment

Models 303 6.6 Cutting Stock Models 320 6.7 Conclusion 324 CASE 6.1 Giant Motor Company 334 CASE 6.2 Selecting Telecommunication Carriers to

Obtain Volume Discounts 336 CASE 6.3 Project Selection at Ewing Natural Gas 337

CHAPTER 7 Nonlinear Optimization Models 339 7.1 Introduction 340 7.2 Basic Ideas of Nonlinear Optimization 341 7.3 Pricing Models 347 7.4 Advertising Response and Selection Models 365 7.5 Facility Location Models 374 7.6 Models for Rating Sports Teams 378 7.7 Portfolio Optimization Models 384 7.8 Estimating the Beta of a Stock 394 7.9 Conclusion 398 CASE 7.1 GMS Stock Hedging 405

CHAPTER 8 Evolutionary Solver: An Alternative Optimization Procedure 407

8.1 Introduction 408 8.2 Introduction to Genetic Algorithms 411 8.3 Introduction to Evolutionary Solver 412 8.4 Nonlinear Pricing Models 417 8.5 Combinatorial Models 424 8.6 Fitting an S-Shaped Curve 435 8.7 Portfolio Optimization 439 8.8 Optimal Permutation Models 442 8.9 Conclusion 449 CASE 8.1 Assigning MBA Students to Teams 454 CASE 8.2 Project Selection at Ewing Natural Gas 455

Contents xi

CASE 11.1 College Fund Investment 664 CASE 11.2 Bond Investment Strategy 665 CASE 11.3 Project Selection Ewing Natural Gas 666

CHAPTER 12 Queueing Models 667 12.1 Introduction 668 12.2 Elements of Queueing Models 670 12.3 The Exponential Distribution 673 12.4 Important Queueing Relationships 678 12.5 Analytic Steady-State Queueing Models 680 12.6 Queueing Simulation Models 699 12.7 Conclusion 709 CASE 12.1 Catalog Company Phone Orders 713

CHAPTER 13 Regression and Forecasting Models 715 13.1 Introduction 716 13.2 Overview of Regression Models 717 13.3 Simple Regression Models 721 13.4 Multiple Regression Models 734 13.5 Overview of Time Series Models 745 13.6 Moving Averages Models 746 13.7 Exponential Smoothing Models 751 13.8 Conclusion 762 CASE 13.1 Demand for French Bread at Howie’s

Bakery 768 CASE 13.2 Forecasting Overhead at Wagner

Printers 769 CASE 13.3 Arrivals at the Credit Union 770

CHAPTER 14 Data Mining 771 14.1 Introduction 772 14.2 Classification Methods 774 14.3 Clustering Methods 795 14.4 Conclusion 806 CASE 14.1 Houston Area Survey 808

References 809

Index 815

MindTap Chapters

CHAPTER 15 Project Management 15-1 15.1 Introduction 15-2 15.2 The Basic CPM Model 15-4 15.3 Modeling Allocation of Resources 15-14 15.4 Models with Uncertain Activity Times 15-30 15.5 A Brief Look at Microsoft Project 15-35 15.6 Conclusion 15-39

CHAPTER 16 Multiobjective Decision Making 16-1 16.1 Introduction 16-2 16.2 Goal Programming 16-3 16.3 Pareto Optimality and Trade-Off Curves 16-12 16.4 The Analytic Hierarchy Process (AHP) 16-20 16.5 Conclusion 16-25

CHAPTER 17 Inventory and Supply Chain Models 17-1 17.1 Introduction 17-2 17.2 Categories of Inventory and Supply Chain

Models 17-3 17.3 Types of Costs in Inventory and Supply Chain

Models 17-5 17.4 Economic Order Quantity (EOQ) Models 17-6 17.5 Probabilistic Inventory Models 17-21 17.6 Ordering Simulation Models 17-34 17.7 Supply Chain Models 17-40 17.8 Conclusion 17-50 CASE 17.1 Subway Token Hoarding 17-57

xiii

Practical Management Science provides a spreadsheet- based, example-driven approach to management science. Our initial objective in writing the book was to reverse negative attitudes about the course by making the subject relevant to students. We intended to do this by imparting valuable modeling skills that students can appreciate and take with them into their careers. We are very gratified by the success of previous editions. The book has exceeded our initial objectives. We are especially pleased to hear about the success of the book at many other colleges and universities around the world. The acceptance and excitement that has been generated has motivated us to revise the book and make the current edition even better. When we wrote the first edition, management science courses were regarded as irrelevant or uninteresting to many business students, and the use of spreadsheets in management science was in its early stages of development. Much has changed since the first edition was published in 1996, and we believe that these changes are for the better. We have learned a lot about the best practices of spreadsheet modeling for clarity and communication. We have also developed better ways of teaching the materials, and we understand more about where students tend to have difficulty with the concepts. Finally, we have had the opportunity to teach this material at several Fortune 500 companies (including Eli Lilly, PricewaterhouseCoopers, General Motors, Tomkins, Microsoft, and Intel). These companies, through their enthusiastic support, have further enhanced the realism of the examples included in this book. Our objective in writing the first edition was very simple—we wanted to make management science relevant and practical to students and professionals. This book continues to distinguish itself in the market in four fundamental ways:

■ Teach by Example. The best way to learn modeling concepts is by working through examples and solving an abundance of problems. This active learning approach is not new, but our text has more fully developed this approach than any book in the field. The feedback we have received from many of you has confirmed the success of this pedagogical approach for management science.

■ Integrate Modeling with Finance, Marketing, and Operations Management. We integrate modeling into all functional areas of business. This is an important feature because the majority of business students major in finance and marketing. Almost all competing textbooks emphasize operations management–related examples. Although these examples are important, and many are included in the book, the application of modeling to problems in finance and marketing is too important to ignore. Throughout the book, we use real examples from all functional areas of business to illustrate the power of spreadsheet modeling to all of these areas. At Indiana University, this led to the development of two advanced MBA electives in finance and marketing that built upon the content in this book.

■ Teach Modeling, Not Just Models. Poor attitudes among students in past management science courses can be attributed to the way in which they were taught: emphasis on algebraic formulations and memorization of models. Students gain more insight into the power of management science by developing skills in modeling. Throughout the book, we stress the logic associated with model development, and we discuss solutions in this context. Because real problems and real models often include limitations or alternatives, we include several “Modeling Issues” sections to discuss these important matters. Finally, we include “Modeling Problems” in most chapters to help develop these skills.

■ Provide Numerous Problems and Cases. Whereas all textbooks contain problem sets for students to practice, we have carefully and judiciously crafted the problems and cases contained in this book. Each chapter contains four types of problems: easier Level A Problems, more difficult Level B Problems, Modeling Problems, and Cases. Most of the problems following sections of chapters ask students to extend the examples in the preceding section. The end-of-chapter problems then ask students to explore new models. Selected solutions are available to students through MindTap and are

Preface

xiv Preface

denoted by the second-color numbering of the problem. Solutions for all of the problems and cases are provided to adopting instructors. In addition, shell files (templates) are available for many of the problems for adopting instructors. The shell files contain the basic structure of the problem with the relevant formulas omitted. By adding or omitting hints in individual solutions, instructors can tailor these shell files to best meet the specific needs of students.

New to the Sixth Edition The immediate reason for the sixth edition was the introduction of Excel 2016. Admittedly, this is not really a game changer, but it does provide new features that ought to be addressed. In addition, once we were motivated by Excel 2016 to revise the book, we saw the possibility for other changes that will hopefully improve the book. Important changes to the sixth edition include the following:

■ The book is now entirely geared to Excel 2016. In particular, all screenshots are from this newest version of Excel. However, the changes are not dramatic, and users of Excel 2013, Excel 2010, and even Excel 2007 should have no trouble following. Also, the latest changes in the accompanying @RISK, PrecisionTree, and StatTools add-ins have been incorporated into the text.

■ Many of the problems (well over 100) have new data. Even though these problems are basically the same as before, the new data results in different solutions. Similarly, the time series data in several of the chapter examples have been updated.

■ A new chapter on Data Mining has been added. It covers classification problems (including a section on neural networks) and clustering. To keep the size of the physical book roughly the same as before, the chapter on Inventory and Supply Chain Models has been moved online as Chapter 17.

■ Probably the single most important change is that the book is now incorporated into Cengage’s MindTap platform. This provides an enhanced learning environment for both instructors and students. Importantly, dozens of new multiple choice questions are included in MindTap. These are not of the memorization variety. Instead, they

require students to understand the material, and many of them require students to solve problems similar to those in the book. They are intended to help instructors where grading in large classes is a serious issue.

MindTap: Empower Your Students MindTap is a platform that propels students from memorization to mastery. It gives you the instructor complete control of your course, so you can provide engaging content, challenge every learner, and build student confidence. You can customize interactive syllabi to emphasize priority topics, then add your own material or notes to the eBook as desired. This outcomes-driven application gives you the tools needed to empower your students and boost both understanding and performance.

Access Everything You Need in One Place MindTap’s preloaded and organized course materials, including interactive multimedia, assignments, quizzes, and more, allow you to cut down on prep time and teach more efficiently. In addition, the full textbook is available for smartphone via the MindTap mobile app. This gives your students the power to read, listen, and study on their phones, so that they can learn in the way best suited to them.

Empower Students to Reach their Potential Twelve distinct metrics give you actionable insights into student engagement. You can identify topics troubling your entire class and instantly communicate with those struggling. Students can track their scores to stay motivated towards their goals.

Control Your Course—and Your Content MindTap gives you the flexibility to reorder textbook chapters, add your own notes, and embed a variety of content, including Open Educational Resources (OER). You can personalize course content to your students’ needs. Students can even read your notes, add their own, and highlight key text to aid their learning.

Get a Dedicated Team, Whenever You Need Them MindTap isn’t just a tool. It is backed by a personalized team eager to support you. We can help set up your

Preface xv

course and tailor it to your specific objectives, so you will be ready to make an impact from day one. You can be confident that we will be standing by to help you and your students until the final day of the term.

Student Website Access to the companion site for this text can be found at cengage.com/login. Students will need to set up a free account and then search for this text and edition by author name, title, and/or ISBN. The site includes access to the student problem files, example files, case files, an Excel tutorial, and SolverTable. In addition, a link to access download instructions for Palisade’s DecisionTools Suite is available. Note: An access code is not needed to access this software; only the index that is in the back of this textbook is needed to download the Decision Tools Suite.

Software We continue to be very excited about offering the most comprehensive suite of software ever available with a management science textbook. The commercial value of the software available with this text exceeds $1,000 if purchased directly. This software is available free with new copies of the sixth edition. The following Palisade software is available from www.cengagebrain.com.

■ Palisade’s DecisionTools™ Suite, including the award-winning @RISK, PrecisionTree, StatTools, TopRank, NeuralTools, Evolver, and BigPicture. This software is not available with any competing textbook and comes in an educational version that is only slightly scaled- down from the expensive commercial version. (StatTools replaces Albright’s StatPro add-in that came with the second edition. Although it is no longer maintained, StatPro is still freely available from www.kelley.iu.edu/albrightbooks.) For more information about the Palisade Corporation and the DecisionTools Suite, visit Palisade’s website at www.palisade.com.

■ To make sensitivity analysis for optimization models useful and intuitive, we continue to provide Albright’s SolverTable add-in (which is also freely available from www.kelley.iu.edu /albrightbooks). SolverTable provides data table– like sensitivity output for optimization models that is easy to interpret.

Example Files, Data Sets, Problem Files, and Cases Also on the student website are the Excel files for all of the examples in the book, as well as many data files required for problems and cases. As in previous editions, there are two versions of the example files: a completed version and a template to get students started. Because this book is so example- and problem- oriented, these files are absolutely essential. There are also a few extra example files, in Extra Examples folders, that are available to instructors and students. These extras extend the book examples in various ways.

Ancillaries Instructor Materials Adopting instructors can obtain all resources online. Please go to login.cengage.com to access the following resources:

■ PMS6e Problem Database.xlsx file, which contains information about all problems in the book and the correspondence between them and those in the previous edition

■ Solution files (in Excel format) for all of the problems and cases in the book and solution shells (templates) for selected problems

■ PowerPoint® presentation files ■ Test Bank in Word format and also in the online

testing service, Cognero

Albright also maintains his own website at www .kelley.iu.edu/albrightbooks. Among other things, the instructor website includes errata for each edition.

Companion VBA Book Soon after the first edition appeared, we began using Visual Basic for Applications (VBA), the program ming language for Excel, in some of our management science courses. VBA allows you to develop decision support systems around the spreadsheet models. (An example appears near the end of Chapter 3.) This use of VBA has been popular with our students, and many instructors have expressed interest in learning how to do it. For additional support on this topic, a companion book by Albright, VBA for Modelers, 5e (ISBN 9781285869612) is available. It assumes no prior experience in computer programming, but it progresses rather quickly to the

xvi Preface

development of interesting and nontrivial applications. The sixth edition of Practical Manage ment Science depends in no way on this companion VBA book, but we encourage instructors to incorporate some VBA into their management science courses. This is not only fun, but students quickly learn to appreciate its power. If you are interested in adopting VBA for Modelers, contact your local Cengage Learning representative.

Mac Users We are perfectly aware that more students, maybe even the majority of students, are now using Macs. This is a fact of life, and we can no longer assume that we’re targeting only Windows users. There are two possible solutions for you Mac users. First, you can use a Windows emulation program such as Boot Camp or Parallels. Our Mac users at IU have been doing this for years with no problems. Second, you can use Excel for the Mac, with the latest 2016 version highly recommended. Its user interface is now very similar to the Windows version, so it should be easy to get used to. However, you should be aware that not everything will work. Specifically, the Palisade and SolverTable add-ins will not work with Excel for Mac, and this is not likely to change in the future. Also, some features of Excel for Windows (mostly advanced features not covered in this book such as pivot charts and histograms) have not yet been incorporated in Excel for the Mac.

Acknowledgments This book has gone through several stages of reviews, and it is a much better product because of them. The majority of the reviewers’ suggestions were very

good ones, and we have attempted to incorporate them. We would like to extend our appreciation to:

Mohammad Ahmadi, University of Tennessee at Chattanooga

Ehsan Elahi, University of Massachusetts–Boston Kathryn Ernstberger, Indiana University Southeast Levon R. Hayrapetyan, Houston Baptist University Bradley Miller, University of Houston Sal Agnihothri, Binghamton University, SUNY Ekundayo Shittu, The George Washington

University Yuri Yatsenko, Houston Baptist University We would also like to thank three special people. First, we want to thank our original editor Curt Hinrichs. Curt’s vision was largely responsible for the success of the early editions of Practical Management Science. Second, we were then lucky to move from one great editor to another in Charles McCormick. Charles is a consummate professional. He was both patient and thorough, and his experience in the publishing business ensured that the tradition Curt started was carried on. Third, after Charles’s retirement, we were fortunate to be assigned to one more great editor, Aaron Arnsparger, for the current edition. We hope to continue working with Aaron far into the future. We would also enjoy hearing from you—we can be reached by e-mail. And please visit either of the following websites for more information and occasional updates: ■ www.kelley.iu.edu/albrightbooks ■ www.cengagebrain.com

S. Christian Albright (albright@indiana.edu) Bloomington, Indiana Wayne L. Winston (winston@indiana.edu)

Introduction to Modeling

C H A P T E R

BUSINESS ANALYTICS PROVIDES INSIGHTS AND IMPROVES PERFORMANCE

This book is all about using quantitative modeling to help companies make better decisions and improve performance. We have been teach- ing management science for decades, and companies have been using the management science methods discussed in this book for decades to improve performance and save millions of dollars. Indeed, the applied journal Interfaces, discussed later in this chapter, has chronicled management science success stories for years. Therefore, we were a bit surprised when a brand new term, Business Analytics (BA), became hugely popular several years ago. All of a sudden, BA promised to be the road to success. By using quantitative BA methods—data analysis, optimization, simulation, prediction, and others—companies could drastically improve business performance. Haven’t those of us in management science been doing this for years? What is different about BA that has made it so popular, both in the academic world and even more so in the business world?

The truth is that BA does use the same quantitative methods that have been the hallmark of management science for years, the same methods you will learn in this book. BA has not all of a sudden invented brand new quantitative methods to eclipse traditional management science methods. The main difference is that BA uses big data to solve business problems and provide insights. Companies now have access to huge sources of data,

ic ha

el ju

ng /S

hu tte

rs to

ck .c

and better/faster algorithms and technology are now available to use huge data sets for statistical and quantitative analysis, predictive modeling, optimization, and simulation. In short, the same quantitative methods that have been available for years can now be even more effective by utilizing big data and the corresponding algorithms and technology.

For a quick introduction to BA, you should visit the BA Wikipedia site (search the Web for “business analytics”). Among other things, it lists areas where BA plays a prominent role, including the following: retail sales analytics; financial services analytics; risk and credit analytics; marketing analytics; pricing analytics; supply chain analytics; and transportation analytics. If you glance through the examples and problems in this book, you will see that most of them come from these same areas. Again, the difference is that we use relatively small data sets to get you started—we do not want to overwhelm you with gigabytes of data—whereas real applications of BA use huge data sets to advantage.

A more extensive discussion of BA can be found in the Fall 2011 research report, Analytics: The Widening Divide, published in the MIT Sloan Management Review in collabo- ration with IBM, a key developer of BA software (search the Web for the article’s title). This 22-page article discusses what BA is and provides several case studies. In addition, it lists three key competencies people need to compete successfully in the BA world—and hopefully you will be one of these people.

■ Competency 1: Information management skills to manage the data. This competency involves expertise in a variety of techniques for managing data. Given the key role of data in BA methods, data quality is extremely important. With data coming from a number of disparate sources, both internal and external to an organi- zation, achieving data quality is no small feat.

■ Competency 2: Analytics skills and tools to understand the data. We were not surprised, but rather very happy, to see this competency listed among the requirements because these skills are exactly the skills we cover throughout this book—optimization with advanced quantitative algorithms, simulation, and others.

■ Competency 3: Data-oriented culture to act on the data. This refers to the culture within the organization. Everyone involved, especially top management, must believe strongly in fact-based decisions arrived at using analytical methods.

The article argues persuasively that the companies that have these competencies and have embraced BA have a distinct competitive advantage over companies that are just starting to use BA methods or are not using them at all. This explains the title of the article. The gap between companies that embrace BA and those that do not will only widen in the future.

One final note about the relationship between BA and management science is that the journal Management Science published a special issue in June 2014 with an emphasis on BA. The following is an excerpt from the Call for Papers for this issue (search the Web for “management science business analytics special issue”).

“We envision business analytics applied to many domains, including, but surely not limited to: digital market design and operation; network and social-graph analysis; pricing and revenue management; targeted marketing and customer relationship management; fraud and security; sports and entertainment; retailing to healthcare to financial services to many other industries. We seek novel modeling and empirical work which includes, among others, probability modeling, structural empirical models, and/or optimization methods.”

This is even more confirmation of the tight relationship between BA and management science. As you study this book, you will see examples of most of the topics listed in this quote. ■

2 Chapter 1 Introduction to Modeling

1.2 A Capital Budgeting Example 3

1.1 INTRODUCTION The purpose of this book is to expose you to a variety of problems that have been solved successfully with management science methods and to give you experience in modeling these problems in the Excel spreadsheet package. The subject of management science has evolved for more than 60 years and is now a mature field within the broad category of applied mathematics. This book emphasizes both the applied and mathematical aspects of management science. Beginning in this chapter and continuing throughout the rest of the book, we discuss many successful management science applications, where teams of highly trained people have implemented solutions to the problems faced by major com- panies and have saved these companies millions of dollars. Many airlines, banks, and oil companies, for example, could hardly operate as they do today without the support of management science. In this book, we will lead you through the solution procedure for many interesting and realistic problems, and you will experience firsthand what is required to solve these problems successfully. Because we recognize that most of you are not highly trained in mathematics, we use Excel spreadsheets to solve problems, which makes the quantitative analysis much more understandable and intuitive.

The key to virtually every management science application is a mathematical model. In simple terms, a mathematical model is a quantitative representation, or idealization, of a real problem. This representation might be phrased in terms of mathematical expressions (equations and inequalities) or as a series of related cells in a spreadsheet. We prefer the lat- ter, especially for teaching purposes, and we concentrate primarily on spreadsheet models in this book. However, in either case, the purpose of a mathematical model is to represent the essence of a problem in a concise form. This has several advantages. First, it enables managers to understand the problem better. In particular, the model helps to define the scope of the problem, the possible solutions, and the data requirements. Second, it allows analysts to use a variety of the mathematical solution procedures that have been developed over the past half century. These solution procedures are often computer-intensive, but with today’s cheap and abundant computing power, they are usually feasible. Finally, the modeling process itself, if done correctly, often helps to “sell” the solution to the people who must work with the system that is eventually implemented.

In this introductory chapter, we begin by discussing a relatively simple example of a mathematical model. Then we discuss the distinction between modeling and a collection of models. Next, we discuss a seven-step modeling process that can be used, in essence if not in strict conformance, in most successful management science applications. Finally, we discuss why the study of management science is valuable, not only to large corporations, but also to students like you who are about to enter the business world.

1.2 A CAPITAL BUDGETING EXAMPLE As indicated earlier, a mathematical model is a set of mathematical relationships that rep- resent, or approximate, a real situation. Models that simply describe a situation are called descriptive models. Other models that suggest a desirable course of action are called optimi- zation models. To get started, consider the following simple example of a mathematical model. It begins as a descriptive model, but it then becomes an optimization model.

A Descriptive Model A company faces capital budgeting decisions. (This type of model is discussed in detail in Chapter 6.) There are seven potential investments. Each has an investment cost and a

corresponding stream of cash flows (including the investment cost) summarized by a net present value (NPV). These are listed in Figure 1.1. Row 7 also lists the return on invest- ment (ROI) for each investment, the ratio of NPV to cost, minus 1.

The company must decide which of these seven investments to make. There are two constraints that affect the decisions. First, each investment is an all-or-nothing decision. The company either invests entirely in an investment, or it ignores the investment completely. It is not possible to go part way, incurring a fraction of the cost and receiving a fraction of the revenues. Second, the company is limited by a budget of $15 million. The total cost of the investments it chooses cannot exceed this budget. With these constraints in mind, the company wants to choose the investments that maximize the total NPV.

A descriptive model can take at least two forms. One form is to show all of the elements of the problem in a diagram, as in Figure 1.2. This method, which will be used extensively in later chapters, helps the company to visualize the problem and to better understand how the elements of the problem are related. Our conventions are to use red ovals for decisions, blue rectangles for given inputs, yellow rounded rectangles for calcu- lations, and gray-bordered rectangles for objectives to optimize. (These colors are visible when you open the files in Excel.)

Although the diagram in Figure 1.2 helps the company visualize the problem, it does not provide any numeric information. This can be accomplished with the second descriptive form of the model in Figure 1.3. Any set of potential decisions, 0/1 values, can be entered in row 10 to indicate which of the investments are undertaken. Then sim- ple Excel formulas that relate the decisions to the inputs in rows 5 and 6 can be used to calculate the total investment cost and the total NPV in cells B14 and B17. For example, the formula in cell B14 is

=SUMPRODUCT(B5:H5,B10:H10)

(If you don’t already know Excel’s SUMPRODUCT function, you will learn it in the next chapter and then use it extensively in later chapters.) The company can use this model to investigate various decisions. For example, the current set of decisions looks good in terms of total NPV, but it is well over budget. By trying other sets of 0/1 values in row 10, the company can play “what-if” to attempt to find a good set of decisions that stays within budget.

4 Chapter 1 Introduction to Modeling

Figure 1.1 Costs and NPVs for Capital Budgeting Model

1 2 3 4 5 6 7

HGFEDCB Capital budgeting model

Input data on potential investments ($ millions) Investment

1 $5.0 $5.6

12.0%

$2.4 $2.7

12.5%

$3.5 $3.9

11.4%

$5.9 $6.8

15.3%

$6.9 $7.7

11.6%

$4.5 $5.1

13.3%

$3.0 $3.3

10.0%

2 3 4 5 6 7 Cost NPV ROI

Figure 1.2 Relationships among Elements of the Model

Whether to invest

Investment cost

Investment NPV

Budget< =Total cost ofinvestments

Maximize total NPV

1.2 A Capital Budgeting Example 5

Because there are two possible values for each cell in row 10, 0 or 1, there are 27 5 128 possible sets of decisions, some of which will be within the budget and some of which will be over the budget. This is not a huge number, so the company could potentially try each of these to find the optimal investment strategy. However, you can probably see that this “exhaustive search” strategy can easily become overwhelming. For example, if there were 14 potential investments, the number of possible sets of decisions would increase to 214 5 16,384. The company would probably not want to search through all of these, which is why the optimization model discussed next is so useful.

An Optimization Model The company’s dream at this point is to have software that can quickly search through all potential sets of decisions and find the one that maximizes total NPV while staying within the budget. Fortunately, this software exists, and you own it! It is called Solver, an add-in to Excel, and it is discussed in detail in Chapters 3 to 8. All the company needs to do, after creating the descriptive model in Figure 1.3, is to invoke Solver. This opens a dialog box (not shown here) where the company can specify the objective cell, the range of decision variable cells, and any constraints. Then Solver finds the optimal solution, usually in a matter of seconds.

The optimal solution for this particular model appears in Figure 1.4. The company should undertake investments 3, 5, and 6, and it should ignore the others. Although not quite all of the budget is used, the company is guaranteed that no other set of decisions can obtain a higher total NPV and stay within the budget.

Optimal solutions are sometimes surprising. You might have expected the company to choose the investments with the largest ROIs, at least until the budget runs out. However, this is not what the optimal solution prescribes. For example, investment 4 has the largest ROI, but it is not chosen, and investment 5, which is chosen, has a lower ROI than three of the investments that are not chosen. As this example illustrates, an optimization model often provides insights that your intuition alone cannot provide.

Incorporating Uncertainty Most of the optimization models in this book are deterministic, meaning that there is no uncertainty about any of the model inputs. Specifically, the NPVs in row 6 of the capital budgeting model are assumed to be known when the company makes its investment

Figure 1.3 Descriptive Model for What-If Analysis

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17

HGFEDCB Capital budgeting model

Decisions: whether to invest

Budget constraint

Objective to maximize

Input data on potential investments ($ millions) Investment 1

$5.0 $5.6

12.0%

Total cost $17.3

$19.4

Budget $15

1 1 0 1 1 0

$2.4 $2.7

12.5%

$3.5 $3.9

11.4%

$5.9 $6.8

15.3%

$6.9 $7.7

11.6%

$4.5 $5.1

13.3%

$3.0 $3.3

10.0%

2 3 4 5 6 7 Cost

NPV ROI

1 if yes, 0 if no

Total NPV

decisions. This is clearly a strong assumption; uncertainties about future revenues almost surely exist. This uncertainty can be built into the model by replacing fixed input values by probability distributions. This takes us into the realm of simulation models, which are dis- cussed in detail in Chapters 10 and 11. Simulation lets the company see how an output such as total NPV varies, for any given set of decisions, as uncertain inputs vary over their ranges of possible values. Fortunately, another Excel add-in called @RISK for performing simulations is available with this book, and it is used extensively in Chapters 10 and 11. @RISK even has a tool called RISKOptimizer that can be used to find the optimal set of decisions in the face of uncertainty. We discuss RISKOptimizer only briefly, but it is very powerful software.

1.3 MODELING VERSUS MODELS Management science, at least as it has been taught in many traditional courses, has evolved as a collection of mathematical models. These include various linear program- ming models (the transportation model, the product mix model, the shortest route model, and others), inventory models, queueing models, and more. Much time has been devoted to teaching the intricacies of these particular models. Management science practitio- ners, however, have justifiably criticized this emphasis on specific models. They argue that the majority of real-world management science problems cannot be neatly cate- gorized as one of the handful of models typically included in a management science textbook. That is, there is often no “off-the-shelf” model that can be used, at least not without modification, to solve a company’s real problem. Unfortunately, management science students might get the impression that all problems must be “shoe-horned” into one of the textbook models.

The good news is that this emphasis on specific models has been changing in the past decade or two, and our goal in this book is to embrace this change. Specifically, this book stresses modeling, not models. The distinction between modeling and models will become clear as you proceed through the book. Learning specific models is essentially a memori- zation process—memorizing the details of a particular model, such as the transportation model, and possibly learning how to “trick” other problems into looking like a transporta- tion model. Modeling, on the other hand, is a process in which you abstract the essence of a real problem into a model, spreadsheet or otherwise. Although many problems fall naturally into several categories, successful modelers do not try to shoe-horn each problem into one

6 Chapter 1 Introduction to Modeling

Figure 1.4 Optimal Solution 12

3 4 5 6 7 8 9

10 11 12 13 14 15 16 17

HGFEDCB Capital budgeting model

Decisions: whether to invest

Budget constraint

Objective to maximize

Input data on potential investments ($ millions) Investment 1

$5.0 $5.6

12.0%

Total cost $14.9

$16.7

Budget $15

0 1 0 1 1 0

$2.4 $2.7

12.5%

$3.5 $3.9

11.4%

$5.9 $6.8

15.3%

$6.9 $7.7

11.6%

$4.5 $5.1

13.3%

$3.0 $3.3

10.0%

2 3 4 5 6 7 Cost

NPV ROI

1 if yes, 0 if no

Total NPV

of a small number of well-studied models. Instead, they treat each problem on its own merits and model it appropriately, using all of the logical, analytical, and spreadsheet skills they have at their disposal—and, of course, using their experience with previous models they have developed. This way, if they come across a problem that does not look exactly like any- thing they have ever seen, they still have the skills and flexibility to model it successfully.

This doesn’t mean you won’t learn some “classical” models from management sci- ence in this book; in fact, we will discuss the transportation model in linear programming, the M/M/1 model in queueing, the EOQ model in inventory, and other classics. These are important models that should not be ignored; however, you certainly do not have to memo- rize these specific models. They are simply a few of the many models you will learn how to develop. The real emphasis throughout is on the modeling process—how a real-world problem is abstracted into a spreadsheet model of that problem. We discuss this modeling process in more detail in the following section.

1.4 A SEVEN-STEP MODELING PROCESS The discussion of the capital budgeting model in section 1.2 presented some of the basic principles of management science modeling. This section further expands on these ideas by characterizing modeling as the following seven-step process.

Step 1: Problem Definition The analyst first defines the organization’s problem. Defining the problem includes speci- fying the organization’s objectives and the parts of the organization that must be studied before the problem can be solved. In the capital budgeting example, the organization’s problem is to choose the best set of investments, given a spending budget.

Step 2: Data Collection After defining the problem, the analyst collects data to estimate the value of parameters that affect the organization’s problem. These estimates are used to develop a mathematical model (step 3) of the organization’s problem and predict solutions (step 4). In the capital budgeting example, the company’s financial analysts must determine the stream of cash flows from the various investments. As discussed in the Business Analytics opener to this chapter, data are becoming more important than ever to the decision-making process, both because of the huge amounts of data available and because of the powerful technology available to take advantage of it.

Step 3: Model Development In the third step, the analyst develops a model of the problem. In this book, we present a variety of examples to show how this can be done.1 Some of these are deterministic opti- mization models, where all of the problem inputs are assumed to be known and the goal is to determine values of decision variables that maximize or minimize an objective. Others are simulation models, where some of the inputs are modeled with probability distribu- tions. Occasionally, the models are so complex mathematically that no simple formulas can be used to relate inputs to outputs. Prime examples of this are the analytic queuing

1.4 A Seven-Step Modeling Process 7

1All of these models can generically be called mathematical models. However, because we implement them in spreadsheets, we generally refer to them as spreadsheet models.

models in Chapter 12. Nevertheless, such models can still be analyzed by appealing to extensive academic research and possibly implementing complex formulas with Excel macros.

Step 4: Model Verification The analyst now tries to determine whether the model developed in the previous step is an accurate representation of reality. At the very least, the model must pass “plausibility checks.” In this case, various input values and decision variable values are entered into the model to see whether the resulting outputs are plausible.

Step 5: Optimization and Decision Making Given a model and a set of possible decisions, the analyst must now choose the decision or strategy that best meets the organization’s objectives. We briefly discussed an optimiza- tion model for the capital budgeting example, and we will discuss many other optimization models throughout the book.

Step 6: Model Communication to Management The analyst presents the model and the recommendations from the previous steps to the organization. In some situations, the analyst might present several alternatives and let the organization choose the best one.

Step 7: Model Implementation If the organization has accepted the validity and usefulness of the model, the analyst then helps to implement its recommendations. The implemented system must be monitored constantly (and updated dynamically as the environment changes) to ensure that the model enables the organization to meet its objectives.

Flowchart of Procedure and Discussion of Steps Figure 1.5 illustrates this seven-step process. As the arrows pointing down and to the left indicate, there is certainly room for feedback in the process. For example, at various steps, the analyst might realize that the current model is not capturing some key aspects of the real problem. In this case, the problem definition can be changed, more data can be collected, or a new model can be developed.

The following discussion explores these seven steps in more detail.

Step 1: Problem Definition Typically, a management science model is initiated when an organization believes it has a problem. Perhaps the company is losing money, perhaps its market share is declining,

8 Chapter 1 Introduction to Modeling

Figure 1.5 Flowchart for the Seven-Step Process

Problem definition

Data collection

Model development

Model verification

Possible feedback loops

Optimization and decision

making

Model communication

to management

Model implementation

perhaps its customers are waiting too long for service—any number of problems might be evi dent. The organization (which we refer to as the client) calls in a management scientist (the analyst) to help solve this problem.2 In such cases, the problem has probably already been defined by the client, and the client hires the analyst to solve this particular problem.

As Miser (1993) and Volkema (1995) point out, however, the analyst should do some investigating before accepting the client’s claim that the problem has been properly defined. Failure to do so could mean solving the wrong problem and wasting valuable time, money, and energy.

For example, Miser cites the experience of an analyst who was hired by the military to investigate overly long turnaround times between fighter planes landing and taking off again to rejoin the battle. The military (the client) was convinced that the problem was caused by inefficient ground crews—if they worked faster, turnaround times would cer- tainly decrease. The analyst nearly accepted this statement of the problem and was about to do classical time-and-motion studies on the ground crew to pinpoint the sources of their inefficiency. However, by investigating further, he found that the problem lay elsewhere. Specifically, he learned that the trucks that refueled the planes were frequently late, which in turn was due to the inefficient way they were refilled from storage tanks at another loca- tion. After this latter problem was solved—and its solution was embarrassingly simple— the turnaround times decreased to an acceptable level without any changes on the part of the ground crews. If the analyst had accepted the client’s statement of the problem, the real problem would never have been located or solved.

The moral of this story is clear: If an analyst defines a problem incorrectly or too narrowly, the solution to the real problem might never emerge. In his article, Volkema (1995) advocates spending as much time thinking about the problem and defining it properly as modeling and solving it. This is undoubtedly good advice, especially in real-world applica- tions where problem boundaries are often difficult to define.

Step 2: Data Collection This crucial step in the modeling process is often the most tedious. All organizations keep track of various data on their operations, but the data are often not in the form the analyst requires. In addition, data are often stored in different places throughout the organization (or in external data sets outside the organization) and in different formats. Therefore, one of the analyst’s first jobs is to gather exactly the right data and put the data into an appro- priate and consistent format for use in the model. This typically requires asking questions of key people (such as the cost accountants) throughout the organization, studying exist- ing organizational databases, and performing time-consuming observational studies of the organization’s processes. In short, it typically entails a lot of legwork.

In this book, as in most management science textbooks, we shield you from this data-collection process by supplying the appropriate data to develop and solve a model. Although this makes the overall modeling process seem easier than it really is, it is impractical in most class settings to have you go to companies and gather data. (In many cases, it would not even be allowed for proprietary reasons.) Nevertheless, we provide some insights with “Where Do the Numbers Come From?” sections. If nothing else, these sections remind you that in real applications, someone has to gather the necessary data.

Step 3: Model Development This step, along with step 5, is where the analyst brings his or her quantitative skills into play. After defining the client’s problem and gathering the necessary data, the analyst must develop a model of the problem. Several properties are desirable for a good model. First,

It is important to solve the correct problem, and defining that problem is not always easy.

The data collection step often takes the most time.

Steps 3 and 5, developing and optimizing models, are the steps emphasized most heavily in this book.

1.4 A Seven-Step Modeling Process 9

2Most organizations hire outside consultants, sometimes academics, to help solve problems. However, a number of large organizations employ a staff of management scientists who function as inside consultants.

it should represent the client’s real problem accurately. If it uses a linear (straight-line) function for costs when the real cost function is highly nonlinear (curved), the recommen- dations of the model can be very misleading. Similarly, if the model ignores an important constraint, such as an upper bound on capacity, its recommendations might not be possible to implement. Finally, if a model ignores uncertainty when uncertainty is a key aspect of the problem, its findings won’t be very believable.

However, the model should be as simple as possible. Most good models (where “good” really means useful) capture the essence of the problem without getting bogged down in less important details. They should be approximations of the real world, not mirror images of every last detail. Overly complex models are often of little practical use. First, overly complex models are sometimes too difficult to solve with the solution algorithms avail- able. Second, complex models tend to be incomprehensible to clients. If a client cannot understand a model, there is a good chance that the model’s recommendations will never be implemented. Therefore, a good model should achieve the right balance between being too simple and too complex. This is usually much easier said than done.

Step 4: Model Verification This step is particularly important in real management science applications. A client is much more likely to accept an analyst’s model if the analyst can provide some type of veri fication. This verification can take several forms. For example, the analyst can use the model with the company’s current values of the input data. If the model’s outputs are then in line with the outputs currently observed by the client, the analyst has at least shown that the model can duplicate the current situation.

A second way to verify a model is to enter several sets of input values, even if they are not the company’s current input values, and see whether the outputs from the model are plausible. One common approach is to use extreme values of the inputs to determine whether the outputs behave as they should.

If the model’s outputs for certain inputs are not as expected, there are two possible causes. First, the model could be a poor approximation of the actual situation. In this case, the analyst must refine the model until it lines up more accurately with reality. Second, the model might be fine, but the analyst’s intuition might not be very good. That is, when asked what he or she thinks would happen if certain input values are used, the analyst might provide totally wrong predictions. In this case, the fault lies with the analyst, not the model. Sometimes, good models prove that people’s ability to predict outcomes in complex environments is unfortunately not very good.

Step 5: Optimization and Decision Making After the problem has been defined, the data have been collected, and the model has been developed and verified, it is time to use the model to recommend decisions or strategies. In many management science models, this requires the optimization of an objective, such as maximizing profit or minimizing cost.

The optimization phase is typically the most difficult phase from a mathematical standpoint. Indeed, much of the management science literature (mostly from academics) has focused on complex solution algorithms for various classes of models. Fortunately, this research has led to a number of solution algorithms—and computer packages that implement these algorithms—that can be used to solve real problems. The most famous of these is the simplex algorithm. This algorithm, which has been implemented by many commercial software packages, including Excel’s Solver add-in, is used on a daily basis to solve linear optimization models for many companies. (We take advantage of the simplex method in the optimization chapters, particularly Chapters 3 to 5.)

10 Chapter 1 Introduction to Modeling

Not all solution procedures find the optimal solution to a problem. Many models are either too large or too complex to be solved exactly. Therefore, many complex problems use heuristic methods to locate “good” solutions. A heuristic is a solution method that is guided by common sense, intuition, and trial and error to achieve a good, but probably not optimal, solution. Some heuristics are “quick and dirty,” whereas others are quite sophisti- cated. As models become larger and more complex, good heuristics are sometimes the best that can be achieved—and they are often perfectly adequate.

Step 6: Model Communication to Management The analyst must eventually communicate a model and its recommendations to the client. To appreciate this step, you must understand the large gap that typically exists between management science analysts and the managers of organizations. Managers know their business, but they often don’t understand much about mathematics or mathematical models—even spreadsheet implementations of these models. The burden is therefore on the analyst to present the model in terms that nonmathematical people can understand; otherwise, a perfectly good model might never see the light of day, an unfortunately common occurrence.

The best strategy for successful communication is to involve key people in the organization, including top executives, in the project from the beginning. If these people have been working with the analyst, helping to supply appropriate data and helping the analyst to understand how the organization really works, they are much more likely to accept the eventual model. Step 6, therefore, should really occur throughout the modeling process, not just toward the end.

The analyst should also try to make the model as intuitive and user-friendly as possible. Clients appreciate menu-driven systems with plenty of graphics. They also appreciate the ability to ask what-if questions and get answers quickly in a form that is easy to under- stand. This is one reason for developing spreadsheet models. Although not all models can be developed in spreadsheets due to size and/or complexity, the spreadsheet approach in this book is an excellent choice whenever possible because most business people are com- fortable with spreadsheets. Spreadsheet packages support the use of graphics, customized menus and toolbars, data tables and other tools for what-if analyses, and even macros (that can be made transparent to users) for running complex programs.

Step 7: Model Implementation A real management science application is not complete until it has been implemented. A successful implementation can occur only when step 6 has been accomplished. That is, the analyst must demonstrate the model to the client, and the client must be convinced that the model adds real value and can be used by the people who will be using it. For this reason, the analyst’s job is not really complete until the system is up and running on a daily basis. To achieve a successful implementation, it isn’t enough for management to accept the model; the people who will run it every day must also be thoroughly trained to use it. At the very least, they should understand how to enter appropriate inputs, run what-if analyses, and interpret the model’s outputs correctly. If they conclude that the model is more trouble than it’s worth, they might simply refuse to use it, and the whole exercise will have been a waste of time. An interesting trend (as evidenced in many of the Interfaces articles discussed shortly) is for analysts to build a user-friendly Excel “front end” for their clients, even if the actual number crunching is performed behind the scenes in some non-Excel package. Because many employees understand at least the basics of Excel, a user-friendly front end makes the system much more attractive for daily use.

A heuristic is a relatively simple solution method that often provides “good” but not necessarily optimal solutions.

1.4 A Seven-Step Modeling Process 11

Many successful management science applications take on a life of their own after the initial implementation. After an organization sees the benefits of a useful model— and of management science in general—it is likely to expand the model or create new models for uses beyond those originally intended. Knowing that this is often the case, the best analysts design models that can be expanded. They try to anticipate problems the organization might face besides the current problem. They also stay in contact with the organization after the initial implementation, just in case the organization needs guidance in expanding the scope of the model or in developing new models.

This discussion of the seven-step modeling process has taken an optimistic point of view by assuming that a successful study uses these seven steps, in approximately this chronological order, and that everything goes smoothly. It does not always work out this way. Numerous potential applications are never implemented even though the technical aspects of the models are perfectly correct. The most frequent cause is a failure to com- municate. The analyst builds a complex mathematical model, but the people in the organi- zation don’t understand how it works and are reluctant to use it. Also, company politics can be a model’s downfall, especially if the model recommends a course of action that top management simply does not want to follow—for whatever reasons.

Even for applications that are eventually implemented, an analyst doesn’t always proceed through the seven steps exactly as described in this section. He or she might backtrack considerably throughout the process. For example, based on a tentative definition of the problem, a model is built and demonstrated to management. Management says that the model is impressive, but it doesn’t really solve the company’s problem. Therefore, the analyst returns to step 1, redefines the problem, and builds a new model (or modifies the original model). In this way, the analyst generates several iterations of some or all of the seven steps before the project is considered complete.

The Model as a Beginning, Not an End This book places heavy emphasis on developing spreadsheet models, which is step 3 of the seven-step modeling process. We lead you, step-by-step, through the model develop- ment process for many examples, and we ask you to do this on your own in numerous problems and cases. Given this emphasis, it is easy to think of the completed model as the end of the process—you complete the model and then proceed to the next model. However, a completed model is really a starting point. After you have a working model of the problem, you can—and you should—use it as a tool for gaining insights. For most models, many what-if questions can be asked. If the model has been developed correctly, it should be capable of answering such what-if questions fairly easily. In other words, it should be relatively easy to perform sensitivity analysis on the model. This is, in fact, how management science models are used in the business world. They are typically developed to solve a particular problem, but they are then used as a tool to analyze a number of varia- tions of the basic problem.

For most of the examples in the book, we not only show you how to develop a model to obtain “the answer,” but we often include a section called “Discussion of the Solution” (or a similar title) and a section called “Sensitivity Analysis.” The first of these asks you to step back and look at the solution. Does it make sense? Does it provide any insights, espe- cially surprising ones? The second section indicates how the model can be expanded in one or more natural ways. What happens if there is more or less of some scarce resource? What happens if a new constraint is added? The point is that before moving to the next model, you should spend some time taking a close look at the model you just developed. This is not just for pedagogical purposes; it is exactly the way real management science projects proceed.

12 Chapter 1 Introduction to Modeling

1.5 A GREAT SOURCE FOR MANAGEMENT SCIENCE APPLICATIONS: INTERFACES

Many of the chapter openers in this book are based on successful management science applications that have been reported in the Interfaces journal. This is a highly respected bimonthly journal that chronicles real applications of management science that have gen- erated proven benefits, often in the millions or even hundreds of millions of dollars. The applications are in a wide range of industries, they are global, and they use a variety of management science techniques.

Of special interest are the January–February and (since 1999) the September–October issues. Each January–February issue contains the winner and finalists for that year’s Franz Edelman Award for Achievement in Operations Research and the Management Sciences.3

This is the profession’s most honored prize for the practice of management science. The prize is awarded for “implemented work that has had significant, verifiable, and prefer- ably quantifiable impact.” Similarly, each September–October issue contains the winner and runners-up for that year’s Daniel H. Wagner Prize for Excellence in Operations Research Practice. Each prize is named after a pioneer in the field of operations research and management science, and the winning papers honor them by documenting the practice of management science at its best.

The journal is probably available from your school’s library, either in paper or elec- tronic format. Check with your librarian about gaining access to Interfaces. Its articles will confirm that management science has made a huge difference to both large and small organizations all over the world.

1.6 WHY STUDY MANAGEMENT SCIENCE? We hope that you are convinced by now that management science is an important area and that highly trained analysts are needed to solve the large and complex problems faced by the business world. However, unless you are one of the relatively few students who intends to become a professional management scientist, you are probably wondering why you need to study management science. This is a legitimate concern. For many years, those in the field of management science education received criticism from students and educators that management science courses were irrelevant for the majority of students who were required to take them. Looking back, it is difficult to argue with these critics. Typical management courses were centered primarily around a collection of very specific models and, worse, a collection of mind-numbing mathematical solution techniques— techniques that students were often required to implement by hand! (Some courses are probably still taught this way, but we hope the number is small.)

Two forces have helped to change this tendency toward irrelevance. First, the many vocal critics motivated many of us to examine our course materials and teaching methods. Certain topics have been eliminated and replaced by material that is more relevant and interesting to students. We have certainly attempted to do so in this book. The second force is the emergence of powerful computers and the accompanying easy-to-use soft- ware, especially spreadsheet software. With the availability of computers to do the number crunching, there is no need—except possibly in advanced courses—to delve into the math- ematical details of the solution techniques. This task can be delegated to machines that are far better at it than humans. The time formerly spent on such details can now be used to develop valuable modeling skills.

1.6 Why Study Management Science? 13

3Operations research is another term for management science. Over the years, the two terms have become practically synonymous, although some people in the field still prefer to be called management scientists, whereas others prefer to be called operations researchers.

The intent in this book is not just to cover specific models and specific approaches to these models but also to teach a more general approach to the model-building process. We believe that the spreadsheet approach is the best way to do this because it appeals to the largest audience. We have been teaching our own courses with this spreadsheet-modeling approach for over two decades—to a wide range of business students—and have received very few complaints about irrelevance. In fact, many students have stated that this is the most valuable business course they have taken. The following are some of the reasons for this new-found relevance and popularity:

■ The modeling approach emphasized throughout this book is an important way to think about problems in general, not just the specific problems we discuss. This approach forces you to think logically. You must discover how given data can be used (or which data are necessary), you must determine the elements of the problem that you can control (the decision variables), and you must determine how the elements of the problem are logically related. These logical thinking skills are valuable for your career, regardless of the specific field you enter.

■ Management science is admittedly built around quantitative skills—it deals primar- ily with numbers and relationships between numbers. Some critics object that not everything in the real world can be reduced to numbers, but as one of our reviewers correctly points out, “a great deal that is of importance can.” As you work through the many models in this book, your quantitative skills will improve immensely. In a business world driven increasingly by numbers, quantitative skills are an important asset.

■ No matter what your spreadsheet abilities are when you enter this course, by the time you are finished, you will be a proficient spreadsheet user. We deliberately chose the spreadsheet package Excel, which is arguably the most widely used pack- age (other than word-processing packages) in the business world today. Many of our students state that the facility they gain in Excel is the most valuable part of the course. That doesn’t mean this is a course in spreadsheet fundamentals and neat tricks, although you will undoubtedly pick up many useful Excel skills along the way. A great spreadsheet package—and we strongly believe that Excel is the greatest spreadsheet package written to date—gives you complete control over your model. You can apply spreadsheets to an endless variety of problems. Excel gives you the flexibility to work in a way that suits your style best, and it enables you to present results (and often catch errors) almost immediately. As you succeed with relatively easy problems, your confidence will build, and before long, you will be able to tackle more difficult problems successfully. In short, spreadsheets enable everyone, not just people with technical skills, to develop and use their quantitative skills.

■ Management science modeling helps you develop your intuition, and it also indicates where intuition alone sometimes fails. When you confront a problem, you often make an educated (or maybe not so educated) guess at the solution. If the problem is suf- ficiently complex, as many of the problems in this book are, this guess will frequently be wide of the mark. In this sense, the study of management science can be a humbling experience—you find that your unaided intuition is often not very good. However, by studying many models and examining their solutions, you can sharpen your intuition considerably. This is sometimes called the “Aha!” effect. All of a sudden, you see why a certain solution is preferred. When you originally thought about the problem, you might have forgotten to consider an important constraint or a key relationship, which caused your poor initial guess. Presumably, the more problems you analyze, the better you will become at recognizing the critical elements of new problems. Experienced management scientists tend to have excellent intuition, the ability to see through to the essence of a problem almost immediately. However, they are not born with this talent; it comes through the kind of analysis you will be performing as you work through this book.

14 Chapter 1 Introduction to Modeling

1.7 SOFTWARE INCLUDED WITH THIS BOOK Very few business problems are small enough to be solved with pencil and paper. They require powerful software. The software included in this book, together with Microsoft®

Excel, provides you with a powerful software combination that you will use for this course and beyond. This software is being used—and will continue to be used—by leading com- panies all over the world to solve large, complex problems. The experience you obtain with this software, through working the examples and problems in this book, will give you a key competitive advantage in the marketplace.

It all begins with Excel. All the quantitative methods that we discuss are implemented in Excel. Specifically, in this edition we use Excel 2016.4 Although it is impossible to fore- cast the state of computer software into the long-term or even medium-term future, as we are writing this book, Excel is the most heavily used spreadsheet package on the market, and there is every reason to believe that this state will persist for quite some time. Most companies use Excel, most employees and most students have been trained in Excel, and Excel is a very powerful, flexible, and easy-to-use package.

Although Excel has a huge set of tools for performing quantitative analysis, several add-ins that accompany this book make Excel even more powerful. (Access to add-ins are available on the Essential Resource Web site at www.cengagebrain.com.) We discuss these briefly here and in much more depth in the specific chapters where they are used.

Together with Excel and the add-ins included in this book, you have a wealth of software at your disposal. The examples and step-by-step instructions throughout the book will help you to become a power user of this software. This takes plenty of practice and a willingness to experiment, but it is certainly within your grasp. When you are finished, don’t be surprised if you rate improved software skills as one of the most valuable things you have learned from the book.

Excel Tutorial Virtually everyone in the business world knows the basic features of Excel, but relatively few know some of its more powerful features. In short, relatively few people are the “power users” we expect you to become by working through this book. To get you started, the file Excel Tutorial for Windows.xlsx explains many features of Excel—features that we expect you to be able to use. (You can access this file at www.cengagebrain.com or at the author’s website www.kelley.iu.edu/albrightbooks/Free_downloads.htm. This latter website also has a link to the file Excel Tutorial for the Mac.xlsx for Mac users.) These features include the SUMPRODUCT, VLOOKUP, IF, NPV, and COUNTIF functions, as well as many other Excel functions. They also include range names, data tables, the Paste Special option, the Goal Seek tool, and many others. Finally, although we assume you can perform routine spreadsheet tasks such as copying and pasting, the tutorial includes many tips to help you perform these tasks more efficiently.

Solver Add-in In Chapters 3 through 8 and occasionally in other chapters, we make heavy use of Excel’s Solver add-in. This add-in, developed by Frontline Systems (not Microsoft), uses power- ful algorithms—all behind the scenes—to perform spreadsheet optimization. Before this

1.7 Software Included with This Book 15

4Excel 2007 was a big change from Excel 2003 and earlier versions. The changes in Excel 2010 and later are much more minor, at least for our purposes. So if you have been using Excel 2007, 2010, or 2013, you will see very few changes here. On the other hand, if you are one of the many Mac users, be aware that there are draw- backs to using the Mac version of Excel. Specifically, most of the add-ins discussed next don’t work with the Mac version of Excel. If possible, you should install a Windows emulation program such as Bootcamp or Parallels and then run a Windows version of Excel. Many of our students have done this and have had no problems.

type of spreadsheet optimization add-in was available, specialized (nonspreadsheet) soft- ware was required to solve optimization problems. Now you can do it all within a familiar spreadsheet environment.

SolverTable Add-in An important theme throughout this book is sensitivity analysis: How do outputs change when inputs change? Typically these changes are made in spreadsheets with a data table, a built-in Excel tool. However, data tables don’t work in optimization models, where the goal is to see how the optimal solution changes when certain inputs change. Therefore, we include an Excel add-in called SolverTable, which works almost exactly like Excel’s data tables. (This add-in was developed by Albright, and versions of it for different versions of Excel are available at the website www.kelley.iu.edu/albrightbooks/Free_downloads.htm.) In Chapters 3 through 8, we illustrate the use of SolverTable.

Palisade DecisionTools Suite In addition to SolverTable and built-in Excel add-ins, you also have access to an educa- tional version of Palisade Corporation’s powerful DecisionTools suite. (You can download this suite from www.cengagebrain.com.) All of the programs in this suite are Excel add-ins, so the learning curve isn’t very steep. There are seven separate add-ins in this suite: @RISK, PrecisionTree, StatTools, BigPicture, TopRank, NeuralTools, and Evolver. We will use the first four most heavily in this book, but all are useful for certain tasks and are described briefly below.

@RISK The simulation add-in @RISK enables you to run as many iterations of a spreadsheet simula- tion as you like. As the simulation runs, @RISK automatically keeps track of the outputs you select, and it then displays the results in a number of tabular and graphical forms. @RISK also enables you to perform a sensitivity analysis, so that you can see which inputs have the most effect on the outputs. @RISK provides a number of spreadsheet functions that enable you to generate random numbers from a variety of probability distributions. Finally, @RISK contains a tool called RISKOptimizer for optimizing in an uncertain environment.

PrecisionTree The PrecisionTree add-in is used in Chapter 9 to analyze decision problems with uncer- tainty. The primary method for performing this type of analysis is to draw decision trees. Decision trees are inherently graphical, so they are difficult to implement in spreadsheets, which are based on rows and columns. However, PrecisionTree does this in a very clever and intuitive way. Equally important, once a decision tree has been built, it is easy to use PrecisionTree to perform a sensitivity analysis on the model’s inputs.

StatTools Palisade has also developed a statistics add-in called StatTools, which enhances the statistical capabilities of Excel. Excel’s built-in statistical tools are rather limited. It has several func- tions, such as AVERAGE and STDEV for summarizing data, and it includes the Analysis ToolPak, an add-in that was developed by a third party many years ago (and has never been updated). However, these tools are not sufficiently powerful or flexible for the heavy-duty statistical analysis that is sometimes required. StatTools provides a collection of tools that help fill this gap. Admittedly, this is not a statistics book, but StatTools will come in particu- larly handy in Chapter 13 for regression analysis and forecasting.

16 Chapter 1 Introduction to Modeling

BigPicture BigPicture is the newest addition to the Palisade suite. It is basically drawing software that complements Excel’s built-in drawing tools. The purpose of BigPicture is to represent the elements and relationships in a model so that you can better visualize the problem. In fact, we used BigPicture to create the diagram in Figure 1.2, and we use it in many later examples as a “bridge” from the verbal description of a problem to the eventual spread- sheet model of the problem.

TopRank TopRank is a “what-if” add-in used for sensitivity analysis. It starts with any spreadsheet model, where a set of inputs, along with a number of spreadsheet formulas, leads to one or more outputs. TopRank then performs a sensitivity analysis to see which inputs have the largest effect on a given output. For example, it might indicate which input affects after-tax profit the most: the tax rate, the risk-free rate for investing, the inflation rate, or the price charged by a competitor. Unlike @RISK, TopRank models do not incorporate uncertainty explicitly. However, they consider uncertainty implicitly by performing sensitivity analysis on the important model inputs.

NeuralTools In Chapter 13, we show how regression can be used to find a linear equation that quanti- fies the relationship between a dependent variable and one or more explanatory variables. Although linear regression is a powerful tool, it is not capable of quantifying all possible relationships. The NeuralTools add-in mimics the working of the human brain to find “neural networks” that quantify complex nonlinear relationships. We discuss how it can be used in Chapter 14 on data mining.

Evolver In Chapter 8, we show how Solver Evolutionary algorithm (introduced in Excel 2010) can be used to solve some “non-smooth” nonlinear models that Solver’s other algorithms cannot handle. Evolutionary Solver uses genetic algorithms to solve these difficult problems. Although we will not use it in this book, Palisade’s Evolver add-in is an alternative implementation of genetic algorithms.

1.8 CONCLUSION In this chapter, we have introduced the field of management science and the process of mathematical modeling. To provide a more concrete understanding of these concepts, we reviewed a capital budgeting model. We also explored a seven-step model-building process that begins with problem definition and proceeds through final implementation. Finally, we discussed why the study of management science is a valuable experience, even if you do not intend to pursue a professional career in this field.

Don’t worry if you don’t understand some of the terms, such as linear programming, that were introduced in this chapter. Although the seven-step process is not too difficult to comprehend, especially when discussed in the context of real applications, it typically entails some rather complex logical relationships and mathematical concepts. These ideas are presented in much greater detail in the rest of this book. Specifically, you will learn how to build spreadsheet models in Excel, how to use them to answer what-if questions, how to find optimal solutions with the help of Excel’s Solver add-in, and how to develop

1.8 Conclusion 17

and run simulation models that incorporate uncertainty. For practical reasons, most of your work will take place in the classroom or in front of your own computer as you work through the examples and problems. The primary emphasis of this book, therefore, is on steps 3 through 6, that is, developing the model, testing the model with different inputs, optimizing the model, and presenting (and interpreting) the results to a client—probably your instructor.

Keep in mind, however, that with real problems, you must take crucial steps before and after the procedures you will be practicing in this book. Because real problems don’t come as nicely packaged as those we discuss and because the necessary data are seldom given to you on a platter, you will have to wrestle with the problem’s scope and precise data requirements when you solve problems in a real setting. (We have included “modeling problems” at the ends of most chapters. These problems are not as well structured as the other problems, so the burden is on you to determine an appropriate structure and decide the necessary data.) Also, because a mathematically accurate model doesn’t necessarily result in a successful implementation, your work is not finished just because the numbers check out. To gain acceptance for a model, an analyst must have the right combination of technical skills and people skills. Try to keep this in mind as you write up your solutions to the problems in this book. Don’t just hand in a mass of numbers with little or no explana- tion. Sell your solution!

18 Chapter 1 Introduction to Modeling

Introduction to Spreadsheet Modeling

C H A P T E R

REAL APPLICATIONS OF SPREADSHEET MODELS

This chapter introduces spreadsheet modeling in Excel, and the rest of the book shows how a wide variety of business problems can be mod- eled in Excel. You might wonder at this point whether many companies actually use spreadsheets to model their large, complex problems. We can- not claim that they all do—some problems are simply too large and complex to solve inside a spreadsheet, and they require more specialized heavy-duty software—but we can provide evidence that spreadsheet models are used in the business world. Much of this evidence is anecdotal. We have talked to companies and many of our own former students, and they have told us that they use Excel modeling on an everyday basis.

Further evidence can be found in a special July–August 2008 issue of the Interfaces journal, which was discussed in the previous chapter. This issue is devoted entirely to the use of spreadsheet software (Excel) in real applications of management science. As the editors of the special edition, Larry LeBlanc and Tom Grossman, state in their introduction, the Excel spreadsheet package as a software development platform has several advan- tages over other possible software development platforms: (1) it allows fast deployment and high developer productivity; (2) powerful add-ins to Excel are available for optimization, simulation, decision analysis, and other techniques (including higher-powered versions of Solver than the version built into Excel); (3) Excel’s powerful and relatively easy to learn VBA macro language can be used to build powerful applications that can be run with the

to ck

ph ot

o. co

m /L

is e

G ag

20 Chapter 2 Introduction to Spreadsheet Modeling

click of a button; (4) virtually all business users have access to Excel on their desktops or laptops; and (5) most business managers know Excel and trust models built with Excel. The only disadvantage they cite—and it is an important one—is that Excel models do not scale well. For example, it is not a trivial task to transform a spreadsheet product mix model with 10 possible products and 5 scarce resources into a similar model with 100 products and 20 scarce resources, whereas this is easy in many special-purpose optimization software packages. Still, the potential advantages of spreadsheet modeling outweigh the negatives for many companies.

A list of the titles of the articles in this special edition (with authors in parentheses) indicates the breadth of real spreadsheet applications:

■ Analyzing Supply Chains at HP Using Spreadsheet Models (Amaral and Kuettner) ■ Spreadsheet Models for Inventory Target Setting at Procter & Gamble (Farasyn,

Perkoz, and Van de Velde) ■ Lockheed Martin Space Systems Company Optimizes Infrastructure Project-

Portfolio Selection (Gurgur and Morley) ■ Disseminating Emergency Preparedness Planning Models as Automatically

Generated Custom Spreadsheets (Herrmann) ■ A Spreadsheet Implementation of an Ammunition Requirements Planning Model for

the Canadian Army (Hurley and Balez) ■ Hypo International Strengthens Risk Management with a Large-Scale, Secure

Spreadsheet-Management Framework (Jafry, Marrison, and Umkehrer-Neudeck) ■ Optimal Trading of ETFs: Spreadsheet Prototypes and Applications to Client-Server

Applications (Kumiega and Van Vliet) ■ Spreadsheet Decision-Support Tools: Lessons Learned at Hewlett-Packard (Olavson

and Fry) ■ Spreadsheet Model Helps to Assign Medical Residents at the University of Vermont’s

College of Medicine (Ovchinnikov and Milner) ■ Integrating Excel, Access, and Visual Basic to Deploy Performance Measurement and

Evaluation at the American Red Cross (Pasupathy and Medina-Borja)

We urge you to look at this special issue of Interfaces and read at least one of the articles. Admittedly, some of these applications are more ambitious, or at least larger, than the spreadsheet models presented in this book. However, the general spreadsheet modeling approach is the same, regardless of the size or complexity of the problem. Starting in this chapter, you will see how it is done. ■

2.1 INTRODUCTION This book is all about spreadsheet modeling. By the time you are finished, you will have seen some reasonably complex—and realistic—models. Many of you will also be trans- formed into Excel “power” users. However, we don’t want to start too quickly or assume too much background on your part. For practice in getting up to speed with basic Excel features, we have included an Excel tutorial on this textbook’s essential resource Web site. (See the Excel Tutorial for Windows.xlsx or Excel Tutorial for the Mac.xlsx file.) You can work through this tutorial at your own speed and cover the topics you need help with. Even if you have used Excel extensively, give this tutorial a look. You might be surprised how some of the tips can improve your productivity.

In addition, this chapter provides an introduction to Excel modeling and illustrates some interesting and relatively simple models. The chapter also covers the modeling pro- cess and includes some of the less well-known, but particularly helpful, Excel tools that

You can download either Excel tutorial file at www.cengagebrain.com or at www.kelley.iu .edu/albrightbooks /Free_downloads.htm.

2.2 Basic Spreadsheet Modeling: Concepts and Best Practices 21

are available. These tools include data tables, Goal Seek, lookup tables, and auditing com- mands. Keep in mind, however, that our objective is not the same as that of the many “how-to” Excel books on the market. We are not teaching Excel just for its many interest- ing features. Rather, we plan to use these features to provide insights into real business problems. In this book, Excel is a problem-solving tool, not an end in itself.

2.2 BASIC SPREADSHEET MODELING: CONCEPTS AND BEST PRACTICES

Most mathematical models, including spreadsheet models, involve inputs, decision vari- ables, and outputs. The inputs have given fixed values, at least for the purposes of the model. The decision variables are those a decision maker controls. The outputs are the ultimate values of interest; they are determined by the inputs and the decision variables. For example, suppose a manager must place an order for a certain seasonal product. This product will go out of date fairly soon, so this is the only order that will be made for the product. The inputs are: the fixed cost of the order; the unit variable cost of each item ordered; the price charged for each item sold; the salvage value for each item, if any, left in inventory after the product has gone out of date; and the demand for the product. The deci- sion variable is the number of items to order. Finally, the key output is the profit (or loss) from the product. This output can also be broken down into the outputs that contribute to it: the total ordering cost; the revenue from sales; and the salvage value from leftover items. These outputs must be calculated to obtain profit.

Spreadsheet modeling is the process of entering the inputs and decision variables into a spreadsheet and then relating them appropriately, by means of formulas, to obtain the outputs. After you have done this, you can then proceed in several directions. You might want to perform a sensitivity analysis to see how one or more outputs change as selected inputs or decision variables change. You might want to find the values of the decision variable(s) that minimize or maximize a particular output, possibly subject to certain con- straints. You might also want to create charts that show graphically how certain parameters of the model are related.

Several examples in this chapter illustrate these operations. Getting all the spreadsheet logic correct and producing useful results is a big part of the battle; however, we go farther by stressing good spreadsheet modeling practices. You probably won’t be developing spread- sheet models for your sole use; instead, you will be sharing them with colleagues or even a boss (or an instructor). The point is that other people will be reading and trying to make sense of your spreadsheet models. Therefore, you should construct your spreadsheet models with readability in mind. Features that can improve readability include the following:

■ A clear, logical layout to the overall model

■ Separation of different parts of a model, possibly across multiple worksheets

■ Clear headings for different sections of the model and for all inputs, decision vari- ables, and outputs

■ Use of range names

■ Use of boldface, italics, larger font size, coloring, indentation, and other formatting features

■ Use of cell comments

■ Use of text boxes for assumptions and explanations

Obviously, the formulas and logic in any spreadsheet model must be correct; however, correctness will not take you very far if no one can understand what you have done. Much of the power of spreadsheets derives from their flexibility. A blank spreadsheet is like a

Some inputs, such as demand in this example, contain a considerable degree of uncertainty. In some cases, as in Example 2.4 later in this chapter, this uncertainty is modeled explicitly.

22 Chapter 2 Introduction to Spreadsheet Modeling

big blank canvas waiting for you to insert useful data and formulas. Almost anything is allowed. However, you can abuse this flexibility if you don’t have an overall plan for what should go where. Plan ahead before diving in, and if your plan doesn’t look good after you start filling in the spreadsheet, revise your plan.

The following example illustrates the process of building a spreadsheet model accord- ing to these guidelines. We build this model in stages. In the first stage, we build a model that is correct, but not very readable. At each subsequent stage, we modify the model to make it more readable. You do not need to go through each of these stages explicitly when you build your own models. You can often strive for the final stage right away, at least after you get accustomed to the modeling process. The various stages are shown here simply for contrast.

EXAMPLE 2.1 ORDERING NCAA T-SHIRTS

It is March, and the annual NCAA Basketball Tournament is down to the final four teams. Randy Kitchell is a T-shirt vendor who plans to order T-shirts with the names of the final four teams from a manufacturer and then sell them to the fans. The fixed cost of any order is $750, the variable cost per T-shirt to Randy is $8, and Randy’s selling price is $18. However, this price will be charged only until a week after the tournament. After that time, Randy figures that interest in the T-shirts will be low, so he plans to sell all remaining T-shirts, if any, at $6 each. His best guess is that demand for the T-shirts during the full- price period will be 1500. He is thinking about ordering 1450 T-shirts, but he wants to build a spreadsheet model that will let him experiment with the uncertain demand and his order quantity. How should he proceed?

Objective To build a spreadsheet model in a series of stages, with all stages being correct but each stage being more readable and flexible than the previous stages.

Solution The logic behind the model is fairly simple, but the model is built for generality. Specifically, the formulas allow for the order quantity to be less than, equal to, or greater than demand. If demand is greater than the order quantity, Randy will sell all the T-shirts ordered for $18 each. However, if demand is less than the order quantity, Randy will sell as many T-shirts as are demanded at the $18 price and all leftovers at the $6 price. You can implement this logic in Excel with an IF function.

A first attempt at a spreadsheet model appears in Figure 2.1. (See the file TShirt Sales Finished.xlsx, where each stage appears on a separate worksheet.1) You enter a possible demand in cell B3, a possible order quantity in cell B4, and then calculate the profit in cell B5 with the formula

5275028*B41IF(B3>B4,18*B4,18*B316*(B42B3))

This formula subtracts the fixed and variable costs and then adds the revenue accord- ing to the logic just described.

1 2 3 4 5

A B NCAA T-shirt sales

Demand Order Profit

1500 1450

13750

Figure 2.1 Base Model

1There are two versions (in separate folders) of all example files: an “example template” version and a “finished example” version. The latter shows the completed spreadsheet, along with helpful comments; the former shows just the labels and inputs, so that you can complete it according to the instructions in the book.

Excel Function: IF Excel’s IF function is probably already familiar to you, but it is too important not to discuss. It has the syntax 5IF(condition,result_if_True,result_if_False). The condition is any expression that is either true or false. The two expressions result_if_True and result_if_False can be any expressions you would enter in a cell: numbers, text, or other Excel functions (including other IF functions). Note that if either expression is text, it must be enclosed in double quotes, such as

5IF(Score>590,"A","B")

Finally, condition can be complex combinations of conditions, using the keywords AND or OR. Then the syntax is, for example,

5IF(AND(Score1<60,Score2<60),"Fail","Pass")

This model in Figure 2.1 is entirely correct, but it isn’t very readable or flexible because it breaks a rule that you should strive never to break: It hard codes input values into the profit formula. A spreadsheet model should never include input numbers in formulas. Instead, the spreadsheet model should store input values in separate cells and then include cell references to these inputs in its formulas. A remedy appears in Figure 2.2. Here, the inputs have been entered in the range B3:B6, and the profit formula in cell B10 has been changed to

52B32B4*B91IF(B8>B9,B5*B9,B5*B81B6*(B92B8))

Never hard code numbers into Excel formulas. Use cell references instead.

1 2 3 4 5 6 7 8 9

A B NCAA T-shirt sales

Fixed order cost Variable cost Selling price Discount price

Demand Order Profit

$750 $8

$18 $6

1500 1450

$13,750

Figure 2.2 Model with Input Cells

This is exactly the same formula as before, but it is now more flexible. If an input changes, the profit recalculates automatically. Most important, the inputs are no longer buried in the formula. Still, the profit formula is not very readable as it stands. You can make it more read- able by using range names. The mechanics of range names are covered in detail later in this chapter. For now, the results of using range names for cells B3 through B6, B8, and B9 are shown in Figure 2.3. This model looks exactly like the previous model, but the formula in cell B10 is now

52Fixed_order_cost−Variable_cost*Order1IF(Demand>Order, Selling_price*Order,Selling_price*Demand1Discount_price*(Order2Demand))

This formula is admittedly more long-winded, but it is certainly easier to read.

1 2 3 4 5 6 7 8 9

A B C D E F NCAA T-shirt sales

Fixed order cost Range names used Variable cost Selling price Discount price

Order Demand Order Profit

$750 $8

$18 $6

1500 1450

$13,750

Demand Discount_price Fixed_order_cost

Selling_price Variable_cost

='Model 3'!$B$8 ='Model 3'!$B$6 ='Model 3'!$B$3 ='Model 3'!$B$9 ='Model 3'!$B$5 ='Model 3'!$B$4

Figure 2.3 Model with Range Names in Profit Formula

2.2 Basic Spreadsheet Modeling: Concepts and Best Practices 23

24 Chapter 2 Introduction to Spreadsheet Modeling

2This color convention shows up clearly in the Excel files that accompany the book. However, in this two-color book (shades of gray and blue), it is difficult to see the color-coding scheme. We recommend that you look not only at the figures in the book but also at the actual Excel files.

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17

A B C D E F NCAA T-shirt sales

Fixed order cost $750 Range names used Variable cost $8 Demand ='Model 4'!$B$8 Selling price $18 Discount_price ='Model 4'!$B$6 Discount price $6 Fixed_order_cost ='Model 4'!$B$3

Order ='Model 4'!$B$9 ledoM'=ecirp_gnilleS0051dnameD 4'!$B$5 ledoM'=tsoc_elbairaV0541redrO 4'!$B$4

Costs Fixed cost $750 Variable costs $11,600

Revenues Full-price shirts $26,100 Discount-price shirts $0

$13,750tiforP

Figure 2.4 Model with Intermediate Outputs

Randy might like to have profit broken down into various costs and revenues as shown in Figure 2.4, rather than one single profit cell. The formulas in cells B12, B13, B15, and B16 are straightforward, so they are not repeated here. You can then accumulate these to get profit in cell B17 with the formula

52(B121B13)1(B151B16)

Of course, range names could be used for these intermediate output cells, but this is prob- ably more work than it’s worth. You should always use some judgment when deciding how many range names to use.

If Randy’s assistant is presented with this model, how does she know at a glance which cells contain inputs or decision variables or outputs? Labels and/or color coding can help to distinguish these types. A blue/red/gray color-coding style has been applied in Figure 2.5,

along with descriptive labels in boldface. The blue cells at the top are input cells, the red cell in the mid- dle is a decision variable, and the gray cell at the bottom is the key output.2 There is nothing sacred about this particular convention. Feel free to adopt your own convention and style, but be sure to use it consistently.

The model in Figure 2.5 is still not the last word on this example. As shown in later examples, you can create data tables to see how sensitive profit is to the inputs, the demand, and the order quantity. You can also create charts to show any numerical results graphically. But this is enough for now. You can see that the model in Figure 2.5 is now much more readable and flexible than the original model in Figure 2.1. ■

Because good spreadsheet style is so important, the appendix to this chapter discusses a few tools for editing and documenting your spreadsheet mod- els. Use these tools right away and as you progress through the book.

Fundamental Insight

Spreadsheet Layout and Documentation

If you want your spreadsheets to be used (and you want your value in your company to rise), think carefully about your spreadsheet layout and then document your work. For layout, think about whether certain data are best oriented in rows or columns, whether your work is better placed in a single sheet or in multiple sheets, and so on. For documentation, use descriptive labels and headings, color coding, cell comments, and text boxes to make your spreadsheets more readable. It takes time and careful planning to design and then document your spreadsheet models, but the time is well spent. And if you come back in a few days to a spreadsheet model you developed and you can’t make sense of it, don’t be afraid to redesign your work completely— from the ground up.

2.3 Cost Projections 25

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22

A B C D E F NCAA T-shirt sales

Input egnaRselbairav names used Fixed order cost $750 Demand ='Model 5'!$B$10 Variable cost $8 Discount_price ='Model 5'!$B$7 Selling price $18 Fixed_order_cost ='Model 5'!$B$4 Discount price $6 Order ='Model 5'!$B$13

Selling_price ='Model 5'!$B$6 Uncertain variable Variable_cost ='Model 5'!$B$5

0051dnameD

Decision variable 0541redrO

Output variables Costs

Fixed cost $750 Variable costs $11,600

Revenues Full-price shirts $26,100 Discount-price shirts $0

$13,750tiforP

Figure 2.5 Model with Category Labels and Color Coding

In the rest of this chapter, we discuss a number of interesting examples and introduce important modeling concepts (such as sensitivity analysis), important Excel features (such as data tables), and even some important business concepts (such as net present value). To get the most from these examples, follow along at your own computer, starting with a blank spreadsheet or the appropriate “example template” spreadsheet that accompanies the book. It is one thing to read about spreadsheet modeling; it is quite another to actually do it!

2.3 COST PROJECTIONS In this next example, a company wants to project its costs of producing products, given that material and labor costs are likely to increase through time. We build a simple model and then use Excel’s charting capabilities to obtain a graphical image of projected costs.

EXAMPLE 2.2 PROJECTING THE COSTS OF BOOKSHELVES AT WOODWORKS

The Woodworks Company produces a variety of custom-designed wood furniture for its customers. One favorite item is a bookshelf, made from either cherry or oak. The company knows that wood prices and labor costs are likely to increase in the future. Table 2.1 shows the number of board-feet and labor hours required for a bookshelf, the current costs per board-foot and labor hour, and the anticipated annual increases in these costs. (The top row indicates that either type of bookshelf requires 30 board-feet of wood and 16 hours of labor.) Build a spreadsheet model that enables the company to experiment with the growth rates in wood and labor costs so that a manager can see, both numerically and graphically, how the costs of the bookshelves vary in the next few years.

Homework is Completed By:

Writer	Writer Name	Amount	Client Comments & Rating
ONLINE	Instant Homework Helper 4.8 4305 Orders Completed	$36	She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up! 5.00
Answer.docx Turnitin Report.pdf Contact Writer For Solution Contact Writer For Solution

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 3 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 6 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 12 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

6 writers have sent their proposals to do this homework:

Writer	Writer Name	Offer	Chat
ONLINE	Online Assignment Help I have written research reports, assignments, thesis, research proposals, and dissertations for different level students and on different subjects. 4.8 8547 Orders Completed	$44	Chat With Writer
ONLINE	Quick Mentor As an experienced writer, I have extensive experience in business writing, report writing, business profile writing, writing business reports and business plans for my clients. 4.1 210 Orders Completed	$15	Chat With Writer
ONLINE	Top Academic Guru I reckon that I can perfectly carry this project for you! I am a research writer and have been writing academic papers, business reports, plans, literature review, reports and others for the past 1 decade. 4.8 9639 Orders Completed	$36	Chat With Writer
ONLINE	Math Specialist I am an academic and research writer with having an MBA degree in business and finance. I have written many business reports on several topics and am well aware of all academic referencing styles. 4.9 1407 Orders Completed	$41	Chat With Writer
ONLINE	George M. Being a Ph.D. in the Business field, I have been doing academic writing for the past 7 years and have a good command over writing research papers, essay, dissertations and all kinds of academic writing and proofreading. 4.8 1344 Orders Completed	$15	Chat With Writer
ONLINE	Academic Master After reading your project details, I feel myself as the best option for you to fulfill this project with 100 percent perfection. 4.8 2877 Orders Completed	$19	Chat With Writer