Management Science: The Art of Modeling with Spreadsheets, Third Edition

Management Science: The Art of Modeling with Spreadsheets, Third Edition

by Kenneth R. Baker, Stephen G. Powell
Released October 2010
Publisher(s): Wiley
ISBN: 9780470530672

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Now in its third edition, Management Science helps business professionals gain the essential skills needed to develop real expertise in business modeling. The biggest change in the text is the conversion of software from Crystal Ball to Risk Solver to reflect changes in the field. More coverage of management science topics has been added. Broader coverage of Excel demonstrates how to create models. Additional open-ended case studies that are less structured have also been included along with new exercises. These changes will help business professionals learn how to apply the information in the field.

Table of contents

  1. Copyright
  2. Preface
    1. WHY THIS BOOK?
    2. NEW IN THE THIRD EDITION
    3. TO THE READER
    4. TO THE TEACHER
    5. SOFTWARE ACCOMPANYING THE THIRD EDITION
    6. ACKNOWLEDGMENTS
  3. About The Authors
  4. 1. Introduction
    1. 1.1. MODELS AND MODELING
      1. 1.1.1. Why Study Modeling?
      2. 1.1.2. Models in Business
      3. 1.1.3. Models in Business Education
      4. 1.1.4. Benefits of Business Models
    2. 1.2. THE ROLE OF SPREADSHEETS
      1. 1.2.1. Risks of Spreadsheet Use
      2. 1.2.2. Challenges for Spreadsheet Users
      3. 1.2.3. Background Knowledge for Spreadsheet Modeling
    3. 1.3. THE REAL WORLD AND THE MODEL WORLD
    4. 1.4. LESSONS FROM EXPERT AND NOVICE MODELERS
      1. 1.4.1. Expert Modelers
      2. 1.4.2. Novice Modelers
    5. 1.5. ORGANIZATION OF THE BOOK
    6. 1.6. SUMMARY
    7. 1.7. SUGGESTED READINGS
  5. 2. Modeling in a Problem-Solving Framework
    1. 2.1. INTRODUCTION
    2. 2.2. THE PROBLEM-SOLVING PROCESS
      1. 2.2.1. Some Key Terms
      2. 2.2.2. The Six-Stage Problem-Solving Process
      3. 2.2.3. Mental Models and Formal Models
    3. 2.3. INFLUENCE CHARTS
      1. 2.3.1. A First Example
      2. 2.3.2. An Income Statement as an Influence Chart
      3. 2.3.3. Principles for Building Influence Charts
      4. 2.3.4. Two Additional Examples
    4. 2.4. CRAFT SKILLS FOR MODELING
      1. 2.4.1. Simplify the Problem
      2. 2.4.2. Break the Problem into Modules
      3. 2.4.3. Build a Prototype and Refine It
      4. 2.4.4. Sketch Graphs of Key Relationships
      5. 2.4.5. Identify Parameters and Perform Sensitivity Analysis
      6. 2.4.6. Separate the Creation of Ideas from Their Evaluation
      7. 2.4.7. Work Backward from the Desired Answer
      8. 2.4.8. Focus on Model Structure, not on Data Collection
    5. 2.5. SUMMARY
    6. 2.6. SUGGESTED READINGS
    7. 2.7. EXERCISES
  6. 3. Basic Excel Skills
    1. 3.1. INTRODUCTION
    2. 3.2. EXCEL PREREQUISITES
    3. 3.3. THE EXCEL WINDOW
    4. 3.4. CONFIGURING EXCEL
    5. 3.5. MANIPULATING WINDOWS AND SHEETS
    6. 3.6. NAVIGATION
    7. 3.7. SELECTING CELLS
    8. 3.8. ENTERING TEXT AND DATA
    9. 3.9. EDITING CELLS
    10. 3.10. FORMATTING
    11. 3.11. BASIC FORMULAS
    12. 3.12. BASIC FUNCTIONS
    13. 3.13. CHARTING
    14. 3.14. PRINTING
    15. 3.15. HELP OPTIONS
    16. 3.16. SUMMARY
    17. 3.17. SUGGESTED READINGS
  7. 4. Advanced Excel Skills
    1. 4.1. INTRODUCTION
    2. 4.2. KEYBOARD SHORTCUTS
    3. 4.3. CONTROLS
    4. 4.4. CELL COMMENTS
    5. 4.5. NAMING CELLS AND RANGES
    6. 4.6. ADVANCED FORMULAS AND FUNCTIONS
      1. 4.6.1. R1C1 Reference Style
      2. 4.6.2. Mixed Addresses
      3. 4.6.3. Nesting Calculations
      4. 4.6.4. Parameterization
      5. 4.6.5. Advanced Functions
    7. 4.7. RECORDING MACROS AND USING VBA
      1. 4.7.1. Recording a Macro
      2. 4.7.2. Editing a Macro
      3. 4.7.3. Creating a User-Defined Function
    8. 4.8. SUMMARY
    9. 4.9. SUGGESTED READINGS
  8. 5. Spreadsheet Engineering
    1. 5.1. INTRODUCTION
    2. 5.2. DESIGNING A SPREADSHEET
      1. 5.2.1. Sketch the Spreadsheet
      2. 5.2.2. Organize the Spreadsheet into Modules
      3. 5.2.3. Start Small
      4. 5.2.4. Isolate Input Parameters
      5. 5.2.5. Design for Use
      6. 5.2.6. Keep It Simple
      7. 5.2.7. Design for Communication
      8. 5.2.8. Document Important Data and Formulas
    3. 5.3. DESIGNING A WORKBOOK
      1. 5.3.1. Use Separate Worksheets to Group Similar Kinds of Information
      2. 5.3.2. Design Workbooks for Ease of Navigation and Use
      3. 5.3.3. Design a Workbook as a Decision-Support System
    4. 5.4. BUILDING A WORKBOOK
      1. 5.4.1. Follow a Plan
      2. 5.4.2. Build One Worksheet or Module at a Time
      3. 5.4.3. Predict the Outcome of Each Formula
      4. 5.4.4. Copy and Paste Formulas Carefully
      5. 5.4.5. Use Relative and Absolute Addressing to Simplify Copying
      6. 5.4.6. Use the Function Wizard to Ensure Correct Syntax
      7. 5.4.7. Use Range Names to Make Formulas Easy to Read
      8. 5.4.8. Choose Input Data to Make Errors Stand Out
    5. 5.5. TESTING A WORKBOOK
      1. 5.5.1. Check That Numerical Results Look Plausible
      2. 5.5.2. Check That Formulas Are Correct
      3. 5.5.3. Test That Model Performance Is Plausible
    6. 5.6. SUMMARY
    7. 5.7. SUGGESTED READINGS
    8. 5.8. EXERCISES
  9. 6. Analysis Using Spreadsheets
    1. 6.1. INTRODUCTION
    2. 6.2. BASE-CASE ANALYSIS
    3. 6.3. WHAT-IF ANALYSIS
      1. 6.3.1. Benchmarking
      2. 6.3.2. Scenarios
      3. 6.3.3. Parametric Sensitivity
      4. 6.3.4. Tornado Charts
    4. 6.4. BREAKEVEN ANALYSIS
    5. 6.5. OPTIMIZATION ANALYSIS
    6. 6.6. SIMULATION AND RISK ANALYSIS
    7. 6.7. SUMMARY
    8. 6.8. EXERCISES
  10. 7. Data Analysis for Modeling
    1. 7.1. INTRODUCTION
    2. 7.2. FINDING FACTS FROM DATABASES
      1. 7.2.1. Searching and Editing
      2. 7.2.2. Sorting
      3. 7.2.3. Filtering
      4. 7.2.4. Tabulating
    3. 7.3. ANALYZING SAMPLE DATA
    4. 7.4. ESTIMATING PARAMETERS: POINT ESTIMATES
    5. 7.5. ESTIMATING PARAMETERS: INTERVAL ESTIMATES
      1. 7.5.1. Interval Estimates for the Mean
      2. 7.5.2. Interval Estimates for a Proportion
      3. 7.5.3. Sample-Size Determination
    6. 7.6. SUMMARY
    7. 7.7. SUGGESTED READINGS
    8. 7.8. EXERCISES
  11. 8. Regression Analysis
    1. 8.1. INTRODUCTION
    2. 8.2. A DECISION-MAKING EXAMPLE
      1. 8.2.1. Base-case Analysis
      2. 8.2.2. Sensitivity Analysis
      3. 8.2.3. Base-case Summary
    3. 8.3. EXPLORING DATA: SCATTER PLOTS AND CORRELATION
    4. 8.4. SIMPLE LINEAR REGRESSION
    5. 8.5. GOODNESS-OF-FIT
    6. 8.6. SIMPLE REGRESSION IN THE BPI EXAMPLE
    7. 8.7. SIMPLE NONLINEAR REGRESSION
    8. 8.8. MULTIPLE LINEAR REGRESSION
    9. 8.9. MULTIPLE REGRESSION IN THE BPI EXAMPLE
    10. 8.10. REGRESSION ASSUMPTIONS
    11. 8.11. USING THE EXCEL TOOLS TRENDLINE AND LINEST
      1. 8.11.1. Trendline
      2. 8.11.2. LINEST
    12. 8.12. SUMMARY
    13. 8.13. SUGGESTED READINGS
    14. 8.14. EXERCISES
  12. 9. Short-Term Forecasting
    1. 9.1. INTRODUCTION
    2. 9.2. FORECASTING WITH TIME-SERIES MODELS
      1. 9.2.1. The Moving-Average Model
      2. 9.2.2. Measures of Forecast Accuracy
    3. 9.3. THE EXPONENTIAL SMOOTHING MODEL
    4. 9.4. EXPONENTIAL SMOOTHING WITH A TREND
    5. 9.5. EXPONENTIAL SMOOTHING WITH TREND AND CYCLICAL FACTORS
    6. 9.6. SUMMARY
    7. 9.7. SUGGESTED READINGS
    8. 9.8. EXERCISES
  13. 10. Nonlinear Optimization
    1. 10.1. INTRODUCTION
    2. 10.2. AN OPTIMIZATION EXAMPLE
      1. 10.2.1. Optimizing Q1
      2. 10.2.2. Optimization Over All Four Quarters
      3. 10.2.3. Incorporating the Budget Constraint
    3. 10.3. BUILDING MODELS FOR SOLVER
      1. 10.3.1. Formulation
      2. 10.3.2. Layout
      3. 10.3.3. Interpreting Results
    4. 10.4. MODEL CLASSIFICATION AND THE NONLINEAR SOLVER
    5. 10.5. NONLINEAR PROGRAMMING EXAMPLES
      1. 10.5.1. Facility Location
      2. 10.5.2. Revenue Maximization
      3. 10.5.3. Curve Fitting
      4. 10.5.4. Economic Order Quantity
    6. 10.6. SENSITIVITY ANALYSIS FOR NONLINEAR PROGRAMS
    7. 10.7. THE PORTFOLIO OPTIMIZATION MODEL
    8. 10.8. SUMMARY
    9. 10.9. SUGGESTED READINGS
    10. 10.10. EXERCISES
  14. 11. Linear Optimization
    1. 11.1. INTRODUCTION
      1. 11.1.1. Linearity
      2. 11.1.2. Simplex Algorithm
    2. 11.2. ALLOCATION MODELS
      1. 11.2.1. Formulation
      2. 11.2.2. Spreadsheet Model
      3. 11.2.3. Optimization
    3. 11.3. COVERING MODELS
      1. 11.3.1. Formulation
      2. 11.3.2. Spreadsheet Model
      3. 11.3.3. Optimization
    4. 11.4. BLENDING MODELS
      1. 11.4.1. Blending Constraints
      2. 11.4.2. Formulation
      3. 11.4.3. Spreadsheet Model
      4. 11.4.4. Optimization
    5. 11.5. SENSITIVITY ANALYSIS FOR LINEAR PROGRAMS
      1. 11.5.1. Sensitivity to Objective Function Coefficients
      2. 11.5.2. Sensitivity to Constraint Constants
    6. 11.6. PATTERNS IN LINEAR PROGRAMMING SOLUTIONS
      1. 11.6.1. Identifying Patterns
      2. 11.6.2. Further Examples
      3. 11.6.3. Review
    7. 11.7. DATA ENVELOPMENT ANALYSIS
    8. 11.8. SUMMARY
    9. 11.9. SUGGESTED READINGS
    10. 11.10. EXERCISES
    11. 11.11. THE SOLVER SENSITIVITY REPORT
  15. 12. Optimization of Network Models
    1. 12.1. INTRODUCTION
    2. 12.2. THE TRANSPORTATION MODEL
      1. 12.2.1. Flow Diagram
      2. 12.2.2. Model Formulation
      3. 12.2.3. Spreadsheet Model
      4. 12.2.4. Optimization
      5. 12.2.5. Modifications to the Model
      6. 12.2.6. Sensitivity Analysis
    3. 12.3. ASSIGNMENT MODEL
      1. 12.3.1. Model Formulation
      2. 12.3.2. Spreadsheet Model
      3. 12.3.3. Optimization
      4. 12.3.4. Sensitivity Analysis
    4. 12.4. THE TRANSSHIPMENT MODEL
      1. 12.4.1. Formulation
      2. 12.4.2. Spreadsheet Model
      3. 12.4.3. Optimization
      4. 12.4.4. Sensitivity Analysis
    5. 12.5. A STANDARD FORM FOR NETWORK MODELS
    6. 12.6. NETWORK MODELS WITH YIELDS
      1. 12.6.1. Yields as Reductions in Flow
      2. 12.6.2. Yields as Expansions in Flow
      3. 12.6.3. Patterns in General Network Models
    7. 12.7. NETWORK MODELS FOR PROCESS TECHNOLOGIES
      1. 12.7.1. Formulation
      2. 12.7.2. Spreadsheet Model
      3. 12.7.3. Optimization
    8. 12.8. SUMMARY
    9. 12.9. EXERCISES
  16. 13. Integer Optimization
    1. 13.1. INTRODUCTION
    2. 13.2. INTEGER VARIABLES AND THE INTEGER SOLVER
    3. 13.3. BINARY VARIABLES AND BINARY CHOICE MODELS
      1. 13.3.1. The Capital Budgeting Problem
      2. 13.3.2. The Set Covering Problem
    4. 13.4. BINARY VARIABLES AND LOGICAL RELATIONSHIPS
      1. 13.4.1. Relationships Among Projects
      2. 13.4.2. Linking Constraints and Fixed Costs
      3. 13.4.3. Threshold Levels and Quantity Discounts
    5. 13.5. THE FACILITY LOCATION MODEL
      1. 13.5.1. The Capacitated Problem
      2. 13.5.2. The Uncapacitated Problem
      3. 13.5.3. The Assortment Model
    6. 13.6. SUMMARY
    7. 13.7. SUGGESTED READINGS
    8. 13.8. EXERCISES
  17. 14. Optimization of Non-Smooth Models
    1. 14.1. INTRODUCTION
    2. 14.2. FEATURES OF THE EVOLUTIONARY SOLVER
    3. 14.3. AN ILLUSTRATIVE EXAMPLE: NONLINEAR REGRESSION
    4. 14.4. THE ADVERTISING BUDGET PROBLEM (REVISITED)
    5. 14.5. THE CAPITAL BUDGETING PROBLEM (REVISITED)
    6. 14.6. THE FIXED COST PROBLEM (REVISITED)
    7. 14.7. THE MACHINE-SEQUENCING PROBLEM
    8. 14.8. THE TRAVELING SALESPERSON PROBLEM
    9. 14.9. GROUP ASSIGNMENT
    10. 14.10. SUMMARY
    11. 14.11. EXERCISES
  18. 15. Decision Analysis
    1. 15.1. INTRODUCTION
    2. 15.2. PAYOFF TABLES AND DECISION CRITERIA
      1. 15.2.1. Benchmark Criteria
      2. 15.2.2. Incorporating Probabilities
    3. 15.3. USING TREES TO MODEL DECISIONS
      1. 15.3.1. Decision Trees
      2. 15.3.2. Decision Trees for a Series of Decisions
      3. 15.3.3. Principles for Building and Analyzing Decision Trees
      4. 15.3.4. The Cost of Uncertainty
    4. 15.4. USING DECISION TREE SOFTWARE
      1. 15.4.1. Solving a Simple Example with Decision Tree
      2. 15.4.2. Sensitivity Analysis with Decision Tree
      3. 15.4.3. Minimizing Expected Cost with Decision Tree
    5. 15.5. MAXIMIZING EXPECTED UTILITY WITH DECISION TREE
    6. 15.6. SUMMARY
    7. 15.7. SUGGESTED READINGS
    8. 15.8. EXERCISES
  19. 16. Monte Carlo Simulation
    1. 16.1. INTRODUCTION
    2. 16.2. A SIMPLE ILLUSTRATION
    3. 16.3. THE SIMULATION PROCESS
      1. 16.3.1. Base-Case Model
      2. 16.3.2. Sensitivity Analysis
      3. 16.3.3. Specifying Probability Distributions
      4. 16.3.4. Specifying Outputs
      5. 16.3.5. Setting Simulation Parameters
      6. 16.3.6. Analyzing Simulation Outputs
    4. 16.4. CORPORATE VALUATION USING SIMULATION
      1. 16.4.1. Base-Case Model
      2. 16.4.2. Sensitivity Analysis
      3. 16.4.3. Selecting Probability Distributions
      4. 16.4.4. Simulation Analysis
      5. 16.4.5. Simulation Sensitivity
    5. 16.5. OPTION PRICING USING SIMULATION
      1. 16.5.1. The Logic of Options
      2. 16.5.2. Modeling Stock Prices
      3. 16.5.3. Pricing an Option
      4. 16.5.4. Sensitivity to Volatility
      5. 16.5.5. Simulation Precision
    6. 16.6. SELECTING UNCERTAIN PARAMETERS
    7. 16.7. SELECTING PROBABILITY DISTRIBUTIONS
      1. 16.7.1. Empirical Data and Judgmental Data
      2. 16.7.2. Six Essential Distributions
      3. 16.7.3. Fitting Distributions to Data
    8. 16.8. ENSURING PRECISION IN OUTPUTS
      1. 16.8.1. Illustrations of Simulation Error
      2. 16.8.2. Precision versus Accuracy
      3. 16.8.3. An Experimental Method
      4. 16.8.4. Precision Using the MSE
      5. 16.8.5. Simulation Error in a Decision Context
    9. 16.9. INTERPRETING SIMULATION OUTCOMES
      1. 16.9.1. Simulation Results
      2. 16.9.2. Displaying Results on the Spreadsheet
    10. 16.10. WHEN TO SIMULATE AND WHEN NOT TO SIMULATE
    11. 16.11. SUMMARY
    12. 16.12. SUGGESTED READINGS
    13. 16.13. EXERCISES
  20. 17. Optimization In Simulation
    1. 17.1. INTRODUCTION
    2. 17.2. OPTIMIZATION WITH ONE OR TWO DECISION VARIABLES
      1. 17.2.1. Base-case Model
      2. 17.2.2. Grid Search
      3. 17.2.3. Optimizing using Simulation Sensitivity
      4. 17.2.4. Optimizing Using Solver
    3. 17.3. STOCHASTIC OPTIMIZATION
      1. 17.3.1. Optimization of the Base-Case Model
      2. 17.3.2. A Portfolio Optimization Problem
    4. 17.4. CHANCE CONSTRAINTS
    5. 17.5. TWO-STAGE PROBLEMS WITH RECOURSE
    6. 17.6. SUMMARY
    7. 17.7. SUGGESTED READINGS
    8. 17.8. EXERCISES
  21. Modeling Cases
    1. RETIREMENT PLANNING
    2. DRAFT TV COMMERCIALS
    3. ICEBERGS FOR KUWAIT
    4. THE RACQUETBALL RACKET
    5. THE XYZ COMPANY
    6. MEDICAL SUPPLIES FOR BANJUL
    7. REID'S RAISIN COMPANY
    8. THE BIG RIG TRUCK RENTAL COMPANY
    9. FLEXIBLE INSURANCE COVERAGE
    10. SNOEY SOFTWARE COMPANY
    11. COX CABLE AND WIRE COMPANY
      1. Background
      2. The Contract
      3. The Analysis
    12. THE BMW COMPANY
      1. Background
      2. Economics of disposal
      3. Trends in the Market
    13. THE ERP DECISION
      1. The Situation at Mega Corporation
      2. The Case for ERP
      3. The Case against ERP
      4. Assumptions
      5. Analysis
    14. NATIONAL LEASING, INC.
      1. Background
      2. Company Profile: A Leader in Trouble
      3. Current Situation
      4. Your Challenge
    15. MEGA PHARMA AND MICRO PHARMA
      1. Background
      2. Product Development Process
      3. Mega's Perspective on Its Compound
      4. Mega's Perspective on Micro
  22. A. Basic Probability Concepts
    1. A.1. INTRODUCTION
    2. A.2. PROBABILITY DISTRIBUTIONS
    3. A.3. EXAMPLES OF DISCRETE DISTRIBUTIONS
    4. A.4. EXAMPLES OF CONTINUOUS DISTRIBUTIONS
    5. A.5. EXPECTED VALUES
    6. A.6. CUMULATIVE DISTRIBUTION FUNCTIONS
    7. A.7. TAIL PROBABILITIES
    8. A.8. VARIABILITY
    9. A.9. SAMPLING THEORY

Product information

  • Title: Management Science: The Art of Modeling with Spreadsheets, Third Edition
  • Author(s): Kenneth R. Baker, Stephen G. Powell
  • Release date: October 2010
  • Publisher(s): Wiley
  • ISBN: 9780470530672