MODELING FOR INSIGHT A Master Class for Business Analysts Stephen G. Powell Robert J. Batt Tuck School of Business Dartmouth College Hanover, NH A JOHN WILEY & SONS, INC. PUBLICATION MODELING FOR INSIGHT MODELING FOR INSIGHT A Master Class for Business Analysts Stephen G. Powell Robert J. Batt Tuck School of Business Dartmouth College Hanover, NH A JOHN WILEY & SONS, INC. PUBLICATION Copyright © 2008 by John Wiley & Sons, Inc. All rights reserved. Published by John Wiley & Sons, Inc., Hoboken, New Jersey Published simultaneously in Canada No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning, or otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web at www.copyright.com. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permission. Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifi cally disclaim any implied warranties of merchantability or fi tness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profi t or any other commercial damages, including but not limited to special, incidental, consequential, or other damages. For general information on our other products and services or for technical support, please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www.wiley.com. Library of Congress Cataloging-in-Publication Data Powell, Stephen G. Modeling for insight : a master class for business analysts / Stephen G. Powell, Robert J. Batt. p. cm. Includes bibliographical references and index. ISBN 978-0-470-17555-2 (cloth) 1. Decision making. 2. Business planning. 3. Business—Computer simulation. 4. Electronic spreadsheets. I. Batt, Robert J. II. Title. HD30.23.P69 2008 658.4′0352—dc22 2008009579 Printed in the United States of America 10 9 8 7 6 5 4 3 2 1 For Nick and Annika CONTENTS PREFACE xiii USING THIS BOOK xix ACKNOWLEDGMENTS xxiii ACKNOWLEDGMENTS FOR CASES xxv ABOUT THE AUTHORS xxvii PART I 1 1. Introduction 3 1.0 Models and Modeling 3 1.1 Well-Structured versus Ill-Structured Problems 4 1.2 Modeling versus Problem Solving 5 1.3 Modeling for Insight 6 1.4 Novice Modelers and Expert Modelers 7 1.5 Craft Skills in Modeling 8 1.6 A Structured Modeling Process 9 1.7 Modeling Tools 9 1.8 Summary 10 2. Foundations of Modeling for Insight 11 2.0 Introduction 11 2.1 The Modeling Process 11 2.1.1 Frame the Problem 12 2.1.2 Diagram the Problem 15 2.1.3 Build a Model 16 2.1.4 Generate Insights 16 2.2 Tools for Modeling 19 2.2.1 Infl uence Diagrams 19 2.2.2 Spreadsheet Engineering 22 2.2.3 Parameterization 23 2.2.4 Sensitivity Analysis 24 2.2.5 Strategy Analysis 25 vii viii CONTENTS 2.2.6 Iterative Modeling 25 2.3 Presentation Skills 26 2.3.1 Special Challenges for Quantitative Analysts 27 2.3.2 Principles for Effective Presentations 27 2.3.3 The Process of Creating an Effective Presentation 30 2.3.4 Pitfalls in Presentations 33 2.4 Summary 34 3. Spreadsheet Engineering 36 3.0 Why Use Spreadsheets? 36 3.1 Spreadsheet Engineering 37 3.1.1 Design 37 3.1.2 Build 39 3.1.3 Test 40 3.1.4 Analyze 43 3.2 Summary 49 PART II 51 4. A First Example—The Red Cross Problem 53 4.0 Introduction 53 4.1 The Red Cross Problem 53 4.1.1 Frame the Problem 54 4.1.2 Diagram the Problem 56 4.1.3 Build the Model—Surplus 62 4.1.4 Generate Insights 66 4.2 Bringing Blood Quality into the Analysis 69 4.2.1 Infl uence Diagram for Quality 69 4.2.2 Build the Model—Blood Quality 73 4.2.3 Generate Insights 76 4.2.4 Wrap-up 78 4.3 Improving and Iterating 78 4.4 Summary 79 5. Retirement Planning Problem 80 5.0 Introduction 80 5.1 Retirement Planning (A) 80 5.1.1 Frame the Problem 80 5.1.2 Diagram the Problem 83