Skip to main content

Business Analytics: The Art of Modeling With Spreadsheets, 5th Edition

E-Book Rental (120 Days)

$23.00

Business Analytics: The Art of Modeling With Spreadsheets, 5th Edition

Stephen G. Powell, Kenneth R. Baker

ISBN: 978-1-119-29833-5 October 2016

E-Book Rental (120 Days)
$23.00
E-Book Rental (150 Days)
$25.00
E-Book
$84.00
Paperback
$120.95
Download Product Flyer

Download Product Flyer

Download Product Flyer is to download PDF in new tab. This is a dummy description. Download Product Flyer is to download PDF in new tab. This is a dummy description. Download Product Flyer is to download PDF in new tab. This is a dummy description. Download Product Flyer is to download PDF in new tab. This is a dummy description.

Description

Now in its fifth edition, Powell and Baker’s Business Analytics: The Art of Modeling with Spreadsheets provides students and business analysts with the technical knowledge and skill needed to develop real expertise in business modeling. In this book, the authors cover spreadsheet engineering, management science, and the modeling craft. The briefness & accessibility of this title offers opportunities to integrate other materials –such as cases -into the course. It can be used in any number of courses or departments where modeling is a key skill.

Related Resources

PREFACE XI

ABOUT THE AUTHORS XV

CHAPTER 1 INTRODUCTION 1

1.1 Models and Modeling 1

1.1.1 Why Study Modeling? 2

1.1.2 Models in Business 2

1.1.3 Models in Business Education 3

1.1.4 Benefits of Business Models 3

1.2 The Role of Spreadsheets 4

1.2.1 Risks of Spreadsheet Use 5

1.2.2 Challenges for Spreadsheet Users 6

1.2.3 Background Knowledge for Spreadsheet Modeling 7

1.3 The Real World and the Model World 7

1.4 Lessons from Expert and Novice Modelers 9

1.4.1 Expert Modelers 9

1.4.2 Novice Modelers 11

1.5 Organization of the Book 12

1.6 Summary 13

Suggested Readings 14

CHAPTER 2 MODELING IN A PROBLEM-SOLVING FRAMEWORK 15

2.1 Introduction 15

2.2 The Problem-Solving Process 16

2.2.1 Some Key Terms 16

2.2.2 The Six-Stage Problem-Solving Process 18

2.2.3 Mental Models and Formal Models 23

2.3 Influence Charts 24

2.3.1 A First Example 25

2.3.2 An Income Statement as an Influence Chart 27

2.3.3 Principles for Building Influence Charts 27

2.3.4 Two Additional Examples 28

2.4 Craft Skills for Modeling 31

2.4.1 Simplify the Problem 33

2.4.2 Break the Problem into Modules 34

2.4.3 Build a Prototype and Refine It 35

2.4.4 Sketch Graphs of Key Relationships 38

2.4.5 Identify Parameters and Perform Sensitivity Analysis 39

2.4.6 Separate the Creation of Ideas from Their Evaluation 41

2.4.7 Work Backward from the Desired Answer 42

2.4.8 Focus on Model Structure, not on Data Collection 43

2.5 Summary 45

Suggested Readings 46

Exercises 46

CHAPTER 3 SPREADSHEET ENGINEERING 49

3.1 Introduction 49

3.2 Designing a Spreadsheet 51

3.2.1 Sketch the Spreadsheet 51

3.2.2 Organize the Spreadsheet into Modules 52

3.2.3 Start Small 53

3.2.4 Isolate Input Parameters 54

3.2.5 Design for Use 54

3.2.6 Keep It Simple 54

3.2.7 Design for Communication 55

3.2.8 Document Important Data and Formulas 55

3.3 Designing a Workbook 57

3.3.1 Use Separate Worksheets to Group Similar Kinds of Information 58

3.3.2 Design Workbooks for Ease of Navigation and Use 59

3.3.3 Design a Workbook as a Decision-Support System 60

3.4 Building a Workbook 62

3.4.1 Follow a Plan 62

3.4.2 Build One Worksheet or Module at a Time 62

3.4.3 Predict the Outcome of Each Formula 62

3.4.4 Copy and Paste Formulas Carefully 62

3.4.5 Use Relative and Absolute Addressing to Simplify Copying 62

3.4.6 Use the Function Wizard to Ensure Correct Syntax 63

3.4.7 Use Range Names to Make Formulas Easy to Read 63

3.4.8 Choose Input Data to Make Errors Stand Out 64

3.5 Testing a Workbook 64

3.5.1 Check That Numerical Results Look Plausible 64

3.5.2 Check That Formulas Are Correct 65

3.5.3 Test That Model Performance Is Plausible 68

3.6 Summary 68

Suggested Readings 69

Exercises 69

CHAPTER 4 ANALYSIS USING SPREADSHEETS 71

4.1 Introduction 71

4.2 Base-case Analysis 72

4.3 What-if Analysis 72

4.3.1 Benchmarking 73

4.3.2 Scenarios 74

4.3.3 Parametric Sensitivity 77

4.3.4 Tornado Charts 79

4.4 Breakeven Analysis 81

4.5 Optimization Analysis 83

4.6 Simulation and Risk Analysis 84

4.7 Summary 85

Exercises 85

CHAPTER 5 DATA EXPLORATION AND PREPARATION 89

5.1 Introduction 89

5.2 Dataset Structure 90

5.3 Types of Data 93

5.4 Data Exploration 93

5.4.1 Understand the Data 94

5.4.2 Organize and Subset the Data 94

5.4.3 Examine Individual Variables Graphically 98

5.4.4 Calculate Summary Measures for Individual Variables 99

5.4.5 Examine Relationships among Variables Graphically 101

5.4.6 Examine Relationships among Variables Numerically 105

5.5 Data Preparation 109

5.5.1 Handling Missing Data 109

5.5.2 Handling Errors and Outliers 111

5.5.3 Binning Continuous Data 111

5.5.4 Transforming Categorical Data 111

5.5.5 Functional Transformations 112

5.5.6 Normalizations 113

5.6 Summary 113

Suggested Readings 114

Exercises 114

CHAPTER 6 CLASSIFICATION AND PREDICTION METHODS 117

6.1 Introduction 117

6.2 Preliminaries 117

6.2.1 The Data-Mining Process 118

6.2.2 The Problem of Overfitting 118

6.2.3 Partitioning the Dataset 120

6.2.4 Measures of Model Quality 120

6.2.5 Variable Selection 125

6.2.6 Setting the Cutoff in Classification 126

6.3 Classification and Prediction Trees 127

6.3.1 Classification Trees 128

6.3.2 An Application of Classification Trees 130

6.3.3 Prediction Trees 137

6.3.4 An Application of Prediction Trees 138

6.3.5 Ensembles of Trees 141

6.4 Additional Algorithms for Classification 143

6.4.1 Logistic Regression 144

6.4.2 Naïve Bayes 150

6.4.3 k-Nearest Neighbors 158

6.4.4 Neural Networks 162

6.5 Additional Algorithms for Prediction 169

6.5.1 Multiple Linear Regression 169

6.5.2 k-Nearest Neighbors 177

6.5.3 Neural Networks 178

6.6 Strengths and Weaknesses of Algorithms 181

6.7 Practical Advice 182

6.8 Summary 183

Suggested Readings 184

Exercises 184

CHAPTER 7 SHORT-TERM FORECASTING 187

7.1 Introduction 187

7.2 Forecasting with Time-Series Models 187

7.2.1 The Moving-Average Model 188

7.2.2 Measures of Forecast Accuracy 191

7.3 The Exponential Smoothing Model 192

7.4 Exponential Smoothing with a Trend 196

7.5 Exponential Smoothing with Trend and Cyclical Factors 198

7.6 Using XLMiner for Short-Term Forecasting 202

7.7 Summary 202

Suggested Readings 203

Exercises 203

CHAPTER 8 NONLINEAR OPTIMIZATION 207

8.1 Introduction 207

8.2 An Optimization Example 208

8.2.1 Optimizing Q1 208

8.2.2 Optimization over All Four Quarters 210

8.2.3 Incorporating the Budget Constraint 211

8.3 Building Models for Solver 213

8.3.1 Formulation 213

8.3.2 Layout 214

8.3.3 Interpreting Results 215

8.4 Model Classification and the Nonlinear Solver 215

8.5 Nonlinear Programming Examples 217

8.5.1 Facility Location 217

8.5.2 Revenue Maximization 219

8.5.3 Curve Fitting 221

8.5.4 Economic Order Quantity 225

8.6 Sensitivity Analysis for Nonlinear Programs 227

8.7 The Portfolio Optimization Model 231

8.8 Summary 234

Suggested Readings 234

Exercises 234

CHAPTER 9 LINEAR OPTIMIZATION 239

9.1 Introduction 239

9.1.1 Linearity 239

9.1.2 Simplex Algorithm 240

9.2 Allocation Models 241

9.2.1 Formulation 241

9.2.2 Spreadsheet Model 242

9.2.3 Optimization 244

9.3 Covering Models 246

9.3.1 Formulation 246

9.3.2 Spreadsheet Model 247

9.3.3 Optimization 247

9.4 Blending Models 248

9.4.1 Blending Constraints 249

9.4.2 Formulation 251

9.4.3 Spreadsheet Model 252

9.4.4 Optimization 252

9.5 Sensitivity Analysis for Linear Programs 253

9.5.1 Sensitivity to Objective Function Coefficients 254

9.5.2 Sensitivity to Constraint Constants 255

9.6 Patterns in Linear Programming Solutions 258

9.6.1 Identifying Patterns 258

9.6.2 Further Examples 260

9.6.3 Review 264

9.7 Data Envelopment Analysis 265

9.8 Summary 269

Suggested Readings 270

Exercises 270

Appendix 9.1 The Solver Sensitivity Report 274

CHAPTER 10 OPTIMIZATION OF NETWORK MODELS 277

10.1 Introduction 277

10.2 The Transportation Model 277

10.2.1 Flow Diagram 278

10.2.2 Model Formulation 278

10.2.3 Spreadsheet Model 279

10.2.4 Optimization 280

10.2.5 Modifications to the Model 281

10.2.6 Sensitivity Analysis 282

10.3 Assignment Model 286

10.3.1 Model Formulation 287

10.3.2 Spreadsheet Model 287

10.3.3 Optimization 288

10.3.4 Sensitivity Analysis 288

10.4 The Transshipment Model 289

10.4.1 Formulation 290

10.4.2 Spreadsheet Model 291

10.4.3 Optimization 292

10.4.4 Sensitivity Analysis 293

10.5 A Standard Form for Network Models 293

10.6 Network Models with Yields 295

10.6.1 Yields as Reductions in Flow 295

10.6.2 Yields as Expansions in Flow 297

10.6.3 Patterns in General Network Models 300

10.7 Network Models for Process Technologies 301

10.7.1 Formulation 301

10.7.2 Spreadsheet Model 303

10.7.3 Optimization 304

10.8 Summary 304

Exercises 305

CHAPTER 11 INTEGER OPTIMIZATION 309

11.1 Introduction 309

11.2 Integer Variables and the Integer Solver 310

11.3 Binary Variables and Binary Choice Models 312

11.3.1 The Capital Budgeting Problem 312

11.3.2 The Set Covering Problem 315

11.4 Binary Variables and Logical Relationships 316

11.4.1 Relationships among Projects 317

11.4.2 Linking Constraints and Fixed Costs 319

11.4.3 Threshold Levels and Quantity Discounts 323

11.5 The Facility Location Model 324

11.5.1 The Capacitated Problem 325

11.5.2 The Uncapacitated Problem 327

11.5.3 The Assortment Model 329

11.6 Summary 330

Suggested Readings 331

Exercises 331

CHAPTER 12 OPTIMIZATION OF NONSMOOTH MODELS 335

12.1 Introduction 335

12.2 Features of the Evolutionary Solver 335

12.3 Curve Fitting (Revisited) 338

12.4 The Advertising Budget Problem (Revisited) 339

12.5 The Capital Budgeting Problem (Revisited) 342

12.6 The Fixed Cost Problem (Revisited) 344

12.7 The Machine-Sequencing Problem 345

12.8 The Traveling Salesperson Problem 347

12.9 Group Assignment 350

12.10 Summary 352

Exercises 352

CHAPTER 13 DECISION ANALYSIS 357

13.1 Introduction 357

13.2 Payoff Tables and Decision Criteria 358

13.2.1 Benchmark Criteria 358

13.2.2 Incorporating Probabilities 359

13.3 Using Trees to Model Decisions 361

13.3.1 Decision Trees 362

13.3.2 Decision Trees for a Series of Decisions 364

13.3.3 Principles for Building and Analyzing Decision Trees 367

13.3.4 The Cost of Uncertainty 368

13.4 Using Decision Tree Software 369

13.4.1 Solving a Simple Example with Decision Tree 370

13.4.2 Sensitivity Analysis with Decision Tree 371

13.4.3 Minimizing Expected Cost with Decision Tree 373

13.5 Maximizing Expected Utility with Decision Tree 375

13.6 Summary 378

Suggested Readings 378

Exercises 378

CHAPTER 14 MONTE CARLO SIMULATION 383

14.1 Introduction 383

14.2 A Simple Illustration 384

14.3 The Simulation Process 386

14.3.1 Base-Case Model 387

14.3.2 Sensitivity Analysis 388

14.3.3 Specifying Probability Distributions 390

14.3.4 Specifying Outputs 391

14.3.5 Setting Simulation Parameters 391

14.3.6 Analyzing Simulation Outputs 391

14.4 Corporate Valuation Using Simulation 395

14.4.1 Base-Case Model 396

14.4.2 Sensitivity Analysis 398

14.4.3 Selecting Probability Distributions 399

14.4.4 Simulation Analysis 399

14.4.5 Simulation Sensitivity 402

14.5 Option Pricing Using Simulation 404

14.5.1 The Logic of Options 405

14.5.2 Modeling Stock Prices 405

14.5.3 Pricing an Option 408

14.5.4 Sensitivity to Volatility 410

14.5.5 Simulation Precision 410

14.6 Selecting Uncertain Parameters 411

14.7 Selecting Probability Distributions 413

14.7.1 Empirical Data and Judgmental Data 413

14.7.2 Six Essential Distributions 414

14.7.3 Fitting Distributions to Data 418

14.8 Ensuring Precision in Outputs 420

14.8.1 Illustrations of Simulation Error 420

14.8.2 Precision versus Accuracy 421

14.8.3 An Experimental Method 422

14.8.4 Precision Using the MSE 423

14.8.5 Simulation Error in a Decision Context 423

14.9 Interpreting Simulation Outcomes 424

14.9.1 Simulation Results 424

14.9.2 Displaying Results on the Spreadsheet 426

14.10 When to Simulate and When Not To Simulate 426

14.11 Summary 428

Suggested Readings 428

Exercises 429

CHAPTER 15 OPTIMIZATION IN SIMULATION 435

15.1 Introduction 435

15.2 Optimization with One or Two Decision Variables 435

15.2.1 Base-case Model 436

15.2.2 Grid Search 438

15.2.3 Optimizing using Simulation Sensitivity 439

15.2.4 Optimizing using Solver 442

15.3 Stochastic Optimization 442

15.3.1 Optimization of the Base-Case Model 442

15.3.2 A Portfolio Optimization Problem 445

15.4 Chance Constraints 448

15.5 Two-Stage Problems with Recourse 453

15.6 Summary 457

Suggested Readings 458

Exercises 458

MODELING CASES 463

APPENDIX 1 BASIC EXCEL SKILLS 479

Introduction 479

Excel Prerequisites 479

The Excel Window 480

Configuring Excel 482

Manipulating Windows and Sheets 483

Navigation 484

Selecting Cells 485

Entering Text and Data 485

Editing Cells 486

Formatting 487

Basic Formulas 488

Basic Functions 489

Charting 493

Printing 495

Help Options 496

Keyboard Shortcuts 497

Cell Comments 497

Naming Cells and Ranges 499

Some Advanced Tools 502

R1C1 Reference Style 502

Mixed Addresses 503

Advanced Functions 503

APPENDIX 2 MACROS AND VBA 507

Introduction 507

Recording a Macro 507

Editing a Macro 510

Creating a User-Defined Function 512

Suggested Readings 514

APPENDIX 3 BASIC PROBABILITY CONCEPTS 515

Introduction 515

Probability Distributions 515

Examples of Discrete Distributions 518

Examples of Continuous Distributions 519

Expected Values 521

Cumulative Distribution Functions 522

Tail Probabilities 523

Variability 524

Sampling 525

INDEX 529

  • NEW coverage of data exploration and data mining, shifting focus from explanatory modeling to predictive modeling, and the modern emphasis on predictive performance using validation data.
  • UPDATED content reflecting the latest version of Analytic Solver Platform throughout the textbook.
  • EXPANDED coverage of data analysis.
  • REVISED - Streamlined approach to  data exploration and incorporated data preparation directly into the text (Chapter 5)
  • REVISED - Substantial revision and reorganization of Chapter 6 (Classification and Prediction Methods), by presenting one algorithm (CART) and showing how it can be used for both data mining tasks.

 

  • Access to spreadsheet files for all the models presented in the text. Incorporated the latest versions of Excel & Analytic Solver Platform for Education, an integrated software platform for sensitivity analysis, optimization, decision trees, data exploration and mining, and simulation.
  • Focus on three skill areas that a business analyst needs to become an effective modeler: spreadsheet engineering, management science, and modeling craft.
  • Author-maintained website for users of the textbook. Please contact your Wiley representative to get access details.