Recent Orders

Our Reviews

Sample Papers

How It Works

Get First 2 Pages Of Your Homework Absolutely Free!

Messages

Welcome to TutorsOnSpot.Com!

World's No. 1 Assignment Writing Market

Post Your Homework

Proposals

Post your homework and get free proposals here!

Post Your Homework

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Get Free Quotes Post Your Requirements

Cloud computing case study ppt

04/12/2021 Client: muhammad11 Deadline: 2 Day

1 Discussion Case Study Paper And PPT

1) Discussion 8.1
What is your favorite inferential statistic concept?

Discussion writing requirements

150 words (Excluding the references)
List References and Citations

2) Case Study Paper 8.1 - Full 6 Pages In length excluding References.
what is big data analytics?

- discuss the benefits and features

- discuss the challenges and risks

Use the following Case and additional references to write the paper:

Case -

Big Data and cloud computing: innovation opportunities and challenges

https://www.tandfonline.com/doi/full/10.1080/17538947.2016.1239771

https://doi.org/10.1080/17538947.2016.1239771

Additional References -

a) Big DataRedux: New Issues and ChallengesMoving Forward

https://scholarspace.manoa.hawaii.edu/bitstream/10125/59546/0106.pdf

b) Information Granularity, Big Data, and Computational Intelligence, W. Pedrycz and S.-M. Chen (eds.), Vol. 8, 2015Chapter 13:The Role of Cloud Computing Architecture in Big DataAuthors:Mehdi BahramiandMukesh SinghalCloud Lab, University of California, Merced

https://cloudlab.ucmerced.edu/sites/cloudlab.ucmerced.edu/files/documents/mehdi_bahrami_et_al._the_role_of_cloud_computing_architectures_in_big_data.pdf

c) Building Big Data and Analytics Solutions in the Cloud

http://www.redbooks.ibm.com/redpapers/pdfs/redp5085.pdf

Case study paper 8.1 writing requirements

Use the above Case and additional references to write the paper
6-7 pages in length (excluding cover page, abstract, and reference list)
Follow full APA format
List References and Citations

3) Case Study PPT
Create a PowerPoint presentation for above Case Study Paper 8.1.

Writing Requirements

The PowerPoint should be a minimum of 12 slides
Include notes, citations and references
APA format

For The Case study I will submit what ever you have done and then Once I get the feedback you have to correct it accordingly...

BUSINESS ANALYTICS

Data Analysis and Decision Making

SEVENTH EDITION

S. Christian Albright Kelly School of Business,

Indiana University, Emeritus

Wayne L. Winston Kelly School of Business,

Indiana University, Emeritus

Australia • Brazil • Mexico • Singapore • United Kingdom • United States

09953_fm_ptg01_i-xxiv.indd 1 04/03/19 5:53 PM

Copyright 2020 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 it.

This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword for materials in your areas of interest.

Important Notice: Media content referenced within the product description or the product text may not be available in the eBook version.

Unless otherwise noted, all content is © Cengage

ALL RIGHTS RESERVED. No part of this work covered by the copyright herein may be reproduced or distributed in any form or by any means, except as permitted by U.S. copyright law, without the prior written permission of the copyright owner.

For product information and technology assistance, contact us at Cengage

Customer & Sales Support, 1-800-354-9706 or support.cengage.com.

For permission to use material from this text or product,

submit all requests online at www.cengage.com/permissions.

Library of Congress Control Number: 2019935644

Student Edition: ISBN: 978-0-357-10995-3

Loose-leaf Edition: ISBN: 978-0-357-10996-0

Cengage 20 Channel Center Street Boston, MA 02210 USA

Cengage is a leading provider of customized learning solutions with employees residing in nearly 40 different countries and sales in more than 125 countries around the world. Find your local representative at www. cengage.com.

Cengage products are represented in Canada by Nelson Education, Ltd.

To learn more about Cengage platforms and services, register or access your online learning solution, or purchase materials for your course, visit www.cengage.com.

Business Analytics: Data Analysis and Decision Making, 7e

S. Christian Albright and Wayne L. Winston

Senior Vice President, Higher Ed Product, Content, and Market Development: Erin Joyner

Product Director: Jason Fremder

Senior Product Manager: Aaron Arnsparger

Senior Learning Designer: Brandon Foltz

Content Manager: Conor Allen

Digital Delivery Lead: Mark Hopkinson

Product Assistant: Christian Wood

Marketing Manager: Chris Walz

Production Service: Lumina Datamatics Ltd.

Designer, Creative Studio: Erin Griffin

Text Designer: Erin Griffin

Cover Designer: Erin Griffin

Cover Image(s): Ron Dale/ShutterStock.com

Intellectual Property Analyst: Reba Frederics

Intellectual Property Project Manager: Nick

Barrows

Printed in the United States of America Print Number: 01 Print Year: 2019

09953_fm_ptg01_i-xxiv.indd 2 05/03/19 9:35 PM

To my wonderful wife Mary—my best friend and companion; and to Sam, Lindsay,

Teddy, and Archie S.C.A

To my wonderful family W.L.W.

09953_fm_ptg01_i-xxiv.indd 3 04/03/19 5:53 PM

S. Christian Albright got his B.S. degree in Mathematics from Stanford in 1968 and his PhD in Operations Research from Stanford in 1972. He taught in the Operations & Decision Technologies Department in the Kelley School of Business at Indiana University (IU) for close to 40 years, before retiring from teaching in 2011. While at IU, he taught courses in management science, computer simulation, statistics, and computer programming to all levels of business students, including undergraduates, MBAs, and doctoral students. In addition, he taught simula- tion modeling at General Motors and Whirlpool, and he taught database analysis for the Army. He published over 20 articles in leading operations research journals in the area of applied probabil- ity, and he has authored the books Statistics for Business and Economics, Practical Management Science, Spreadsheet Modeling and Applications, Data Analysis for Managers, and VBA for Mod- elers. He worked for several years after “retirement” with the Palisade Corporation developing training materials for its software products, he has developed a commercial version of his Excel® tutorial, called ExcelNow!, and he continues to revise his textbooks.

On the personal side, Chris has been married for 47 years to his wonderful wife, Mary, who retired several years ago after teaching 7th grade English for 30 years. They have one son, Sam, who lives in Philadelphia with his wife Lindsay and their two sons, Teddy and Archie. Chris has many interests outside the academic area. They include activities with his family, traveling with Mary, going to cultural events, power walking while listening to books on his iPod, and reading. And although he earns his livelihood from quantitative methods, his real passion is for playing classical piano music.

Wayne L. Winston taught in the Operations & Decision Technologies Department in the Kelley School of Business at Indiana University for close to 40 before retiring a few years ago. Wayne received his B.S. degree in Mathematics from MIT and his PhD in Operations Research from Yale. He has written the successful textbooks Operations Research: Applications and Algorithms, Mathematical Programming: Applications and Algorithms, Simulation Modeling Using @RISK, Practical Management Science, Data Analysis and Decision Making, Financial Models Using Simulation and Optimization, and Mathletics. Wayne has published more than 20 articles in lead- ing journals and has won many teaching awards, including the school-wide MBA award four times. He has taught classes at Microsoft, GM, Ford, Eli Lilly, Bristol-Myers Squibb, Arthur Andersen, Roche, PricewaterhouseCoopers, and NCR, and in “retirement,” he is currently teach- ing several courses at the University of Houston. His current interest is showing how spread- sheet models can be used to solve business problems in all disciplines, particularly in finance and marketing.

Wayne enjoys swimming and basketball, and his passion for trivia won him an appearance several years ago on the television game show Jeopardy!, where he won two games. He is married to the lovely and talented Vivian. They have two children, Gregory and Jennifer.

ABOUT THE AUTHORS

09953_fm_ptg01_i-xxiv.indd 4 04/03/19 5:53 PM

BRIEF CONTENTS Preface xvi

1 Introduction to Business Analytics 1

PART 1 Data Analysis 37 2 Describing the Distribution of a Variable 38 3 Finding Relationships among Variables 84 4 Business Intelligence (BI) Tools for Data Analysis 132

PART 2 Probability and Decision Making under Uncertainty 183 5 Probability and Probability Distributions 184 6 Decision Making under Uncertainty 242

PART 3 Statistical Inference 293 7 Sampling and Sampling Distributions 294 8 Confidence Interval Estimation 323 9 Hypothesis Testing 368

PART 4 Regression Analysis and Time Series Forecasting 411 10 Regression Analysis: Estimating Relationships 412 11 Regression Analysis: Statistical Inference 472 12 Time Series Analysis and Forecasting 523

PART 5 Optimization and Simulation Modeling 575 13 Introduction to Optimization Modeling 576 14 Optimization Models 630 15 Introduction to Simulation Modeling 717 16 Simulation Models 779

PART 6 Advanced Data Analysis 837 17 Data Mining 838 18 Analysis of Variance and Experimental Design (MindTap Reader only) 19 Statistical Process Control (MindTap Reader only) APPENDIX A: Quantitative Reporting (MindTap Reader only)

References 873

Index 875

09953_fm_ptg01_i-xxiv.indd 5 04/03/19 5:54 PM

CONTENTS Preface xvi

1 Introduction to Business Analytics 1

1-1 Introduction 3

1-2 Overview of the Book 4

1-2a The Methods 4 1-2b The Software 6

1-3 Introduction to Spreadsheet Modeling 8

1-3a Basic Spreadsheet Modeling: Concepts and Best Practices 9 1-3b Cost Projections 12 1-3c Breakeven Analysis 15 1-3d Ordering with Quantity Discounts and Demand Uncertainty 20 1-3e Estimating the Relationship between Price and Demand 24 1-3f Decisions Involving the Time Value of Money 29

1-4 Conclusion 33

PART 1 Data Analysis 37

2 Describing the Distribution of a Variable 38

2-1 Introduction 39

2-2 Basic Concepts 41

2-2a Populations and Samples 41 2-2b Data Sets, Variables, and Observations 41 2-2c Data Types 42

2-3 Summarizing Categorical Variables 45

2-4 Summarizing Numeric Variables 49

2-4a Numeric Summary Measures 49 2-4b Charts for Numeric Variables 57

2-5 Time Series Data 62

2-6 Outliers and Missing Values 69

2-7 Excel Tables for Filtering, Sorting, and Summarizing 71

2-8 Conclusion 77

Appendix: Introduction to StatTools 83

3 Finding Relationships among Variables 84

3-1 Introduction 85

3-2 Relationships among Categorical Variables 86

09953_fm_ptg01_i-xxiv.indd 6 04/03/19 5:54 PM

C O N T E N T S v i i

3-3 Relationships among Categorical Variables and a Numeric Variable 89

3-4 Relationships among Numeric Variables 96

3-4a Scatterplots 96 3-4b Correlation and Covariance 101

3-5 Pivot Tables 106

3-6 Conclusion 126

Appendix: Using StatTools to Find Relationships 131

4 Business Intelligence (BI) Tools for Data Analysis 132

4-1 Introduction 133

4-2 Importing Data into Excel with Power Query 134

4-2a Introduction to Relational Databases 134 4-2b Excel’s Data Model 139 4-2c Creating and Editing Queries 146

4-3 Data Analysis with Power Pivot 152

4-3a Basing Pivot Tables on a Data Model 154 4-3b Calculated Columns, Measures, and the DAX Language 154

4-4 Data Visualization with Tableau Public 162

4-5 Data Cleansing 172

4-6 Conclusion 178

PART 2 Probability and Decision Making under Uncertainty 183

5 Probability and Probability Distributions 184

5-1 Introduction 185

5-2 Probability Essentials 186

5-2a Rule of Complements 187 5-2b Addition Rule 187 5-2c Conditional Probability and the Multiplication Rule 188 5-2d Probabilistic Independence 190 5-2e Equally Likely Events 191 5-2f Subjective Versus Objective Probabilities 192

5-3 Probability Distribution of a Random Variable 194

5-3a Summary Measures of a Probability Distribution 195 5-3b Conditional Mean and Variance 198

5-4 The Normal Distribution 200

5-4a Continuous Distributions and Density Functions 200 5-4b The Normal Density Function 201 5-4c Standardizing: Z-Values 202 5-4d Normal Tables and Z-Values 204

09953_fm_ptg01_i-xxiv.indd 7 04/03/19 5:54 PM

v i i i C O N T E N T S

5-4e Normal Calculations in Excel 205 5-4f Empirical Rules Revisited 208 5-4g Weighted Sums of Normal Random Variables 208 5-4h Normal Distribution Examples 209

5-5 The Binomial Distribution 214

5-5a Mean and Standard Deviation of the Binomial Distribution 217 5-5b The Binomial Distribution in the Context of Sampling 217 5-5c The Normal Approximation to the Binomial 218 5-5d Binomial Distribution Examples 219

5-6 The Poisson and Exponential Distributions 226

5-6a The Poisson Distribution 227 5-6b The Exponential Distribution 229

5-7 Conclusion 231

6 Decision Making under Uncertainty 242

6-1 Introduction 243

6-2 Elements of Decision Analysis 244

6-3 EMV and Decision Trees 247

6-4 One-Stage Decision Problems 251

6-5 The PrecisionTree Add-In 254

6-6 Multistage Decision Problems 257

6.6a Bayes’ Rule 262 6-6b The Value of Information 267 6-6c Sensitivity Analysis 270

6-7 The Role of Risk Aversion 274

6-7a Utility Functions 275 6-7b Exponential Utility 275 6-7c Certainty Equivalents 278 6-7d Is Expected Utility Maximization Used? 279

6-8 Conclusion 280

PART 3 Statistical Inference 293

7 Sampling and Sampling Distributions 294

7-1 Introduction 295

7-2 Sampling Terminology 295

7-3 Methods for Selecting Random Samples 297

7-3a Simple Random Sampling 297 7-3b Systematic Sampling 301 7-3c Stratified Sampling 301

09953_fm_ptg01_i-xxiv.indd 8 04/03/19 5:54 PM

C O N T E N T S i x

7-3d Cluster Sampling 303 7-3e Multistage Sampling 303

7-4 Introduction to Estimation 305

7-4a Sources of Estimation Error 305 7-4b Key Terms in Sampling 306 7-4c Sampling Distribution of the Sample Mean 307 7-4d The Central Limit Theorem 312 7-4e Sample Size Selection 317 7-4f Summary of Key Ideas in Simple Random Sampling 318

7-5 Conclusion 320

8 Confidence Interval Estimation 323

8-1 Introduction 323

8-2 Sampling Distributions 325

8-2a The t Distribution 326 8-2b Other Sampling Distributions 327

8-3 Confidence Interval for a Mean 328

8-4 Confidence Interval for a Total 333

8-5 Confidence Interval for a Proportion 336

8-6 Confidence Interval for a Standard Deviation 340

8-7 Confidence Interval for the Difference between Means 343

8-7a Independent Samples 344 8-7b Paired Samples 346

8-8 Confidence Interval for the Difference between Proportions 348

8-9 Sample Size Selection 351

8-10 Conclusion 358

9 Hypothesis Testing 368

9-1 Introduction 369

9-2 Concepts in Hypothesis Testing 370

9-2a Null and Alternative Hypotheses 370 9-2b One-Tailed Versus Two-Tailed Tests 371 9-2c Types of Errors 372 9-2d Significance Level and Rejection Region 372 9-2e Significance from p-values 373 9-2f Type II Errors and Power 375 9-2g Hypothesis Tests and Confidence Intervals 375 9-2h Practical Versus Statistical Significance 375

9-3 Hypothesis Tests for a Population Mean 376

9-4 Hypothesis Tests for Other Parameters 380

09953_fm_ptg01_i-xxiv.indd 9 04/03/19 5:54 PM

x C O N T E N T S

9-4a Hypothesis Test for a Population Proportion 380 9-4b Hypothesis Tests for Difference between Population Means 382 9-4c Hypothesis Test for Equal Population Variances 388 9-4d Hypothesis Test for Difference between Population Proportions 388

9-5 Tests for Normality 395

9-6 Chi-Square Test for Independence 401

9-7 Conclusion 404

PART 4 Regression Analysis and Time Series Forecasting 411

10 Regression Analysis: Estimating Relationships 412

10-1 Introduction 413

10-2 Scatterplots: Graphing Relationships 415

10-3 Correlations: Indicators of Linear Relationships 422

10-4 Simple Linear Regression 424

10-4a Least Squares Estimation 424 10-4b Standard Error of Estimate 431 10-4c R-Square 432

10-5 Multiple Regression 435

10-5a Interpretation of Regression Coefficients 436 10-5b Interpretation of Standard Error of Estimate and R-Square 439

10-6 Modeling Possibilities 442

10-6a Dummy Variables 442 10-6b Interaction Variables 448 10-6c Nonlinear Transformations 452

10-7 Validation of the Fit 461

10-8 Conclusion 463

11 Regression Analysis: Statistical Inference 472

11-1 Introduction 473

11-2 The Statistical Model 474

11-3 Inferences About the Regression Coefficients 477

11-3a Sampling Distribution of the Regression Coefficients 478 11-3b Hypothesis Tests for the Regression Coefficients and p-Values 480 11-3c A Test for the Overall Fit: The ANOVA Table 481

11-4 Multicollinearity 485

11-5 Include/Exclude Decisions 489

11-6 Stepwise Regression 494

11-7 Outliers 499

09953_fm_ptg01_i-xxiv.indd 10 04/03/19 5:54 PM

C O N T E N T S x i

11-8 Violations of Regression Assumptions 504

11-8a Nonconstant Error Variance 504 11-8b Nonnormality of Residuals 504 11-8c Autocorrelated Residuals 505

11-9 Prediction 507

11-10 Conclusion 512

12 Time Series Analysis and Forecasting 523

12-1 Introduction 524

12-2 Forecasting Methods: An Overview 525

12-2a Extrapolation Models 525 12-2b Econometric Models 526 12-2c Combining Forecasts 526 12-2d Components of Time Series Data 527 12-2e Measures of Accuracy 529

12-3 Testing for Randomness 531

12-3a The Runs Test 534 12-3b Autocorrelation 535

12-4 Regression-Based Trend Models 539

12-4a Linear Trend 539 12-4b Exponential Trend 541

12-5 The Random Walk Model 544

12-6 Moving Averages Forecasts 547

12-7 Exponential Smoothing Forecasts 551

12-7a Simple Exponential Smoothing 552 12-7b Holt’s Model for Trend 556

12-8 Seasonal Models 560

12-8a Winters’ Exponential Smoothing Model 561 12-8b Deseasonalizing: The Ratio-to-Moving-Averages Method 564 12-8c Estimating Seasonality with Regression 565

12-9 Conclusion 569

PART 5 Optimization and Simulation Modeling 575

13 Introduction to Optimization Modeling 576

13-1 Introduction 577

13-2 Introduction to Optimization 577

13-3 A Two-Variable Product Mix Model 579

09953_fm_ptg01_i-xxiv.indd 11 04/03/19 5:54 PM

x i i C O N T E N T S

13-4 Sensitivity Analysis 590

13-4a Solver’s Sensitivity Report 590 13-4b SolverTable Add-In 593 13-4c A Comparison of Solver’s Sensitivity Report and SolverTable 599

13-5 Properties of Linear Models 600

13-6 Infeasibility and Unboundedness 602

13-7 A Larger Product Mix Model 604

13-8 A Multiperiod Production Model 612

13-9 A Comparison of Algebraic and Spreadsheet Models 619

13-10 A Decision Support System 620

13-11 Conclusion 622

14 Optimization Models 630

14-1 Introduction 631

14-2 Employee Scheduling Models 632

14-3 Blending Models 638

14-4 Logistics Models 644

14-4a Transportation Models 644 14-4b More General Logistics Models 651

14-5 Aggregate Planning Models 659

14-6 Financial Models 667

14-7 Integer Optimization Models 677

14-7a Capital Budgeting Models 678 14-7b Fixed-Cost Models 682 14-7c Set-Covering Models 689

14-8 Nonlinear Optimization Models 695

14-8a Difficult Issues in Nonlinear Optimization 695 14-8b Managerial Economics Models 696 14-8c Portfolio Optimization Models 700

14-9 Conclusion 708

15 Introduction to Simulation Modeling 717

15-1 Introduction 718

15-2 Probability Distributions for Input Variables 720

15-2a Types of Probability Distributions 721 15-2b Common Probability Distributions 724 15-2c Using @RISK to Explore Probability Distributions 728

15-3 Simulation and the Flaw of Averages 736

15-4 Simulation with Built-in Excel Tools 738

09953_fm_ptg01_i-xxiv.indd 12 04/03/19 5:54 PM

C O N T E N T S x i i i

15-5 Simulation with @RISK 747

15-5a @RISK Features 748 15-5b Loading @RISK 748 15-5c @RISK Models with a Single Random Input 749 15-5d Some Limitations of @RISK 758 15-5e @RISK Models with Several Random Inputs 758

15-6 The Effects of Input Distributions on Results 763

15-6a Effect of the Shape of the Input Distribution(s) 763 15-6b Effect of Correlated Inputs 766

15-7 Conclusion 771

16 Simulation Models 779

16-1 Introduction 780

16-2 Operations Models 780

16-2a Bidding for Contracts 780 16-2b Warranty Costs 784 16-2c Drug Production with Uncertain Yield 789

16-3 Financial Models 794

16-3a Financial Planning Models 795 16-3b Cash Balance Models 799 16-3c Investment Models 803

16-4 Marketing Models 810

16-4a Customer Loyalty Models 810 16-4b Marketing and Sales Models 817

16-5 Simulating Games of Chance 823

16-5a Simulating the Game of Craps 823 16-5b Simulating the NCAA Basketball Tournament 825

16-6 Conclusion 828

PART 6 Advanced Data Analysis 837

17 Data Mining 838

17-1 Introduction 839

17-2 Classification Methods 840

17-2a Logistic Regression 841 17-2b Neural Networks 846 17-2c Naïve Bayes 851 17-2d Classification Trees 854 17-2e Measures of Classification Accuracy 855 17-2f Classification with Rare Events 857

09953_fm_ptg01_i-xxiv.indd 13 04/03/19 5:54 PM

x i v C O N T E N T S

17-3 Clustering Methods 860

17-4 Conclusion 870

18 Analysis of Variance and Experimental Design (MindTap Reader only)

18-1 Introduction 18-2

18-2 One-Way ANOVA 18-5

18-2a The Equal-Means Test 18-5 18-2b Confidence Intervals for Differences Between Means 18-7 18-2c Using a Logarithmic Transformation 18-11

18-3 Using Regression to Perform ANOVA 18-15

18-4 The Multiple Comparison Problem 18-18

18-5 Two-Way ANOVA 18-22

18-5a Confidence Intervals for Contrasts 18-28 18-5b Assumptions of Two-Way ANOVA 18-30

18-6 More About Experimental Design 18-32

18-6a Randomization 18-32 18-6b Blocking 18-35 18-6c Incomplete Designs 18-38

18-7 Conclusion 18-40

19 Statistical Process Control (MindTap Reader only)

19-1 Introduction 19-2

19-2 Deming’s 14 Points 19-3

19-3 Introduction to Control Charts 19-6

19-4 Control Charts for Variables 19-8

19-4a Control Charts and Hypothesis Testing 19-13 19-4b Other Out-of-Control Indications 19-15 19-4c Rational Subsamples 19-16 19-4d Deming’s Funnel Experiment and Tampering 19-18 19-4e Control Charts in the Service Industry 19-22

19-5 Control Charts for Attributes 19-26

19-5a P Charts 19-26 19-5b Deming’s Red Bead Experiment 19-29

19-6 Process Capability 19-33

19-6a Process Capability Indexes 19-35 19-6b More on Motorola and 6-Sigma 19-40

19-7 Conclusion 19-43

09953_fm_ptg01_i-xxiv.indd 14 04/03/19 5:54 PM

C O N T E N T S x v

APPENDIX A: Quantitative Reporting (MindTap Reader only)

A-1 Introduction A-1

A-2 Suggestions for Good Quantitative Reporting A-2

A-2a Planning A-2 A-2b Developing a Report A-3 A-2c Be Clear A-4 A-2d Be Concise A-4 A-2e Be Precise A-5

A-3 Examples of Quantitative Reports A-6

A-4 Conclusion A-16

References 873

Index 875

09953_fm_ptg01_i-xxiv.indd 15 04/03/19 5:54 PM

PREFACE With today’s technology, companies are able to collect tremendous amounts of data with relative ease. Indeed, many com- panies now have more data than they can handle. However, before the data can be useful, they must be analyzed for trends, patterns, and relationships. This book illustrates in a practical way a variety of methods, from simple to complex, to help you analyze data sets and uncover important information. In many business contexts, data analysis is only the first step in the solution of a problem. Acting on the solution and the information it provides to make good decisions is a critical next step. Therefore, there is a heavy emphasis throughout this book on analytical methods that are useful in decision making. The meth- ods vary considerably, but the objective is always the same—to equip you with decision-making tools that you can apply in your business careers.

We recognize that the majority of students in this type of course are not majoring in a quantitative area. They are typically business majors in finance, marketing, operations management, or some other business discipline who will need to analyze data and make quantitative-based decisions in their jobs. We offer a hands-on, example-based approach and introduce fundamental concepts as they are needed. Our vehicle is spreadsheet software—specifically, Microsoft Excel®. This is a package that most students already know and will almost surely use in their careers. Our MBA students at Indiana University have been so turned on by the required course that is based on this book that almost all of them (mostly finance and marketing majors) have taken at least one of our follow-up elective courses in spreadsheet modeling. We are convinced that students see value in quantitative analysis when the course is taught in a practical and example-based approach.

Rationale for Writing This Book Business Analytics: Data Analysis and Decision Making is different from other textbooks written for statistics and management science. Our rationale for writing this book is based on four fundamental objectives.

• Integrated coverage and applications. The book provides a unified approach to business-related problems by integrat- ing methods and applications that have been traditionally taught in separate courses, specifically statistics and manage- ment science.

• Practical in approach. The book emphasizes realistic business examples and the processes managers actually use to analyze business problems. The emphasis is not on abstract theory or computational methods.

• Spreadsheet-based teaching. The book provides students with the skills to analyze business problems with tools they have access to and will use in their careers. To this end, we have adopted Excel and commercial spreadsheet add-ins.

• Latest tools. This is not a static field. The software keeps changing, and even the mathematical algorithms behind the software continue to evolve. Each edition of this book has presented the most recent tools in Excel and the accompanying Excel add-ins, and the current edition is no exception.

Integrated Coverage and Applications In the past, many business schools have offered a required statistics course, a required decision-making course, and a required management science course—or some subset of these. The current trend, however, is to have only one required course that cov- ers the basics of statistics, some regression analysis, some decision making under uncertainty, some linear programming, some simulation, and some advanced data analysis methods. Essentially, faculty in the quantitative area get one opportunity to teach all business students, so we attempt to cover a variety of useful quantitative methods. We are not necessarily arguing that this trend is ideal, but rather that it is a reflection of the reality at our university and, we suspect, at many others. After several years of teaching this course, we have found it to be a great opportunity to attract students to the subject and to more advanced study.

The book is also integrative in another important aspect. It not only integrates a number of analytical methods, but it also applies them to a wide variety of business problems—that is, it analyzes realistic examples from many business disciplines. We include examples, problems, and cases that deal with portfolio optimization, workforce scheduling, market share analysis, capital budgeting, new product analysis, and many others.

09953_fm_ptg01_i-xxiv.indd 16 04/03/19 5:54 PM

P R E F A C E x v i i

Practical in Approach This book has been designed to be very example-based and practical. We strongly believe that students learn best by working through examples, and they appreciate the material most when the examples are realistic and interesting. Therefore, our approach in the book differs in two important ways from many competitors. First, there is just enough conceptual development to give students an understanding and appreciation for the issues raised in the examples. We often introduce important concepts, such as standard deviation as a measure of variability, in the context of examples rather than discussing them in the abstract. Our experience is that students gain greater intuition and understanding of the concepts and applications through this approach.

Second, we place virtually no emphasis on hand calculations. We believe it is more important for students to understand why they are conducting an analysis and to interpret the results than to emphasize the tedious calculations associated with many analytical techniques. Therefore, we illustrate how powerful software can be used to create graphical and numerical outputs in a matter of seconds, freeing the rest of the time for in-depth interpretation of the results, sensitivity analysis, and alternative modeling approaches.

Spreadsheet-based Teaching We are strongly committed to teaching spreadsheet-based, example-driven courses, regardless of whether the basic area is data analysis or management science. We have found tremendous enthusiasm for this approach, both from students and from faculty around the world who have used our books. Students learn and remember more, and they appreciate the material more. In addition, instructors typically enjoy teaching more, and they usually receive immediate reinforcement through better teaching evaluations. We were among the first to move to spreadsheet-based teaching about two decades ago, and we have never regret- ted the move.

What We Hope to Accomplish in This Book Condensing the ideas in the previous paragraphs, we hope to:

• continue to make quantitative courses attractive to a wide audience by making these topics real, accessible, and interesting;

• give students plenty of hands-on experience with real problems and challenge them to develop their intuition, logic, and problem-solving skills;

• expose students to real problems in many business disciplines and show them how these problems can be analyzed with quantitative methods; and

• develop spreadsheet skills, including experience with powerful spreadsheet add-ins, that add immediate value to stu- dents’ other courses and for their future careers.

New in the Seventh Edition There are several important changes in this edition.

• New introductory material on Excel: Chapter 1 now includes an introductory section on spreadsheet modeling. This provides business examples for getting students up to speed in Excel and covers such Excel tools as IF and VLOOKUP functions, data tables, goal seek, range names, and more.

• Reorganization of probability chapters: Chapter 4, Probability and Probability Distributions, and Chapter 5, Normal, Binomial, Poisson, and Exponential Distributions, have been shortened slightly and combined into a single Chapter 5, Probability and Probability Distributions. This created space for the new Chapter 4 discussed next.

• New material on “Power BI” tools and data visualization: The previous chapters on Data Mining and Importing Data into Excel have been reorganized and rewritten to include an increased focus on the tools commonly included under the Business Analytics umbrella. There is now a new Chapter 4, Business Intelligence Tools for Data Analysis, which includes Excel’s Power Query tools for importing data into Excel, Excel’s Power Pivot add-in (and the DAX language) for even more powerful data analysis with pivot tables, and Tableau Public for data visualization. The old online Chapter 18, Importing Data into Excel, has been eliminated, and its material has been moved to this new Chapter 4.

09953_fm_ptg01_i-xxiv.indd 17 04/03/19 5:54 PM

x v i i i P R E F A C E

• Updated for Office 365, Windows or Mac: The 7th Edition is completely compatible with the latest version of Excel, and all screenshots in the book are from the latest version. However, because the changes from previous versions are not that extensive for Business Analytics purposes, the 7th Edition also works well even if you are still using Microsoft Office 2013, 2010, or 2007. Also, recognizing that many students are now using Macs, we have attempted to make the material compatible with Excel for Mac whenever possible.

• Updated Problems: Numerous problems have been modified to include the most updated data available. In addition, the DADM 7e Problem Database.xlsx file provides instructors with an entire database of problems. This file indicates the context of each of the problems and shows the correspondence between problems in this edition and problems in the previous edition.

• Less emphasis on add-ins (when possible): There is more emphasis in this edition on implementing spreadsheet calculations, especially statistical calculations, with built-in Excel tools rather than with add-ins. For example, there is no reliance on Palisade’s StatTools add-in in the descriptive statistics chapters 2 and 3 or in the confidence interval and hypothesis testing chapters 8 and 9. Nevertheless, Palisade’s add-ins are still relied on in chapters where they are really needed: PrecisionTree for decision trees in Chapter 6; StatTools for regression and time series analysis in Chapters 10, 11, and 12; @RISK for simulation in Chapters 15 and 16; and StatTools and NeuralTools for logistic regression and neu- ral networks in Chapter 17.

• New optional add-in: Although it is not an “official” part of the book, Albright wrote a DADM_Tools add-in for Excel (Windows or Mac), with tools for creating summary stats, histograms, correlations and scatterplots, regression, time series analysis, decision trees, and simulation. This add-in provides a “lighter” alternative to the Palisade add-ins and is freely available at https://kelley.iu.edu/albrightbooks/free_downloads.htm.

Software This book is based entirely on Microsoft Excel, the spreadsheet package that has become the standard analytical tool in busi- ness. Excel is an extremely powerful package, and one of our goals is to convert casual users into power users who can take full advantage of its features. If you learn no more than this, you will be acquiring a valuable skill for the business world. However, Excel has some limitations. Therefore, this book relies on several Excel add-ins to enhance Excel’s capabilities. As a group, these add-ins comprise what is arguably the most impressive assortment of spreadsheet-based software accompanying any book on the market.

DecisionTools® Suite Add-in The textbook website for Business Analytics: Data Analysis and Decision Making provides a link to the powerful DecisionTools® Suite by Palisade Corporation. This suite includes seven separate add-ins:

• @RISK, an add-in for simulation

• StatTools, an add-in for statistical data analysis

• PrecisionTree, a graphical-based add-in for creating and analyzing decision trees

• TopRank, an add-in for performing what-if analyses

• NeuralTools®, an add-in for estimating complex, nonlinear relationships

• EvolverTM, an add-in for performing optimization (an alternative to Excel’s Solver)

• BigPicture, a smart drawing add-in, useful for depicting model relationships

We use @RISK and PrecisionTree extensively in the chapters on simulation and decision making under uncertainty, and we use StatTools as necessary in the data analysis chapters. We also use BigPicture in the optimization and simulation chapters to provide a “bridge” between a problem statement and an eventual spreadsheet model.

Online access to the DecisionTools Suite, available with new copies of the book and for MindTap adopters, is an academic version, slightly scaled down from the professional version that sells for hundreds of dollars and is used by many leading companies. It functions for one year when properly installed, and it puts only modest limitations on the size of data sets or models that can be analyzed.

09953_fm_ptg01_i-xxiv.indd 18 04/03/19 5:54 PM

SolverTable Add-in We also include SolverTable, a supplement to Excel’s built-in Solver for optimization.1 If you have ever had difficulty under- standing Solver’s sensitivity reports, you will appreciate SolverTable. It works like Excel’s data tables, except that for each input (or pair of inputs), the add-in runs Solver and reports the optimal output values. SolverTable is used extensively in the optimization chapters.

Windows versus Mac We have seen an increasing number of students using Macintosh laptops rather than Windows laptops. These students have two basic options when using our book. The first option is to use the latest version of Excel for Mac. Except for a few advanced tools such as Power Pivot (discussed in Chapter 4), the Mac version of Excel is very similar to the Windows version. However, the Palisade and SolverTable add-ins will not work with Excel for Mac. Therefore, the second option, the preferable option, is to use a Windows emulation program (Bootcamp and Parallels are good candidates), along with Office for Windows. Students at Indiana have used this second option for years and have had no problems.

Software Calculations by Chapter This section indicates how the various calculations are implemented in the book. Specifically, it indicates which calculations are performed with built-in Excel tools and which require Excel add-ins.

Important note: The Palisade add-ins used in several chapters do not work in Excel for Mac. This is the primary reason Albright developed his own DADM_Tools add-in, which works in Excel for Windows and Excel for Mac. This add-in is freely available at the author’s website (https://kelley.iu.edu/albrightbooks/free_downloads.htm), together with a Word document on how to use it. However, it is optional and is not used in the book.

Chapter 1 – Introduction to Business Analytics

• The section on basic spreadsheet modeling is implemented with built-in Excel functions.

Chapter 2 – Describing the Distribution of a Variable

• Everything is implemented with built-in Excel functions and charts.

° Summary measures are calculated with built-in functions AVERAGE, STDEV.S, etc. ° Histograms and box plots are created with the Excel chart types introduced in 2016. ° Time series graphs are created with Excel line charts.

• Palisade’s StatTools add-in can do all of this. It isn’t used in the chapter, but it is mentioned in a short appendix, and an Intro to StatTools video is available.

• Albright’s DADM_Tools add-in can do all of this except for time series graphs.

Chapter 3 – Finding Relationships among Variables

• Everything is implemented with built-in Excel functions and charts.

° Summary measures of numeric variables, broken down by categories of a categorical variable, are calculated with built-in functions AVERAGE, STDEV.S, etc. (They are embedded in array formulas with IF functions.)

° Side-by-side box plots are created with the Excel box plot chart type introduced in 2016. ° Scatterplots are created with Excel scatter charts. ° Correlations are calculated with Excel’s CORREL function. A combination of the CORREL and INDIRECT func-

tions is used to create tables of correlations.

• StatTools can do all of this. It isn’t used in the chapter, but it is mentioned in a short appendix.

• DADM_Tools can do all of this.

1 SolverTable is available on this textbook’s website and on Albright’s website, www.kelley.iu.edu/albrightbooks.

P R E F A C E x i x

09953_fm_ptg01_i-xxiv.indd 19 04/03/19 5:54 PM

Chapter 4 - Business Intelligence (BI) Tools for Data Analysis

• Queries are implemented with Excel’s Power Query tools (available only in Excel for Windows).

• Pivot table analysis is implemented with a combination of Excel’s Data Model and the Power Pivot add-in (available only in Excel for Windows).

• Tableau Public (a free program for Windows or Mac) is used for data visualization.

Chapter 5 – Probability and Probability Distributions

• All calculations are performed with built-in Excel functions.

Chapter 6 – Decision Making Under Uncertainty

• Decision trees are implemented with Palisade’s PrecisionTree add-in.

• DADM_Tools implements decision trees.

Chapter 7 – Sampling and Sampling Distributions

• All calculations are performed with built-in Excel functions.

Chapter 8 – Confidence Interval Estimation

• Everything is implemented with Excel functions, which are embedded in a Confidence Interval Template.xlsx file developed by Albright. This isn’t an add-in; it is a regular Excel file where the confidence interval formulas have already been created, and users only need to enter their data.

• StatTools can do all of this, but it isn’t used in the chapter.

• DADM_Tools doesn’t perform confidence interval calculations.

Chapter 9 – Hypothesis Testing

• Everything is implemented with Excel functions, which are embedded in Hypothesis Test Template.xlsx and Normality Tests Template.xlsx files developed by Albright. These aren’t add-ins; they are regular Excel files where the hypothesis test formulas have already been created, and users only need to enter their data.

• StatTools can do all of this, but it isn’t used in the chapter.

• DADM_Tools doesn’t perform hypothesis test calculations.

Chapters 10, 11 – Regression Analysis

• StatTools is used throughout to perform the regression calculations.

• Excel’s built-in regression functions (SLOPE, INTERCEPT, etc.) are illustrated for simple linear regression, but they are used sparingly.

• Excel’s Analysis ToolPak add-in is mentioned, but it isn’t used.

• DADM_Tools implements regression calculations.

Chapter 12 – Time Series Analysis and Forecasting

• Autocorrelations and the runs test for randomness are implemented with Excel functions, which are embedded in Auto- correlation Template.xlsx and Runs Test Template.xlsx files developed by Albright. These aren’t add-ins; they are reg- ular Excel files where the relevant formulas have already been created, and users only need to enter their data.

• Moving averages and exponential smoothing are implemented in StatTools.

• DADM_Tools implements moving averages and exponential smoothing calculations.

x x P R E F A C E

09953_fm_ptg01_i-xxiv.indd 20 04/03/19 5:54 PM

Chapters 13, 14 – Optimization

• The optimization is performed with Excel’s Solver add-in.

• Albright’s SolverTable add-in performs sensitivity analysis on optimal solutions. It works in Excel for Windows but not in Excel for Mac.

Chapters 15, 16 – Simulation

• Built-in Excel functions for generating random numbers from various distributions are illustrated, and the Excel-only way of running simulations with data tables is shown in an introductory example.

• Palisade’s @RISK add-in is used in the rest of the examples.

• DADM_Tools implements simulation.

Chapter 17 – Data Mining

• Logistic regression is implemented with StatTools.

• Neural networks are implemented with Palisade’s NeuralTools add-in.

• Other calculations are implemented with Excel functions.

Chapter 18 – Analysis of Variance and Experimental Design (online)

• Most of the chapter is implemented with StatTools.

• DADM_Tools doesn’t perform ANOVA calculations.

Chapter 19 – Statistical Process Control

• All control charts are implemented with StatTools.

• DADM_Tools doesn’t implement control charts.

Potential Course Structures Although we have used the book for our own required one-semester course, there is admittedly much more material than can be covered adequately in one semester. We have tried to make the book as modular as possible, allowing an instructor to cover, say, simulation before optimization or vice-versa, or to omit either of these topics. The one exception is statistics. Due to the natural progression of statistical topics, the basic topics in the early chapters should be covered before the more advanced topics (regression and time series analysis) in the later chapters. With this in mind, there are several possible ways to cover the topics.

• One-semester Required Course, with No Statistics Prerequisite (or where MBA students need a refresher for whatever statistics they learned previously): If data analysis is the primary focus of the course, then Chapters 2–3, 5, 7–11 should be covered. Depending on the time remaining, any of the topics in Chapters 4 and 17 (advanced data anal- ysis tools), Chapters 6 (decision making under uncertainty), 12 (time series analysis), 13–14 (optimization), or 15–16 (simulation) can be covered in practically any order.

• One-semester Required Course, with a Statistics Prerequisite: Assuming that students know the basic elements of statistics (up through hypothesis testing), the material in Chapters 2–3, 5, and 7–9 can be reviewed quickly, primarily to illustrate how Excel and add-ins can be used to do the number crunching. The instructor can then choose among any of the topics in Chapters 4, 6, 10–11, 12, 13–14, 15–16 (in practically any order), or 17 to fill the remainder of the course.

• Two-semester Required Sequence: Given the luxury of spreading the topics over two semesters, the entire book, or at least most of it, can be covered. The statistics topics in Chapters 2–3 and 7–9 should be covered in chronological order before other statistical topics (regression and time series analysis), but the remaining chapters can be covered in practi- cally any order.

P R E F A C E x x i

09953_fm_ptg01_i-xxiv.indd 21 04/03/19 5:54 PM

x x i i P R E F A C E

Instructor Supplements Textbook Website: www.cengage.com/decisionsciences/albright/ba/7e The companion website provides immediate access to an array of teaching resources—including data and solutions files for all of the Examples, Problems, and Cases in the book, Test Bank files, PowerPoint slides, and access to the DecisionTools® Suite by Palisade Corporation and the SolverTable add-in. Instructors who want to compare the problems in the previ- ous edition of this text to the problems in this edition can also download the file DADM 7e Problem Database.xlsx which details that correlation. You can easily download the instructor resources you need from the password-protected, instructor-only section of the site.

Test Bank Cengage Learning Testing Powered by Cognero® is a flexible, online system that allows you to import, edit, and manipulate content from the text’s Test Bank or elsewhere, including your own favorite test questions; create multiple test versions in an instant; and deliver tests from your LMS, your classroom, or wherever you want.

Student Supplements Textbook Website: www.cengage.com/decisionsciences/albright/ba/7e Every new student edition of this book comes with access to the Business Analytics: Data Analysis and Decision Making, 7e textbook website that links to the following files and tools:

• Excel files for the examples in the chapters (usually two versions of each— a template, or data-only version, and a finished version)

• Data files required for the Problems and Cases

• Excel Tutorial for Windows.xlsx, which contains a useful tutorial for getting up to speed in Excel (Excel Tutorial for the Mac.xlsx is also available)

• DecisionTools® Suite software by Palisade Corporation

• SolverTable add-in

09953_fm_ptg01_i-xxiv.indd 22 04/03/19 5:54 PM

P R E F A C E x x i i i

Acknowledgements We are also grateful to many of the professionals who worked behind the scenes to make this book a success: Aaron Arnsparger, Senior Product Manager; Brandon Foltz, Senior Learning Designer; Conor Allen, Content Manager; Project Manager, Anubhav Kaushal; and Marketing Manager, Chris Walz.

We also extend our sincere appreciation to the reviewers who provided feedback on the authors’ proposed changes that resulted in this seventh edition:

John Aloysius, Walton College of Business, University of Arkansas

Henry F. Ander, Arizona State University

Dr. Baabak Ashuri, School of Building Construction, Georgia Institute of Technology

James Behel, Harding University

Robert H. Burgess, Scheller College of Business, Georgia Institute of Technology

Paul Damien, McCombs School of Business, University of Texas in Austin

Parviz Ghandforoush, Virginia Tech

Betsy Greenberg, University of Texas

Anissa Harris, Harding University

Tim James, Arizona State University

Norman Johnson, C.T. Bauer College of Business, University of Houston

Shivraj Kanungo, The George Washington University

Miguel Lejeune, The George Washington University

José Lobo, Arizona State University

Stuart Low, Arizona State University

Lance Matheson, Virginia Tech

Patrick R. McMullen, Wake Forest University

Barbara A. Price, PhD, Georgia Southern University

Laura Wilson-Gentry, University of Baltimore

Toshiyuki Yuasa, University of Houston

S. Christian Albright

Wayne L. Winston

April 2019

09953_fm_ptg01_i-xxiv.indd 23 04/03/19 5:54 PM

09953_fm_ptg01_i-xxiv.indd 24 04/03/19 5:54 PM

CHAPTER 1 Introduction to Business Analytics

BUSINESS ANALYTICS PROVIDES INSIGHTS AND IMPROVES PERFORMANCE This book is about analyzing data and using quantitative modeling to help companies understand their business, make better decisions, and improve performance. We have been teaching the methods discussed in this book for decades, and companies have been using these methods for decades to improve performance and save millions of dollars. There- fore, we were a bit surprised when a brand new term, Busi- ness Analytics (BA), became hugely popular several years ago. All of a sudden, BA promised to be the road to success. By using quantitative BA methods—data analysis, optimiza-

tion, simulation, prediction, and others—companies could drastically improve business performance. Haven’t those of us in our field been doing this for years? What is different about BA that has made it so popular, both in the academic world and even more so in the business world?

The truth is that BA does use the same quantitative methods that have been used for years, the same methods you will learn in this book. BA has not all of a sudden invented brand new quantitative methods to eclipse traditional methods. The main difference is that BA uses big data to solve business problems and provide insights. Companies now have access to huge sources of data, and the technology is now available to use huge data sets for quantitative analysis, predictive modeling, optimization, and simulation. In short, the same quantitative methods that have been used for years can now be even more effective by utilizing big data and the corresponding technology.

For a quick introduction to BA, you should visit the BA Wikipedia site (search the Web for “business analytics”). Among other things, it lists areas where BA plays a prom- inent role, including the following: retail sales analytics; financial services analytics; risk and credit analytics; marketing analytics; pricing analytics; supply chain analytics; and transportation analytics. If you glance through the examples and problems in this book, you will see that most of them come from these same areas. Again, the difference is that we use relatively small data sets to get you started—we do not want to overwhelm you with gigabytes of data—whereas real applications of BA use huge data sets to advantage.

A more extensive discussion of BA can be found in the Fall 2011 research report, Analytics: The Widening Divide, published in the MIT Sloan Management Review in collaboration with IBM, a key developer of BA software (search the Web for the arti- cle’s title). This 22-page article discusses what BA is and provides several case studies. In addition, it lists three key competencies people need to compete successfully in the BA world—and hopefully you will be one of these people.

• Competency 1: Information management skills to manage the data. This competency involves expertise in a variety of techniques for managing data. Given the key role of data in BA methods, data quality is extremely important. With data coming from a number of disparate sources, both internal and external to an organization, achieving data quality is no small feat.

St oc

kL ite

/S hu

tte rs

to ck

.c om

09953_ch01_ptg01_001-036.indd 1 04/03/19 10:51 PM

2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

• Competency 2: Analytics skills and tools to understand the data. We were not surprised, but rather very happy, to see this competency listed among the requirements because these skills are exactly the skills we cover throughout this book—optimization with advanced quantitative algorithms, simulation, and others.

• Competency 3: Data-oriented culture to act on the data. This refers to the culture within the organization. Everyone involved, especially top management, must believe strongly in fact-based decisions arrived at using analytical methods.

The article argues persuasively that the companies that have these competencies and have embraced BA have a distinct competitive advantage over companies that are just start- ing to use BA methods or are not using them at all. This explains the title of the article. The gap between companies that embrace BA and those that do not will only widen in the future.

This field of BA and, more specifically, data analysis is progressing very quickly, not only in academic areas but also in many applied areas. By analyzing big data sets from various sources, people are learning more and more about the way the world works. One recent book, Everybody Lies: Big Data New Data, and What the Internet Can Tell Us About Who We Really Are, by Stephens-Davidowitz (2017), is especially interesting. The title of the book suggests that people lie to themselves, friends, Face- book, and surveys, but they don’t lie on Google searches. The author, a social scientist, has focused on the data from Google searches to study wide-ranging topics, including economics, finance, education, sports, gambling, racism, and sex. Besides his insightful and often unexpected findings, the author presents several general conclusions about analysis with big data:

• Not surprisingly, insights are more likely to be obtained when analyzing data in novel ways, such as analyzing the organ sizes of race horses instead of their pedigrees. (A data analyst used data on race horse heart size to predict that American Pharaoh would be great. The horse went on to win the Triple Crown in 2015, the first horse to do so since 1978.) On the other hand, if all available data have been analyzed in every conceivable way, as has been done with stock market data, even analysis with big data is unlikely to produce breakthroughs.

• Huge data sets allow analysts to zoom in on smaller geographical areas or smaller subsets of a population for keener insights. For example, when analyzing why some areas of the U.S. produce more people who warrant an entry on Wikipedia, you might find that richer and more educated states have more success. However, with a data set of more than 150,000 Americans of Wikipedia fame, the author was able to pinpoint that a disproportionate number were born in college towns. Traditional small data sets don’t allow drilling down to this level of detail.

• Companies are increasingly able to run controlled experiments, usually on the Web, to learn the causes of behavior. The new term for these experiments is A/B testing. The purpose of a test is to determine whether a person prefers A or B. For example, Google routinely runs A/B tests, showing one randomly selected group of customers one thing and a second randomly selected group another. The difference could be as slight as the presence or absence of an arrow link on a Web page. Google gets immediate results from such tests by studying the customers’ clicking behavior. The company can then use the results of one test to lead to another follow-up test. A/B testing is evidently happening all the time, whether or not we’re aware of it.

• A potential drawback of analyzing big data is dimensionality, where there are so many potential explanatory variables that one will almost surely look like a winner—even though it isn’t. The author illustrates this by imagining daily coin flips of 1000 coins and tracking which of them comes up heads on days when the stock market is up. One of these coins, just by chance, is likely to correlate highly with the stock market, but this coin is hardly a useful predictor of future stock market movement.

09953_ch01_ptg01_001-036.indd 2 04/03/19 10:51 PM

1-1 Introduction 3

The analysis of big data will not solve all the world’s problems, and there are ethical issues about studying people’s behavior—and acting on the findings—from Web-based data such as Google searches. However, the potential for new and important insights into human behavior is enormous.

1-1 Introduction We are living in the age of technology. This has two important implications for every- one entering the business world. First, technology has made it possible to collect huge amounts of data. Technology companies like Google and Amazon capture click data from websites, retailers collect point-of-sale data on products and customers every time a trans- action occurs; credit agencies collect data on people who have or would like to obtain credit; investment companies have a limitless supply of data on the historical patterns of stocks, bonds, and other securities; and government agencies have data on economic trends, the environment, social welfare, consumer product safety, and virtually everything else imaginable. It has become relatively easy to collect the data. As a result, data are plen- tiful. However, as many organizations have discovered, it is a challenge to make sense of the data they have collected.

A second important implication of technology is that it has given many more people the power and responsibility to analyze data and make decisions on the basis of quantitative analysis. People entering the business world can no longer pass all the quantitative analy- sis to the “quant jocks,” the technical specialists who have traditionally done the number crunching. The vast majority of employees now have a computer at their disposal, access to relevant data, and training in easy-to-use software, particularly spreadsheet and database software. For these employees, quantitative methods are no longer forgotten topics they once learned in college. Quantitative analysis is now an essential part of their daily jobs.

A large amount of data already exists, and it will only increase in the future. Many companies already complain of swimming in a sea of data. However, enlightened com- panies are seeing this expansion as a source of competitive advantage. In fact, one of the hottest topics in today’s business world is business analytics, also called data analytics. These terms have been created to encompass the types of analysis discussed in this book, so they aren’t really new; we have been teaching them for years. The new aspect of busi- ness analytics is that it typically implies the analysis of very large data sets, the kind that companies currently encounter. For this reason, the term big data has also become popular. By using quantitative methods to uncover the information from the data and then act on this information—again guided by quantitative analysis—companies are able to gain advantages over their less enlightened competitors. Here are several pertinent examples.

• Direct marketers analyze enormous customer databases to see which customers are likely to respond to various products and types of promotions. Marketers can then target different classes of customers in different ways to maximize profits—and give their customers what they want.

• Hotels and airlines also analyze enormous customer databases to see what their customers want and are willing to pay for. By doing this, they have been able to devise very clever pricing strategies, where different customers pay different prices for the same accommodations. For example, a business traveler typically makes a plane reservation closer to the time of travel than a vacationer. The airlines know this. Therefore, they reserve seats for these business travelers and charge them a higher price for the same seats. The airlines profit from clever pricing strategies, and the customers are happy.

• Financial planning services have a virtually unlimited supply of data about security prices, and they have customers with widely differing preferences for various types of investments. Trying to find a match of investments to customers is a very

09953_ch01_ptg01_001-036.indd 3 04/03/19 10:51 PM

4 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

challenging problem. However, customers can easily take their business elsewhere if good decisions are not made on their behalf. Therefore, financial planners are under extreme competitive pressure to analyze masses of data so that they can make informed decisions for their customers.1

• We all know about the pressures U.S. manufacturing companies have faced from foreign competition in the past few decades. The automobile companies, for example, have had to change the way they produce and market automobiles to stay in business. They have had to improve quality and cut costs by orders of magnitude. Although the struggle continues, much of the success they have had can be attributed to data analysis and wise decision making. Starting on the shop floor and moving up through the organization, these companies now measure almost everything, analyze these measurements, and then act on the results of their analysis.

We talk about companies analyzing data and making decisions. However, companies don’t really do this; people do it. And who will these people be in the future? They will be you! We know from experience that students in all areas of business, at both the undergraduate and graduate level, will be required to analyze large complex data sets, run regression anal- yses, make quantitative forecasts, create optimization models, and run simulations. You are the person who will be analyzing data and making important decisions to help your com- pany gain a competitive advantage. And if you are not willing or able to do so, there will be plenty of other technically trained people who will be more than happy to do it.

The goal of this book is to teach you how to use a variety of quantitative methods to analyze data and make decisions in a very hands-on way. We discuss a number of quan- titative methods and illustrate their use in a large variety of realistic business situations. As you will see, this book includes many examples from finance, marketing, operations, accounting, and other areas of business. To analyze these examples, we take advantage of the Microsoft Excel® spreadsheet software, together with several powerful Excel add-ins. In each example we provide step-by-step details of the method and its implementation in Excel.

This is not a “theory” book. It is also not a book where you can lean comfortably back in your chair and read about how other people use quantitative methods. It is a “get your hands dirty” book, where you will learn best by actively following the examples through- out the book on your own computer. By the time you have finished, you will have acquired some very useful skills for today’s business world.

1-2 Overview of the Book This section provides an overview of the methods covered in this book and the software that is used to implement them. Then the rest of the chapter illustrates how some of Excel’s basic tools can be used to solve quantitative problems.

1-2a The Methods This book is rather unique in that it combines topics from two separate fields: statistics and management science. Statistics is the area of data analysis, whereas management sci- ence is the area of model building, optimization, and decision making. In the academic arena these two fields have traditionally been separated, sometimes widely. Indeed, they are often housed in separate academic departments. However, both are useful in accom- plishing what the title of this book promises: data analysis and decision making.

Therefore, we do not distinguish between the statistics and the management science parts of this book. Instead, we view the entire book as a collection of useful quantita- tive methods for analyzing data and helping to make business decisions. In addition, our

1 For a great overview of how quantitative techniques have been used in the financial world, read the book The Quants, by Scott Patterson. It describes how quantitative models made millions for a lot of bright young analysts, but it also describes the dangers of relying totally on quantitative models, at least in the complex world of global finance.

09953_ch01_ptg01_001-036.indd 4 04/03/19 10:51 PM

1-2 Overview of the Book 5

choice of software helps to integrate the various topics. By using a single package, Excel, together with several Excel add-ins, you will see that the methods of statistics and man- agement science are similar in many important respects.

Three important themes run through this book. Two of them are in the title: data anal- ysis and decision making. The third is dealing with uncertainty.2 Each of these themes has subthemes. Data analysis includes data description, data visualization, data inference, and the search for relationships in data. Decision making includes optimization techniques for problems with no uncertainty, decision analysis for problems with uncertainty, and structured sensitivity analysis. Dealing with uncertainty includes measuring uncertainty and modeling uncertainty explicitly. There are obvious overlaps between these themes and subthemes. When you make inferences from data and search for relationships in data, you must deal with uncertainty. When you use decision trees to help make decisions, you must deal with uncertainty. When you use simulation models to help make decisions, you must deal with uncertainty, and then you often make inferences from the simulation results.

Figure 1.1 shows where these themes and subthemes are discussed in the book. The next few paragraphs discuss the book’s contents in more detail.

2 The fact that the uncertainty theme did not find its way into the title of this book does not detract from its importance. We just wanted to keep the title reasonably short.

Figure 1.1 Themes and Subthemes

We begin in Chapters 2, 3, and 4 by illustrating a number of ways to summarize the information in data sets. These include graphical and tabular summaries, as well as numeric summary measures such as means, medians, and standard deviations. As stated earlier, organizations are now able to collect huge amounts of raw data, but what does it all mean? Although there are very sophisticated methods for analyzing data, some of which are covered in later chapters, the “simple” methods in Chapters 2, 3, and 4 are crucial for obtaining an initial understanding of the data. Fortunately, Excel and available add-ins now make this quite easy. For example, Excel’s pivot table tool for “slicing and dicing” data is an analyst’s dream come true. You will be amazed at the insights you can gain from pivot tables—with very little effort.

Uncertainty is a key aspect of most business problems. To deal with uncertainty, you need a basic understanding of probability. We discuss the key concepts in Chapter 5. This chapter covers basic rules of probability and then discusses the extremely important con- cept of probability distributions, with emphasis on two of the most important probability distributions, the normal and binomial distributions.

Themes Subthemes Chapters Where Emphasized

2−4, 10, 12, 17

7−9, 11, 18−19

3, 10−12, 17−18

6, 13−16

5−12, 15−16, 18−19

5−6, 10−12, 15−16, 18−19

13, 14

Data Analysis

Description

Inference

Relationships

Optimization

Decision Analysis with Uncertainty

Sensitivity Analysis

Measuring

Modeling

Decision Making

Uncertainty

09953_ch01_ptg01_001-036.indd 5 04/03/19 10:51 PM

6 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

In Chapter 6 we apply probability to decision making under uncertainty. These types of problems—faced by all companies on a continual basis—are characterized by the need to make a decision now, even though important information, such as demand for a product or returns from investments, will not be known until later. The methods in Chapter 6 pro- vide a rational basis for making such decisions.

In Chapters 7, 8, and 9 we discuss sampling and statistical inference. Here the basic problem is to estimate one or more characteristics of a population. If it is too expensive or time-consuming to learn about the entire population—and it usually is—it is instead com- mon to select a random sample from the population and then use the information in the sample to infer the characteristics of the population.

In Chapters 10 and 11 we discuss the extremely important topic of regression analy- sis, which is used to study relationships between variables. The power of regression anal- ysis is its generality. Every part of a business has variables that are related to one another, and regression can often be used to estimate relationships between these variables.

From regression, we move to time series analysis and forecasting in Chapter 12. This topic is particularly important for providing inputs into business decision problems. For example, manufacturing companies must forecast demand for their products to make sen- sible decisions about order quantities from their suppliers. Similarly, fast-food restaurants must forecast customer arrivals, sometimes down to the level of 15-minute intervals, so that they can staff their restaurants appropriately. Chapter 12 illustrates some of most fre- quently used forecasting methods.

Chapters 13 and 14 are devoted to spreadsheet optimization. We assume a company must make several decisions, and there are constraints that limit the possible decisions. The job of the decision maker is to choose the decisions such that all the constraints are satisfied and an objective, such as total profit or total cost, is optimized. The solution pro- cess consists of two steps. The first step is to build a spreadsheet model that relates the decision variables to other relevant quantities by means of logical formulas. The second step is then to find the optimal solution. Fortunately, Excel contains a Solver add-in that performs the optimization. All you need to do is specify the objective, the decision vari- ables, and the constraints; Solver then uses powerful algorithms to find the optimal solu- tion. As with regression, the power of this approach is its generality.

Chapters 15 and 16 illustrate a number of computer simulation models. As mentioned earlier, most business problems have some degree of uncertainty. The demand for a prod- uct is unknown, future interest rates are unknown, the delivery lead time from a supplier is unknown, and so on. Simulation allows you to build this uncertainty explicitly into spread- sheet models. Some cells in the model contain random values with given probability dis- tributions. Every time the spreadsheet recalculates, these random values change, which causes “bottom-line” output cells to change as well. The trick then is to force the spread- sheet to recalculate many times and keep track of interesting outputs. In this way you can see an entire distribution of output values that might occur, not just a single best guess.

Chapter 17 then returns to data analysis. It provides an introduction to data mining, a topic of increasing importance in today’s data-driven world. Data mining is all about exploring data sets, especially large data sets, for relationships and patterns that can help companies gain a competitive advantage. It employs a number of relatively new technolo- gies to implement various algorithms, several of which are discussed in this chapter.

Finally, there are two online chapters, 18 and 19, that complement topics included in the book itself. Chapter 18 discusses analysis of variance (ANOVA) and experimen- tal design. Chapter 19 discusses quality control and statistical process control. These two online chapters follow the same structure as the chapters in the book, complete with many examples and problems.

1-2b The Software The methods in this book can be used to analyze a wide variety of business problems. However, they are not of much practical use unless you have the software to implement them. Very few business problems are small enough to be solved with pencil and paper. They require powerful software.

09953_ch01_ptg01_001-036.indd 6 04/03/19 10:51 PM

1-2 Overview of the Book 7

The software available with this book, together with Microsoft Excel, provides you with a powerful combination. This software is being used—and will continue to be used— by leading companies all over the world to analyze large, complex problems. We firmly believe that the experience you obtain with this software, through working the examples and problems in the book, will give you a key competitive advantage in the business world.

It all begins with Excel. Almost all the quantitative methods discussed in the book are implemented in Excel. We cannot forecast the state of computer software in the long-term future, but Excel is currently the most heavily used spreadsheet package on the market, and there is every reason to believe that this state will persist for many years. Most compa- nies use Excel, most employees and most students have been trained in Excel, and Excel is a very powerful, flexible, and easy-to-use package.

It helps to understand Microsoft’s versions of Excel. Until recently, we referred to Excel 2007, Excel 2010, and so on. Every few years, Microsoft released a new version of Office, and hence Excel. The latest was Excel 2016, and the next is going to be Excel 2019, which might be out by the time you read this. Each of these versions is basically fixed. For example, if you had Excel 2013, you had to wait for Excel 2016 to get the newest features. However, within the past few years, Microsoft has offered a subscription service, Office 365, which allows you to install free updates about once per month. Will Microsoft eventually discontinue the fixed versions and push everyone to the subscription service? We don’t know, but it would be nice. Then we could assume that everyone had the same version of Excel, with all the latest features. Actually, this isn’t quite true. There are different levels of Microsoft Office 365, some less expensive but with fewer features. You can find details on Microsoft’s website. This book assumes you have the features in Excel 2016 or the ProPlus version of Office 365. You can see your version by opening Excel and selecting Account from the File menu.

We also realize that many of you are using Macs. There is indeed a version of Office 365 for the Mac, but its version of Excel doesn’t yet have all the features of Excel for Windows. For example, we discuss two of Microsoft’s “power” tools, Power Query and Power Pivot, in Chapter 4. At least as of this writing, these tools are not available in Excel for Mac. On the other hand, some features, including pivot charts, histograms, and box plots, were not in Office 365 for Mac until recently, when they suddenly appeared. So Microsoft is evidently catching up. In addition, some third-party add-ins for Excel, notably the Palisade DecisionTools Suite used in some chapters, are not available for Macs and probably never will be. If you really need these features and want to continue using your Mac, your best option is to install Windows emulation software such as Boot Camp or Parallels. Our students at Indiana have been doing this successfully for years.

Built-in Excel Features Virtually everyone in the business world knows the basic features of Excel, but relatively few know some of its more powerful features. In short, relatively few people are the “power users” we expect you to become by working through this book. To get you started, the files Excel Tutorial for Windows.xlsx and Excel Tutorial for the Mac.xlsx explain some of the “intermediate” features of Excel—features that we expect you to be able to use. (See the Preface for instructions on how to access the resources that accompany this textbook.) These include the SUMPRODUCT, VLOOKUP, IF, NPV, and COUNTIF, functions. They also include range names, data tables, Paste Special, Goal Seek, and many others. Finally, although we assume you can perform routine spreadsheet tasks such as copying and pasting, the tutorial provides many tips to help you perform these tasks more efficiently.3

In addition to this tutorial, the last half of this chapter presents several examples of modeling quantitative problems in Excel. These examples provide a head start in using Excel tools you will use in the rest of the book. Then later chapters will introduce other useful Excel tools as they are needed.

3 Albright and several colleagues have created a more robust commercial version of this tutorial called excelNow!. The Excel Tutorial files explain how you can upgrade to this commercial version.

09953_ch01_ptg01_001-036.indd 7 04/03/19 10:51 PM

8 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

Analysis ToolPak All versions of Excel, extending back at least two decades, have included an add-in called Analysis ToolPak. It has several tools for data analysis, including correlation, regression, and inference. Unfortunately, Microsoft hasn’t updated Analysis ToolPak for years, so it is not as good as it could be. We will mention Analysis ToolPak a few times throughout the book, but we will use other Excel tools whenever they are available.

Solver Add-in Chapters 13 and 14 make heavy use of Excel’s Solver add-in. This add-in, developed by Frontline Systems®, not Microsoft, uses powerful algorithms to perform spreadsheet opti- mization. Before this type of spreadsheet optimization add-in was available, specialized (nonspreadsheet) software was required to solve optimization problems. Now you can do it all within the familiar Excel environment.

SolverTable Add-in An important theme throughout this book is sensitivity analysis: How do outputs change when inputs change? Typically these changes are made in spreadsheets with a data table, a built-in Excel tool. However, data tables don’t work in optimization models, where the goal is to see how the optimal solution changes when certain inputs change. Therefore, we include an Excel add-in called SolverTable, which works almost exactly like Excel’s data tables. (This add-in was developed for Excel for Windows by Albright. Unfortunately, it doesn’t work for Excel for Mac.) Chapters 13 and 14 illustrate the use of SolverTable.

Palisade DecisionTools Suite In addition to SolverTable and built-in Excel add-ins, an educational version of Palisade Corporation’s powerful DecisionTools® Suite is available. All programs in this suite are Excel add-ins, so the learning curve isn’t very steep. There are seven separate add-ins in this suite: @RISK, BigPicture, StatTools, PrecisionTree, NeuralTools, TopRank, and Evolver. The add-ins we will use most often are StatTools (for data analysis), Precision- Tree (for decision trees), and @RISK (for simulation). These add-ins will be discussed in some detail in the chapters where they are used.

DADM_Tools Add-In We realize that some of you prefer not to use the Palisade software because it might not be available in companies where your students are eventually employed. Nevertheless, some of the methods discussed in the book, particularly decision trees and simulation, are difficult to implement with Excel tools only. Therefore, Albright recently developed an add-in called DADM_Tools that implements decision trees and simulation, as well as forecasting and several basic data analysis tools. This add-in is freely available from the author’s website at https://kelley.iu.edu/albrightbooks/free_downloads.htm, and students can continue to use it after the course is over, even in their eventual jobs, for free. You can decide whether you want to use the Palisade software, the DADM_Tools add-in, or neither.

1-3 Introduction to Spreadsheet Modeling4 A common theme in this book is spreadsheet modeling, where the essential elements of a business problem are entered and related in an Excel spreadsheet for further analysis. This section provides an introduction to spreadsheet modeling with some relatively simple models. Together with the Excel tutorial mentioned previously, the goal of this section is to get you “up to speed” in using Excel effectively for the rest of the book.

4 If you (or your students) are already proficient in basic Excel tools, you can skip this section, which is new to this edition of the book.

09953_ch01_ptg01_001-036.indd 8 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 9

1-3a Basic Spreadsheet Modeling: Concepts and Best Practices

Most spreadsheet models involve inputs, decision variables, and outputs. The inputs have given fixed values, at least for the purposes of the model. The decision variables are those a decision maker controls. The outputs are the ultimate values of interest; they are deter- mined by the inputs and the decision variables.

Spreadsheet modeling is the process of entering the inputs and decision variables into a spreadsheet and then relating them appropriately, by means of formulas, to obtain the outputs. After you have done this, you can then proceed in several directions. You might want to perform a sensitivity analysis to see how one or more outputs change as selected inputs or decision variables change. You might want to find the values of the decision variable(s) that minimize or maximize a particular output, possibly subject to certain con- straints. You might also want to create charts that show graphically how certain parameters of the model are related.

Getting all the spreadsheet logic correct and producing useful results is a big part of the battle. However, it is also important to use good spreadsheet modeling practices. You probably won’t be developing spreadsheet models for your sole use; instead, you will be sharing them with colleagues or even a boss (or an instructor). The point is that other peo- ple will be reading and trying to make sense out of your spreadsheet models. Therefore, you should construct your spreadsheet models with readability in mind. Features that can improve readability include the following:5

• A clear, logical layout to the overall model • Separation of different parts of a model, possibly across multiple worksheets • Clear headings for different sections of the model and for all inputs, decision variables,

and outputs • Use of range names • Use of boldface, italics, larger font size, coloring, indentation, and other formatting

features • Use of cell comments • Use of text boxes for assumptions and explanations

The following example illustrates the process of building a spreadsheet model accord- ing to these guidelines. We build this model in stages. In the first stage, we build a model that is correct, but not very readable. At each subsequent stage, we modify the model to enhance its readability.

5 For further guidelines that attempt to make spreadsheet models more flexible and less prone to errors, see the article by LeBlanc et al. (2018).

EXAMPLE

1.1 ORDERING NCAA T-SHIRTS It is March, and the annual NCAA Basketball Tournament is down to the final four teams. Randy Kitchell is a T-shirt vendor who plans to order T-shirts with the names of the final four teams from a manufacturer and then sell them to the fans. The fixed cost of any order is $750, the variable cost per T-shirt to Randy is $8, and Randy’s selling price is $18. However, this price will be charged only until a week after the tournament. After that time, Randy figures that interest in the T-shirts will be low, so he plans to sell all remaining T-shirts, if any, at $6 each. His best guess is that demand for the T-shirts during the full-price period will be 1500. He is thinking about ordering 1450 T-shirts, but he wants to build a spreadsheet model that will let him experiment with the uncertain demand and his order quantity. How should he proceed?

Objective To build a spreadsheet model in a series of stages, with all stages being correct but each stage being more readable and flexible than the previous stages.

09953_ch01_ptg01_001-036.indd 9 04/03/19 10:51 PM

Solution The logic behind the model is fairly simple, but the model is built for generality. Specifically, the formulas allow for the order quantity to be less than, equal to, or greater than demand. If demand is greater than the order quantity, Randy will sell all the T-shirts ordered for $18 each. If demand is less than the order quantity, Randy will sell as many T-shirts as are demanded at the $18 price and all leftovers at the $6 price. You can implement this logic in Excel with an IF function.

A first attempt at a spreadsheet model appears in Figure 1.2. (See the file TShirt Sales Finished.xlsx, where each stage appears on a separate worksheet.) You enter a possible demand in cell B3, a possible order quantity in cell B4, and then calcu- late the profit in cell B5 with the formula

5−750−8*B41IF(B3>B4,18*B4,18*B316*(B4−B3))

This formula subtracts the fixed and variable costs and then adds the revenue according to the logic just described.

This is exactly the same formula as before, but it is now more flexible. If an input changes, the profit recalculates automati- cally. Most important, the inputs are no longer buried in the formula.

Still, the profit formula is not very readable as it stands. You can make it more readable by using range names. The mechanics of range names are covered in detail later in this section. For now, the results of using range names for cells

This model in Figure 1.2 is entirely correct, but it isn’t very readable or flexible because it breaks a rule that you should never break: It hard codes input values into the profit formula. A spreadsheet model should never include input numbers in formulas. Instead, it should store input values in sepa- rate cells and then use cell references to these inputs in its formulas. A remedy appears in Figure 1.3, where the inputs have been entered in the range B3:B6, and the profit formula in cell B10 has been changed to

5−B3−B4*B91IF(B8>B9,B5*B9,B5*B81B6*(B9−B8))

Never hard code numbers into Excel formulas. Use cell references instead.

1 0 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

1 2 3 4 5

A B NCAA t-shirt sales

Demand Order Profit

1500 1450

13750

Figure 1.2 Base Model

1 2 3 4 5 6 7 8 9

A B NCAA t-shirt sales

Fixed order cost Variable cost Selling price Discount price

Demand Order Profit

$750 $8

$18 $6

1500 1450

$13,750

Figure 1.3 Model with Input Cells

Excel’s IF function has the syntax =IF(condition, result_if_True,result_if_False). The condition is any expression that is either true or false. The two expressions result_if_True and result_if_False can be any expressions you would enter in a cell: numbers, text, or other Excel functions (including other IF functions). If either expression is text, it must be enclosed in double quotes, such as 5IF(Score.590,"A","B"). Also, the condition can be complex combinations of conditions, using the keywords AND or OR. Then the syntax is, for example, 5IF(AND(Score1,60,Score2,60),"Fail","Pass").

Excel Function

09953_ch01_ptg01_001-036.indd 10 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 1 1

B3 through B6, B8, and B9 are shown in Figure 1.4. This model looks exactly like the previous model, but the formula in cell B10 is now

5−Fixed_order_cost−Variable_cost*Order1IF(Demand>Order, Selling_price*Order,Selling_price*Demand 1Discount_Price*(Order−Demand))

This formula is admittedly more long-winded, but it is easier to read.

Next, Randy might like to have profit broken down into various costs and revenues as shown in Figure 1.5, rather than one single profit cell. The formulas in cells B12, B13, B15, and B16 are straightforward, so they are not repeated here. You can then accumulate these to get profit in cell B17 with the formula

5−(B121B13)1(B151B16)

Of course, range names could be used for these intermediate output cells, but this is probably more work than it’s worth. You should always use discretion when deciding how many range names to use.

If you see the model in Figure 1.5, how do you know which cells contain inputs or decision variables or outputs? Labels and/or color coding can help to distinguish these types. A blue/red/gray color-coding style has been applied in Figure 1.6, along with descriptive labels in boldface. The blue cells at the top are input cells, the red cell in the middle is a decision vari- able, and the gray cell at the bottom is the key output. There is nothing sacred about this particular convention. Feel free to adopt your own convention and style, but use it consistently.

1 2 3 4 5 6 7 8 9

A B C D E F NCAA t-shirt sales

Fixed order cost Range names used Variable cost Selling price Discount price

Order Demand Order Profit

$750 $8

$18 $6

1500 1450

$13,750

Demand Discount_price Fixed_order_cost

Selling_price Variable_cost

='Model 3'!$B$8 ='Model 3'!$B$6 ='Model 3'!$B$3 ='Model 3'!$B$9 ='Model 3'!$B$5 ='Model 3'!$B$4

Figure 1.4 Model with Range Names in Profit Formula

Figure 1.5 Model with Intermediate Outputs

Spreadsheet Layout and Documentation

If you want your spreadsheets to be used (and you want your value in your company to rise), think carefully about your spreadsheet layout and then document your work carefully. For layout, consider whether certain data are best oriented in rows or columns, whether your work is better placed in a single sheet or in multiple sheets, and so on. For documentation, use descriptive labels and headings, color coding, cell comments, and text boxes to make your spreadsheets more readable. It takes time and careful planning to design and then document your spreadsheet models, but the time is well spent. And if you come back in a few days to a spreadsheet model you developed and you can’t make sense of it, don’t be afraid to redesign your work completely—from the ground up.

Fundamental Insight

09953_ch01_ptg01_001-036.indd 11 04/03/19 10:51 PM

1-3b Cost Projections In the following example, a company wants to project its costs of producing products, given that material and labor costs are likely to increase through time. We build a simple model and then use Excel’s charting capabilities to obtain a graphical image of projected costs.

The model in Figure 1.6 is still not the last word on this example. As shown in later examples, you can create data tables to see how sensitive profit is to the inputs, the demand, and the order quantity. You can also create charts to show results graph- ically. But this is enough for now. You can see that the model in Figure 1.6 is now much more readable and flexible than the original model in Figure 1.2.

1 2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22

A B C D E F NCAA t-shirt sales

Input egnaRselbairav names used Fixed order cost $750 Demand ='Model 5'!$B$10 Variable cost $8 Discount_price ='Model 5'!$B$7 Selling price $18 Fixed_order_cost ='Model 5'!$B$4 Discount price $6 Order ='Model 5'!$B$13

Selling_price ='Model 5'!$B$6 Uncertain variable Variable_cost ='Model 5'!$B$5

0051dnameD

Decision variable 0541redrO

Output variables Costs

Fixed cost $750 Variable costs $11,600

Revenues Full-price shirts $26,100 Discount-price shirts $0

$13,750tiforP

Figure 1.6 Model with Category Labels and Color Coding

EXAMPLE

1.2 PROJECTING THE COSTS OF BOOKSHELVES AT WOODWORKS The Woodworks Company produces a variety of custom-designed wood furniture for its customers. One favorite item is a bookshelf, made from either cherry or oak. The company knows that wood prices and labor costs are likely to increase in the future. Table 1.1 shows the number of board-feet and labor hours required for a bookshelf, the current costs per board-foot and labor hour, and the anticipated annual increases in these costs. (The top row indicates that either type of bookshelf requires 30 board-feet of wood and 16 hours of labor.) Build a spreadsheet model that enables the company to experiment with the growth rates in wood and labor costs so that a manager can see, both numerically and graphically, how the costs of the book- shelves increase in the next few years.

resource Cherry Oak Labor

Required per bookshelf 30 30 16

Current unit cost $5.50 $4.30 $18.50

Anticipated annual cost increase

2.4% 1.7% 1.5%

Table 1.1 Input Data for Manufacturing a Bookshelf

Objective To learn good spreadsheet practices, to create copyable formulas with the careful use of relative and absolute addresses, and to create line charts from multiple series of data.

09953_ch01_ptg01_001-036.indd 12 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 1 3

Solution The completed spreadsheet model appears in Figure 1.7 and in the file Bookshelf Costs Finished.xlsx. You can develop it with the following steps.

1. Inputs. You should usually enter the inputs for a model in the upper-left corner of a work- sheet as you can see in the shaded ranges in Figure 1.7. We have used our standard conven- tion of coloring inputs—the numbers from the statement of the problem—blue. You can develop your own convention, but the input cells should be distinguished in some way. Note that the inputs are grouped logically and are explained with appropriate labels. You should always document your spreadsheet model with descriptive labels. Also, note that by entering inputs explicitly in input cells, you can refer to them later in Excel formulas.

Always enter input values in input cells and then refer to them in Excel formulas. Do not bury input values in formulas.

Figure 1.7 Bookshelf Cost Model

Relative and Absolute Addresses in Formulas

Relative and absolute addresses are used in Excel formulas to facilitate copying. A dollar sign next to a column or row address indicates that the address is absolute and will not change when copying. The lack of a dollar sign indi- cates that the address is relative and will change when copying. After you select a cell in a formula, you can press the F4 key repeatedly to cycle through the relative/absolute possibilities: =B4 (both column and row relative); =$B$4 (both column and row absolute); =B$4 (column relative, row absolute); and =$B4 (column absolute, row relative).

Excel Tip

Always try to organize your spreadsheet model so that you can copy formulas across multiple cells.

2. Design output table. Plan ahead for how you want to structure your outputs. We created a table where there is a row for every year in the future (year 0 corresponds to the current year), there are three columns for projected unit costs (columns B–D), and there are two columns for projected total bookshelf costs (columns E and F). The headings reflect this design. This isn’t the only possible design, but it works well. The important point is that you should have some logical design in mind before you dive in.

3. Projected unit costs of wood. The dollar values in the range B19:F25 are calculated from Excel formulas. Although the logic in this example is straightforward, it is still important to have a strategy in mind before you enter formulas. In particular, you should always try to design your spreadsheet so that you can enter a single formula and then copy it. This saves work and avoids errors. For the costs per board-foot in columns B and C, enter the formula

=B9

in cell B19 and copy it to cell C19. Then enter the general formula

5B19*(11B$10)

in cell B20 and copy it to the range B20:C25. We assume you know the rules for absolute and relative addresses (dollar sign for absolute, no dollar sign for relative), but it takes some planning to use these so that copying is possible. Make sure you understand why we made row 10 absolute but column B relative.

Typing dollar signs in formulas for absolute references is inefficient. Press the F4 key instead.

Press the Fn key and the F4 key (together) on Mac keyboards.

09953_ch01_ptg01_001-036.indd 13 04/03/19 10:51 PM

4 Projected unit labor costs. To calculate projected hourly labor costs, enter the formula

=B13

in cell D19. Then enter the formula

5D19*(11B$14)

in cell D20 and copy it down column D.

5 Projected bookshelf costs. Each bookshelf cost is the sum of its wood and labor costs. By a careful use of absolute and relative addresses, you can enter a single formula for these costs—for all years and for both types of wood. To do this, enter the formula

5B$5*B191B$6*$D19

in cell E19 and copy it to the range E19:F25. The idea here is that the units of wood and labor per bookshelf are always in rows 5 and 6, and the projected unit labor cost is always in column D, but all other references are relative to allow copying.

6 Chart. A chart is a valuable addition to any table of data, especially in the busi- ness world, so charting in Excel is a skill worth mastering. We illustrate some of the possibilities here, but we urge you to experiment with other possibilities on your own. Start by selecting the range E18:F25—yes, including the labels in row 18. Next, click the Line dropdown list on the Insert ribbon and select the Line with Markers type. You instantly get the line chart you want, with one series for Cherry and another for Oak. Also, when the chart is selected (that is, it has a border around it), you see two Chart Tools tabs, Design and Format. There are also three useful buttons to the right of the chart. (These three buttons were intro- duced in Excel 2013, and the two tabs condense the tools in the three tabs from Excel 2007 and 2010.) The most important button is the Select Data button on the Design ribbon. It lets you choose the ranges of the data for charting in case Excel’s default choices aren’t what you want. (The default choices are based on the selected range when you create the chart.)

Click Select Data now to obtain the dialog box in Figure 1.8. On the left, you control the series (one series or multiple series) being charted; on the right, you control the data used for the horizontal axis. By selecting E18:F25, you have the series on the left correct, including the names of these series (Cherry and Oak), but if you didn’t, you could select one of the series and click the Edit button to change it. The data on the horizontal axis are currently the default 1, 2, and so on. To use the data in column A, click the Edit button on the right and select the range A19:A25. Then you can experiment with various formatting options to make the chart even better. For example, we rescaled the vertical axis to start at $300 rather than $0 (right-click any of the numbers on the vertical axis and select Format Axis), and we added a chart title at the top and a title for the horizontal axis at the bottom.

1 4 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

The many chart options are easily accessible from the Chart Tools tabs that are visible when a chart is selected. Don’t be afraid to experiment with them to produce professional-looking charts.

Figure 1.8 Select Data Source Dialog Box

The Select Data Source dialog box in Excel for Mac has a different layout, but

the options are basically the same.

The three buttons to the right of the chart don’t appear in Excel for Mac.

09953_ch01_ptg01_001-036.indd 14 04/03/19 10:51 PM

Using the Model for What-If Questions The model in Figure 1.7 can now be used to answer many what-if questions. In fact, many models are built for the purpose of permitting experimentation with various scenarios. The important point is that the model has been built in such a way that a manager can enter any desired values in the input cells, and all outputs, including the chart, will update automatically.

1-3 Introduction to Spreadsheet Modeling 1 5

1-3c Breakeven Analysis Many business problems require you to find the appropriate level of some activity. This might be the level that maximizes profit (or minimizes cost), or it might be the level that allows a company to break even—no profit, no loss. The following example illustrates a typical breakeven analysis.

EXAMPLE

1.3 BREAKEVEN ANALYSIS AT QUALITY SWEATERS The Quality Sweaters Company sells hand-knitted sweaters. The company is planning to print a catalog of its products and undertake a direct mail campaign. The cost of printing the catalog is $20,000 plus $0.10 per catalog. The cost of mailing each catalog (including postage, order forms, and buying names from a mail-order database) is $0.15. In addition, the company plans to include direct reply envelopes in its mailings and incurs $0.20 in extra costs for each direct mail envelope used by a respondent. The average size of a customer order is $40, and the company’s variable cost per order (primarily due to labor and material costs) averages about 80% of the order’s value—that is, $32. The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions:

1. How does a change in the response rate affect profit? 2. For what response rate does the company break even?

Objective To learn how to work with range names, to learn how to answer what-if questions with one-way data tables, to introduce Excel’s Goal Seek tool, and to learn how to document and audit Excel models with cell comments and Excel’s formula auditing tools.

Solution The completed spreadsheet model appears in Figure 1.9. (See the file Breakeven Analysis Finished.xlsx.) First, note the clear layout of the model. The input cells are colored blue, they are separated from the outputs, headings are boldfaced, several headings are indented, numbers are formatted appropriately, and a list to the right spells out all range names we have used. (See the next Excel Tip on how to create this list.) Also, following the convention we use throughout the book, the decision variable (number mailed) is colored red, and the bottom-line output (profit) is colored gray.

Creating Range Names

To create a range name for a range of cells (which could be a single cell), highlight the cell(s), click in the Name Box just to the left of the Formula Bar, and type a range name. Alternatively, if a column (or row) of labels appears next to the cells to be range-named, you can use these labels as the range names. To do this, highlight the labels and the cells to be named (for example, A4:B5 in Figure 1.9), click Create from Selection on the Formulas ribbon, and make sure the appropriate box in the resulting dialog box is checked. The labels in our example are to the left of the cells to be named, so the Left column box should be checked. This is a quick way to create range names, and we did it for all range names in the example. Note that if a label contains any “illegal” range-name characters, such as a space, the illegal characters are converted to underscores.

Excel Tip

Adopt some layout and formatting conventions, even if they differ from ours, to make your spreadsheets readable and easy to follow.

09953_ch01_ptg01_001-036.indd 15 04/03/19 10:51 PM

To create this model, you can proceed through the following steps.

1. Headings and range names. We have named a lot of cells, more than you might want to name, but you will see their value when you create formulas.

2. Values of input variables and the decision variable. Enter these values and format them appropriately. As usual, we have used our blue/red/gray color-coding scheme. Note that the number mailed has been designated as a decision variable, not as an input variable (and it is colored red, not blue). This is because the company gets to choose the value of this vari- able. Finally, some of the values have been combined in the statement of the problem. For example, the $32.20 in cell B12 is really 80% of the $40 average order size, plus the $0.20 per return envelope. To document this, comments appear in a few cells, as shown in Figure 1.10.

1 6 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

1 2 3 4 5 6 7 8 9

10 11 12 13

IHGFEDCBA Quality Sweaters direct mail model Range names used

11$B$!ledoM=redro_egarevA Catalog ledoMstupni of responses Fixed_cost_of_printing =Model!$B$4 Fixed cost of printing $20,000 Response 8$B$!ledoM=deliam_rebmuN%8etar Variable cost of printing mailing $0.25 Number of 5$E$!ledoM=sesnopser_fo_rebmuN0008sesnopser

31$E$!ledoM=tiforP Decision ledoMelbairav of revenue, costs, and profit 4$E$!ledoM=etar_esnopseR Number latoT000001deliam 21$E$!ledoM=tsoc_latoT000,023$euneveR

Fixed cost of printing $20,000 Total_Revenue =Model!$E$8 Order inputs Total variable cost of printing mailing $25,000 Variable_cost_of_printing_mailing =Model!$B$5 Average latoT04$redro variable cost of orders $257,600 Variable_cost_per_order =Model!$B$12 Variable cost per order $32.20 Total 006,203$tsoc

004,71$tiforP

Trial value, will do sensitivity analysis on

Includes $0.10 for printing and $0.15 for mailing each catalog

Includes 80% of the average $40 order size, plus $0.20 per return envelope

Figure 1.10 Cell Comments in Model

Figure 1.9 Quality Sweaters Model

1 2 3 4 5 6 7 8 9

10 11 12 13

IHGFEDCBA Quality Sweaters direct mail model Range names used

31$E$!ledoM=tiforP Decision ledoMelbairav of revenue, costs, and profit 4$E$!ledoM=etar_esnopseR Number latoT000001deliam 21$E$!ledoM=tsoc_latoT000,023$euneveR

004,71$tiforP

Pasting Range Names

Including a list of the range names in your spreadsheet is useful for documentation. To do this, select a cell (such as cell G2 in Figure 1.9), select the Use in Formula dropdown list from the Formulas ribbon, and then click the Paste List option. You get a list of all range names and their cell addresses. However, if you change any of these range names (delete one, for example), the paste list does not update automatically; you have to create it again.

Excel Tip

Inserting Cell Comments

Inserting comments in cells is a great way to document non-obvious aspects of your spreadsheet models. To enter a comment in a cell, right-click the cell, select the Insert Comment item, and type your comment. This creates a little red mark in the cell, indicating a comment, and you can see the comment by moving the cursor over the cell. When a cell contains a comment, you can edit or delete the comment by right-clicking the cell and selecting the appropriate item.

Excel Tip

This option to paste a list of range names is evidently not available in Excel for Mac.

09953_ch01_ptg01_001-036.indd 16 04/03/19 10:51 PM

3. Model the responses. Enter any reasonable value, such as 8%, in the Response_rate cell. You will perform sensitivity on this value later on. Then enter the formula

5Number_mailed*Response_rate

in cell E5. (Are you starting to see the advantage of range names?)

4. Model the revenue, costs, and profits. Enter the following formulas in cells E8 through E13:

5Number_of_responses*Average_order 5Fixed_cost_of_printing 5Variable_cost_of_printing_mailing*Number_mailed 5Number_of_responses*Variable_cost_per_order 5SUM(E9:E11) 5Total_revenue-Total_cost

These formulas should all be self-explanatory because of the range names used.

Entering Formulas with Range Names

To enter a formula that contains range names, you do not have to type the full range names. You actually have two options. First, you can click or drag the cells, and range names will appear in your formulas. Second, you can start typing the range name in the formula and, after a few letters, Excel will show you a list you can choose from.

Excel Tip

Forming a One-Way Data Table Now that a basic model has been created, the questions posed by the company can be answered. For question 1, you can form a one-way data table to show how profit varies with the response rate as shown in Figure 1.11. Data tables are used often in this book, so make sure you understand how to create them. First, enter a sequence of trial values of the response rate in column A, and enter a link to profit in cell B17 with the formula 5Profit. This cell is shaded for emphasis, but this isn’t necessary. (In general, other outputs could be part of the table, and they would be placed in columns C, D, and so on. There would be a link to each output in row 17.) Finally, highlight the entire table range, A17:B27, and select Data Table from the What-If Analysis dropdown list on the Data ribbon to bring up the dialog box in Figure 1.12. Fill it in as shown to indicate that the only input, Response_rate, is listed along a column. (You can enter either a range name or a cell address in this dialog box. The easiest way is to point to the cell.)

When you click OK, Excel substitutes each response rate value in the table into the Response_rate cell, recalculates profit, and reports it in the table. For a final touch, you can create a chart of the values in the data table. To do this, select the A18:B27 range and select the type of chart you want from the Insert ribbon. Then you can fix it up by adding titles and making other modifications to suit your taste.

Data tables are also called what-if tables. They let you see what happens to selected outputs as selected inputs change.

15 16 17 18 19 20 21 22 23 24 25 26 27

FEDCBA Question 1 - sensitivity of profit to response rate

Response rate Profit $17,400

1% –$37,200 2% –$29,400 3% –$21,600 4% –$13,800 5% –$6,000 6% $1,800 7% $9,600 8% $17,400 9% $25,200

10% $33,000

$40,000 $30,000 $20,000 $10,000

–$10,000 –$20,000 –$30,000 –$40,000 –$50,000

$0 0% 2% 4% 6% 8% 10% 12%

Profit versus Response Rate

Pr of

Response Rate

Figure 1.11 Data Table and Chart for Profit

1-3 Introduction to Spreadsheet Modeling 1 7

The second option below doesn’t appear to work in Excel for Mac.

09953_ch01_ptg01_001-036.indd 17 04/03/19 10:51 PM

Figure 1.12 Data Table Dialog Box

the power of Data tables

Unfortunately, many Excel users are unaware of data tables, which are among the most powerful and useful tools Excel has to offer. After you have developed a model that relates inputs to outputs, you can then build data tables in a matter of seconds to see how the outputs vary as key inputs vary over some range. Data tables are perfect for answering a large number of what-if questions quickly and easily.

Fundamental Insight

As the chart indicates, profit increases in a linear manner as the response rate varies. More specifically, each percentage point increase in the response rate increases profit by $7800. Here is the reasoning. Each percentage point increase in response rate results in 100,000(0.01) 5 1000 more orders. Each order yields a revenue of $40, on average, but incurs a variable cost of $32.20. The net gain in profit is $7.80 per order, or $7800 for 1000 orders.

Using Goal Seek From the data table, you can see that profit changes from negative to positive when the response rate is somewhere between 5% and 6%. Question 2 asks for the exact breakeven point. You could find this by trial and error, but it is easier to use Excel’s Goal Seek tool. Essentially, Goal Seek is used to solve a single equation in a single unknown. Here, the equation is Profit 5 0, and the unknown is the response rate. To implement Goal Seek, select Goal Seek from the What-If Analysis dropdown list on the Data ribbon and fill in the resulting dialog box as shown in Figure 1.13. (Range names or cell addresses can be used in the top and bottom boxes, but a number must be entered in the middle box.) After you click OK, the Response_rate and Profit cells have values 5.77% and $0. In words, if the response rate is 5.77%, Quality Sweaters breaks even. If the response rate is greater than 5.77%, the company makes money; if the rate is less than 5.77%, the company loses money. However, this assumes that the company mails 100,000 catalogs. If it sends more or fewer catalogs, the breakeven response rate will change.

The purpose of the Goal Seek tool is to solve one equation in one unknown. It is used here to find the response rate that makes profit equal to 0.

1 8 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

One-Way Data Table

A one-way data table allows you to see how one or more outputs vary as a single input varies over a selected range of values. These input values can be arranged vertically in a column or horizontally in a row. We explain only the vertical arrangement because it is the most common. To create the table, enter the input values in a column range, such as A18:A27 of Figure 1.11, and enter links to one or more output cells in columns to the right and one row above the inputs, as in cell B17. Then select the entire table, beginning with the upper-left blank cell (A17), select Data Table from the What-If Analysis dropdown list on the Data ribbon, and fill in the resulting dialog box as in Figure 1.12. Leave the Row Input cell blank and use the cell where the original value of the input lives as the Column Input cell. When you click OK, each value in the left column of the table is substituted into the column input cell, the spreadsheet recalculates, and the resulting value of the output is placed in the table. Also, if you click anywhere in the body of the table (B18:B27 in the figure), you will see that Excel has entered the TABLE function to remind you that a data table lives here. Note that the column input cell must be on the same worksheet as the table itself; otherwise, Excel issues an error.

Excel Tool

09953_ch01_ptg01_001-036.indd 18 04/03/19 10:51 PM

Figure 1.13 Goal Seek Dialog Box

Goal Seek

The purpose of the Goal Seek tool is to solve one equation in one unknown. Specifically, Goal Seek allows you to vary a single input cell to force a single output cell to a selected value. To use it, select Goal Seek from the What-If Analysis dropdown list on the Data ribbon and fill in the resulting dialog box in Figure 1.13. Enter a reference to the output cell in the Set cell box, enter the numeric value you want the output cell to equal in the To value box, and enter a reference to the input cell in the By changing cell box. Note that Goal Seek sometimes stops when the Set cell is close, but not exactly equal to, the desired value. To improve Goal Seek’s accuracy, you can select Options from the File menu and then the Formulas link. Then you can check the Enable iterative calculation box and reduce Maximum Change to any desired level of precision. We chose a precision level of 0.000001. For this level of precision, Goal Seek searches until profit is within 0.000001 of the desired value, $0.

Excel Tool

The Formula Auditing tool is indispensable for untangling the logic in a spreadsheet, especially if someone else developed it and you are trying to figure out what they did.

Using the Formula Auditing Tool The model in this example is fairly small and simple. Even so, you can use a handy Excel tool to see how all the parts are related. This is the Formula Auditing tool, which is available on the Formulas ribbon. See Figure 1.14.

The Trace Precedents and Trace Dependents buttons are probably the most useful buttons in this group. To see which formulas have direct links to the Number_of_ responses cell, select this cell and click the Trace Dependents button. Arrows are drawn to each cell that directly depends on the number of responses, as shown in Figure 1.15. Alternatively, to see which cells are used to create the formula in the Total_ revenue cell, select this cell and click the Trace Precedents button. Now you see that the Average_order and Number_of_responses cells are used directly to calculate revenue, as shown in Figure 1.16. Using these two buttons, you can trace your logic (or someone else’s logic) as far backward or forward as you like. When you are finished, just click the Remove Arrows button.

Figure 1.15 Dependents of Number_of_responses Cell 1

2 3 4 5 6 7 8 9

10 11 12 13

EDCBA Quality Sweaters direct mail model

Catalog ledoMstupni of responses Fixed cost of printing $20,000 Response %8etar Variable cost of printing mailing $0.25 Number of 0008sesnopser

Decision ledoMelbairav of revenue, costs, and profit Number latoT000001deliam 000,023$euneveR

Fixed cost of printing $20,000 Order inputs Total variable cost of printing mailing $25,000 Average latoT04$redro variable cost of orders $257,600 Variable cost per order $32.20 Total 006,203$tsoc

004,71$tiforP

Figure 1.14 Formula Auditing Group

1-3 Introduction to Spreadsheet Modeling 1 9

Change the Goal Seek accuracy in Excel for Mac by selecting Preferences from the Excel menu and then choosing the Calculation group.

09953_ch01_ptg01_001-036.indd 19 04/03/19 10:51 PM

Figure 1.16 Precedents of Total_revenue Cell 1

2 3 4 5 6 7 8 9

10 11 12 13

EDCBA Quality Sweaters direct mail model

Catalog ledoMstupni of responses Fixed cost of printing $20,000 Response %8etar Variable cost of printing mailing $0.25 Number of 0008sesnopser

Decision ledoMelbairav of revenue, costs, and profit Number latoT000001deliam 000,023$euneveR

Fixed cost of printing $20,000 Order inputs Total variable cost of printing mailing $25,000 Average latoT04$redro variable cost of orders $257,600 Variable cost per order $32.20 Total 006,203$tsoc

004,71$tiforP

1-3d Ordering with Quantity Discounts and Demand Uncertainty In the following example, we again attempt to find the appropriate level of some activity: how much of a product to order when customer demand for the product is uncertain. Two important features of this example are the presence of quantity discounts and the explicit use of probabilities to model uncertain demand.

Formula Auditing Toolbar

The formula auditing toolbar allows you to see dependents of a selected cell (which cells have formulas that reference this cell) or precedents of a given cell (which cells are referenced in this cell’s formula). You can even see dependents or precedents that reside on a different worksheet. In this case, the auditing arrows appear as dashed lines and point to a small spreadsheet icon. By double-clicking the dashed line, you can see a list of dependents or precedents on other worksheets. These tools are especially useful for understanding how someone else’s spreadsheet works.

Excel Tool

2 0 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

EXAMPLE

1.4 ORDERING WITH QUANTITY DISCOUNTS AT SAM’S BOOKSTORE Sam’s Bookstore, with many locations across the United States, places orders for all of the latest books and then distributes them to its individual bookstores. Sam’s needs a model to help it order the appropriate number of any title. For example, Sam’s plans to order a popular new hardback novel, which it will sell for $30. It can purchase any number of this book from the publisher, but due to quantity discounts, the unit cost for all books it orders depends on the number ordered. If the number ordered is less than 1000, the unit cost is $24. After each 1000, the unit cost drops: to $23 for at least 1000 copies; to $22.25 for at least 2000; to $21.75 for at least 3000; and to $21.30 (the lowest possible unit cost) for at least 4000. For example, if Sam’s orders 2500 books, its total cost is $22.25(2500) 5 $55,625. Sam’s is uncertain about the demand for this book—it estimates that demand could be anywhere from 500 to 4500. Also, as with most hardback novels, this one will eventually come out in paperback. Therefore, if Sam’s has any hardbacks left when the paperback comes out, it will put them on sale for $10, at which price it believes all leftovers will be sold. How many copies of this hardback novel should Sam’s order from the publisher?

09953_ch01_ptg01_001-036.indd 20 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 2 1

Objective To learn how to build in complex logic with IF formulas, to get help about Excel functions, to learn how to use lookup functions, to see how two-way data tables provide answers to more extensive what-if questions, and to learn about Excel’s SUMPRODUCT function.

Solution The profit model appears in Figure 1.17. (See the file Quantity Discounts Finished.xlsx.) The order quantity and demand in the Order_quantity and Demand cells are trial values. (Comments in these cells are a reminder of this.) You can enter any values in these cells to test the logic of the model. The Order_quantity cell is colored red because the company can choose its value. In contrast, the Demand cell is colored green to indicate that this input value is uncertain and is being treated explicitly as such. Also, a table is used to indicate the quantity discounts cost structure. You can use the following steps to build the model.

1 A B C D E F G H I J K

Ordering decision with quantity egnaRstnuocsid names used: 2 3 4 5 6 7 8 9

10 11 12

!$ledoM=tsoC B$18 ytitnauQstupnI discount structure 9$E$:5$D$!ledoM=pukooLtsoC

Unit cost - see table to tAthgir least Unit 21$B$!ledoM=dnameDtsoc Regular 6$B$!ledoM=ecirp_revotfeL00.42$003$ecirp Leftover 9$B$!ledoM=ytitnauq_redrO00.32$000101$ecirp

53$J$:53$B$!ledoM=seitilibaborP52.22$0002 Decision variable 91$B$!ledoM=tiforP57.12$0003 Order 5$B$!ledoM=ecirp_ralugeR03.12$00040052ytitnauq

71$B$!ledoM=euneveR Uncertain quantity Units_sold_at_leftover_price =Model!$B$16

Units_sold_at_regular_price =Model!$B$150002dnameD 13 14 15 16 17 18 19

Profit model Units sold at regular price 2000 Units sold at leftover price 500

000,56$euneveR 526,55$tsoC 573,9$tiforP

Figure 1.17 Sam’s Profit Model

1. Inputs and range names. Enter all inputs and name the ranges as indicated. The Create from Selection shortcut was used to name all ranges except for CostLookup and Probabilities. For these latter two, you can select the ranges and enter the names in the Name Box—the “manual” method.

2. Revenues. The company can sell only what it has, and it sells any leftovers at the discounted sale price. Therefore, enter the following formulas in cells B15, B16, and B17:

5MIN(Order_quantity,Demand) 5IF(Order_quantity>Demand, Order_quantity-Demand,0) 5Units_sold_at_regular_price*Regular_price1Units_sold_at_leftover_price*Leftover_price

The logic in the first two of these cells is necessary to account correctly for the cases when the order quantity is greater than demand and when it is less than or equal to demand. You could use the following equivalent alternative to the IF function in cell B16:

5 MAX(Order_quantity-Demand,0)

fx Button and Function Library Group

To learn more about an Excel function, click the fx button next to the Formula bar. This is called the Insert Function button, although some people call it the Function Wizard. If there is already a function, such as an IF function, in a cell and you then click the fx button, you will get help on this function. If you select an empty cell and then click the fx button, you can choose a function to get help on. (The same help is available from the Function Library group on the Formulas ribbon.)

Excel Tool The fx button in Excel for Mac opens a Formula Builder pane to the right, but the functionality is essentially the same as in Excel for Windows.

09953_ch01_ptg01_001-036.indd 21 04/03/19 10:51 PM

2 2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

3. Total ordering cost. Depending on the order quantity, you can find the appropriate unit cost from the unit cost table and multiply it by the order quantity to obtain the total ordering cost. This can be accomplished with a complex nested IF for- mula, but a better way is to use the VLOOKUP function. Specifically, enter the formula

5VLOOKUP(Order_quantity,CostLookup,2)*Order_quantity

in cell B18. The VLOOKUP part of this formula says to compare the order quantity to the first (leftmost) column of the table in the CostLookup range and return the corresponding value in the second column (because the third argument is 2).

VLOOKUP

The VLOOKUP function is one of Excel’s most useful functions. To use it, first create a vertical lookup table, with values to use for comparison listed in the left column of the table and corresponding output values in as many columns to the right as you like. (See the CostLookup range in Figure 1.17 for an example.) Then the VLOOKUP function takes three or four arguments: (1) the value you want to compare to the values in the left column of the table; (2) the lookup table range; (3) the index of the column you want the returned value to come from, where the index of the left column is 1, the index of the next column is 2, and so on; and optionally (4) TRUE (for an approximate match, the default) or FALSE (for an exact match). If you omit the last argument, the values in the left column of the table must be entered in ascending order. (See online help for more details.) If the last argument is TRUE or is omitted, Excel scans down the leftmost column of the table and finds the last entry less than or equal to the first argument. (In this sense, it finds an approximate match.) There is also an HLOOKUP function that works exactly the same way, except that the lookup table is arranged in rows, not columns.

Excel Function

4. Profit. Calculate the profit with the formula

5Revenue-Cost

Two-Way Data Table The next step is to create a two-way data table for profit as a function of the order quantity and demand (see Figure 1.18). To create this table, first enter a link to the profit with the formula 5Profit in cell A22, and enter possible order quantities and possible demands in column A and row 22, respectively. (We used the same values for both order quantity and demand, from 500 to 4500 in increments of 500. This is not necessary—the demand could change in increments of 100 or even 1—but it is reasonable. Perhaps Sam’s is required by the publisher to order in multiples of 500.) Then select Data Table from the What-If Analysis dropdown list on the Data ribbon, and enter the Demand cell as the Row Input cell and the Order_quantity cell as the Column Input cell.

A two-way data table allows you to see how a single output varies as two inputs vary simultaneously.

21 A B C D E F G H I J

Data table of profit as a function of order quantity (along side) and demand (along top) 22 23 24 25 26 27 28 29 30

$9,375 500 1000 1500 2000 2500 3000 3500 4000 4500 500 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000 $3,000

1000 -$3,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 $7,000 1500 -$9,500 $500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500 $10,500 2000 -$14,500 -$4,500 $5,500 $15,500 $15,500 $15,500 $15,500 $15,500 $15,500 2500 -$20,625 -$10,625 -$625 $9,375 $19,375 $19,375 $19,375 $19,375 $19,375 3000 -$25,250 -$15,250 -$5,250 $4,750 $14,750 $24,750 $24,750 $24,750 $24,750 3500 -$31,125 -$21,125 -$11,125 -$1,125 $8,875 $18,875 $28,875 $28,875 $28,875 4000 -$35,200 -$25,200 -$15,200 -$5,200 $4,800 $14,800 $24,800 $34,800 $34,800

31 4500 -$40,850 -$30,850 -$20,850 -$10,850 -$850 $9,150 $19,150 $29,150 $39,150

Figure 1.18 Profit as a Function of Order Quantity and Demand

09953_ch01_ptg01_001-036.indd 22 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 2 3

Two-Way Data Table

A two-way data table allows you to see how a single output cell varies as you vary two input cells. Unlike a one- way data table, only a single output cell can be used. To create this type of table, enter a reference to the output cell in the top-left corner of the table, enter possible values of the two inputs below and to the right of this corner cell, and select the entire table. Then select Data Table from the What-If Analysis dropdown on the Data ribbon, and enter references to the cells where the original two input variables live. The Row Input cell corresponds to the values along the top row of the table, and the Column Input cell corresponds to the values along the left-most column of the table. When you click OK, Excel substitutes each pair of input values into these two input cells, recalculates the spreadsheet, and enters the corresponding output value in the table.

Excel Tool

SUMPRODUCT

The SUMPRODUCT function takes two range arguments, which must be exactly the same size and shape, and it sums the products of the corresponding values in these two ranges. For example, the formula 5SUMPRODUCT(A10:B11,E12:F13) is a shortcut for a formula involving the sum of four products: 5A10*E121A11*E131B10*F121B11*F13. This is an extremely useful function, especially when the rang- es involved are large, and it is used repeatedly throughout the book. (Actually, the SUMPRODUCT function can have more than two range arguments, all the same size and shape, but the most common use of SUMPRODUCT is when only two ranges are involved.)

Excel Function

The resulting data table shows that profit depends heavily on both order quantity and demand and (by scanning across rows) how higher demands lead to larger profits. But it is still unclear which order quantity Sam’s should select. Remember that Sam’s can choose the order quantity (the row of the data table), but it has no direct control over demand (the column of the table).

The ordering decision depends not only on which demands are possible but also on which demands are likely to occur. The usual way to express this information is with a set of probabilities that sum to 1. Suppose Sam’s estimates these as the values in row 35 of Figure 1.19. These estimates are probably based on other similar books it has sold in the past. The most likely demands are 2000 and 2500, with other values on both sides less likely. You can use these probabilities to find an expected profit for each order quantity. This expected profit is a weighted average of the profits in any row in the data table, using the probabilities as the weights. The easiest way to do this is to enter the formula

5SUMPRODUCT(B23:J23,Probabilities)

in cell B38 and copy it down to cell B46. You can also create a chart of these expected profits, as shown in Figure 1.19. (Excel refers to these as column charts. The height of each bar is the expected profit for that particular order quantity.)

This is actually a preview of decision making under uncertainty. To calculate an expected profit, you multiply each profit by its probability and add the products.

The largest of the expected profits, $12,250, corresponds to an order quantity of 2000, so we would recommend that Sam’s order 2000 copies of the book. This does not guarantee that Sam’s will make a profit of $12,250—the actual profit depends on the eventual demand—but it represents a reasonable way to proceed in the face of uncertain demand.

09953_ch01_ptg01_001-036.indd 23 04/03/19 10:51 PM

33 A B C D E F G H I J K

Model of expected demands 34 35 36 37 38 39 40 41 42 43 44

00540004005300030052000200510001005dnameD 510.040.070.051.052.052.051.050.0520.0ytilibaborP

Sum of probabilities --> 1 Order quantity Expected profit

500 $3,000 1000 $6,750 1500 $9,500 2000 $12,250 2500 $11,375 3000 $9,500 3500 $4 875

Order 2000 to maximize the expected profit.

45 46 47 48 49 50 51

4000 $1,350 4500 -$4,150 Ex

pe ct

ed P

ro fit

1 2 3 4 5 6 7 8

Expected Profit versus Order Quantity

Order Quantity

$6,000 $8,000

$10,000 $12,000 $14,000

-$6,000 -$4,000 -$2,000

$0 $2,000 $4,000

Figure 1.19 Comparison of Expected Profits

2 4 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

1-3e Estimating the Relationship Between Price and Demand The following example illustrates a very important modeling concept: estimating relation- ships between variables by curve fitting. The ideas can be illustrated at a relatively low level by taking advantage of some useful Excel tools.

EXAMPLE

1.5 ESTIMATING SENSITIVITY OF DEMAND TO PRICE AT THE LINKS COMPANY The Links Company sells its golf clubs at golf outlet stores throughout the United States. The company knows that demand for its clubs varies considerably with price. In fact, the price has varied over the past 12 months, and the demand at each price level has been observed. The data are in the data sheet of the file Golf Club Demand.xlsx (see Figure 1.20). For example, during month 12, when the price was $390, 6800 sets of clubs were sold. (The demands in column C are in hundreds of units. The cell comment in cell C3 is a reminder of this.) The company wants to estimate the relationship between demand and price and then use this estimated relationship to answer the following questions:

1. Assuming the unit cost of producing a set of clubs is $250 and the price must be a multiple of $10, what price should Links charge to maximize its profit?

2. How does the optimal price depend on the unit cost of producing a set of clubs?

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15

A B C Demand for golf clubs

Month Price Demand 1 450 45 2 300 103 3 440 49 4 360 86 5 290 125 6 450 52 7 340 87 8 370 68 9 500 45

10 490 44 11 430 58 12 390 68

Figure 1.20 Demand and Price Data for Golf Clubs

09953_ch01_ptg01_001-036.indd 24 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 2 5

Objective To illustrate Excel’s Trendline tool, and to illustrate conditional formatting.

Solution This example is divided into two parts: estimating the relationship between price and demand, and creating the profit model. (Both can be found in the file Golf Club Demand Finished.xlsx.)

Estimating the Relationship Between Price and Demand A scatterplot of demand versus price appears in Figure 1.21. (This can be created in the usual way as an Excel chart of the scatter type.) Obviously, demand decreases as price increases, but the goal is to quantify this relationship. Therefore, after cre- ating this chart, right-click any point on the chart and select Add Trendline to see the pane in Figure 1.22. This allows you to superimpose several different curves (including a straight line) on the chart. We consider three possibilities, the linear, power, and exponential curves, defined by the following general equations (where y and x, a general output and a general input, cor- respond to demand and price for this example):

• Linear: y 5 a 1 bx • Power: y 5 axb

• Exponential: y 5 aebx

De m

an d

Price

100

110

120

130

280 330 380 430 480 530

Figure 1.21 Scatterplot of Demand Versus Price

Figure 1.22 Trendline Options Dialog Box

09953_ch01_ptg01_001-036.indd 25 04/03/19 10:51 PM

2 6 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

Before proceeding, we describe some general properties of these three functions because of their widespread applicability. The linear function is the easiest. Its graph is a straight line. When x changes by 1 unit, y changes by b units. The constant a is called the intercept, and b is called the slope.

The power function is a curve except in the special case where the exponent b is 1. (Then it is a straight line.) Assuming that a is positive, the shape of this curve depends on the exponent b. If b 7 1, y increases at an increasing rate as x increases. If 0 6 b 6 1, y increases, but at a decreasing rate, as x increases. Finally, if b 6 0, y decreases as x increases. An important property of the power curve is that when x changes by 1%, y changes by a constant percentage, and this percentage is approx- imately equal to b%. For example, if y 5 100x2 2.35, then every 1% increase in x leads to an approximate 2.35% decrease in y.

The exponential function also represents a curve whose shape depends on the constant b in the exponent. Again, assume that a is positive. Then if b 7 0, y increases as x increases; if b 6 0, y decreases as x increases. An important property of the exponential function is that if x changes by 1 unit, y changes by a constant percentage, and this percentage is approximately equal to 100 3 b%. For example, if y 5 100e2 0.014x, then whenever x increases by 1 unit, y decreases by approximately 1.4%. Here, e is the special number 2.7182 . . . , and e to any power can be calculated in Excel with the EXP function. For example, you can calculate e2 0.014 with the formula =EXP(−0.014).

Returning to the example, if you superimpose any of these curves on the chart of demand versus price, Excel chooses the best-fitting curve of that type. In addition, if you check the Display Equation on Chart option, you see the equation of this best-fitting curve. For example, the best-fitting power curve appears in Figure 1.23. (The equation might not appear exactly as in the figure. However, it can be resized and reformatted to appear as shown.)

Each of these curves provides the best-fitting member of its “family” to the demand/price data, but which of these three is best overall? You can answer this question by finding the mean absolute percentage error (MAPE) for each of the three curves. To do so, for any price in the data set and any of the three curves, first predict demand by substituting the given price into the equation for the curve. The predicted demand is typically not the same as the observed demand, so you can calculate the absolute percentage error (APE) with the general formula:

APE 5 |Observed demand 2 Predicted demand| }}}}}

Observed demand (1.1)

Then for any curve, MAPE is the average of these APE values. The curve with the smallest MAPE is the best fit overall. MAPE is a popular error measure, but it is not the only error measure used. Another popular error measure is RMSE

(root mean square error), the square root of the average of the squared errors. Another possible measure is MAE (mean absolute error), the average of the absolute errors. (MAE is sometimes called MAD, mean absolute deviation.) These three measures often, but not always, give the same rankings of fits, so any of them can be used, and all three are calculated in this example. However, the advantage of MAPE is that it is easy to interpret a value such as 5.88%: the fitted values are off, on average, by 5.88%. The values of RMSE and MAE are more difficult to interpret because they depend on the units of the observations.

The calculations appear in Figure 1.24. (You can check the formulas in the finished version of the file.) As you can see, the three error measures provide the same rankings, with the power fit being best according to all three. In particular, MAPE for the power curve is 5.88%, meaning that its predictions are off, on average, by 5.88%. This power curve predicts that each 1% increase in price leads to an approximate 1.9% decrease in demand.

110

100

120

130

40 280 330 380

Price

De m

an d

Power Fit

430 480 530

y = 6E+06x -1.908

Figure 1.23 Best-Fitting Power Curve

09953_ch01_ptg01_001-036.indd 26 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 2 7

Developing the Profit Model Now we move to the profit model, which assumes the company produces exactly enough sets of clubs to meet demand. This model, shown in Figure 1.25, is straightforward to develop; you can see the details in the finished version of the file.

Maximizing Profit To see which price maximizes profit, you can build the data table shown in Figure 1.26. Here, the column input cell is B11 and the linking formula in cell B25 is 5B17. The corresponding chart shows that profit first increases and then decreases. You can find the maximum profit and corresponding price in at least three ways. First, you can estimate them from the chart. Second, you can scan down the data table for the maximum profit, which is shown in the figure. The following Excel Tip describes a third method that uses some of Excel’s more powerful features.

17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

A B C D E F G H I J Parameters of best-fitting curves

laitnenopxErewoPraeniLlaitnenopxErewoPraeniLraeniL %87.31%98.21%89.4102.1508.0547.1513.112tpecretnI

Slope - %38.3%19.6%78.149.60121.01139.4016453.0 %57.9%12.8%38.2187.3520.3592.55rewoP %83.7%85.9%37.256.9767.7756.384601785tnatsnoC

Exponent - %41.01%10.6%22.3123.21184.71184.8012809.1 %35.1%13.2%05.002.1508.0547.15laitnenopxE %00.1%23.0%13.478.7837.6857.0915.664tnatsnoC

Exponent - %25.11%35.8%18.7148.5708.3711.0819400.0 34.01 41.55 40.06 24.42% 7.67% 10.99% 37.56 43.18 42.07 14.65% 1.86% 4.38% 58.83 55.40 56.49 1.43% 4.48% 2.61% 73.02 66.75 68.74 7.38% 1.84% 1.09%

etulosbAnoitciderP percentage error K

Linear 45.43

3.72 39.51

5.50 273.04

0.07 14.03

146.60 120.78

41.53 0.69

25.16

33.63 50.72 16.20 67.83 56.53

1.44 0.08

33.68 11.92

0.67 6.75 1.56

38.47 15.53 22.84 40.28

160.70 0.64 0.76

61.41 24.44

3.72 2.29 0.55

6.74 1.93 6.29 2.35

16.52 0.26 3.75

12.11 10.99

6.44 0.83 5.02

5.80 7.12 4.02 8.24 7.52 1.20 0.27 5.80 3.45 0.82 2.60 1.25

6.20 3.94 4.78 6.35

12.68 0.80 0.87 7.84 4.94 1.93 1.51 0.74

Power Exponential ExponentialLinear Power

L M N O P Squared error Absolute error

MAPE 9.68% 5.88% 6.50% 7.72 4.84 5.57 6.10 4.01 4.38

RMSE MAE

Figure 1.24 Finding the Best-Fitting Curve Overall

A B C D E 1 2 3 4 5 6 7

Profit model, using best fitting power curve for estimating demand

Parameters of best-fitting power curve (from Estimation sheet) Constant 5871064 Exponent –1.9082

Monetary inputs 8 9

10 11 12 13

Unit cost to produce $250

Decision variable Unit price (trial value) $400

Profit model 14 15 16 17

Predicted demand 63.601 Total revenue $25,441 Total cost $15,900

045,9$tiforP

Figure 1.25 Profit Model

Conditional Formatting

Cell B39 in Figure 1.26 is colored because it corresponds to the maximum profit in the column, but Excel’s Conditional Formatting tool can do this for you—automatically. To color the maximum profit, select the range of profits, B26:B47, click the Conditional Formatting dropdown arrow on the Home ribbon, then Top/Bottom Rules, and then Top 10 Items to bring up the dialog box in Figure 1.27. By asking for the top 1 item, the max- imum value in the range is colored. You can experiment with the many other Conditional Formatting options. This is a great tool.

Excel Tip

09953_ch01_ptg01_001-036.indd 27 04/03/19 10:51 PM

2 8 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

Figure 1.26 Profit as a Function of Price 19

A B C D E F G H I Maximum profit from data table below, with corresponding best unit price

20 21 22 23 24 25 26 27

Maximum profit $10,409 Best price 025$

Data table for Profit as a func�on of Unit price Unit price Profit

$9,540 $260 $1,447 $280 $3,769

$5 50628 29 30 31 32 33 34 35 36 37

$300 , $320 $340 $360 $8,554 $380 $9,118 $400 $9,540

$480 38 39 40 41 42 43 44 45

$420 $9,851 $440 $10,075 $460 $10,230

$10,330 $500 $10,387 $520 $10,409 $540 $10,403 $560 $10,375 $580 $10,329

46 47

$600 $10,269 $620 $10,197 $640 $10,116 $660 $10,029 $680 $9,936

$6,815 $7,805

Maximum profit. Condi�onal forma�ng is used to color the largest profit. This used to be fairly difficult, but Excel versions 2007 and later make it easy with the “Top 10” op�ons – in this case, the top 1.

$260 $310 $360 $410 $460 $510 $560 $610 $660 $710

Pr of

Price

Profit versus Price

$2,000

$4,000

$6,000

$8,000

$10,000

$12,000

What about the corresponding best price, shown in cell B21 of Figure 1.26? You could enter this manually, but wouldn’t it be nice if you could get Excel to find the maximum profit in the data table, determine the price in the cell to its left, and report it in cell B21, all automatically? This is indeed possible. Enter the formula

5INDEX(A26:A47,MATCH(B20,B26:B47,0),1)

in cell B21, and the best price appears. This formula uses two Excel functions, MATCH and INDEX. MATCH compares the first argument (the maximum profit in cell B20) to the range specified in the second argument (the range of profits) and returns the index of the cell where a match appears. (The third argument, 0, specifies that you want an exact match.) In this case, the MATCH function returns 14 because the maximum profit is in the 14th cell of the profits range. Then the INDEX function is called effectively as =INDEX(A26:A47,14,1). The first argument is the range of prices, the second is a row index, and the third is a column index. Very simply, this function says to return the value in the 14th row and first column of the prices range.

Sensitivity to Variable Cost We now return to question 2 in the example: How does the best price change as the unit variable cost changes? You can answer this question with a two-way data table. Remember that this is a data table with two inputs—one along the left side and the other across the top row—and a single output. The two inputs for this problem are unit variable cost and unit price, and the single output is profit. The corresponding data table is in the range A55:F83, shown in Figure 1.28. To develop this table, enter desired inputs in column A and row 55, enter the linking formula 5B17 in cell A55 (it always goes in the top-left corner of a two-way data table), select the entire table, select Data Table from the What-If Analysis dropdown list, and enter B8 as the Row Input cell and B11 as the Column Input cell.

As before, you can scan the columns of the data table for the maximum profits and enter them (manually) in rows 51 and 52. (Alternatively, you can use the Excel features described in the previous Excel Tip to accomplish these tasks. Take a look at the finished version of the file for details. This file also explains how conditional formatting is used to color the max- imum profit in each column of the table.) Then you can create a chart of maximum profit (or best price) versus unit cost. The chart in Figure 1.28 shows that the maximum profit decreases at a decreasing rate as the unit cost increases.

Figure 1.27 Conditional Formatting Dialog Box

09953_ch01_ptg01_001-036.indd 28 04/03/19 10:51 PM

Figure 1.28 Profit as a Function of Unit Cost and Unit Price

Other Modeling Issues The layout of the Golf Club Demand.xlsx file is fairly straightforward. However, note that instead of a single worksheet, there are two worksheets, partly for logical purposes and partly to reduce clutter. There is one worksheet for estimation of the demand function and the various scatterplots, and there is another for the profit model.

One last issue is the placement of the data tables for the sensitivity analysis. You might be inclined to put these on a sepa- rate Sensitivity worksheet. However, Excel does not allow you to build a data table on one worksheet that uses a row or column input cell from another worksheet. Therefore, you are forced to put the data tables on the same worksheet as the profit model.

1-3f Decisions Involving the Time Value of Money In many business situations, cash flows are received at different points in time, and a com- pany must determine a course of action that maximizes the “value” of cash flows. Here are some examples:

• Should a company buy a more expensive machine that lasts for 10 years or a less expensive machine that lasts for 5 years?

• What level of plant capacity is best for the next 20 years? • A company must market one of several midsize cars. Which car should it market?

To make decisions when cash flows are received at different points in time, the key concept is that the later a dollar is received, the less valuable the dollar is. For example, suppose you can invest money at a 5% annual interest rate. Then $1.00 received now is essentially equivalent to $1.05 a year from now. The reason is that if you have $1.00 now, you can invest it and gain $0.05 in interest in one year. If r 5 0.05 is the interest rate (expressed as a decimal), we can write this as

$1.00 now 5 $1.05 a year from now 5 $1.00(1 1 r) (1.2)

Dividing both sides of Equation (1.2) by 1 1 r, this can be rewritten as

$1.00 3 1>(1 1 r) now 5 $1.00 a year from now (1.3) The value 1>(1 1 r) in Equation (1.3) is called the discount factor, and it is always less than 1. The quantity on the left, which evaluates to $0.952 for r 5 0.05, is called the pres- ent value of $1.00 received a year from now. The idea is that if you had $0.952 now, you could invest it at 5% and have it grow to $1.00 in a year.

1-3 Introduction to Spreadsheet Modeling 2 9

09953_ch01_ptg01_001-036.indd 29 04/03/19 10:51 PM

3 0 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

In general, if money can be invested at annual rate r compounded each year, then $1 received t years from now has the same value as 1>(1 1 r)t dollars received today— that is, the $1 is discounted by the discount factor raised to the t power. If you multiply a cash flow received t years from now by 1>(1 1 r)t to obtain its present value, the total of these present values over all years is called the net present value (NPV) of the cash flows. Financial theory states that projects with positive NPVs increase the value of the company, whereas projects with negative NPVs decrease the value of the company.

The rate r (usually called the discount rate) used by major corporations is gener- ally based on their weighted average cost of capital. The value of r used to evaluate any particular project depends on a number of things and can vary from project to project. Because this is the focus of finance courses, we will not pursue it here. But given a suit- able value of r, the following example illustrates how spreadsheet models and the time value of money can be used to make complex business decisions.

the time Value of Money

Money earned in the future is less valuable than money earned today, for the sim- ple reason that money earned today can be invested to earn interest. Similarly, costs incurred in the future are less “costly” than costs incurred today. This is why you typically don’t simply sum up revenues and costs in a multiperiod model. You instead discount future revenues and costs for a fair comparison with revenues and costs incurred today. The resulting sum of discounted cash flows is the net present value (NPV), and it forms the cornerstone of much of financial theory and applications.

Fundamental Insight

EXAMPLE

1.6 CALCULATING NPV AT ACRON Acron is a large drug company. At the current time, the beginning of year 0, Acron is trying to decide whether one of its new drugs, Niagra, is worth pursuing. Niagra is in the final stages of development and will be ready to enter the market one year from now. The final cost of development, to be incurred at the beginning of year 1, is $15 million. Acron estimates that the demand for Niagra will gradually grow and then decline over its useful lifetime of 20 years. Specifically, the company expects its gross margin (revenue minus cost) to be $1.5 million in year 1, then to increase at an annual rate of 6% through year 8, and finally to decrease at an annual rate of 5% through year 20. Acron wants to develop a spreadsheet model of its 20-year cash flows, assuming its cash flows, other than the initial development cost, are incurred at the ends of the respective years. (To simplify the model, taxes are ignored.) Using an annual discount rate of 7.5% for the purpose of calculating NPV, the drug company wants to answer the following questions:

1. Is the drug worth pursuing, or should Acron abandon it now and not incur the $15 million development cost? 2. How do changes in the model inputs change the answer to question 1?

Objective To illustrate efficient selection and copying of large ranges and to learn Excel’s NPV function.

Solution The model of Acron’s cash flows appears in Figure 1.29. As with many financial spreadsheet models that extend over a multi- year period, you enter “typical” formulas in the first year or two and then copy this logic down to all years. (We could make the years go across, not down. In that case, splitting the screen is useful so that you can see the first and last years of data. Splitting the screen is explained in the following Excel Tip.)

The discount factor is 1 divided by (1 plus the discount rate). To discount a cash flow that occurs t years from now, multiply it by the discount factor raised to the t power. The NPV is the sum of all discounted cash flows.

09953_ch01_ptg01_001-036.indd 30 04/03/19 10:51 PM

1-3 Introduction to Spreadsheet Modeling 3 1

Splitting the Screen

To split the screen horizontally and vertically, select the cell where you want the split to occur and click Split on the View ribbon. This creates horizontal and vertical split lines that you can move if you like.

Excel Tip

To create the model, complete the following steps. (See the file Calculating NPV Finished.xlsx.)

1. Inputs and range names. Enter the given input data in the blue cells, and name the ranges as shown. As usual, the range names for cells B4 through B9 can be created all at once with the Create from Selection shortcut, as can the range name for the gross margins in column B. In the latter case, select the range B12:B32 and then use the Create from Selection shortcut.

2. Cash flows. Start by entering the formula

5Gross_margin_year_1

in cell B13 for the year 1 gross margin. Then enter the general formula

5IF(A14<5Peak_year,B13*(11Rate_of_increase),B13*(1−Rate_of_decrease))

in cell B14 and copy it down to cell B32 to calculate the other yearly gross margins. Note how this IF function checks the year index in column A to see whether sales are still increasing or have started to decrease. By using the (range-named) input cells in this formula, you can change any of these inputs in cells B6 through B8, and the calculated cells will auto- matically update. This is a much better practice than embedding the numbers in the formula itself.

Figure 1.29 Acron’s Model of 20-Year NPV

1 2 3 4 5 6 7 8 9

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

A B C D E F G Calculating NPV at egnaRnorcA names used:

Development_cost =Model!$B$4 Inputs 9$B$!ledoM=etar_tnuocsiD Development Gross margin year 1 =Model!$B$5Gross_margin_year_11.2 Peak year Rate of increase Rate of decrease

cost 15 Gross_margin =Model!$B$13:$B$32

8 Peak_year =Model!$B$6 6% Rate_of_decrease =Model!$B$8 5% Rate_of_increase =Model!$B$7

Discount rate 7.5%

Cash flows End of year Gross margin

VPN

(through peak year) (after peak year)

1 1.2000 2 1.3200 3 1.4520 4 1.5972 5 1.7569 6 1.9326 7 2.1259 8 2.3385 9 2.2215

10 2.1105 11 2.0049 12 1.9047 13 1.8095 14 1.7190 15 1.6330 16 1.5514 17 1.4738 18 1.4001 19 1.3301 20 1.2636

2.3961

09953_ch01_ptg01_001-036.indd 31 04/03/19 10:51 PM

3 2 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

3. Net present value. The NPV is based on the sequence of cash flows in column B. From the general discussion of NPV, to discount everything back to the beginning of year 1, the value in cell B13 should be multiplied by 1>(1 1 r)1, the value in cell B14 should be multiplied by 1>(1 1 r)2, and so on, and these quantities should be summed to obtain the NPV. (Here, r 5 0.075 is the discount rate.) Fortunately, how- ever, Excel has a built-in NPV function to accomplish this calculation. To use it, enter the formula

5−Development_cost1NPV(Discount_rate,Gross_margin)

in cell B33. The NPV function takes two arguments: the discount rate and a range of cash flows. It assumes that the first cell in this range is the cash flow at the end of year 1, the second cell is the cash flow at the end of year 2, and so on. This explains why the development cost is subtracted outside the NPV function—it is incurred at the beginning of year 1. In general, any cash flow incurred at the beginning of year 1 must be placed outside the NPV function so that it won’t be discounted.

Note that the sum of the cash flows in column B is slightly more than $34.54 million, but the NPV (aside from the devel- opment cost) is only about $18.06 million. This is because values farther into the future are discounted so heavily. At the extreme, the $1.2188 million cash flow in year 20 is equivalent to only $1.218831>(1 1 0.075)204 5 $0.287 million now.

The stream of cash flows in the NPV function must occur at the ends of year 1, year 2, and so on. If the timing is irregular, you can discount “manually” or you can use Excel’s XNPV function.

Use the Ctrl1Enter shortcut to enter a formula in a range all at once. It is equivalent to copying.

Efficient Selection

An easy way to select a large range, assuming that the first and last cells of the range are visible, is to select the first cell and then, with your finger on the Shift key, select the last cell. (Don’t forget that you can split the screen to make these first and last cells visible when the range is large.) This selects the entire range and is easier than scrolling.

Excel Tip

Efficient Copying with Ctrl1Enter

An easy way to enter the same formula in a range all at once is to select the range (as in the preceding Excel Tip), type the formula, and press Ctrl1Enter (both keys at once). After you get used to this shortcut, you will probably use it all the time.

Excel Tip The shortcut for copying in Excel for Mac is command+enter.

NPV

The NPV function takes two arguments, the discount rate (entered as a decimal, such as 0.075 for 7.5%) and a stream of cash flows. These cash flows are assumed to occur in consecutive years, starting at the end of year 1. If there is an initial cash flow at the beginning of year 1, such as an initial investment, it should be entered outside the NPV function. (There is also an XNPV function that has three arguments: a discount rate, a series of cash flows, and a series of dates when the cash flows occur. Because these dates do not have to be equally spaced through time, this function is more flexible than the NPV function. We will not use the XNPV function in this book, but you can learn more about it in Excel’s online help.)

Excel Function

09953_ch01_ptg01_001-036.indd 32 04/03/19 10:51 PM

1-4 Conclusion In this chapter we have tried to convince you that the skills in this book are important for you as you enter the business world. The methods we discuss are no longer the sole province of the “quant jocks.” By having a computer loaded with powerful soft- ware, you incur a responsibility to use this software to analyze business problems effectively. We have also given you a taste of the spreadsheet modeling process you will see in much of the rest of the book, and you have learned a number of useful Excel tools for analyzing spreadsheet models. You will get to use these and other Excel tools in the next few chapters as you enter the exciting world of data analysis.

Deciding Whether to Continue with the Drug NPV calculations are typically used to see whether a certain project should be undertaken. If the NPV is positive, the project is worth pursuing. If the NPV is negative, the company should look for other places to invest its money. Figure 1.29 shows that the NPV for this drug is positive, over $3.06 million. Therefore, if Acron is comfortable with its predictions of future cash flows, it should continue with the development and marketing of the drug. However, Acron might first want to see how sensitive the NPV is to changes in the sales predictions. After all, these predictions are intel- ligent guesses at best.

One possible sensitivity analysis appears in Figure 1.30. This shows a one-way data table to see how the NPV changes when the number of years of increase (the input in cell B6) changes. Again, the important question is whether the NPV stays positive. It certainly does when the input variable is greater than its current value of 8. However, if sales start decreasing soon enough—that is, if the value in B6 is 4 or less—the NPV turns negative. This should probably not concern Acron, because its best guess for the years of increase is considerably greater than 4.

Another possibility is to see how long and how good the good years are. To do this, you can create the two-way data table shown in Figure 1.31, where cell B7 is the row input cell and cell B6 is the column input cell. Now you can see that if sales increase through year 6, all reasonable yearly increases result in a positive NPV. However, if sales increase only through year 5, then a low enough yearly increase can produce a negative NPV. Acron might want to step back and estimate how likely these bad scenarios are before proceeding with the drug.

11 12 13 14 15 16 17 18 19 20

D E Sensitivity to peak year (cell B6)

6.7451 3 −0.9700 4 0.5688 5 2.1146 6 3.6629 7 5.2084 8 6.7451 9 8.2657

10 9.7616

Figure 1.30 Sensitivity of NPV to Years of Sales Increase

22 23 24 25 26 27 28 29 30 31

D E F G H I J Sensitivity to rate of increase in early years (cell B7) and peak year (cell B6)

6.7451 5% 6% 7% 8% 3 4 5 6 7 8 9

–2.0305 –1.0958 –0.1995

0.6574 1.4739 2.2489 2.9809 3.6682

–1.8221 –0.7742

0.2401 1.2191 2.1609 3.0633 3.9237 4.7393

–1.6119 –0.4470

0.6911 1.8000 2.8768 3.9183 4.9206 5.8798

–1.3998 –0.1142

1.1537 2.4006 3.6227 4.8156 5.9746 7.0940

–1.1858 0.2244 1.6281 3.0214 4.3995 5.7573 7.0886 8.3864

–0.9700 0.5688 2.1146 3.6629 5.2084 6.7451 8.2657 9.7616

9% 10%

Figure 1.31 Sensitivity of NPV to Years of Increase and Yearly Increase

1-4 Conclusion 3 3

09953_ch01_ptg01_001-036.indd 33 04/03/19 10:51 PM

3 4 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

Summary of Key Terms TERM EXPLANATION EXCEL PAGES Model inputs The given numeric values in any problem

statement 21

Decision variables The variables a decision maker has control over to obtain the best solutions

Model outputs The numeric values that result from com- binations of inputs and decision variables through the use of logical formulas

IF function Useful for implementing logic =IF(condition,result_If_True, result_If_False)

relative, absolute cell addresses

Useful for copying formulas; absolute row or column stays fixed, relative row or column “moves”

A1 (relative), $A1 or A$1 (mixed), $A$1 (absolute); press F4 to cycle through possibilities

range names Useful for making formulas more readable

Type name in Name box, or use Create from Selection shortcut on Formulas ribbon

pasting range names Provides a list of all range names in the current workbook

Use Paste List from Use in Formula dropdown list on Formulas ribbon

Cell comments Useful for documenting contents of cells Right-click cell, select Insert Comment menu item

One-way data table Shows how one or more outputs vary as a single input varies

Use Data Table from What-If Analysis dropdown list on Data ribbon

Goal Seek Solves one equation in one unknown Use Goal Seek from What-If Analysis dropdown list on Data ribbon

Formula auditing toolbar Useful for checking which cells are related to other cells through formulas

Use Formula Auditing buttons on Formulas ribbon

fx button Useful for getting help on Excel functions On Formulas ribbon 41

VLOOKUp function Useful for finding a particular value based on a comparison

=VLOOKUP(valueToCompare, lookupTable,columnToReturn,True/False)

two-way data table Shows how a single output varies as two inputs vary

Use Data Table from What-If Analysis dropdown list on Data ribbon

SUMprODUCt function Calculates the sum of products of values in two (or more) same-sized ranges

=SUMPRODUCT(range1,range2) 43

trendline tool Superimposes the best-fitting line or curve of a particular type on a scatter chart or time series graph

With chart selected, right-click any point and select Add Trendline

Conditional formatting Formats cells depending on whether specified conditions hold

Use Conditional Formatting on Home ribbon

Net present value (NpV) The current worth of a stream of cash flows that occur in the future

Discount rate Interest rate used for discounting future cash flows to calculate present values

Splitting screen Useful for separating the screen horizontally and/or vertically

Select Split from View ribbon 56

efficient selection Useful for selecting a large rectangular range

While pressing the Shift key, select the upper-left and bottom-right cells of the range

efficient copying Shortcut for copying a formula to a range Select the range, enter the formula, and press Ctrl1Enter

NpV function Calculates NPV of a stream of cash flows at the ends of consecutive years, starting in year 1

=NPV(discountRate,cashFlows) 57

09953_ch01_ptg01_001-036.indd 34 04/03/19 10:51 PM

1-4 Conclusion 3 5

Problems Solutions for problems whose numbers appear within a colored box can be found in the Student Solution Files.

Level A 1. The sensitivity analysis in Example 1.3 was on the

response rate. Suppose now that the response rate is known to be 8%, and the company wants to perform a sensitivity analysis on the number mailed. After all, this is a variable under direct control of the company. Cre- ate a one-way data table and a corresponding line chart of profit versus the number mailed, where the number mailed varies from 80,000 to 150,000 in increments of 10,000. Does it appear, from the results you see here, that there is an optimal number to mail, from all possible values, that maximizes profit?

2. Continuing the previous problem, use Goal Seek for each value of number mailed (once for 80,000, once for 90,000, and so on). For each, find the response rate that allows the company to break even. Then chart these val- ues, where the number mailed is on the horizontal axis, and the breakeven response rate is on the vertical axis. Explain the behavior in this chart.

3. In Example 1.3, the range E9:E11 does not have a range name. Open your completed Excel file and name this range Costs. Then look at the formula in cell E12. It does not automatically use the new range name. Modify the formula so that it does. Then select cell G4 and paste the new list of range names over the previous list.

4. In some ordering problems, like the one in Example 1.4, whenever demand exceeds existing inventory, the excess demand is not lost but is filled by expedited orders—at a premium cost to the company. Change Sam’s model to reflect this behavior. Assume that the unit cost of expediting is $40, well above the highest regular unit cost.

5. The spreadsheet model for Example 1.4 contains a two-way data table for profit versus order quantity and demand. Experiment with Excel’s chart types to create a chart that shows this information graphically in an intu- itive format. (Choose the format you would choose to give a presentation to your boss.)

6. In Example 1.4, the quantity discount structure is such that all the units ordered have the same unit cost. For example, if the order quantity is 2500, then each unit costs $22.25. Sometimes the quantity discount structure is such that the unit cost for the first so many items is one value, the unit cost for the next so many units is a slightly lower value, and so on. Modify the model so that Sam’s pays $24 for units 1 to 1500, $23 for units 1501 to 2500, and $22 for units 2501 and above. For example, the total cost for an order quantity of 2750 is 1500(24) 1 1000(23) 1 250(22). (Hint: Use IF func- tions, not VLOOKUP.)

7. Suppose you have an extra six months of data on demands and prices, in addition to the data in Exam- ple 1.5. These extra data points are (350,84), (385,72), (410,67), (400,62), (330,92), and (480,53). (The price is shown first and then the demand at that price.) After adding these points to the original data, use Excel’s Trendline tool to find the best-fitting linear, power, and exponential trend lines. Then calculate the MAPE for each of these, based on all 18 months of data. Does the power curve still have the smallest MAPE?

8. Consider the power curve y 5 10000x222.35. Calculate y when x 5 5; when x 5 10; and when x 5 20. For each of these values of x, find the percentage change in y when x increases by 1%. That is, find the percentage change in y when x increases from 5 to 5.05; when it increases from 10 to 10.1; and when it increases from 20 to 20.2. Is this percentage change constant? What num- ber is it very close to? Write a brief memo on what you have learned about power curves from these calcula- tions.

9. Consider the exponential curve y 5 1000e20.014x. Calcu- late y when x 5 5; when x 5 10; and when x 5 20. For each of these values of x, find the percentage change in y when x increases by one unit. That is, find the per- centage change in y when x increases from 5 to 6; when it increases from 10 to 11; and when it increases from 20 to 21. Is this percentage change constant? When expressed as a decimal, what number is it very close to? Write a brief memo on what you have learned about exponential curves from these calculations.

10. Modify the model in Example 1.6 so that development lasts for an extra year. Specifically, assume that develop- ment costs of $12 million and $3 million are incurred at the beginnings of years 1 and 2, and then the sales in the current model occur one year later, that is, from year 2 until year 21. Again, calculate the NPV discounted back to the beginning of year 1, and perform the same sensi- tivity analyses. Comment on the effects of this change in timing.

11. Modify the model in Example 1.6 so that sales increase, then stay steady, and finally decrease. Specifically, assume that the gross margin is $1.5 million in year 1, then increases by 10% annually through year 6, then stays constant through year 10, and finally decreases by 5% annually through year 20. Perform a sensitivity anal- ysis with a two-way data table to see how NPV varies with the length of the increase period (currently 6 years) and the length of the constant period (currently 4 years). Comment on whether the company should pursue the drug, given your results.

12. Create a one-way data table in the model in Example 1.6 to see how the NPV varies with discount rate, which is allowed to vary from 6% to 10% in increments of 0.5%. Explain intuitively why the results go in the direction they go—that is, the NPV decreases as the discount

09953_ch01_ptg01_001-036.indd 35 04/03/19 10:51 PM

rate increases. Should the company pursue the drug for all of these discount rates?

13. Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand is $50.00. Her best guess is that she can sell 300 cups per week at $0.50 per cup. The variable cost of producing a cup of lemonade is $0.20. a. Given her other assumptions, what level of sales vol-

ume will enable Julie to break even? b. Given her other assumptions, discuss how a change in

sales volume affects profit. c. Given her other assumptions, discuss how a change in

sales volume and variable cost jointly affect profit. d. Use Excel’s Formula Auditing tools to show which

cells in your spreadsheet affect profit directly. 14. You are thinking of opening a small copy shop. It costs

$5000 to rent a copier for a year, and it costs $0.03 per copy to operate the copier. Other fixed costs of running the store will amount to $400 per month. You plan to charge an average of $0.10 per copy, and the store will be open 365 days per year. Each copier can make up to 100,000 copies per year. a. For one to five copiers rented and daily demands of

500, 1000, 1500, and 2000 copies per day, find annual profit. That is, find annual profit for each of these combinations of copiers rented and daily demand.

b. If you rent three copiers, what daily demand for cop- ies will allow you to break even?

c. Graph profit as a function of the number of copiers for a daily demand of 500 copies; for a daily demand of 2000 copies. Interpret your graphs.

15. Dataware is trying to determine whether to give a $10 rebate, cut the price $6, or have no price change on a software product. Currently, 40,000 units of the prod- uct are sold each week for $45 apiece. The variable cost of the product is $5. The most likely case appears to be that a $10 rebate will increase sales 30%, and half of all people will claim the rebate. For the price cut, the most likely case is that sales will increase 20%. a. Given all other assumptions, what increase in sales

from the rebate would make the rebate and price cut equally desirable?

b. Dataware does not really know the increase in sales that will result from a rebate or price cut. However, the company is sure that the rebate will increase sales

by between 15% and 40% and that the price cut will increase sales by between 10% and 30%. Perform a sensitivity analysis that could be used to help deter- mine Dataware’s best decision.

16. A company manufacturers a product in the United States and sells it in England. The unit cost of manufacturing is $50. The current exchange rate (dollars per pound) is 1.221. The demand function, which indicates how many units the company can sell in England as a function of price (in pounds) is of the power type, with constant 27556759 and exponent 22.4. a. Develop a model for the company’s profit (in dollars)

as a function of the price it charges (in pounds). Then use a data table to find the profit-maximizing price to the nearest pound.

b. If the exchange rate varies from its current value, does the profit-maximizing price increase or decrease? Does the maximum profit increase or decrease?

17. The yield of a chemical reaction is defined as the ratio (expressed as a percentage) of usable output to the amount of raw material input. Suppose the yield of a chemical reaction depends on the length of time the pro- cess is run and the temperature at which the process is run. The yield can be expressed as follows:

Yield 5 90.79 2 1.095x1 2 1.045x2 2 2.781x 2 1

22.524x22 2 0.775x1x2

Here x1 5 (Temperature 2 125)/10 and x2 5 (Time 2 300)/30, where temperature is measured in degrees Fahrenheit, and time is measured in seconds. Use a data table to find the temperature and time settings that max- imize the yield of this process.

18. The payback of a project is the number of years it takes before the project’s total cash flow is positive. Pay- back ignores the time value of money. It is interesting, however, to see how differing assumptions on project growth impact payback. Suppose, for example, that a project requires a $300 million investment right now. The project yields cash flows for 10 years, and the year 1 cash flow will be between $30 million and $100 mil- lion. The annual cash flow growth will be between 5% and 25% per year. (Assume that this growth is the same each year.) Use a data table to see how the project pay- back depends on the year 1 cash flow and the cash flow growth rate.

3 6 C h a p t e r 1 I n t r o d u c t i o n t o B u s i n e s s a n a l y t i c s

09953_ch01_ptg01_001-036.indd 36 04/03/19 10:51 PM

CHAPTER 2 Describing the Distribution of a Variable

CHAPTER 3 Finding Relationships among Variables

CHAPTER 4 Business Intelligence (BI) Tools for Data Analysis

P A R T 1 DATA ANALYSIS

09953_ch02_ptg01_037-083.indd 37 04/03/19 10:41 PM

CHAPTER 2 Describing the Distribution of a Variable

RECENT PRESIDENTIAL ELECTIONS Presidential elections in the United States are scrutinized more than ever. It hardly seems that one is over before we start hearing plans and polls for the next. There is thor- ough coverage of the races leading up to the elections, but it is also interesting to analyze the results after the elec- tions have been held. This is not difficult, given the many informative websites that appear immediately with elec- tion results. For example, a Web search for “2016 pres- idential election results” finds many sites with in-depth results, interactive maps, and more. In addition, the resulting data can often be imported into Excel® for fur- ther analysis.

The file Presidential Elections.xlsx contains such downloaded data for the 2000 (Bush versus Gore), 2004 (Bush versus Kerry), 2008 (Obama versus McCain), 2012 (Obama versus Romney), and 2016 (Trump versus Clinton) elec- tions. The results of the 2000 election are particularly interesting. As you might remember, this was one of the closest elections of all time, with Bush defeating Gore by a very narrow margin in the electoral vote, 271 to 266, following a dis- puted recount in Florida. In fact, Gore actually beat Bush in the total count of U.S. votes, 50,999,897 to 50,456,002. However, because of the all-or-nothing nature of electoral votes in each state, Bush’s narrow margin of victory in many closely con- tested states won him a lot of electoral votes. In contrast, Gore outdistanced Bush by a wide margin in several large states, winning him the same electoral votes he would have won even if these races had been much closer.

A closer analysis of the state-by-state results shows how this actually hap- pened. The Excel file contains two new columns: Bush Votes minus Gore Votes and Pct for Bush minus Pct for Gore, with a value for each state (including the District of Columbia). We then created column charts of these two variables, as shown in Figures 2.1 and 2.2.

Each of these charts tells the same story, but in slightly different ways. From Figure 2.1, you can see how Gore won big (large vote difference) in several large states, most notably California, Massachusetts, and New York. Bush’s only comparable margin of victory was in his home state of Texas. However, Bush won a lot of close races in states with relatively few electoral votes—but enough to add up to an overall win. As Figure 2.2 indicates, many of these “close” races, such as Alaska and Idaho for Bush

Sc ot

t O ls

on /G

et ty

Im ag

es N

ew s/

G et

ty Im

ag es

09953_ch02_ptg01_037-083.indd 38 04/03/19 10:41 PM

2-1 Introduction 3 9

and District of Columbia for Gore, were not that close after all, at least not from a per- centage standpoint. This is one case of many where multiple charts can be created to “tell a story.” Perhaps an argument can be made that Figure 2.1 tells the story best, but Figure 2.2 is also interesting.

Figure 2.1 Chart of Vote Differences

Homework is Completed By:

Writer	Writer Name	Amount	Client Comments & Rating
ONLINE	Instant Homework Helper 4.8 4305 Orders Completed	$36	She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up! 5.00
Answer.docx Turnitin Report.pdf Contact Writer For Solution Contact Writer For Solution

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 3 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 6 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

100% Plagiarism Free
Proper APA/MLA/Harvard Referencing
Delivery in 12 Hours After Placing Order
Free Turnitin Report
Unlimited Revisions
Privacy Guaranteed

Order Now

6 writers have sent their proposals to do this homework:

Writer	Writer Name	Offer	Chat
ONLINE	Math Specialist I will provide you with the well organized and well research papers from different primary and secondary sources will write the content that will support your points. 4.9 1407 Orders Completed	$25	Chat With Writer
ONLINE	Smart Tutor I have done dissertations, thesis, reports related to these topics, and I cover all the CHAPTERS accordingly and provide proper updates on the project. 4.9 1008 Orders Completed	$17	Chat With Writer
ONLINE	Solutions Store As per my knowledge I can assist you in writing a perfect Planning, Marketing Research, Business Pitches, Business Proposals, Business Feasibility Reports and Content within your given deadline and budget. 4.9 1512 Orders Completed	$35	Chat With Writer
ONLINE	Peter O. I am an experienced researcher here with master education. After reading your posting, I feel, you need an expert research writer to complete your project.Thank You 4.9 1260 Orders Completed	$33	Chat With Writer
ONLINE	Finance Homework Help I have done dissertations, thesis, reports related to these topics, and I cover all the CHAPTERS accordingly and provide proper updates on the project. 4.8 2499 Orders Completed	$31	Chat With Writer
ONLINE	Top Quality Assignments Being a Ph.D. in the Business field, I have been doing academic writing for the past 7 years and have a good command over writing research papers, essay, dissertations and all kinds of academic writing and proofreading. 4.9 1071 Orders Completed	$46	Chat With Writer