Spreadsheet Modeling & Decision Analysis

1 Licensed to: Spreadsheet Modeling & DecisionAnalysis, Sixth Edition Cliff T. RagsdaleVice President of Editorial, Business:Jack W. CalhounPublisher: Joe SabatinoSr. Acquisitions Editor: Charles McCormick, Editor: Maggie KubaleEditorial Assistant: Nora HeinkMarketing Director: Adam MarshSr. Content Project Manager: Holly HenjumMedia Editor: Chris ValentinePrint Buyer: Miranda KlapperSr. Marketing Communications Manager:Libby ShippProduction Service: MPS Limited, a Macmillan CompanySr. Art Director: Stacy Jenkins ShirleyInternal Designer: cMiller DesignCover Designer: Stuart Kunkler, triARTisCommunicationsCover Image: Detail showing profile view ofthe Farnese Hercules by Glykon. Imageby Araldo de Luca/CORBIS; o o Alexander HafemannyRights Acquisitions Specialist: MardellGlinski Schultz 2012, 2011 South-Western, Cengage LearningALL RIGHTS RESERVED.

2 No part of this work covered by the copyright herein maybe reproduced, transmitted, stored, or used in any form or by any means graphic,electronic, or mechanical, including but not limited to photocopying, recording,scanning, digitizing, taping, web distribution, information networks, or informationstorage and retrieval systems, except as permitted under Section 107 or 108 of the1976 United States Copyright Act, without the prior written permission of is a registered trademark of eInstruction Corp. Windows is aregistered trademark of the Microsoft Corporation used herein under and Power Macintosh are registered trademarks of AppleComputer, Inc. used herein under license. 2008 Cengage Learning. All Rights of Congress Control Number: 2010935205 Student Edition ISBN-13: 978-0-538-74632-8 Student Edition ISBN-10: 0-538-74632-7 South-Western Cengage Learning5191 Natorp BoulevardMason, OH 45040 USAC engage Learning products are represented in Canada by Nelson Education, your course and learning solutions, visit any of our products at your local college store or at ourpreferred online store product information and technology assistance, contact us atCengage Learning Customer & Sales Support, 1-800-354-9706 For permission to use material from this text or product,submit all requests online at permissions questions can be emailed in the United States of America1 2 3 4 5 6 7 14 13 12 11 10/4/10 3.

3 46 PM Page iiCopyright 2010 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 IntroductionThis book is titled Spreadsheet Modeling and Decision Analysis : A Practical Introduction toManagement Science, so let s begin by discussing exactly what this title means. By thevery nature of life, all of us must continually make decisions that we hope will solveproblems and lead to increased opportunities for ourselves or the organizations forwhich we work.

4 But making good decisions is rarely an easy task. The problems facedby Decision makers in today s competitive, fast-paced business environment are oftenextremely complex and can be addressed by numerous possible courses of action. Eval-uating these alternatives and choosing the best course of action represents the essence ofdecision the past decade, millions of business people discovered that one of the mosteffective ways to analyze and evaluate Decision alternatives involves using electronicspreadsheets to build computer models of the Decision problems they face. Acomputermodelis a set of mathematical relationships and logical assumptions implemented in acomputer as a representation of some real-world object, Decision problem or phenome-non.

5 Today, electronic spreadsheets provide the most convenient and useful way forbusiness people to implement and analyze computer models. Indeed, most businesspeople would probably rate the electronic Spreadsheet as their most important analyti-cal tool apart from their brain! Using a Spreadsheet model (a computer model imple-mented via a Spreadsheet ), a businessperson can analyze Decision alternatives beforehaving to choose a specific plan for book introduces you to a variety of techniques from the field of management sci-ence that can be applied in Spreadsheet models to assist in the Decision - Analysis our purposes, we will define management scienceas a field of study that uses com-puters, statistics, and mathematics to solve business problems.

6 It involves applying themethods and tools of science to management and Decision making. It is the science ofmaking better decisions. management science is also sometimes referred to as opera-tions research or Decision science. See Figure for a summary of how management sci-ence has been applied successfully in a number of real-world the not-too-distant past, management science was a highly specialized field thatgenerally could be practiced only by those who had access to mainframe computers andwho possessed an advanced knowledge of mathematics and computer programminglanguages. However, the proliferation of powerful PCs and the development of easy-to-use electronic spreadsheets have made the tools of management science far morepractical and available to a much larger audience.

7 Virtually everyone who uses a1 Chapter 1 Introduction to Modelingand Decision 10/1/10 4:45 PM Page 1 Copyright 2010 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 to:2 Chapter 1 Introduction to Modeling and Decision AnalysisOver the past decade, scores of operations research and management science pro-jects saved companies millions of dollars.

8 Each year, the Institute for OperationsResearch and the management Sciences (INFORMS) sponsors the Franz EdelmanAwards competition to recognize some of the most outstanding OR/MS projectsduring the past year. Here are some of the home runs from the 2008 EdelmanAwards (described in Interfaces, Vol. 39, No. 1, January-February, 2009). Xeroxhas invented, tested, and implemented an innovative group of productivity-improvement solutions, trademarked LDP Lean DocumentProduction solutions, for the $100 billion printing industry in the UnitedStates. These solutions have provided dramatic productivity and cost im-provements for both print shops and document-manufacturing solutions have extended the use of operations research to small- andmedium-sized print shops, while increasing the scope of applications inlarge document-production facilities.

9 Benefits:LDP solutions have gener-ated approximately $200 million of incremental profit across the Xerox cus-tomer value chain since their initial introduction in 2000 and improved pro-ductivity by 20 40%. The network for transport of natural gas on the Norwegian Continental Shelfis the world s largest offshore pipeline network. The gas flowing through thisnetwork represents approximately 15% of European consumption. In a net-work of interconnected pipelines, system effects are prevalent, and the net-work must be analyzed as a whole to determine the optimal operation. Themain Norwegian shipper of natural gas, StatoilHydro, uses a tool called GassOpt that allows users to graphically model their network and run opti-mizations to find the best network configuration and routing for transportinggas.

10 Benefits:The company estimates that its accumulated savings related tothe use of GassOpt were approximately US $2 billion as of 2008. In the United States, the Federal Aviation Administration(FAA) is responsi-ble for providing air traffic management services and frequently faces situa-tions where a large-scale weather system reduces airspace capacity. In June2006, the FAA began using a tool known as Airspace Flow Programs that gavethe FAA the ability to control activity in congested airspaces by issuing grounddelays customized for each individual flight when large-scale thunderstormsblock major flight routes. Benefits:During its first two years of use, the systemsaved aircraft operators an estimated $190 million.