Management Science
The Art of Modeling with Spreadsheets - Excel 2007 Update
Published on 18. August 2008
Book
Hardback
528 pages
978-0-470-39376-5 (ISBN)
Description
The update to the second edition of Management Science: The Art of Modeling with Spreadsheets by Steve Powell and Ken Baker is revised to be compatible with Microsoft Excel 2007. Like the original second edition, the text expands upon the essential skills needed to develop real expertise in business modeling. In principle, two students could work side by side in a course, one using the Second Edition and relying on Excel 2003, the other using the Update Edition and relying on Excel 2007. They will be able to learn the same skills, as both versions of the book are self-contained.
More details
Language
English
Place of publication
Chichester
United Kingdom
Publishing group
John Wiley and Sons Ltd
Target group
Professional and scholarly
Dimensions
Height: 283 mm
Width: 220 mm
Thickness: 23 mm
Weight
1164 gr
ISBN-13
978-0-470-39376-5 (9780470393765)
Copyright in bibliographic data is held by Nielsen Book Services Limited or its licensors: all rights reserved.
Schweitzer Classification
Persons
Steve Powell is Professor at the Tuck School of Business at Dartmouth College. His primary research interest lies in modeling production and service processes, but he has also been active in research energy economics, marketing, and operations. At Tuck, he has developed a variety of courses in management science, including the core Decision Science course and electives in the Art of Modeling, Business Process Redesign, and Applications of Simulation. He originated the Teacher's Forum column in Interfaces, and has written a number of articles on teaching modeling to practitioners. He organized the Workshop on Teaching Management Science with Spreadsheet in the summer of 1998, and is the Academic Director of a new series of INFORMS workshops on teaching management science. In 2001, he was awarded the INFORMS Prize for the Teaching of Operations Research/Management Science Practice. Ken Baker is a faculty member at Dartmouth College. He is currently Nathaniel Leverone Professor of Management at the Tuck School of Business and also Adjunct Professor at the Thayer School of Engineering. At Dartmouth, he has taught courses relating to Decision Science, Manufacturing Management, and Environmental Management. Over the years, much of his teaching and research has dealt with Production Planning and Control, and he is widely known for his textbook, Elements of Sequencing and Scheduling, in addition to a variety of technical articles. He has served as Tuck School's Associate Dean and directed the Tuck School's management development programs in the manufacturing area. In 2001, he was named a Fellow of INFORMS' Manufacturing and Service Operations Management (MSOM) Society.
Content
CHAPTER 1: INTRODUCTION. 1.1 Models and Modeling. 1.1.1 Why Study Modeling? 1.1.2 Models in Business. 1.1.3 Models in Business Education. 1.1.4 Benefits of Business Models. 1.2 The Role of Spreadsheets. 1.2.1 Risks of Spreadsheet Use. 1.2.2 Challenges for Spreadsheet Users. 1.2.3 Background Knowledge for Spreadsheet Modeling. 1.3 The Real World and the Model World. 1.4 Lessons from Expert and Novice Modelers. 1.4.1 Expert Modelers. 1.4.2 Novice Modelers. 1.5 Organization of the Book. 1.6 Summary. CHAPTER 2 MODELING IN A PROBLEM-SOLVING FRAMEWORK. 2.1 Introduction. 2.2 The Problem-Solving Process. 2.2.1 Some Key Terms. 2.2.2 The Six-Stage Problem-Solving Process. 2.2.3 Mental Models and Formal Models. 2.3 Influence Charts. 2.3.1 A First Example. 2.3.2 An Income Statement as an Influence. 2.3.3 Principles for Building Influence. 2.3.4 Two Additional Examples. 2.4 Craft Skills for Modeling. 2.4.1 Simplify the Problem. 2.4.2 Break the Problem into Modules. 2.4.3 Build a Prototype and Refine It. 2.4.4 Sketch Graphs of Key Relationships. 2.4.5 Identify Parameters and Perform Sensitivity Analysis. 2.4.6 Separate the Creation of Ideas from Their Evaluation. 2.4.7 Work Backward from the Desired Answer. 2.4.8 Focus on Model Structure, not on Data Collection. CHAPTER 3 BASIC EXCEL SKILLS. 3.1 Introduction. 3.2 Excel Prerequisites. 3.3 The Excel Window. 3.4 Configuring Excel. 3.5 Manipulating Windows and Sheets. 3.6 Navigation. 3.7 Selecting Cells. 3.8 Entering Text and Data. 3.9 Editing Cells. 3.10 Formatting. 3.11 Basic Formulas. 3.12 Basic Functions. 3.13 Charting. 3.14 Printing. 3.15 Help Options. 3.16 Summary. CHAPTER 4 ADVANCED EXCEL SKILLS. 4.1 Introduction. 4.2 Keyboard Shortcuts. 4.3 Controls. 4.4 Cell Comments. 4.5 Naming Cells and Ranges. 4.6 Advanced Formulas and Functions. 4.6.1 R1C1 Reference Style. 4.6.2 Mixed Addresses. 4.6.3 Nesting Calculations. 4.6.4 Parameterization. 4.6.5 Advanced Functions. 4.7 Recording Macros And Using VBA. 4.7.1 Recording a Macro. 4.7.2 Editing a Macro. 4.7.3 Creating a User-Defined Function. 4.8 Summary. CHAPTER 5 SPREADSHEET ENGINEERING. 5.1 Introduction. 5.2 Designing a Spreadsheet. 5.2.1 Sketch the Spreadsheet. 5.2.2 Organize the Spreadsheet into Modules. 5.2.3 Start Small. 5.2.4 Isolate Input Parameters. 5.2.5 Design for Use. 5.2.6 Keep It Simple. 5.2.7 Design for Communication. 5.2.8 Document Important Data and Formulas. Designing a Workbook. 5.3.1 Use Separate Worksheets to Group Similar Kinds of Information. 5.3.2 Design Workbooks for Ease of Navigation and Use. 5.3.3 Design a Workbook as a Decision-Support System. 5.4 Building a Workbook. 5.4.1 Follow a Plan. 5.4.2 Build One Worksheet or Module at a Time. 5.4.3 Predict the Outcome of Each Formula. 5.4.4 Copy and Paste Formulas Carefully. 5.4.5 Use Relative and Absolute Addressing to Simplify Copying. 5.4.6 Use the Function Wizard to Ensure Correct Syntax. 5.4.7 Use Range Names to Make Formulas Easy to Read. 5.4.8 Choose Input Data to Make Errors Stand Out. 5.5 Testing a Workbook. 5.5.1 Check That Numerical Results Look Plausible. 5.5.2 Check That Formulas Are Correct. 5.5.3 Test That Model Performance Is Plausible. 5.6 Auditing Software: Spreadsheet Professional. 5.6.1 Building Tools. 5.6.2 Testing Tools. 5.6.3 Documenting Tools. 5.6.4 Usage Tools. 5.7 Summary. CHAPTER 6 ANALYSIS USING SPREADSHEETS. 6.1 Introduction. 6.2 Base-case Analysis. 6.3 What-If Analysis. 6.3.1 Benchmarking. 6.3.2 Scenarios. 6.3.3 Data Sensitivity. 6.3.4 Tornado Charts. 6.4 Breakeven Analysis. 6.5 Optimization Analysis. 6.6 Simulation and Risk Analysis. 6.7 Summary. CHAPTER 7 DATA ANALYSIS FOR MODELING. 7.1 Introduction. 7.2 Finding Facts from Databases. 7.2.1 Searching and Editing. 7.2.2 Sorting. 7.2.3 Filtering. 7.2.4 Tabulating. 7.3 Analyzing Sample Data. 7.4 Estimating Parameters: Point Estimates. 7.5 Estimating Parameters: Interval Estimates. 7.5.1 Interval Estimates for the Mean. 7.5.2 Interval Estimates for a Proportion. 7.5.3 Sample-size Determination. 7.6 Summary. CHAPTER 8 REGRESSION ANALYSIS. 8.1 Introduction. 8.2 A Decision-Making Example. 8.2.1 Base-case Analysis. 8.2.2 Sensitivity Analysis. 8.2.3 Base-case Summary. 8.3 Exploring Data: Scatter Plots and Correlation. 8.4 Simple Linear Regression. 8.5 Goodness-of-Fit. 8.6 Simple Regression in the BPI Example. 8.7 Simple Nonlinear Regression. 8.8 Multiple Linear Regression. 8.9 Multiple Regression in the BPI Example. 8.10 Regression Assumptions. 8.11 Using the Excel Tools Trendline and LINEST. 8.11.1 Trendline. 8.11.2 LINEST. 8.12 Summary. CHAPTER 9 SHORT-TERM FORECASTING. 9.1 Introduction. 9.2 Forecasting with Time Series Models. 9.2.1 The Moving Average Model. 9.2.2 Measures of Forecast Accuracy. 9.3 The Exponential Smoothing Model. 9.4 Exponential Smoothing with a Trend. 9.5 Exponential Smoothing with Trend and Cyclical Factors. 9.6 Using CB Predictor. 9.6.1 Single Moving Average. 9.6.2 Single Exponential Smoothing. 9.7 Summary. CHAPTER 10 NONLINEAR OPTIMIZATION. 10.1 Introduction. 10.2 An Optimization Example. 10.2.1 Optimizing Q1. 10.2.2 Optimization Over All Four Quarters. 10.2.3 Incorporating the Budget Constraint. 10.3 Building Models for Solver. 10.3.1 Formulation. 10.3.2 Layout. 10.3.3 Interpreting Results. 10.4 Model Classification and the Nonlinear Solver. 10.5 Nonlinear Programming Examples. 10.5.1 Facility Location. 10.5.2 Revenue Maximization. 10.5.3 Curve Fitting. 10.5.4 Economic Order Quantity. 10.6 Sensitivity Analysis for Nonlinear Programs. 10.7 The Portfolio Optimization Model. 10.8 CHAPTER 11 LINEAR PROGRAMMING. 11.1 Introduction. 11.1.1 Linearity. 11.1.2 Simplex Algorithm. 11.2 Allocation Models. 11.2.1 Formulation. 11.2.2 Spreadsheet Model. 11.2.3 Optimization. 11.3 Covering Models. 11.3.1 Formulation. 11.3.2 Spreadsheet Model. 11.3.3 Optimization. 11.4 Blending Models. 11.4.1 Blending Constraints. 11.4.2 Formulation. 11.4.3 Spreadsheet Model. 11.4.4 Optimization. 11.5 Sensitivity Analysis for Linear Programs. 11.5.1 Sensitivity to Objective Function Coefficients. 11.5.2 Sensitivity to Constraint Constants. 11.6 Patterns in Linear Programming Solutions. 11.6.1 Identifying Patterns. 11.6.2 Further Examples. 11.6.3 Review. 11.7 Data Envelopment Analysis. 11.8 Summary. Appendix 11.1 The Sensitivity Report. CHAPTER 12 NETWORK MODELS. 12.1 Introduction. 12.2 The Transportation Model. 12.2.1 Flow Diagram. 12.2.2 Formulation. 12.2.3 Spreadsheet Model. 12.2.4 Optimization. 12.2.5 Modifications to the Model. 12.2.6 Sensitivity Analysis. 12.3 The Assignment Model. 12.3.1 Formulation. 12.3.2 Spreadsheet Model. 12.3.3 Optimization. 12.3.4 Sensitivity Analysis. 12.4 The Transshipment Model. 12.4.1 Formulation. 12.4.2 Spreadsheet Model. 12.4.3 Optimization. 12.4.4 Sensitivity Analysis. 12.5 A Standard Form for Network Models. 12.6 Network Models with Yields. 12.6.1 Yields as Reductions in Flow. 12.6.2 Yields as Expansions in Flow. 12.6.3 Patterns in General Network Models. 12.7 Network Models for Process Technologies. 12.7.1 Formulation. 12.7.2 Spreadsheet Model. 12.7.3 Optimization. 12.8 Summary. CHAPTER 13 INTEGER PROGRAMMING. 13.1 Introduction. 13.2 Integer Variables and the Integer Solver. 13.3 Binary Variables and Binary Choice Models. 13.3.1 The Capital Budgeting Problem. 13.3.2 The Set Covering Problem. 13.4 Binary Variables and Logical Relationships. 13.4.1 Relationships among Projects. 13.4.2 Linking Constraints and Fixed Costs. 13.4.3 Threshold Levels and Quantity Discounts. 13.5 The Facility Location Model. 13.5.1 The Capacitated Problem. 13.5.2 The Uncapacitated Problem. 13.5.3 The Assortment Model. 13.6 Summary. CHAPTER 14 DECISION ANALYSIS. 14.1 Introduction. 14.2 Payoff Tables and Decision Criteria. 14.2.1 Benchmark Criteria. 14.2.2 Incorporating Probabilities. 14.3 Using Trees to Model Decisions. 14.3.1 Decision Trees. 14.3.2 Decision Trees for a Series of Decisions. 14.3.3 Principles for Building and Analyzing Decision Trees. 14.3.4 The Cost of Uncertainty. 14.4 Using TreePlan Software. 14.4.1 Solving a Simple Example with TreePlan. 14.4.2 Sensitivity Analysis with TreePlan. 14.4.3 Minimizing Expected Costs with TreePlan. 14.5 Maximizing Expected Utility with TreePlan. 14.6 Summary. CHAPTER 15 MONTE CARLO SIMULATION. 15.1 Introduction. 15.2 A Simple Illustration. 15.3 The Simulation Process. 15.3.1 Base-case Model. 15.3.2 Sensitivity Analysis. 15.3.3 Selecting Probability Distributions-Creating Assumption Cells. 15.3.4 Selecting Outputs-Creating Forecast Cells. 15.3.5 Setting Simulation Parameters. 15.3.6 Analyzing Simulation Outputs. 15.4 Corporate Valuation Using Simulation. 15.4.1 Base-case Model. 15.4.2 Sensitivity Analysis. 15.4.3 Selecting Probability Distributions. 15.4.4 Simulation Analysis. 15.4.5 Simulation Sensitivity. 15.5 Option Pricing Using Simulation. 15.5.1 The Logic of Options. 15.5.2 Modeling Stock Prices. 15.5.3 Pricing an Option. 15.5.4 Sensitivity to Volatility. 15.5.5 Simulation Accuracy. 15.6 Selecting Uncertain Parameters. 15.7 Selecting Probability Distributions. 15.7.1 Empirical Data and Judgmental Data. 15.7.2 Six Essential Distributions. 15.7.3 Fitting Distributions to Data. 15.8 Ensuring Precision in Outputs. 15.8.1 Illustrations of Simulation Error. 15.8.2 Precision Versus Accuracy. 15.8.3 An Experimental Method. 15.8.4 Simulation Error in a Decision Context. 15.9 Interpreting Simulation Outcomes. 15.9.1 Forecast Charts. 15.9.2 Statistics and Percentiles. 15.10When Not to Simulate. 15.11 Summary. Appendix 15.1 Choosing Crystal Ball Settings. Appendix 15.2 Additional features of Crystal Ball. CHAPTER 16 OPTIMIZATION IN SIMULATION. 16.1 Introduction. 16.2 Optimization with One or Two Decision Variables. 16.2.1 Base-case Model. 16.2.2 Grid Search. 16.2.3 Replicating the Model. 16.2.4 Using CB Sensitivity. 16.3 Complex Optimization Problems. 16.3.1 OptQuest Concepts. 16.3.2 A Production Planning Problem. 16.3.3 A Portfolio Optimization Problem. 16.3.4 A Cash-Management Problem. 16.4 Embedded Optimization: Using Solver within Crystal Ball. 16.4.1 A Capacity Planning Example. 16.4.2 Creating a Macro to Embed Solver. 16.5 Summary. MODELING CASES. APPENDIX: BASIC PROBABILITY CONCEPTS. INDEX. Optional Sections.