Mgmt 650 Business Data Analysis

Business Data Analysis

SCH-MGMT 650

STATISTICS FOR MANAGERS
USING Microsoft Excel
David M. Levine David F. Stephan
Timothy C. Krehbiel Mark L. Berenson

Custom Edition for

UMASS-Amherst
Professor Robert Nakosteen
Taken from:
Statistics for Managers: Using Microsoft Excel, Fifth Edition by David M. Levine, David F. Stephan, Timothy C. Krehbiel, and Mark L. Berenson

Cover photo taken by Lauren Labrecque.
Taken from:
Statistics for Managers: Using Microsoft Excel, Fifth Edition by David M. Levine, David F. Stephan, Timothy C. Krehbiel, and Mark L. Berenson
Copyright 2008, 2005, 2002, 1999, 1997 by Pearson Education, Inc.
Published by Prentice Hall
Upper Saddle River, New Jersey 07458
All rights reserved. No part
of this book may be reproduced, in any form or by any means, without permission in writing from the publisher.
This special edition published in cooperation with Pearson Custom Publishing.
The information, illustrations, and/or software contained in this book, and regarding the above-mentioned programs, are provided As Is, without warranty of any kind, express or implied, including without limitation any warranty concerning the accuracy, adequacy, or completeness of such information. Neither the publisher, the authors, nor the copyright holders shall be responsible for any claims attributable to errors, omissions, or other inaccuracies contained in this book. Nor shall they be liable for direct, indirect, special, incidental, or consequential damages arising out of the use of such information or material.
All trademarks, service marks, registered trademarks, and registered service marks are the property of their respective owners and are used herein for identification purposes only.

Printed in the United States of America
10 9 8 7 6 5 4 3 2 1
ISBN 0-536-04080 X
2008600006
KA
Please visit our web site at www.pearsoncustom.com

PEARSON CUSTOM PUBLISHING
501 Boylston Street, Suite 900, Boston, MA 02116
A Pearson Education Company

To our wives,
Marilyn L., Mary N., Patti K., and Rhoda B., and to our children
Sharyn, Mark, Ed, Rudy, Rhonda, Kathy, and Lori

ABOUT

THE

AUTHORS

The textbook authors meet to discuss statistics at Shea Stadium for a Mets v. Phillies game.
Shown left to right,
Mark Berenson, David
Stephan, David Levine,
Tim Krehbiel.

David M. Levine is Professor Emeritus of Statistics and Computer Information Systems at
Bernard M. Baruch College (City University of New York). He received B.B.A. and M.B.A. degrees in Statistics from City College of New York and a Ph.D. degree from New York
University in Industrial Engineering and Operations Research. He is nationally recognized as a leading innovator in statistics education and is the co-author of 14 books including such best selling statistics textbooks as Statistics for Managers using Microsoft Excel, Basic Business
Statistics: Concepts and Applications, Business Statistics: A First Course, and Applied
Statistics for Engineers and Scientists using Microsoft Excel and Minitab.
He also recently wrote Even You Can Learn Statistics and Statistics for Six Sigma Green Belts published by Financial Times-Prentice-Hall. He is coauthor of Six Sigma for Green Belts and
Champions and Design for Six Sigma for Green Belts and Champions, also published by
Financial Times-Prentice-Hall, and Quality Management Third Ed., McGraw-Hill-Irwin
(2005). He is also the author of Video Review of Statistics and Video Review of Probability, both published by Video Aided Instruction. He has published articles in various journals including Psychometrika, The American Statistician, Communications in Statistics,
Multivariate Behavioral Research, Journal of Systems Management, Quality Progress, and The
American Anthropologist and given numerous talks at Decision Sciences, American Statistical
Association, and Making Statistics More Effective in Schools of Business conferences. While at Baruch College, Dr. Levine received several awards for outstanding teaching and curriculum development. David F. Stephan is an instructional designer and lecturer who pioneered the teaching of spreadsheet applications to business school students in the 1980 s. He has over 20 years experience teaching at Baruch College, where he developed the first personal computing lab to support statistics and information systems studies and was twice nominated for his excellence in teaching. He is also proud to have been the lead designer and assistant project director of a
U.S. Department of Education FIPSE project that brought interactive, multimedia learning to
Baruch College.
Today, David focuses on developing materials that help users make better use of the information analysis tools on their computer desktops and is a co-author, with David M. Levine, of
Even You Can Learn Statistics.

vi

About the Authors

vii

Timothy C. Krehbiel is Professor of Decision Sciences and Management Information
Systems at the Richard T. Farmer School of Business at Miami University in Oxford, Ohio. He teaches undergraduate and graduate courses in business statistics. In 1996 he received the prestigious Instructional Innovation Award from the Decision Sciences Institute. In 2000 he received the Richard T. Farmer School of Business Administration Effective Educator Award.
He also received a Teaching Excellence Award from the MBA class of 2000.
Krehbiel s research interests span many areas of business and applied statistics. His work appears in numerous journals including Quality Management Journal, Ecological Economics,
International Journal of Production Research, Journal of Marketing Management,
Communications in Statistics, Decision Sciences Journal of Innovative Education, Journal of
Education for Business, Marketing Education Review, and Teaching Statistics. He is a coauthor of three statistics textbooks published by Prentice Hall: Business Statistics: A First
Course, Basic Business Statistics, and Statistics for Managers Using Microsoft Excel. Krehbiel is also a co-author of the book Sustainability Perspectives in Business and Resources.
Krehbiel graduated summa cum laude with a B.A. in history from McPherson College in 1983, and earned an M.S. (1987) and Ph.D. (1990) in statistics from the University of Wyoming.

Mark L. Berenson is Professor of Management and Information Systems at Montclair State
University (Montclair, New Jersey) and also Professor Emeritus of Statistics and Computer
Information Systems at Bernard M. Baruch College (City University of New York). He currently teaches graduate and undergraduate courses in statistics and in operations management in the School of Business and an undergraduate course in international justice and human rights that he co-developed in the College of Humanities and Social Sciences.
Berenson received a B.A. in economic statistics and an M.B.A. in business statistics from City
College of New York and a Ph.D. in business from the City University of New York.
Berenson s research has been published in Decision Sciences Journal of Innovative Education,
Review of Business Research, The American Statistician, Communications in Statistics,
Psychometrika, Educational and Psychological Measurement, Journal of Management
Sciences and Applied Cybernetics, Research Quarterly, Stats Magazine, The New York
Statistician, Journal of Health Administration Education, Journal of Behavioral Medicine, and
Journal of Surgical Oncology. His invited articles have appeared in The Encyclopedia of
Measurement & Statistics and in Encyclopedia of Statistical Sciences. He is co-author of
11 statistics texts published by Prentice Hall, including Statistics for Managers using Microsoft
Excel, Basic Business Statistics: Concepts and Applications, and Business Statistics: A First
Course.
Over the years, Berenson has received several awards for teaching and for innovative contributions to statistics education. In 2005 he was the first recipient of The Catherine A. Becker
Service for Educational Excellence Award at Montclair State University.

BRIEF CONTENTS
Preface xix
1 INTRODUCTION AND DATA COLLECTION 1
2 PRESENTING DATA IN TABLES AND CHARTS 31
3 NUMERICAL DESCRIPTIVE MEASURES 95
4 BASIC PROBABILITY 147
5 SOME IMPORTANT DISCRETE PROBABILITY DISTRIBUTIONS 179
6 THE NORMAL DISTRIBUTION AND OTHER CONTINUOUS DISTRIBUTIONS 217
7 SAMPLING AND SAMPLING DISTRIBUTIONS 251
8 CONFIDENCE INTERVAL ESTIMATION 283
9 FUNDAMENTALS OF HYPOTHESIS TESTING: ONE-SAMPLE TESTS 327
10 SIMPLE LINEAR REGRESSION 369
11 INTRODUCTION TO MULTIPLE REGRESSION 429
Appendices A-F 471
Self-Test Solutions and Answers to Selected Even-Numbered Problems 513
Index 535

CD-ROM TOPICS
4.5
5.6
6.6
7.6
8.7
9.7

COUNTING RULES CD4-1
USING THE POISSON DISTRIBUTION TO APPROXIMATE THE BINOMIAL DISTRIBUTION CD5-1
THE NORMAL APPROXIMATION TO THE BINOMIAL DISTRIBUTION CD6-1
SAMPLING FROM FINITE POPULATIONS CD7-1
ESTIMATION AND SAMPLE SIZE DETERMINATION FOR FINITE POPULATIONS CD8-1
THE POWER OF A TEST CD9-1

ix

CONTENTS
Preface xix
1 INTRODUCTION AND DATA COLLECTION 1
1.1
1.2

Why Learn Statistics 2
Statistics for Managers 2
How This Text is Organized 3
Using Statistics @ Good Tunes 4
1.3 Basic Vocabulary of Statistics 4
1.4 Data Collection 6
1.5 Types of Variables 8
Levels of Measurement and Measurement Scales 9
1.6 Microsoft Excel Worksheets 11
Worksheet Cells 11
Designing Effective Worksheets 12
Summary 13
Key Terms 13
Chapter Review Problems 14
End-of-Chapter Cases 15
Learning with the Web Cases 16
References 17
Excel Companion to Chapter 1 18
Key Terms 30

2 PRESENTING DATA IN TABLES AND CHARTS 31
Using Statistics @ Choice Is Yours, Part I 32
2.1 Tables and Charts for Categorical Data 32
The Summary Table 33
The Bar Chart 33
The Pie Chart 34
The Pareto Diagram 35
2.2 Organizing Numerical Data 40
The Ordered Array 41
The Stem-and-Leaf Display 41
2.3 Tables and Charts for Numerical Data 44
The Frequency Distribution 44
The Relative Frequency Distribution and the Percentage Distribution 46
The Cumulative Distribution 47
The Histogram 48
The Polygon 50
The Cumulative Percentage Polygon (Ogive) 51
2.4 Cross Tabulations 54
The Contingency Table 55
The Side-by-Side Bar Chart 56

xi

xii

Contents

2.5

2.6

Scatter Plots and Time-Series Plots 58
The Scatter Plot 58
The Time-Series Plot 59
Misusing Graphs and Ethical Issues 62
Microsoft Excel Graphs 64

Summary 66
Key Terms 66
Chapter Review Problems 67
Managing the Springville Herald 73
Web Case 74
References 74
Excel Companion to Chapter 2 75

3 NUMERICAL DESCRIPTIVE MEASURES 95
Using Statistics @ Choice Is Yours, Part II 96
3.1 Measures of Central Tendency 96
The Mean 97
The Median 99
The Mode 100
Quartiles 101
The Geometric Mean 103
3.2 Variation and Shape 105
The Range 105
The Interquartile Range 106
The Variance and the Standard Deviation 106
The Coefficient of Variation 110
Z Scores 111
Shape 112
Visual Explorations: Exploring Descriptive Statistics 113
Microsoft Excel Descriptive Statistics Results 114
3.3 Numerical Descriptive Measures for a Population 118
The Population Mean 118
The Population Variance and Standard Deviation 119
The Empirical Rule 120
The Chebyshev Rule 120
3.4 Exploratory Data Analysis 122
The Five-Number Summary 123
The Box-and-Whisker Plot 124
3.5 The Covariance and the Coefficient of Correlation 127
The Covariance 127
The Coefficient of Correlation 128
3.6 Pitfalls in Numerical Descriptive Measures and Ethical Issues 133
Ethical Issues 133
Summary 134
Key Equations 134
Key Terms 135
Chapter Review Problems 135
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

Contents

xiii

Managing the Springville Herald 142
Web Case 142
References 142
Excel Companion to Chapter 3 143

4 BASIC PROBABILITY 147
Using Statistics @ The Consumer Electronics Company 148
4.1 Basic Probability Concepts 149
Events and Sample Spaces 150
Contingency Tables 151
Simple (Marginal) Probability 151
Joint Probability 152
General Addition Rule 154
4.2 Conditional Probability 157
Computing Conditional Probabilities 157
Decision Trees 159
Statistical Independence 161
Multiplication Rules 162
Marginal Probability Using the General Multiplication Rule 163
4.3 Bayes Theorem 166
4.4 Ethical Issues and Probability 171
4.5
(CD-ROM Topic) Counting Rules 172
Summary 172
Key Equations 172
Key Terms 172
Chapter Review Problems 173
Web Case 176
References 176
Excel Companion to Chapter 4 177

5 SOME IMPORTANT DISCRETE PROBABILITY
DISTRIBUTIONS 179
Using Statistics @ Saxon Home Improvement 180
5.1 The Probability Distribution for a Discrete Random Variable 180
Expected Value of a Discrete Random Variable 181
Variance and Standard Deviation of a Discrete Random Variable 182
5.2 Covariance and Its Application in Finance 184
Covariance 184
Expected Value, Variance, and Standard Deviation of the Sum of Two Random Variables 186
Portfolio Expected Return and Portfolio Risk 186
5.3 Binomial Distribution 189
5.4 Poisson Distribution 197
5.5 Hypergeometric Distribution 201
5.6
(CD-ROM Topic) Using the Poisson Distribution to Approximate the Binomial Distribution 204
Summary 204
Key Equations 204
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

xiv

Contents

Key Terms 205
Chapter Review Problems 206
Managing the Springville Herald 209
Web Case 209
References 210
Excel Companion to Chapter 5 211

6 THE NORMAL DISTRIBUTION AND OTHER
CONTINUOUS DISTRIBUTIONS 217
Using Statistics @ OurCampus! 218
6.1 Continuous Probability Distributions 218
6.2 The Normal Distribution 219
Visual Explorations: Exploring the Normal Distribution 229
6.3 Evaluating Normality 234
Comparing Data Characteristics to Theoretical Properties 234
Constructing the Normal Probability Plot 236
6.4 The Uniform Distribution 238
6.5 The Exponential Distribution 241
6.6
(CD-ROM Topic) The Normal Approximation to the Binomial
Distribution 243
Summary 243
Key Equations 243
Key Terms 243
Chapter Review Problems 244
Managing the Springville Herald 246
Web Case 246
References 246
Excel Companion to Chapter 6 247

7 SAMPLING AND SAMPLING DISTRIBUTIONS 251
Using Statistics @ Oxford Cereals 252
7.1 Types of Sampling Methods 252
Simple Random Samples 253
Systematic Samples 256
Stratified Samples 256
Cluster Samples 257
7.2 Evaluating Survey Worthiness 258
Survey Error 259
Ethical Issues 260
7.3 Sampling Distributions 261
7.4 Sampling Distribution of the Mean 262
The Unbiased Property of the Sample Mean 262
Standard Error of the Mean 264
Sampling from Normally Distributed Populations 265
Sampling from Non-Normally Distributed Populations
The Central Limit Theorem 268
Visual Explorations: Exploring Sampling Distributions 270
7.5 Sampling Distribution of the Proportion 272
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

Contents

7.6

xv

(CD-ROM Topic) Sampling From Finite Populations 275

Summary 275
Key Equations 276
Key Terms 276
Chapter Review Problems 276
Managing the Springville Herald 279
Web Case 279
References 280
Excel Companion to Chapter 7 281

8 CONFIDENCE INTERVAL ESTIMATION 283
Using Statistics @ Saxon Home Improvement 284
8.1 Confidence Interval Estimation for the Mean (* Known) 285
8.2 Confidence Interval Estimation for the Mean (* Unknown) 290
Student s t Distribution 290
Properties of the t Distribution 290
The Concept of Degrees of Freedom 291
The Confidence Interval Statement 292
8.3 Confidence Interval Estimation for the Proportion 296
8.4 Determining Sample Size 299
Sample Size Determination for the Mean 300
Sample Size Determination for the Proportion 302
8.5 Applications of Confidence Interval Estimation in Auditing 306
Estimating the Population Total Amount 307
Difference Estimation 308
One-Sided Confidence Interval Estimation of the Rate of Noncompliance with Internal Controls 311
8.6 Confidence Interval Estimation and Ethical Issues 313
8.7
(CD-ROM Topic) Estimation and Sample Size Determination for Finite Populations 314
Summary 314
Key Equations 314
Key Terms 315
Chapter Review Problems 315
Managing the Springville Herald 320
Web Case 321
References 321
Excel Companion to Chapter 8 322

9 FUNDAMENTALS OF HYPOTHESIS TESTING:
ONE-SAMPLE TESTS 327
Using Statistics @ Oxford Cereals, Part II 328
9.1 Hypothesis-Testing Methodology 328
The Null and Alternative Hypotheses 328
The Critical Value of the Test Statistic 330
Regions of Rejection and Nonrejection 330
Risks in Decision Making Using Hypothesis-Testing Methodology 331
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

xvi

Contents

9.2

9.3

9.4

9.5

9.6
9.7

Z Test of Hypothesis for the Mean (* Known) 334
The Critical Value Approach to Hypothesis Testing 334
The p-Value Approach to Hypothesis Testing 337
A Connection Between Confidence Interval Estimation and Hypothesis Testing 340
One-Tail Tests 342
The Critical Value Approach 342
The p-Value Approach 343 t Test of Hypothesis for the Mean (* Unknown) 346
The Critical Value Approach 347
The p-Value Approach 349
Checking Assumptions 349
Z Test of Hypothesis for the Proportion 353
The Critical Value Approach 354
The p-Value Approach 355
Potential Hypothesis-Testing Pitfalls and Ethical Issues 357
(CD-ROM Topic) The Power of a Test 359

Summary 359
Key Equations 360
Key Terms 360
Chapter Review Problems 360
Managing the Springville Herald 363
Web Case 363
References 363
Excel Companion to Chapter 9 364

10 SIMPLE LINEAR REGRESSION 369
Using Statistics @ Sunflowers Apparel 370
10.1 Types of Regression Models 370
10.2 Determining the Simple Linear Regression Equation 372
The Least-Squares Method 373
Visual Explorations: Exploring Simple Linear Regression Coefficients 376
Predictions in Regression Analysis: Interpolation Versus Extrapolation 377
Computing the Y Intercept, b0, and the Slope, b1 377
10.3 Measures of Variation 382
Computing the Sum of Squares 382
The Coefficient of Determination 384
Standard Error of the Estimate 386
10.4 Assumptions 387
10.5 Residual Analysis 388
Evaluating the Assumptions 388
10.6 Measuring Autocorrelation: The Durbin-Watson Statistic 392
Residual Plots to Detect Autocorrelation 392
The Durbin-Watson Statistic 394
10.7 Inferences About the Slope and Correlation Coefficient 397 t Test for the Slope 397
F Test for the Slope 398

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

Contents

xvii

Confidence Interval Estimate of the Slope (*1) 400 t Test for the Correlation Coefficient 400
10.8 Estimation of Mean Values and Prediction of Individual Values 404
The Confidence Interval Estimate 404
The Prediction Interval 405
10.9 Pitfalls in Regression and Ethical Issues 408
Summary 412
Key Equations 413
Key Terms 414
Chapter Review Problems 414
Managing the Springville Herald 420
Web Case 421
References 421
Excel Companion to Chapter 10 422

11 INTRODUCTION TO MULTIPLE REGRESSION 429
Using Statistics @ OmniFoods 430
11.1 Developing a Multiple Regression Model 430
Interpreting the Regression Coefficients 431
Predicting the Dependent Variable Y 433
11.2 r 2, Adjusted r 2, and the Overall F Test 435
Coefficient of Multiple Determination 436
Adjusted r 2 436
Test for the Significance of the Overall Multiple Regression Model 437
11.3 Residual Analysis for the Multiple Regression Model 439
11.4 Inferences Concerning the Population Regression Coefficients 441
Tests of Hypothesis 441
Confidence Interval Estimation 443
11.5 Testing Portions of the Multiple Regression Model 445
Coefficients of Partial Determination 448
11.6 Using Dummy Variables and Interaction Terms in Regression Models 450
Interactions 453
Summary 460
Key Equations 462
Key Terms 462
Chapter Review Problems 463
Managing the Springville Herald 466
Web Case 466
References 466
Excel Companion to Chapter 11 467

Appendices 471
A.
B.
C.
D.

Review of Arithmetic, Algebra, and Logarithms 472
Summation Notation 474
Statistical Symbols and Greek Alphabet 477
Student CD-ROM Contents 478

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

xviii

Contents

E.
F.

Tables 485
FAQs About Using Microsoft Excel and PHStat2 510

Self-Test Solutions and Answers to Select Even-Numbered
Problems 513
Index 535
CD-ROM Topics
4.5
5.6
6.6
7.6
8.7
9.7

Counting Rules CD4-1
Using the Poisson Distribution To Approximate the Binomial
Distribution CD5-1
The Normal Approximation to the Binomial Distribution CD6-1
Sampling From Finite Populations CD7-1
Estimation and Sample Size Determination for Finite Populations CD8-1
The Power of a Test CD9-1

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

P R E FA C E
Educational Philosophy
In our many years of teaching business statistics, we have continually searched for ways to improve the teaching of these courses. Our active participation in a series of Making Statistics
More Effective in Schools and Business (MSMESB), Decision Sciences Institute (DSI), and
American Statistical Association conferences as well as the reality of serving a diverse group of students at large universities have shaped our vision for teaching these courses. Over the years, our vision has come to include these key principles:
1.

2.

3.

4.

Students need to be shown the relevance of statistics.
Students need a frame of reference when learning statistics, especially when statistics is not their major. That frame of reference for business students should be the functional areas of business that is, accounting, finance, information systems, management, and marketing. Each statistical topic needs to be presented in an applied context related to at least one of these functional areas.
The focus in teaching each topic should be on its application in business, the interpretation of results, the presentation of assumptions, the evaluation of the assumptions, and the discussion of what should be done if the assumptions are violated.
Students need to be familiar with the software used in the business world.
Integrating spreadsheet software into all aspects of an introductory statistics course allows the course to focus on interpretation of results instead of computations.
Introductory business statistics courses should recognize that in business, spreadsheet software is typically available on a decision maker s desktop.
Students need to be given sufficient guidance on using software.
Textbooks should provide enough instructions so that students can effectively use the software integrated with the study of statistics, without having the software instruction dominate the course.
Students need ample practice in order to understand how statistics is used in business.
Both classroom examples and homework exercises should involve actual or realistic data as much as possible.
Students should work with data sets, both small and large, and be encouraged to look beyond the statistical analysis of data to the interpretation of results in a managerial context. New to This Edition: Statistics Coverage
This new fifth edition of Statistics for Managers Using Microsoft Excel enhances the statistical coverage of previous editions in a number of ways:
Every chapter has been rewritten to use a more engaging, conversational writing style that students will appreciate. Complex topics are discussed in simple, straightforward sentences. From the Authors Desktop essays provide greater background for the topic just covered and raise important issues.
This edition includes many more examples from everyday life. Notable examples include what you would do with $1,000 (Chapter 2), time to get ready in the morning (Chapter 3), and waiting time at a fast-food restaurant (Chapter 9).
Many new applied examples and exercises with data from The Wall Street Journal, USA
Today, Consumer Reports, and other sources have been added to the book.
Many problems have been restructured to contain no more than four parts, allowing students to break down the concepts and apply the material more easily.
A Key Equations list at the end of each chapter lists the equations used in the chapter.
Worked-out solutions to self-test questions are provided at the back of the book.

xix
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

xx

Preface

A roadmap for selecting the proper statistical method is included at the front of the text to help students select the proper technique and to make connections between topics.
Student surveys are included as an integrating theme for exercises across many chapters.

New to This Edition: Excel Coverage
This new fifth edition of Statistics for Managers Using Microsoft Excel enhances the Excel coverage of previous editions in a number of ways:
Totally rewritten Excel sections have been organized into end-of-chapter Excel
Companions for easy reference.
Wherever possible, Excel Companions present step-by-step instructions and Excel command sequences that are compatible across all current versions of Excel, including Excel 2007.
Clearly marked separate Excel 97 2003 and Excel 2007 instructions are provided for those
Excel techniques that are fundamentally different in Excel 2007.
Basic Excel sections allow the use of Excel without any outside enhancement, and
PHStat2 sections describe the use of the PHStat2 add-in included on the student CD-ROM.
Margin notes link worksheet and chart illustrations to the instructions of Excel Companion sections. Worksheet illustrations, like the following example, display underlying cell formulas that show how results are computed:

Chapter-by-Chapter Changes in the Fifth Edition
Each chapter includes a new opening page that displays the chapter sections and subsections.
Accompanying each chapter is an Excel Companion that discusses how to apply Microsoft Excel to the statistical techniques of the chapter. In addition, the Excel Companion includes completely new material for using Excel in most chapters. The following changes have been made to this fifth edition: Chapter 1 has rewritten Sections 1.1 (Why Learn Statistics), 1.2 (Statistics for Managers), and 1.3 (Basic Vocabulary of Statistics) and a completely new Section 1.6 (Microsoft Excel
Worksheets). The sections on survey sampling have been moved to Chapter 7.
Chapter 2 includes a new data set concerning mutual fund returns for 2001 2005. Graphs for a single variable are covered prior to graphs for two variables. Graphs for categorical variables are covered prior to graphs for numerical variables. The examples in this chapter refer to what to do with $1,000 and the cost of restaurant meals in addition to mutual fund returns. Chapter 3 includes a new data set concerning mutual fund returns for 2001 2005. The examples in this chapter refer to the time to get ready in the morning as well as mutual fund

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

Preface

xxi

returns.
Z scores for detecting outliers are now included. The sample covariance is now included, as is the coefficient of correlation.
Chapter 4 now includes additional examples.
Chapter 5 covers the Poisson distribution prior to the hypergeometric distribution.
Chapter 6 has a simplified section on the normal probability plot. Coverage of sampling distributions has been moved to a new Chapter 7.
Chapter 7 now covers sampling distributions along with types of survey sampling methods and survey worthiness.
Chapter 8 has 28 new problems.
Chapter 9 uses a simple, six-step method to perform hypothesis tests using the critical value approach and a straightforward five-step method to perform hypothesis tests using the pvalue.
Chapter 10 (formerly Chapter 13) now includes computations for the regression coefficients and sum of squares in chapter examples.
Chapter 11 (formerly Chapter 14) now covers R2, and adjusted R2 prior to residual analysis.

Hallmark Features
We have continued many of the traditions of past editions and have highlighted some of those features below:
Using Statistics business scenarios Each chapter begins with a Using Statistics example that shows how statistics is used in accounting, finance, information systems,
management, or marketing. Each scenario is used throughout the chapter to provide an applied context for the concepts.
Emphasis on data analysis and interpretation of Excel results We believe that the use of computer software is an integral part of learning statistics. Our focus emphasizes analyzing data by interpreting the results from Microsoft Excel while reducing emphasis on doing computations. For example, in the coverage of tables and charts in Chapter 2, the focus is on the interpretation of various charts, not on their construction by hand. In our coverage of hypothesis testing in Chapter 9, extensive computer results have been included so that the p-value approach can be emphasized.
Pedagogical aides An active writing style, boxed numbered equations, set-off examples to provide reinforcement for learning concepts, problems divided into Learning the
Basics and Applying the Concepts, key equations, and key terms are included.
Answers Most answers to the even-numbered exercises are provided in an appendix at the end of the book.
PHStat2 This add-in, which is included on the student CD-ROM, extends the statistical capabilities of Microsoft Excel and executes the low-level menu selection and worksheet entry tasks associated with implementing statistical analysis in Excel. When combined with the Analysis ToolPak add-in, virtually all statistical methods taught in an introductory statistics course can be demonstrated using Microsoft Excel.
Web Cases A chapter-ending Web Case is included for each of the first 11 chapters. By visiting Web sites related to the companies and researching the issues raised in the Using
Statistics scenarios that start each chapter, students learn to identify misuses of statistical information. The Web Cases require students to sift through claims and assorted information in order to discover the data most relevant to the case. Students then determine whether the conclusions and claims are supported by the data. (Instructional tips for using the Web Cases and solutions to the Web Cases are included in the Instructor s Solutions
Manual.)
Case studies and team projects Detailed case studies are included in numerous chapters. A Springville Herald case is included at the end of most chapters as an integrating theme. A team project relating to mutual funds is included in many chapters as an integrating theme.
Visual Explorations Microsoft Excel add-in workbook that allows students to interactively explore important statistical concepts in descriptive statistics, the normal distribution,

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

xxii

Preface

sampling distributions, and regression analysis. For example, in descriptive statistics, students observe the effect of changes in the data on the mean, median, quartiles, and standard deviation. With the normal distribution, students see the effect of changes in the mean and standard deviation on the areas under the normal curve. In sampling distributions, students use simulation to explore the effect of sample size on a sampling distribution. In regression analysis, students have the opportunity of fitting a line and observing how changes in the slope and intercept affect the goodness of fit.

Supplement Package
The supplement package that accompanies this text includes the following:
Instructor s Solutions Manual This manual includes solutions for end-of-section and end-of-chapter problems, answers to case questions, where applicable, and teaching tips for each chapter. Electronic solutions are provided in Excel and Word formats.
Student Solutions Manual This manual provides detailed solutions to virtually all the even-numbered exercises and worked-out solutions to the self-test problems.
Test Item File The Test Item File contains true/false, multiple-choice, fill-in, and problem-solving questions based on the definitions, concepts, and ideas developed in each chapter of the text.
TestGen software A test bank has been designed for use with the TestGen test-generating software. This computerized package allows instructors to custom design, save, and generate classroom tests. The test program permits instructors to edit, add, or delete questions from the test bank; edit existing graphics and create new graphics; analyze test results; and organize a database of tests and student results. This software allows for flexibility and ease of use. It provides many options for organizing and displaying tests, along with a search and sort feature. The program is available on the instructor s CD-ROM, and associated conversion files can be found online at the Instructor s Resource Center.
Instructor s Resource Center The Instructor s Resource Center contains the electronic files for the complete Instructor s Solutions Manual, the Test Item File, and Lecture
PowerPoint presentations (www.prenhall.com/levine).
Course and Homework Management Tools
Prentice Hall s OneKey This tool offers the best teaching and learning resources, all in one place. OneKey for Statistics for Managers Using Microsoft Excel, is all an instructor needs to plan and administer a course and is all students need for anytime, anywhere access to course materials. Conveniently organized by textbook chapter, the compiled resources include links to quizzes, PowerPoint presentations, data files, links to Web
Cases, a PHStat2 download, a Visual Explorations download, the Student Solutions
Manual, and additional instructor resources.
WebCT and Blackboard With a local installation of either course management system, Prentice Hall provides content designed especially for this textbook to create a complete course suite, tightly integrated with the system s course management tools.
PH GradeAssist This online homework and assessment system allows the instructor to assign problems for student practice, homework, or quizzes. The problems, taken directly from the text, are algorithmically generated, so each student gets a slightly different problem with a different answer. This feature allows students multiple attempts for more practice and improved competency. PH GradeAssist grades the results and can export them to
Microsoft Excel worksheets.
Companion Web site www.prenhall.com/levine contains the following:
An online study guide with true/false, multiple-choice, and essay questions designed to test students comprehension of chapter topics
PowerPoint presentation files with chapter outlines and key equations
Student data files for text problems in Excel
PHStat2 Web site PHStat2 has a home page at www.prenhall.com/phstat.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

Preface

xxiii

Index page Web site An index page for the supporting material for all the
Web Cases included in the text can be found at www.prenhall.com/Springville/
SpringvilleSFM5e.htm.

Acknowledgments
We are extremely grateful to the Biometrika Trustees, American Cyanimid Company, the RAND
Corporation, the American Society for Testing and Materials for their kind permission to publish various tables in Appendix E and the American Statistical Association for its permission to publish diagrams from the American Statistician. Also, we are grateful to Professors George A. Johnson and Joanne Tokle of Idaho State University and Ed Conn, Mountain States Potato Company, for their kind permission to incorporate parts of their work as our Mountain States Potato Company case in Chapter 15.

A Note of Thanks
We would like to thank John Beyers, University of Maryland, University College; Ephrem Eyob,
Virginia State University; Mickey Hepner, University of Central Oklahoma; Bill Jedicka, Harper
College; Morgan Jones, University of North Carolina; Michael Lewis, West Virginia State
University; Susan Pariseau, Merrimack College; Rupert Rhodd, Florida Atlantic University; Jim
Robison, Sonoma State University; Abdulhamid Sukar, Cameron University; and Gary Tikriti,
University of South Florida, St. Petersburg, for their comments, which have made this a better book.
We would especially like to thank Mark Pfaltzgraff, Jeff Shelstad, Eric Frank, Anne Graydon,
Cynthia Zonneveld, Nancy Welcher, Ashley Lulling, Barbara Witmer, Kelly Loftus, and Laura
Cirigliano of the editorial, marketing, and production teams at Prentice Hall. We would like to thank our statistical reader and accuracy checker Annie Puciloski for her diligence in checking our work; Kitty Jarrett for her copyediting; Julie Kennedy for her proofreading; and Heidi Allgair,
Sandra Krausman, and Cindy Miller of GGS Book Services, for their work in the production of this text.
Finally, we would like to thank our parents, wives, and children for their patience, understanding, love, and assistance in making this book a reality. It is to them that we dedicate this book.

Concluding Remarks
We have gone to great lengths to make this text both pedagogically sound and error free. If you have any suggestions or require clarification about any of the material, or if you find any errors, please contact us at [e-mail] or [e-mail]. Include the phrase SMUME edition 5 in the subject line of your email. For more information about using PHStat2, see Appendix F, review the PHStat2 readme file on the student CD-ROM, and visit the PHStat2 Web site, at www.prenhall.com/phstat.
David M. Levine
David F. Stephan
Timothy C. Krehbiel
Mark L. Berenson

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

CHAPTER 1
Introduction and Data Collection
1.1

WHY LEARN STATISTICS

1.2

STATISTICS FOR MANAGERS
How This Text Is Organized

USING STATISTICS @ Good Tunes
1.3

BASIC VOCABULARY OF STATISTICS

1.4

DATA COLLECTION

1.5

TYPES OF VARIABLES
Levels of Measurement and Measurement Scales

1.6

EXCEL COMPANION TO CHAPTER 1
E1.1 Preliminaries: Basic Computing Skills
E1.2 Basic Workbook Operations
E1.3 Worksheet Entries
E1.4 Worksheet Formatting
E1.5 Copy-and-Paste Operations
E1.6 Add-ins: Making Things Easier for You

MICROSOFT EXCEL WORKSHEETS
Worksheet Cells
Designing Effective Worksheets

LEARNING OBJECTIVES
This chapter will help you learn:
* How statistics is used in business
* The sources of data used in business
* The types of data used in business
* The basics of Microsoft Excel

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

2

CHAPTER ONE Introduction and Data Collection

1.1

WHY LEARN STATISTICS
The reality TV series The Apprentice stars the real estate developer Donald Trump. When it premiered several years ago, Trump assigned two teams of contestants the task of setting up and running a lemonade stand. At the time, a number of businesspeople criticized that task as not being a realistic business task. They saw the task of selling lemonade as a simple act of salesmanship that was more dependent on the persuasive skills of the seller than anything else.
If you have ever sold lemonade or held other childhood jobs such as selling cookies or delivering daily newspapers, you know your task was fairly simple. For example, to deliver newspapers, you need only to keep track of a list of addresses and perhaps record the weekly or monthly payments. In contrast, sales and marketing managers of the newspaper need to keep track of much more data including the incomes, education levels, lifestyles, and buying preferences of their subscribers in order to make appropriate decisions about increasing circulation and attracting advertisers. But unless that newspaper has a tiny circulation, those managers are probably not looking at data directly. Instead, they are looking at summaries, such as the percentage of subscribers who attended at least some college, or trying to uncover useful patterns, such as whether more subscriptions are delivered to single-family homes in areas associated with heavy sales of luxury automobiles. That is to say, the managers at the newspaper are using statistics, the subject of this text.
Statistics is the branch of mathematics that transforms data into useful information for decision makers. These transformations often require complex calculations that are practical only if done by computer, so using statistics usually means also using computers. This is especially true when dealing with the large volumes of data that a typical business collects.
Attempting to do statistics using manual calculations for such data would be too timeconsuming to benefit a business.
When you learn statistics, you learn a set of methods and the conditions under which it is appropriate for you to use those methods. And because so many statistical methods are practical only when you use computers, learning statistics also means learning more about using computer programs that perform statistical analyses.

1.2

STATISTICS FOR MANAGERS
Today, statistics plays an ever increasing important role for business managers. These decision makers use statistics to:
*
*
*
*

1The

statistical terms population and sample are formally defined in Section
1.3, on page 5.

Present and describe business data and information properly
Draw conclusions about large populations, using information collected from samples1
Make reliable forecasts about a business activity
Improve business processes

Statistics for managers means knowing more than just how to perform these tasks.
Managers need a conceptual understanding of the principles behind each statistical analysis they undertake in order to have confidence that the information produced is correct and appropriate for a decision-making situation.
To help you master these necessary skills, every chapter of Statistics for Managers Using
Microsoft Excel has a Using Statistics scenario. While the scenarios are fictional, they represent realistic situations in which you will be asked to make decisions while using Microsoft Excel to transform data into statistical information. For example, in one chapter, you will be asked to decide the location in a supermarket that best enhances sales of a cola drink, and in another chapter, you will be asked to forecast sales for a clothing store. (You will not be asked, as the television apprentices were asked, to decide how best to sell lemonade on a New York City street corner.) Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M.
Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

1.2: Statistics for Managers

3

How This Text Is Organized
Table 1.1 shows the chapters of Statistics for Managers Using Microsoft Excel organized according to the four activities for which decision makers use statistics.

TAB LE 1 .1
Organization of This Text

Presenting and Describing Information
Data Collection (Chapter 1)
Presenting Data in Tables and Charts (Chapter 2)
Numerical Descriptive Measures (Chapter 3)
Drawing Conclusions About Populations Using Sample Information
Basic Probability (Chapter 4), a prerequisite for the rest of the chapters of this group
Some Important Discrete Probability Distributions (Chapter 5)
The Normal Distribution and Other Continuous Distributions (Chapter 6) and Sampling and Sampling Distributions (Chapter 7), which lead to Confidence Interval Estimation
(Chapter 8) and Hypothesis Testing (Chapter 9)
Making Reliable Forecasts
Simple Linear Regression (Chapter 10)
Introduction to Multiple Regression (Chapter 11)

Methods presented in the Chapters 1 3 are all examples of descriptive statistics,
the branch of statistics that collects, summarizes, and presents data. Methods discussed in
Chapters 7 through 9 are examples of inferential statistics, the branch of statistics that uses sample data to draw conclusions about an entire population. (Chapters 4 6 provide the foundation in probability and probability distributions needed for Chapters 7 9.) The definition of inferential statistics uses the terms sample and population, the second time you have encountered these words in this section. You can probably figure out that you cannot learn much about statistics until you learn the basic vocabulary of statistics. Continue now with the first Using Statistics scenario, which will help introduce you to several important terms used in statistics.

USING STATISTICS @ Good Tunes
Good Tunes, a growing four-store home entertainment systems retailer, seeks to double their number of stores within the next three years. The managers have decided to approach local area banks for the cash needed to underwrite this expansion. They need to prepare an electronic slide show and a formal prospectus that will argue that Good
Tunes is a thriving business that is a good candidate for expansion.
You have been asked to assist in the process of preparing the slide show and prospectus. What data would you include that will convince bankers to extend the credit it needs to Good Tunes? How would you present that data?

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

4

CHAPTER ONE Introduction and Data Collection

In this scenario, you need to identify the most relevant data for the bankers. Because Good
Tunes is an ongoing business, you can start by reviewing the company s records, which show both its current and recent past status. Because Good Tunes is a retailer, presenting data about the company s sales seems a reasonable thing to do. You could include the details of every sales transaction that has occurred for the past few years as a way of demonstrating that Good Tunes is a thriving business.
However, presenting the bankers with the thousands of transactions would overwhelm them and not be very useful. As mentioned in Section 1.1, you need to transform the transactions data into information by summarizing the details of each transaction in some useful way that would allow the bankers to (perhaps) uncover a favorable pattern about the sales over time.
One piece of information that the bankers would presumably want to see is the dollar sales totals by year. Tallying and totaling sales is a common process of transforming data into information and a very common statistical analysis. When you tally sales or any other relevant data about Good Tunes you choose to use you follow normal business practice and tally by a business period such as by month, quarter, or year. When you do so, you end up with multiple values: sales for this year, sales for last year, sales for the year before that, and so on. How best to refer to these multiple values requires learning the basic vocabulary of statistics.

1.3

BASIC VOCABULARY OF STATISTICS
Variables are characteristics of items or individuals and are what you analyze when you use a statistical method. For the Good Tunes scenario, sales, expenses by year, and net profit by year are variables that the bankers would want to analyze.
VARIABLE
A variable is a characteristic of an item or individual.
When used as an adjective in everyday speech, variable suggests that something changes or varies, and you would expect the sales, expenses, and net profit to have different values from year to year. These different values are the data associated with a variable, and more simply, the data to be analyzed. In later sections, you will be sometimes asked to enter the cell range of a variable in
Excel. When you see such an instruction, you should enter the cell range of the different values that collectively are the data to be analzyed. (Section 1.6 on page 11 explains what a cell range is and further discusses how to enter data in Excel.)
Variables can differ for reasons other than time. For example, if you conducted an analysis of the composition of a large lecture class, you would probably want to include the variables class standing, gender, and major field of study. Those variables would vary, too, because each student in the class is different. One student might be a freshman male Economics major, while another may be a sophomore female Finance major.
You also need to remember that values are meaningless unless their variables have operational definitions. These definitions are universally accepted meanings that are clear to all associated with an analysis. While the operational definition for sales per year might seem clear, miscommunication could occur if one person was referring to sales per year for the entire chain of stores and another to sales per year per store. Even individual values for variables sometimes need definition for the class standing variable, for example, what exactly is meant by the words sophomore and junior? (Perhaps the most famous example of vague definitions was the definition of a valid vote in the state of Florida during the 2000 U.S. presidential election. Vagueness about the operational definitions there ultimately required a U.S. Supreme
Court ruling.)
Understanding the distinction between variables and their values helps in learning four other basic vocabulary terms, two of which you have already encountered in previous sections.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

1.3: Basic Vocabulary of Statistics

5

POPULATION
A population consists of all the items or individuals about which you want to draw a conclusion.
SAMPLE
A sample is the portion of a population selected for analysis.
PARAMETER
A parameter is a numerical measure that describes a characteristic of a population.
STATISTIC
A statistic is a numerical measure that describes a characteristic of a sample.
All the Good Tunes sales transactions for a specific year, all the customers who shopped at
Good Tunes this weekend, all the full-time students enrolled in a college, and all the registered voters in Ohio are examples of populations. Examples of samples from these four populations would be 200 Good Tunes sales transactions randomly selected by an auditor for study, 30 Good
Tunes customers asked to complete a customer satisfaction survey, 50 full-time students selected for a marketing study, and 500 registered voters in Ohio contacted via telephone for a political poll. In each sample, the transactions or people in the sample represent a portion of the items or individuals that make up the population.
The average amount spent by all customers who shopped at Good Tunes this weekend is an example of a parameter because the amount spent in the entire population is needed. In contrast, the average amount spent by the 30 customers completing the customer satisfaction survey is an example of a statistic because the amount spent from only the sample of 30 people is required.

From the Authors Desktop

Using and Learning Microsoft Excel

A

lthough we have talked a lot about statistics to this point, we haven t mentioned much about using Microsoft Excel.
Using any computer program is a two-step process that begins with learning to operate the program and then advances to mastering how to apply the program to a decisionmaking task and Excel is no exception.
The Excel Companion to this chapter will help you become familiar with operating
Excel. In writing that Companion, we have assumed that you have operated a personal computer in the past to do something such as surf the Web, send an instant message, play music or games, or write homework assignments. If you have never used a personal computer for any of these or similar activities, you should ask a friend to introduce you to personal computers before you read the Companion for this chapter. While this Companion was primarily written for novices, experienced
Excel users will benefit from learning the words used to describe the Excel operations in this book.

The Excel Companion to Chapter 2 and those for later chapters will help you understand how you can apply the statistical methods discussed in this book by using Microsoft
Excel. For each method discussed, you will typically learn two ways that you can use
Excel. One way, labeled Basic Excel, uses
Excel without any outside enhancements to the program. The other way, labeled PHStat2, uses the free PHStat2 statistics add-in* that is included on this book s CD.
These two ways are truly interchangeable. The Excel solutions you create using either way will be identical (or nearly so) to each other and the example worksheets and charts you see in this book. You can switch between the two ways at any time as you use this text without losing any comprehension of the Excel material. That this book includes two complementary ways of learning Microsoft
Excel is a distinctive feature of the book.

Which way is best for you?
Unless your instructor requires you to use one way, you may want to choose a way that best

suits how you like to learn. Do you like to learn by building things from scratch, one step at a time? If so, using the Basic Excel way would be the best way for you. Do you worry about the time it takes to build things and the typing errors you might make? Or do you like to learn by closely examining a solution to discover its details, a discovery process some call reverse engineering? In either of these cases, using
PHStat2 would be your best choice.
For a few statistical methods, you will not find either a Basic Excel way or a PHStat2 way, due to the limitations of Excel. For such methods, you will find Excel workbook files on the book s CD that you can open and use as a template for creating your own solutions. (Actually, you will find files that contain template examples for every statistical method discussed in the
Excel Companions and for every Excel worksheet or chart illustrated in this book. A good starting point for these examples are the Excel workbook files named for the chapters of this text, such as Chapter 2.xls.)
Regardless of the way you use and learn
Microsoft Excel, you are invited to go online and

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

6

CHAPTER ONE Introduction and Data Collection explore the Web site for this book.There, you will find supplementary material about using Excel, including discussions of Excel techniques that the authors were not able to include in the book because of space limitations.
Postscript: Isn t Using an Add-in a Bad Thing?
If you are an experienced Microsoft Excel user, you may have concerns about using an add-in such as PHStat2. You may be concerned that you will become dependent on something you would not be able to use in business or think that using PHStat2 somehow means that you

1.4

are not really using and learning Microsoft
Excel.
Both of these concerns are unfounded.
PHStat2 is a learning tool whose sole purpose is to help you understand how Excel can be used to support specific statistical methods.
PHStat2 is designed to make using Microsoft
Excel more convenient, by doing the busy work activities of creating an Excel solution, such as cell formatting, for you. When you read the Excel Companions, you will understand what PHStat2 is doing for you in a generalized way as well as what it is doing specifically for a problem (following the Basic Excel instructions). It is true that other add-ins, including addins for other introductory business statistics textbooks, can obscure Microsoft Excel by not building an Excel-based solution and only reporting outcomes and statistical information that the add-in has internally (and invisibly) computed. Using such add-ins would not be truly learning Microsoft Excel, and using such add-ins would leave you dependent on their use. In contrast, PHStat2 creates model Excel solutions that you can examine and incorporate into your own Excel solutions.
*Section E1.6 that begins on page 28 explains what an add-in is.

DATA COLLECTION
The managers at Good Tunes believe that they will have a stronger argument for expansion if they can show the bankers that the customers of Good Tunes are highly satisfied with the service they received. How could the managers demonstrate that good service was the typical customer experience at Good Tunes?
Unlike the earlier Good Tunes scenario, in which sales per year was automatically collected as part of normal business activities, the managers now face the twin challenges to first identify relevant variables for a customer satisfaction study and then devise a method for data collection that is, collecting the values for those variables.
Many different types of circumstances, such as the following, require data collection:
*

*

*

*

A marketing research analyst needs to assess the effectiveness of a new television advertisement. A pharmaceutical manufacturer needs to determine whether a new drug is more effective than those currently in use.
An operations manager wants to monitor a manufacturing process to find out whether the quality of product being manufactured is conforming to company standards.
An auditor wants to review the financial transactions of a company in order to determine whether the company is in compliance with generally accepted accounting principles.

In each of these examples, and for the Good Tunes managers as well, collecting data from every item or individual in the population would be too difficult or too time-consuming. Because this is the typical case, data collection almost always involves collecting data from a sample. (Chapter 7 discusses methods of sample selection.)
Unlike the Good Tunes example that begins this section, the source of the data to be collected is not always obvious. Data sources are classified as being either primary sources or secondary sources. When the data collector is the one using the data for analysis, the source is primary. When the person performing the statistical analysis is not the data collector, the source is secondary. Sources of data fall into one of four categories:
*
*
*
*

Data distributed by an organization or an individual
A designed experiment
A survey
An observational study

Organizations and individuals that collect and publish data typically use that data as a primary source and then let others use it as a secondary source. For example, the United States federal government collects and distributes data in this way for both public and private purposes. The Bureau of Labor Statistics collects data on employment and also distributes the monthly consumer price index. The Census Bureau oversees a variety of ongoing surveys
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

1.4: Data Collection

7

regarding population, housing, and manufacturing and undertakes special studies on topics such as crime, travel, and health care.
Market research firms and trade associations also distribute data pertaining to specific industries or markets. Investment services such as Mergent s provide financial data on a company-bycompany basis. Syndicated services such as AC Nielsen provide clients with data that enables the comparison of client products with those of their competitors. Daily newspapers are filled with numerical information regarding stock prices, weather conditions, and sports statistics.
Outcomes of a designed experiment are another data source. These outcomes are the results of an experiment, such as a test of several laundry detergents to compare how well each detergent removes a certain type of stain. Developing proper experimental designs is a subject mostly beyond the scope of this text because such designs often involve sophisticated statistical procedures. Conducting a survey is a third type of data source. People being surveyed are asked questions about their beliefs, attitudes, behaviors, and other characteristics. For example, people could be asked their opinion about which laundry detergent best removes a certain type of stain.
(This could lead to a result different from a designed experiment seeking the same answer.)
Conducting an observational study is the fourth important data source. A researcher collects data by directly observing a behavior, usually in a natural or neutral setting. Observational studies are a common tool for data collection in business. Market researchers use focus groups to elicit unstructured responses to open-ended questions posed by a moderator to a target audience. Other, more structured types of studies involve group dynamics and consensus building.
Observational study techniques are also used in situations in which enhancing teamwork or improving the quality of products and service is a management goal.
Identifying the most appropriate source is a critical task because if biases, ambiguities, or other types of errors flaw the data being collected, even the most sophisticated statistical methods will not produce useful information. For the Good Tunes example, variables relevant to the customer experience could take the form of survey questions related to various aspects of the customer experience, examples of which are shown in Figure 1.1.

FI GU R E 1. 1
Questions about the
Good Tunes customer experience 1. How many days did it take from the time you ordered your merchandise to the time you received it?__________
2. Did you buy any merchandise that was featured in the Good Tunes Sunday newspaper sales flyer for the week of your purchase? Yes __________ No__________
3. Was this your first purchase at Good Tunes? Yes __________ No__________
4. Are you likely to buy additional merchandise from Good Tunes in the next 12 months? Yes __________ No__________
5. How much money (in U.S.dollars) do you expect to spend on stereo and consumer electronics equipment in the next 12 months?__________
6. How do you rate the overall service provided by Good Tunes with respect to your recent purchase?
Excellent

Very good

Fair

Poor

7. How do you rate the selection of products offered by Good Tunes with respect to other retailers of home entertainment systems?
Excellent

Very good

Fair

Poor

8. How do you rate the quality of the items you recently purchased from Good Tunes?
Excellent

Very good

Fair

Poor

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

8

CHAPTER ONE Introduction and Data Collection

The survey might also ask questions that seek to classify customers into groups for later analysis. One good way for Good Tunes to avoid data-collection flaws would be to distribute the questionnaire to a random sample of customers (as discussed in Chapter 7). A poor way would be to rely on a business rating Web site that allows online visitors to rate a merchant. Such Web sites cannot provide assurance that those who do the rating are customers.

From the Authors Desktop

Web Surveys

W

eb-based surveys and ratings seem to be of growing importance for many marketers. Their use and misuse raise many concerns. By coincidence, while writing Section 1.4, one of us received an email requesting that he rate the Marriott Rewards travel loyalty program a perfect 10 in the voting for the InsideFlyer Freddie Awards. The author had never heard of those awards, but soon he received other emails from various other travel loyalty programs, also asking that the same high rating be submitted. He even got an email for a program for which he had just signed up in the prior month (and for a travel company of which he was not yet a customer).
At the same time, another one of us found an article in The New York Times that reported

1.5

that Internet travel sites had to closely monitor submitted reviews to avoid fraudulent claims
(C. Elliott, Hotel Reviews Online: In Bed with
Hope, Half-Truths and Hype, The New York
Times, February 7, 2006, pp. C1, C8). The article also reported that a hotel in Key West, Florida, offered its guests a 10% discount if they published a rave review of that hotel on a particular travel Web site! Our co-author with all the
Freddie emails felt cheated.
Have you ever received an email asking you to rate an online merchant? Many of us have, especially when we have just purchased something from an online merchant.
Often, such emails come with an incentive, not unlike the Key West hotel s discount. Would an incentive cause you to rate the merchant? Would the incentive affect your opinion?

What would you say about a ratings Web site that accepts advertising from merchants that are rated on the site? What would you say about a ratings Web site that gets paid a commission if a visitor first views a rating and then clicks on a link for the merchant? These are among the several practices that may raise ethical concerns for some.
If you do use a ratings Web site, be sure to check out the fine print on your next visit. Although you will find a privacy statement that explains how the Web site uses data that can personally identify you, most likely you will not find a data collection statement that explains the methods the
Web site uses to collect its data. Perhaps you should find such a statement.

TYPES OF VARIABLES
Statisticians classify variables as either being categorical or numerical and further classify numerical variables as having either discrete or continuous values. Figure 1.2 shows the relationships and provides examples of each type of variable.

FI GU R E 1. 2
Types of variables

Data Type

Question Types

Responses

Do you currently own any stocks or bonds? Yes

Categorical

No

Discrete

To how many magazines do you currently subscribe?

Number

Continuous

How tall are you?

Inches

Numerical

Categorical variables (also known as qualitative variables) have values that can only be placed into categories, such as yes and no . Questions 2 4 in Figure 1.1 are examples of categorical variables, all of which have yes or no as their values. Categorical variables can also result in more than two possible responses. An example of this type of variable is asking customers to indicate the day of the week on which they made their purchases. Questions 6 8 result in one of four possible responses.
Numerical variables (also known as quantitative variables) have values that represent quantities. For example, Questions 1 and 5 in Figure 1.1 are numerical variables. Numerical variables are further subdivided as discrete or continuous variables.
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

1.5: Types of Variables

9

Discrete variables have numerical values that arise from a counting process. The number of magazines subscribed to is an example of a discrete numerical variable because the response is one of a finite number of integers. You subscribe to zero, one, two, and so on magazines. The number of days it takes from the time you ordered your merchandise to the time you receive it is a discrete numerical variable because you are counting the number of days.
Continuous variables produce numerical responses that arise from a measuring process.
The time you wait for teller service at a bank is an example of a continuous numerical variable because the response takes on any value within a continuum, or interval, depending on the precision of the measuring instrument. For example, your waiting time could be 1 minute, 1.1 minutes, 1.11 minutes, or 1.113 minutes, depending on the precision of the measuring device you use.
Theoretically, with sufficient precision of measurement, no two continuous values will be identical. As a practical matter, however, most measuring devices are not precise enough to detect small differences, and tied values for a continuous variable (i.e., two or more items or individuals with the same value) are often found in experimental or survey data.

Levels of Measurement and Measurement Scales
Using levels of measurement is another way of classifying data. There are four widely recognized levels of measurement: nominal, ordinal, interval, and ratio scales.

Nominal and Ordinal Scales Data from a categorical variable are measured on a nominal scale or on an ordinal scale. A nominal scale (see Figure 1.3) classifies data into distinct categories in which no ranking is implied. In the Good Tunes customer satisfaction survey, the answer to the question Are you likely to buy additional merchandise from Good Tunes in the next 12 months? is an example of a nominal scaled variable, as are your favorite soft drink, your political party affiliation, and your gender. Nominal scaling is the weakest form of measurement because you cannot specify any ranking across the various categories.

Categorical Variable

Categories
Yes

Personal Computer Ownership
Type of Stocks Owned

Growth

Internet Provider

Microsoft Network

Value
AOL

Other

No
None

Other

F I G UR E 1 . 3 Examples of nominal scales
An ordinal scale classifies data into distinct categories in which ranking is implied. In the
Good Tunes survey, the answers to the question How do you rate the overall service provided by Good Tunes with respect to your recent purchase? represent an ordinal scaled variable because the responses excellent, very good, fair, and poor are ranked in order of satisfaction level. Figure 1.4 lists other examples of ordinal scaled variables.
Categorical Variable

Ordered Categories

Student class designation
Product satisfaction

Freshman Sophomore Junior Senior
Very Unsatisfied Fairly Unsatisfied Neutral
Fairly Satisfied Very Satisfied
Professor Associate Professor Assistant Professor
Instructor
AAA AA A BBB BB B CCC CC C DDD DD D
A B C D F

Faculty rank
Standard & Poor s bond ratings
Student grades

FI G UR E 1 . 4 Examples of ordinal scales
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

10

CHAPTER ONE Introduction and Data Collection

Ordinal scaling is a stronger form of measurement than nominal scaling because an observed value classified into one category possesses more of a property than does an observed value classified into another category. However, ordinal scaling is still a relatively weak form of measurement because the scale does not account for the amount of the differences between the categories. The ordering implies only which category is greater, better, or more preferred not by how much.

Interval and Ratio Scales Data from a numerical variable are measured on an interval or a ratio scale. An interval scale (see Figure 1.5) is an ordered scale in which the difference between measurements is a meaningful quantity but does not involve a true zero point. For example, a noontime temperature reading of 67 degrees Fahrenheit is 2 degrees warmer than a noontime reading of
65 degrees. In addition, the 2 degrees Fahrenheit difference in the noontime temperature readings is the same as if the two noontime temperature readings were 74 and 76 degrees Fahrenheit because the difference has the same meaning anywhere on the scale.
FIG UR E 1.5
Examples of interval and ratio scales

Numerical Variable

Level of Measurement

Temperature (in degrees Celsius or Fahrenheit)
Standardized exam score (e.g., ACT or SAT)
Height (in inches or centimeters)
Weight (in pounds or kilograms)
Age (in years or days)
Salary (in American dollars or Japanese yen)

Interval
Interval
Ratio
Ratio
Ratio
Ratio

A ratio scale is an ordered scale in which the difference between the measurements involves a true zero point, as in height, weight, age, or salary measurements. In the Good Tunes customer satisfaction survey, the amount of money (in U.S. dollars) you expect to spend on stereo equipment in the next 12 months is an example of a ratio scaled variable. As another example, a person who weighs 240 pounds is twice as heavy as someone who weighs
120 pounds. Temperature is a trickier case: Fahrenheit and Celsius (centigrade) scales are interval but not ratio scales; the zero value is arbitrary, not real. You cannot say that a noontime temperature reading of 4 degrees Fahrenheit is twice as hot as 2 degrees Fahrenheit. But a Kelvin temperature reading, in which zero degrees means no molecular motion, is ratio scaled. In contrast, the Fahrenheit and Celsius scales use arbitrarily selected zero-degree beginning points.
Data measured on an interval scale or on a ratio scale constitute the highest levels of measurement. They are stronger forms of measurement than an ordinal scale because you can determine not only which observed value is the largest but also by how much.

PROBLEMS FOR SECTION 1.5
Learning the Basics
1.1 Three different beverages are sold at a fastfood restaurant soft drinks, tea, and coffee.
a. Explain why the type of beverage sold is an example of a categorical variable.
b. Explain why the type of beverage sold is an example of a nominal scaled variable.
PH Grade
ASSIST

1.2 Soft drinks are sold in three sizes at a fast-food restaurant small, medium, and large. Explain why the size of the soft drink is an example of an ordinal scaled variable. 1.3 Suppose that you measure the time it takes to download an MP3 file from the Internet.

a. Explain why the download time is a continuous numerical variable.
b. Explain why the download time is a ratio scaled variable. Applying the Concepts
1.4 For each of the following variables, determine whether the variable is categorical or numerical.
If the variable is numerical, determine whether the variable is discrete or continuous. In addition, determine the level of measurement for each of the following.
a. Number of telephones per household
b. Length (in minutes) of the longest long-distance call made per month
SELF
Test

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

1.6: Microsoft Excel Worksheets

c. Whether someone in the household owns a cell phone
d. Whether there is a high-speed Internet connection in the household 1.5 The following information is collected from students upon exiting the campus bookstore during the first week of classes:
Amount of time spent shopping in the bookstore
Number of textbooks purchased
Academic major
Gender

PH Grade
ASSIST

a.
b.
c.
d.

Classify each of these variables as categorical or numerical. If the variable is numerical, determine whether the variable is discrete or continuous. In addition, determine the level of measurement for these variables.
1.6 For each of the following variables, determine whether the variable is categorical or numerical. If the variable is numerical, determine whether the variable is discrete or continuous. In addition, determine the level of measurement for each of the following.
a. Name of Internet provider
b. Amount of time spent surfing the Internet per week
c. Number of emails received in a week
d. Number of online purchases made in a month
PH Grade
ASSIST

1.7 For each of the following variables, determine whether the variable is categorical or numerical. If the variable is numerical, determine whether the variable is discrete or continuous. In addition, determine the level of measurement for each of the following.
a. Amount of money spent on clothing in the past month
b. Favorite department store
c. Most likely time period during which shopping for clothing takes place (weekday, weeknight, or weekend)
d. Number of pairs of winter gloves owned

1.6

11

1.8 Suppose the following information is collected from
Robert Keeler on his application for a home mortgage loan at the Metro County Savings and Loan Association:
a. Monthly payments: $1,427
b. Number of jobs in past 10 years: 1
c. Annual family income: $86,000
d. Marital status: Married
Classify each of the responses by type of data and level of measurement. 1.9 One of the variables most often included in surveys is income. Sometimes the question is phrased What is your income (in thousands of dollars)? In other surveys, the respondent is asked to Place an X in the circle corresponding to your income level and given a number of income ranges to choose from.
a. In the first format, explain why income might be considered either discrete or continuous.
b. Which of these two formats would you prefer to use if you were conducting a survey? Why?
c. Which of these two formats would likely bring you a greater rate of response? Why?
1.10 If two students score a 90 on the same examination, what arguments could be used to show that the underlying variable test score is continuous? PH Grade
ASSIST

1.11 The director of market research at a large department store chain wanted to conduct a survey throughout a metropolitan area to determine the amount of time working women spend shopping for clothing in a typical month.
a. Describe both the population and the sample of interest, and indicate the type of data the director might want to collect. b. Develop a first draft of the questionnaire needed in (a) by writing a series of three categorical questions and three numerical questions that you feel would be appropriate for this survey.

MICROSOFT EXCEL WORKSHEETS
When you use Microsoft Excel, you place the data you have collected in worksheets.
Worksheets appear as pages containing gridlines that separate individually lettered columns from numbered rows. While worksheets look like the simple tables you can create in a word processing program, worksheets have special features that are particularly suited to data analysis. Understanding the special features of worksheets will help you to better understand the interplay of data and results in Microsoft Excel.

Worksheet Cells
The intersections of the columns and rows of worksheets form boxes called cells. You refer to a cell by its column letter and row number. For example, you refer to the cell in the first column and second row as cell A2 and the cell in the fifth column and first row as cell E1. You enter in a cell a single value or an expression that can include a reference to another cell. This flexibility, as explained further in Section E1.3 of the Excel Companion to this chapter, is one of the special features that makes Microsoft Excel more than just a fancy table-oriented word processor.
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

12

CHAPTER ONE Introduction and Data Collection

You can refer to more than one cell in a cell reference. If you want to refer to a group of cells that forms a contiguous rectangular area, you can use a cell range in which references to the upper leftmost cell and the lower rightmost cell are joined with a colon. For example, the cell range A1:C2 refers to the six cells found in the first two rows and three columns of a worksheet. Excel also allows ranges such as A:A or 4:4, as a shorthand way of referring to all the cells in a column or a row. Later in this text, you will see cell ranges such as D1:D8,F1:F8 that refer to cells from two non-adjacent area of a worksheet.
Worksheets exist inside a workbook, a collection of worksheets and other types of sheets, including chart sheets that help visualize data. Usually, you will use only one sheet at any given time and open to a worksheet by clicking its sheet tab (see Section E1.1). If someone says that they are opening an Excel file, they are most likely opening a workbook file. All versions of
Excel can open workbook files saved using the .xls file format (and all Excel files on the
Student CD are in this format). Excel 2007 can also open workbooks saved in the newer .xlsx format discussed in Appendix F.

Designing Effective Worksheets
Because thousands of cells are available on individual worksheets, you will never have to worry about running out of cells to use. This spaciousness of worksheets invites careless use and causes some to ignore the important process of effectively arranging worksheet data. Poor arrangements can increase the chance of user errors, create confusing results, lead to unattractive printouts, or worse.
To be consistent with standard business usage, you should associate column cell ranges with variables. In this arrangement, you use the first (row 1) cell of a column for a name label for a variable and place the data for the variable in the subsequent cells of the column. You do not skip any rows as you enter data, so column cell ranges will never contain any empty cells. (Empty cells can interfere with Excel ability s to process your data and can lead to inaccurate results.)
This standard practice is always used in this text and in all of the Excel files on the student
CD. Because all of the Excel instructions assume this data arrangement, you should never deviate from this practice when you use this book.
Another good practice is to place all the variables on a worksheet that is separate from the worksheet containing the results. Such separation will increase the reusability of your results worksheet and minimize the chance of inadvertent changes to the values of your variables as you construct your results. In the workbooks found on the book s CD as well as the workbooks produced by PHStat2, you will generally find a Results worksheet showing the results separate from the worksheet containing the variables.
Sometimes, worksheets used in this book require only the values of certain parameters or statistics and not the values associated with a variable. For such worksheets, good practice is to place the parameters and statistics at the top of the worksheet so that a user can easily perform what-if analyses, changing values to see their effects on the results. In this book, these values always appear in bold, in cells tinted a shade Excel calls light turquoise and under the heading
Data. When you see such tinted cells, you know that you can change the values in those cells to perform what-if analyses and solve other, similar problems.
Another good design practice is to allow the user to be able to explicitly see the chain of calculations from the starting data, through any intermediate calculations, to the results. This practice is particularly advantageous when preparing statistical worksheets because most intermediate calculations are statistics themselves. Showing the chain of calculations helps you review your worksheet for errors and helps others better understand what your worksheet does.
In the worksheets of this book, intermediate calculations appear under the heading
Intermediate Calculations and are in a cell range that immediately precedes the cell range containing the results. The results appear in cells that are tinted a light yellow and contain boldfaced text. There is also a heading over the results cells that varies with the type of statistical analysis performed.
Whether you use the worksheet design of this book or your own design, do not overlook the importance of skipping rows or columns to create white space to separate different regions
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

Key Terms

13

of the worksheet that present results. In this book, worksheets tend to skip only a single row or a single column. This choice is due more to making all illustrations compact than any hard or fast rule. You should experiment with your own worksheets with an eye to making them easy to follow on both the display screen and the printed page. Do not hesitate to create two copies of your worksheets one optimized for the screen, the other for the printer, if you have anything but the simplest worksheet to produce.

From the Authors Desktop

When to Excel

P

erhaps you have heard from some people that Microsoft Excel shouldn t be used for statistics or you have searched the Internet and discovered that statistics educators have had a long-running discussion over the use of
Excel in the classroom.
As authors of a text whose title includes the phrase Using Microsoft Excel, we believe that Microsoft Excel provides a good way to introduce you to basic statistical methods and demonstrate how to apply these methods in business decision making. Many managers, noting the prevalence of Microsoft Excel on the computers in their businesses, have similarly considered using Excel, rather than a specialized statistical program, for statistical analysis. Microsoft Excel seems like an attractive choice because:

Using Excel means not having to incur the extra costs of using specialized statistical programs. Most business users already have some familiarity with Excel.
Excel is easy to use and easy to learn, at least for casual users.
Excel graphical and statistical functions can use the same worksheet-based data that users have created for other business purposes. Some Excel graphical functions produce more vivid visual outputs than some specialized statistical programs.
While these traits are attractive, those who have chosen Microsoft Excel have not necessarily considered the accuracy and completeness of the statistical results that Excel

produces. Unfortunately, some investigators have determined that certain Microsoft Excel statistical capabilities contain flaws that can lead to invalid results, especially when data sets are very large or have unusual statistical properties (see reference 1, 2, and 4). Even using Microsoft Excel with small data sets to produce the relatively simple descriptive statistics can lead to nonstandard results. (As an example, see the discussion for creating histograms in the Excel Companion to chapter 2 on page 86.) Clearly, when you use Microsoft
Excel, you must be careful about the data and the method you are using. Whether this complication outweighs the benefits of Excel s attractive features is still an unanswered question in business today.

SUMMARY
In this chapter, you have been introduced to the role of statistics in turning data into information and the importance of using computer programs such as Microsoft Excel. In addition, you have studied data collection and the various types of data used in business. In conjunction with the
Using Statistics scenario, you were asked to review the customer survey used by the Good Tunes company (see page 7).
The first and fifth questions of the survey shown will pro-

KEY

duce numerical data. Questions 2, 3, and 4 will produce nominal categorical data. Questions 6 8 will produce ordinal categorical data. The responses to the first question
(number of days) are discrete, and the responses to the fifth question (amount of money spent) are continuous. In the next two chapters, tables and charts and a variety of descriptive numerical measures that are useful for data analysis are developed.

TERMS

categorical variable 8 cell 11 cell range 12 chart sheet 12 continuous variable 9 data 4 descriptive statistics 3 discrete variable 9 inferential statistics 3

interval scale 10 nominal scale 9 numerical variable 8 operational definition 4 ordinal scale 9 parameter 5 population 5 primary source 6 qualitative variable 8

quantitative variable 8 ratio scale 10 sample 5 secondary source 6 statistic 5 statistics 2 variable 4 workbook 12 worksheet 11

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

14

CHAPTER ONE Introduction and Data Collection

CHAPTER

REVIEW

Checking Your Understanding
PH Grade
ASSIST

1.12 What is the difference between a sample and a population?

PH Grade
ASSIST

1.13 What is the difference between a statistic and a parameter?

PH Grade
ASSIST

1.14 What is the difference between descriptive and inferential statistics?

PH Grade
ASSIST

1.15 What is the difference between a categorical variable and a numerical variable?

1.16 What is the difference between a discrete variable and a continuous variable?
1.17 What is an operational definition and why is it so important? 1.18 What are the four levels of measurement scales?

Applying the Concepts
1.19 The Data and Story Library, lib.stat.cmu.edu/
DASL, is an online library of data files and stories that illustrate the use of basic statistical methods. The stories are classified by method and by topic. Go to this site and click on List all topics. Pick a story and summarize how statistics were used in the story.
1.20 Go to the official Microsoft Excel Web site, www.microsoft.com/office/excel. Explain how you think Microsoft Excel could be useful in the field of statistics. 1.21 The Gallup organization releases the results of recent polls at its Web site, www.galluppoll.com. Go to this site and read today s top analysis.
a. Give an example of a categorical variable found in the poll. b. Give an example of a numerical variable found in the poll. c. Is the variable you selected in (b) discrete or continuous?
1.22 The U.S. Census Bureau site, www.census.gov, contains survey information on people, business, geography, and other topics. Go to the site and click on Housing in the People and Households section. Then click on
American Housing Survey.
a. Briefly describe the American Housing Survey.
b. Give an example of a categorical variable found in this survey. c. Give an example of a numerical variable found in this survey. d. Is the variable you selected in (c) discrete or continuous?

PROBLEMS
1.23 On the U.S. Census Bureau site, www.census.gov, click on Survey of Business Owners in the Business &
Industry section and read about The Survey of Business
Owners. Click on Sample SBO-1 Form to view a survey form. a. Give an example of a categorical variable found in this survey. b. Give an example of a numerical variable found in this survey. c. Is the variable you selected in (b) discrete or continuous?
1.24 An online survey of almost 53,000 people
(N. Hellmich, Americans Go for the Quick Fix for
Dinner, USA Today, February 14, 2005, p. B1) indicated that 37% decide what to make for dinner at home at the last minute and that the amount of time to prepare dinner averages 12 minutes, while the amount of time to cook dinner averages 28 minutes.
a. Which of the four categories of data sources listed in
Section 1.4 on page 6 do you think were used in this study? b. Name a categorical variable discussed in this article.
c. Name a numerical variable discussed in this article.
1.25 According to a Harris Interactive survey of 502 senior human resource executives, 58% responded that referrals were one of the methods for finding the best candidates. ( USA Snapshots, USA Today, February 9, 2006,
p. A1).
a. Describe the population for the Harris Interactive survey.
b. Is a response to the question By which methods do you feel you find the best candidates? categorical or numerical? c. Fourteen percent of the senior human resources executives polled indicated that professional associations were one of the methods for finding the best candidates.
Is this a parameter or a statistic?
1.26 A manufacturer of cat food was planning to survey households in the United States to determine purchasing habits of cat owners. Among the questions to be included are those that relate to
1. where cat food is primarily purchased.
2. whether dry or moist cat food is purchased.
3. the number of cats living in the household.
4. whether the cat is pedigreed.
a. Describe the population.
b. For each of the four items listed, indicate whether the variable is categorical or numerical. If it is numerical, is it discrete or continuous?
c. Develop five categorical questions for the survey.
d. Develop five numerical questions for the survey.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

End-of-Chapter Cases

Student Survey Data Base
1.27 A sample of 50 undergraduate students answered the following survey.
1. What is your gender? Female___
Male___
2. What is your age (as of last birthday)?___
3. What is your height (in inches)?___
4. What is your current registered class designation?
Freshman___ Sophomore___ Junior___ Senior___
5. What is your major area of study?
Accounting___ Economics/Finance___
Information Systems___ International Business___
Management___ Marketing/Retailing___ Other___
Undecided___
6. At the present time, do you plan to attend graduate school? Yes___
No___
Not sure___
7. What is your current cumulative grade point average?___ 8. What would you expect your starting annual salary
(in $000) to be if you were to seek employment immediately after obtaining your bachelor s degree?___ 9. What do you anticipate your salary to be (in $000) after five years of full-time work experience?___
10. What is your current employment status?
Full-time___
Part-time___
Unemployed___
11. How many clubs, groups, organizations, or teams are you currently affiliated with on campus?
12. How satisfied are you with the student advisement services on campus?___
Extremely 1 2 3 4 5 6 7 Extremely unsatisfied Neutral satisfied 13. About how much money did you spend this semester for textbooks and supplies?___
The results of the survey are in the file undergradsurvey.xls .
a. Which variables in the survey are categorical?
b. Which variables in the survey are numerical?
c. Which variables are discrete numerical variables?
1.28 A sample of 50 MBA students answered the following survey:

15

1.
2.
3.
4.

What is your gender? Female___
Male___
What is your age (as of last birthday)?___
What is your height (in inches)?___
What is your current major area of study?
Accounting ___ Economics/Finance___
Information Systems___ International Business___
Management___ Marketing/Retailing___ Other___
Undecided___
5. What is your graduate cumulative grade point index?___ 6. What was your undergraduate area of specialization?
Biological Sciences___ Business Administration___
Computers or Math___ Education___
Engineering___ Humanities___ Performing Arts___
Physical Sciences___ Social Sciences___
Other___
7. What was your undergraduate cumulative grade point average?___ 8. What was your GMAT score?___
9. What is your current employment status?___
Full-time___
Part-time___
Unemployed___
10. How many different full-time jobs have you held in the past 10 years?___
11. What do you expect your annual salary (in $000) to be immediately after completion of the MBA program?___ 12. What do you anticipate your salary to be (in $000) after five years of full-time work experience following the completion of the MBA program?___
13. How satisfied are you with the student advisement services on campus?
Extremely 1 2 3 4 5 6 7 Extremely unsatisfied Neutral satisfied 14. About how much money did you spend this semester for textbooks and supplies?___
The results of the survey are in the file gradsurvey.xls .
a. Which variables in the survey are categorical?
b. Which variables in the survey are numerical?
c. Which variables are discrete numerical variables?

End-of-Chapter Cases
At the end of most chapters, you will find a continuing case study that allows you to apply statistics to problems faced by the management of the Springville Herald, a daily

newspaper. Complementing this case are a series of Web
Cases that extend many of the Using Statistics scenarios that begin each chapter.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

16

CHAPTER ONE Introduction and Data Collection

Learning with the Web Cases
People use statistical techniques to help communicate and present important information to others both inside and outside their businesses. Every day, as in these examples, people misuse these techniques:
A sales manager working with an easy-to-use charting program chooses an inappropriate chart that obscures data relationships.
The editor of an annual report presents a chart of revenues with an abridged Y-axis that creates the false impression of greatly rising revenues.
An analyst generates meaningless statistics about a set of categorical data, using analyses designed for numerical data.
Identifying and preventing misuses of statistics, whether intentional or not, is an important responsibility for all managers. The Web Cases help you develop the skills necessary for this important task.
Web Cases send you to Web sites that are related to the
Using Statistics scenarios that begin each chapter. You review internal documents as well as publicly stated claims, seeking to identify and correct the misuses of statistics. Unlike a traditional case study, but much like realworld situations, not all of the information you encounter will be relevant to your task, and you may occasionally discover conflicting information that you need to resolve before continuing with the case.
To assist your learning, the Web Case for each chapter begins with the learning objective and a summary of the problem or issue at hand. Each case directs you to one or more Web pages where you can discover information to answer case questions that help guide your exploration.
If you prefer, you can view these pages by opening corresponding HTML files that can be found on this Web
Case folder on the Student CD. You can find an index of all files/pages by opening the SpringvilleCC.htm file in the Web Case folder or by visiting the Springville
Chamber of Commerce page, at www.prenhall.com/
Springville/SpringvilleCC.htm.

Web Case Example
To illustrate how to learn from a Web Case, open a Web browser and link to www.prenhall.com/Springville/
Good_Tunes.htm, or open the Good_Tunes.htm file in the WebCase folder on the book s CD. This Web page represents the home page of Good Tunes, the online retailer

mentioned in the Using Statistics scenario in this chapter.
Recall that the privately held Good Tunes is seeking financing to expand its business by opening retail locations. Since it is in management s interest to show that
Good Tunes is a thriving business, it is not too surprising to discover the our best sales year ever claim in the
Good Times at Good Tunes entry at the top of their home page.
The claim is also a hyperlink, so click on our best sales year ever to display the page that supports the claim. How would you support such a claim? with a table of numbers? a chart? remarks attributed to a knowledgeable source?
Good Tunes has used a chart to present two years ago and latest twelve months sales data by category. Are there any problems with the choices made on this Web page?
Absolutely!
First, note that there are no scales for the symbols used, so it is impossible to know what the actual sales volumes are. In fact, as you will learn in Section 2.6, charts that incorporate symbols in this way are considered examples of chartjunk and would never be used by people seeking to properly use graphs.
This important point aside, another question that arises is whether the sales data represent the number of units sold or something else. The use of the symbols creates the impression that unit sales data are being presented. If the data are unit sales, does such data best support the claim being made or would something else, such as dollar volumes be a better indicator of sales at
Good Tunes?
Then there are those curious chart labels. Latest twelve months is ambiguous; it could include months from the current year as well as months from one year ago and therefore may not be an equivalent time period to two years ago. Since the business was established in
1997, and the claim being made is best sales year ever, why hasn t management included sales figures for every year? Is Good Tunes management hiding something, or are they just unaware of the proper use of statistics? Either way, they have failed to properly communicate a vital aspect of their story.
In subsequent Web Cases, you will be asked to provide this type of analysis, using the open-ended questions of the case as your guide. Not all the cases are as straightforward as this sample, and some cases include perfectly appropriate applications of statistics.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

References

17

REFERENCES
1. McCullough, B. D., and B. Wilson, On the Accuracy of
Statistical Procedures in Microsoft Excel 97, Computational Statistics and Data Analysis, 31 (1999), 27 37.
2. McCullough, B. D. and B. Wilson, On the Accuracy of
Statistical Proucedures in Microsoft Excel 2003,
Computational Statistics and Data Analysis, 49,
(2005), 1244 1252.

3. Microsoft Excel 2007 (Redmond, WA: Microsoft
Corporation, 2007).
4. Nash, J. C., Spreadsheets in Statistical Practice
Another Look, The American Statistician, 60, (2006),
287 289.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

18

EXCEL COMPANION to Chapter 1

Excel Companion to Chapter 1
This Excel Companion serves as a primer for Microsoft
Excel, helping you become familiar with the concepts and commands that everyday use of Microsoft Excel requires.
Before you intensely study this Companion, you should first consider how you plan to use Excel as you learn statistics. If you skipped reading the From the Authors Desktop: Using and Learning Microsoft Excel on page 5, you may want to review it now so that you have a better understanding of the choices you have in using Excel with this text.
How you plan to use Excel will affect which Excel skills you immediately need to know. If you plan to use the
Basic Excel instructions in later companions, you should know, or at least have some awareness of, just about every skill discussed in the rest of this Companion. If you plan to use PHStat2, you will have a less immediate need for the skills related to worksheet entries and formulas discussed in Sections E1.3, E1.4, and E1.5. (You can master such skills later, as you read through this book.)
The rest of this companion presents skills in an increasing order of difficulty. Make sure you have mastered the skills presented in the first sections before going on to the later sections. If you consider yourself an experienced Excel user, you might want to take the time to scan this companion if only to become familiar with the terms used throughout the book to describe Excel objects and operations.

E1.1

PRELIMINARIES: BASIC
COMPUTING SKILLS

If you have ever surfed the Web, sent an instant message, played music or games, or written word-processed assignments, you have already mastered the skills necessary in order to use Microsoft Excel. However, if you are new to computing, you should use the following countdown of skills on your mark, get ready, get set, go to master the basic computing skills needed with this book. (If you are an experienced computer user, you will want to skim this section and note the definitions of the boldfaced terms.)

On Your Mark!
Like many other programs, Microsoft Excel makes frequent use of a keyboard and a mouse-type pointing device.
Microsoft Excel expects your pointing device to have two

buttons: a primary button (typically the left button) and a secondary button (typically the right). You move your pointing device and use one of the buttons to execute one of these six basic operations1:
Click Move the mouse pointer over an object and press the primary button. You click links on a Web page and many of the user interface elements identified in the Get Set! part of this section. The book also uses the verb clear when telling you to click on a check box to remove its check mark. Select Similar to click, but when you press the primary button, another list of menu commands or choices appear. You click the Microsoft Windows Start menu and then select
Programs or All Programs (depending on the version) to display a list of programs and program folders installed on your system.
Double-click Move the mouse pointer over an object and click the primary button twice in rapid succession. You double-click Desktop program icons to open programs, and you double-click the icons that represent files to open and use those files.
Right-click Move the mouse pointer over an object and click the secondary button. You typically right-click an object to reveal a shortcut menu of commands that apply to that object.
Drag A multipart mouse operation. First, you move the mouse pointer over an object and then, while pressing and holding down the primary button, you move the mouse pointer somewhere else on the screen and release the primary button. You use drag to resize windows and, in Microsoft
Excel, to select a group of adjacent worksheet cells.
Drag-and-drop A multipart mouse operation. First, you move the mouse pointer over an object and then, while pressing and holding down the primary button, you move the mouse pointer over another onscreen object and release the primary button. Drag-and-drop has many applications, but in this text, you mostly use this operation when defining PivotTables (discussed in the Excel Companion to
Chapter 2).
1Alternate

methods of interaction, such as using speech recognition input, are possible on suitably equipped systems.
If you are using an alternate method, make sure you know the equivalents to the mouse operations defined in this section.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

E1.1: Preliminaries: Basic Computing Skills

If these operations are new to you, you can practice them by opening the Mousing Practice.xls file on the Student CD.
(If you do not know how to open a file, ask a friend or use the instructions available in Section E1.2.) Otherwise, you are ready to advance to the Get Ready! stage.

Get Ready!
When you start Microsoft Excel, you see a window that contains the Excel user interface and a workspace area that displays open workbooks. If you start any Excel version other than 2007, you will see a window very much like the one shown in Figure E1.1, the actual window for Excel 2003.
If you start Excel 2007, you will see the very differentlooking window, similar to the one shown in Figure E1.2 on page 20.
In designing Excel 2007, Microsoft tried to minimize the find-and-seek process that many users of earlier versions experience trying to find a particular command or Excel feature. Generally, Excel 2007 displays all relevant commands for a particular task at the top of the window. If you are new to Microsoft Excel and have a choice of using Excel 2007 or an earlier version, you will most likely benefit from choosing Excel 2007 because displaying all relevant commands lessens the initial training you need. However, Excel 2007 is functionally equivalent to earlier Excel versions, and every
Excel activity in this text can be done with any version of
Excel, starting with Excel 97, although sometimes there are special instructions specific to Excel 2007.

Get Set!

19

Excel. When opening Excel, you see a window that is either similar to Figure E1.1 if you use Excel 97, 2000,
2002, or 20032 or Figure E1.2 if you use Excel 2007.
(Should your Excel window not contain some of the elements shown in the figure appropriate for the Excel version you use, see the Microsoft Excel FAQs section in
Appendix F. As you begin using Excel, you will encounter dialog boxes, special windows that display messages to allow you to make entries or selections. Because you will be frequently interacting with dialog boxes, you should be familiar with the objects you will commonly see in these special windows (see Figure E1.3 on page 20).
The rest of this section defines the objects labeled in
Figures E1.1, E1.2, and E1.3. You should be familiar with the objects that apply to the Excel version you use before continuing to the next section.
Excel Window Elements (all versions)
Minimize, resize, and close buttons minimize (that is, hide without closing), resize, and close windows. When you click the sets of labeled buttons in Figures E1.1 and E1.2, you affect the Excel window itself; other, similar buttons operate on other elements in the Excel window, such as the currently opened workbook.
Workspace area displays the currently opened workbook or workbooks. (Although you will not need to open more than one workbook at a time when using this text, you can open multiple workbooks and view all of them by resizing them to fit in the workspace area.)

2In

To get started using Microsoft Excel, you need to be familiar with the objects you will commonly see when using

the remainder of the book, the labels Excel 97 2003 or
97 2003 are used when collectively referring to one of these
Excel versions.

Title bar
Minimize, resize, and close buttons

Formula bar

Menu bar
Standard toolbar
Formatting toolbar

Workspace area with opened workbook

Task pane

Scroll bars
Sheet tabs

F IGU R E E 1 . 1 The Excel 2003 window
Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

20

EXCEL COMPANION to Chapter 1

Title bar

Quick access toolbar

Formula bar

Group
Tabs
(Home tab selected)

Office
Button

Launcher button

Minimize, Resize, and Close buttons

Workspace area with opened wookbook
Scroll bars
Sheet tabs

FIG UR E E 1 . 2 The Excel 2007 window
Formula bar shows the name of the currently selected worksheet cell (see Section E1.3) and the current contents of that cell.
Sheet tabs display the name of each sheet in the opened workbook. You click a sheet tab to select a sheet and make it the currently active sheet. You double-click a sheet tab to rename the sheet.
Scroll bars allow you to travel horizontally or vertically through parts of a worksheet that are offscreen
(for example, row 100 or column T in Figures E1.1 or E1.2).

Title bar displays the name of the currently active workbook and contains the minimize, resize, and close buttons for the Excel window. You drag the title bar to reposition the Excel window on your screen.
Additional Window Objects (Excel 97 2003)
Menu Bar The horizontal list of words at the top of the window that represent sets of commands. You click a menu bar word and pull down lists of command choices, some of which lead to further menu choices.

Question-mark buttons Drop-down lists

Edit box (with spinner buttons

List box Set of option buttons

Check box
OK & Cancel buttons

FIG UR E E 1 . 3 Commonly encountered elements

E1.1: Preliminaries: Basic Computing Skills

Standard Toolbar The toolbar that contains shortcuts to many file-oriented commands, including the common workbook operations discussed in Section E1.4.
Formatting Toolbar The toolbar that contains shortcuts to many common worksheet cell formatting commands (see
Section E1.4).
Task Pane A closable window that contains clickable links that represent shortcuts to menu and toolbar operations
(Excel 2002 and 2003 only).
Additional Windows Objects Excel 2007
Office Button displays a menu of commonly issued commands similar to the File menu in Excel 97 2003. The Office
Button also gives you access to many Excel options settings.
Quick Access Toolbar (to the right of the office button)
Displays buttons that are shortcuts to commonly used commands. When you first see Excel 2007, the Save, Undo, and Redo buttons are displayed, but you can add or remove buttons in this toolbar (see Microsoft Excel 2007-Specific
FAQs in Appendix F to add or remove buttons.)
Tabs Displays groups of commands and features associated with a single type of Excel task. The Home Tab (seen in Figure E1.2) displays all commands and features associated with making worksheet cell entries.
Tab Groups Display named collections of related commands and features. Some tab groups, such as the Font, Alignment, and Number groups shown in Figure E1.2 contain launcher buttons that open related dialog boxes or task panes.
Contextual Tabs Additional tabs that appear only when you are doing a specific task (that is, working in a specific context ), such as creating charts. Contextual tabs are displayed with a title (for example, Chart Tools ) that appears in the title bar.
Ribbon is the collective name for the tab-and-group user interface exclusive to Excel 2007. (See Microsoft Excel
2007-Specific FAQs in Appendix F if your Excel 2007 window does not display the ribbon.)
Common Dialog Box Objects
List boxes Display lists of choices available to you. Should a list exceed the dimensions of a list box, you will see scroll buttons and a slider that you can click in order to see the other choices available.
Drop-down lists Display lists of commands or choices when you click over them. Many drop-downs in Excel
2007 display galleries, which are illustrated (and sometimes annotated) sets of choices.
Edit boxes Areas into which you type entries. Some edit boxes also contain drop-down lists or spinner buttons that you can use to complete the entry. Cell range edit boxes typically include a button that allows you to point to a cell range instead of typing the range.

21

Option buttons Present a set of mutually exclusive choices. When you click one option button, all the other option buttons in the set are cleared.
Check boxes Present optional actions that are not mutually exclusive choices. Unlike with option buttons, clicking a check box does not affect the status of other check boxes, and more than one check box can be checked at a time. If you click an already checked check box, you clear the check from the box.
OK buttons (in dialog boxes) Allow you to execute tasks using the current values and settings of the currently displayed dialog box. Sometimes the OK button will have a different legend, such as Finish, Create, Open, or Save.
Cancel buttons Close dialog boxes and cancel operations represented by the dialog boxes. In most contexts, clicking the Cancel button is equivalent to clicking the Close button in the title bar of the dialog box.
Question-mark buttons Display Excel help messages.
Many dialog boxes contain a button with the legend Help that also displays help messages.

Go!
To start using Microsoft Excel, you need to understand the conventions used in Excel menus and be familiar with commonly used special keys and keystroke combinations.
Excel menus use these conventions (labeled in
Figure E1.4):
*

*

*

An underlined letter in a menu choice represents an accelerator key, a keystroke that is equivalent to selecting the choice with your mouse.
An ellipsis indicates that when you select a menu choice, a dialog box will appear.
A triangle marker indicates that when you select that menu choice, you will see either another menu of choices or a gallery of choices.

You can execute a number of frequently used menu choices or Excel operations by using a combination keystroke that is, holding down one or more keys while pressing another key. In Excel versions other than Excel
2007, many of these combinations appear next to the menu choices they represent. You can print your currently active worksheet by pressing the combination Ctrl+P (that is, while holding down the Ctrl key, press the P key) and save your currently active workbook by pressing Ctrl+S. You can copy (or cut) and paste worksheet cell entries by pressing Ctrl+C (or Ctrl+X) and Ctrl+V.
You can use some of the special keys to execute frequently used operations. When you type an entry, pressing the
Escape key usually cancels that entry. Pressing the
Backspace key when typing an entry erases typed characters

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

22

EXCEL COMPANION to Chapter 1

Keyboard shortcuts Accelerator keys Ellipses

bol to link selections. For example, in Excel versions other than 2007, select Tools * Data Analysis means that you would first select Tools from the Excel menu bar and then select Data Analysis from the submenu of choices that appears after you select Tools. The equivalent Excel 2007 sequence select Data * Data Analysis means select the
Formulas tab and then select Data Analysis (from the
Solutions group).

E1.2
Triangle
markers

FIG UR E E1.4 Excel menu conventions

BASIC WORKBOOK OPERATIONS

As you work with Microsoft Excel, you will need to open workbooks to use data and results created by you or others at an earlier time. You will also need to save workbooks to ensure their future availability and to protect yourself against any computer system failures that might occur as you work with Excel. You may also need to create new workbooks and may want to print out individual sheets for your workbooks for later study or use in projects and assignments.
In all Excel versions, including Excel 2007, these operations involve dialog boxes that differ only in minor ways.
While the dialog box of the version you are using may subtly differ from the ones shown in Figure E1.3 (see page 21), the instructions in this section apply to all Excel versions, except when otherwise noted.

Opening and Saving Workbooks to the left of the cursor, one character at a time. Pressing the
Delete key when typing erases characters to the right of the cursor, one character at a time. Pressing either the Enter or
Tab keys finalizes a typed worksheet cell entry.
When viewing a dialog box, pressing Escape usually cancels its operation. After clicking a workbook object such as a chart, pressing Delete deletes that object. If you get stuck while using Excel, you can move the mouse pointer over an object and then pause to see if Excel displays a Tool Tip, a pop-up help message, about the object.
At any time, you can also press the F1 key to display either a help message or help search box.

Textbook Conventions
In this book, dialog box objects are usually referred to by their names or labels. You will find instructions such as
Click Labels or Click OK when referring to check boxes and command buttons. You will find instructions such as Select the Lower-Tail option when referring to option boxes or drop-down lists. When object names or labels can vary due to context, the book uses italics, as in
Select variable name.
To describe a sequence of menu (Excel 97 2003) or ribbon (Excel 2007) choices, the book uses an arrow sym-

You open and save workbooks by selecting the storage folder to use and then specifying the file name of the workbook.
You begin the process by selecting File from the Excel menu bar in Excel 97 2003 or by clicking the Office Button in
Excel 2007. In either case, a menu of commands appears, as partially shown in Figure E1.4. While the contents of the menus differ (the Excel 2007 menu is on the right in Figure
E1.4), they both have Open and Save As choices. These choices lead to similar dialog boxes, shown in Figure E1.3 and Figure E1.5.
You select the storage folder using the drop-down list at the top of these dialog boxes. You enter (or select from the list box) a file name for the workbook in the File name box. You click Open (obscured in Figure E1.3) or Save to complete the task.
Sometimes when saving files, you will want to change the file type before you click Save. If you use Excel 2007 and want to save your workbook in the format used by earlier Excel versions, you select Excel 97-2003 Workbook
(*.xls) from the Save as type drop-down list before you click Save (shown in Figure E1.5). If you use any version of Excel and want to save data in a form that can be opened by programs that cannot open Excel workbooks, you might select either Text (Tab delimited) (*.txt) or CSV (Comma delimited) (*.csv) as the save type.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

E1.2: Basic Workbook Operations

23

F I GU RE E 1 . 5 Save As dialog box (Excel 2007 version)
Likewise, if you are opening a data file that is not an
Excel workbook format, you can change the file type
(labeled as File of type in Figure E1.3). If you cannot find a file that you are sure is in the current Look in folder, changing the file type to All Files (*.*) can reveal an inadvertent misspelling or missing file extension (the part of the file name after the period) that otherwise prevents the file from being displayed.
Although all versions of Microsoft Excel include a Save command, you should avoid this choice until you gain experience. Using Save makes it too easy for you to inadvertently overwrite your work, and in Excel 2007, it saves your workbook in the new .xlsx workbook format that cannot be used by Excel 97 2003. In contrast, using Save As always presents you with an opportunity to name your file and choose its file type, and using Save As is also the simplest way you can create a backup copy of your workbook as you work.
If you open a workbook from a nonmodifiable source, such as a CD-ROM, Excel marks the workbook readonly. You must use Save As to save a modified version of such a workbook, which is another good reason for always using this command.

Printing Worksheets
When you want to print the contents of a workbook, you should print one sheet at a time to get the best results. You print sheets by first previewing their printed form onscreen and then making any adjustments to the worksheet and/or to the print setup settings.
To print a specific worksheet, you first click on the sheet tab of that worksheet to make the worksheet the currently active one. Then you display the Print
Preview window. If you use a version of Excel other than
2007, select File * Print Preview. If you use Excel
2007, click Office Button, move the mouse pointer over
Print (do not click) and select Print Preview from the
Preview and Print gallery.
The Print Preview windows for all Excel versions are similar to one another. Figure E1.6 shows a partial window for Excel 2003 (top) and Excel 2007 (bottom). If the preview contains errors or displays the worksheet in an unde-

Creating New Workbooks
You create a new workbook through a straightforward process that varies depending on the version of Excel you are using. In Excel 97 or 2000, you select File * New. In
Excel 2002 (also known as Excel XP) or 2003, you select
File * New and then click Blank workbook in the New
Workbook task pane. In Excel 2007, you click Office button * New and in the New Workbook dialog box, you first click Blank workbook and then Create.
New workbooks are created with a fixed number of worksheets. You can delete extra worksheets or insert more sheets by right-clicking a sheet tab and clicking either
Delete or Insert.

FIG UR E E1.6 Partial Print Preview windows (Excel
2003 and 2007 versions)

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

24

EXCEL COMPANION to Chapter 1

sirable manner, click Close (or Close Print Preview in
Excel 2007), make the changes necessary, and reselect the preview command. You can customize your printout by clicking Setup (or Page Setup) and making the appropriate entries in the Page Setup dialog box, which is similar for all Excel versions. For example, to print your worksheet with grid lines and numbered row and lettered column headings (similar to the appearance of the worksheet onscreen), you click the Sheet tab in the Page Setup dialog box and then click Gridlines and Row and column headings and click OK (see Figure E1.7). (You can find more information about using Page Setup in Section F4 of
Appendix F.)

FIG UR E E1.7
Sheet tab of the Page Setup dialog box (Excel
2003 version)
When you are ready to print, you can click Print in the
Print Preview window, but to gain maximum control over your printout, click Close (or Close Print Preview) and then select File * Print (Excel 97 2003) or Office Button
* Print (Excel 2007). In the Print dialog box that appears
(see Figure E1.3 on page 20), you can select the printer to use, make sure you are printing only the currently active worksheet, or print multiple copies at once.

E1.3

WORKSHEET ENTRIES

As first discussed in Section 1.6 (see page 11), you make entries into worksheet cells, the intersections of lettered columns and numbered rows. You use the cursor keys or your pointing device to move a cell pointer through a worksheet and to select a cell for entry. As you type an entry, it appears in the formula bar (see Figures E1.1 and
E1.2 on pages 19 and 20), and you place that entry into the

cell by either pressing Tab or Enter or clicking the checkmark button in the formula bar.
You enter individual numeric and label (sometimes called text) values into cells. You can also enter formulas, which are instructions to perform a calculation or some other task. Usually, formulas use values found in other cells to produce a displayed result. Formulas can automatically change the displayed result when the values in the supporting cells change.
To refer to a cell in a formula, you use a cell address in the form Sheetname!ColumnRow. For example, Data!A2 refers to the cell in the Data worksheet that is in column A and row 2. You can also use just the ColumnRow portion of a full address, for example A2, if you are referring to a cell on the same worksheet as the one into which you are entering a formula.
Sometimes you need to refer to a group of cells, called a cell range. If the group of cells forms a rectangular area for example, a group composed of cells from two adjacent columns you use an address in the form
Sheetname!Upperleftcell:Lowerrightcell. For example,
Data!A1:B10 refers to the 20 cells that are in rows 1 through 10 in columns A and B of the Data worksheet. If your group forms two or more rectangular areas, you enter the range as a list of rectangular areas separated by commas, for example, Data!A1:A10,Data!C1:C10. If the name of the sheet contains spaces or special characters, such as
City Data or Figure-1.2 , you must enclose the sheet name in a pair of single quotes, as in City Data !A1:A10 or Figure-1.2 !A1:A10. 3
(Because you use names of sheets in formulas, you may want to rename sheets from their default names. As mentioned in Section E1.1, you can rename a sheet by double-clicking the sheet tab for the sheet, typing a new, more descriptive name, and then pressing
Enter.)

Entering Formulas
You enter formulas by typing the equal sign (*) followed by some combination of mathematical or other data processing operations. For simple formulas, the symbols +,
,, *, /, and ^ are used for the operations addition, subtraction, multiplication, division, and exponentiation (a number raised to a power), respectively. For example, the
3For

cases in which you need to distinguish between two similarly located cells on two similarly named worksheets in two different workbooks, you use an address in the form
[Workbookname]Sheetname !ColumnRow. For example, you use [Chapter1]Data !A1 to refer to the first cell on the
Data worksheet in the Chapter1 workbook.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

E1.4: Worksheet Formatting

25

FI G UR E E 1 . 8 Insert Function dialog box of the Function Wizard

formula *Data!B2 + Data!B3 + Data!B4 + Data!B5 adds the contents of cells B2, B3, B4, and B5 of the Data worksheet and displays the sum as the value of the cell containing the formula. You can also use worksheet functions to simplify formulas. For example, the formula
=SUM(Data!B2:B5) uses the worksheet SUM function to create a shorter equivalent to the example in this paragraph.
When you are using worksheet functions, you can use the Function Wizard to minimize your typing. To use this wizard, which is similar in all Excel versions, you select
Insert * Function (Excel 97 2003) or Formulas *
Function Wizard (Excel 2007) and then make entries and selections in one or more dialog boxes. For example, to enter the formula =SUM(Data!B2:B5) using this wizard, you select the function SUM and click OK in the Insert
Function dialog box (obscured in Figure E1.8). You then type or point to the cell range B2:B5 in the Number1 box and click OK in the Function Argument dialog box (also shown in Figure E1.8). The wizard then enters the completed formulas in the currently active cell.
As Figure E1.8 shows, the Function Wizard also previews the results of the SUM function (13) and displays the contents of the range B2:B5 (4, 2, 2, and 5).

Verifying Formulas
Whether you enter formulas on your own in a new worksheet, open a workbook that contains formulas, or use commands that add formulas to worksheets, you should review and verify formulas before you use their results. To view the formulas in a worksheet, press Ctrl+` (backtick key).

To restore the original view, the results of the formulas, press Ctrl+\ a second time.
As you create and use more complicated worksheets, you may want to visually examine the relationships among a formula and the cells it uses (called the precedents) and the cells that use the results of the formula (the dependents). To display arrows that show these relationships, use the formula auditing feature of Excel. For Excel
97 or 2000, select Tools * Auditing and for Excel
2002 2003, select Tools * Formula Auditing. Then select one of the choices on the auditing submenu. To use this feature In Excel 2007, select Formulas and then select one of the choices from the Formula Auditing group. The Remove All Arrows choice restores your display by removing all auditing arrows when you are finished auditing the formulas.

E1.4

WORKSHEET FORMATTING

You can use various formatting commands to enhance the appearance of your worksheets. If you use Excel 97 2003, you will find many of the common formatting operations on the Formatting Toolbar (see Figures E1.1 and E1.9) and the rest inside the Format Cells dialog box (select Format
* Cells to view this dialog box). If you use Excel 2007, most formatting operations are visible on the Home tab
(see Figures E1.2 and E1.9), and the rest are available through the launcher buttons of several groups or the
Format choice of the Cells group.
Use Figure E1.9 as a visual guide for locating the common formatting operations.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

26

EXCEL COMPANION to Chapter 1
Fill & Font Color

Font Face
& Size

Boldface

Borders

Left

Merge-and-Center

Percent

Increase &
Decrease Decimal

Borders

Fill & Font Color

FIG UR E E1.9 Formatting Toolbar (Excel 97 2003) and Home tab groups (Excel 2007)

Common Formatting Operations
Font Face & Size Drop-down lists set the font face and font size for the currently selected cells. You will get best results if you select entire rows before changing the font face or size.
Boldface Displays the values of the currently selected cells, using boldface type. Consider using this for cells that contain titles, column headings, or important results. You can also use the Italic and Underline buttons to the immediate right of Boldface for additional type effects.
Left Displays the values of the currently selected cells leftjustified in their cells. The Center and Right buttons next to Left, center, and right-justify values, respectively. In a worksheet with many filled columns, using Center for the values of categorical variables can sometimes improve readability. Merge-and-Center Combines the currently selected cells, merges them into one, and displays the value in the first cell of the group, centered across the merged cells.
Consider using this for a worksheet title or a title that applies to several contiguous rows or columns.
Percent Displays numeric values in the currently selected cells as percentages. The value 0.01 displays as 1%, the value 1 as 100%, and the value 100 as 10000%.
Increase Decimal and Decrease Decimal Increases/ decreases the number of decimal places that will be used to display numeric values in the currently selected cells.
Particularly useful to align the decimal points in a column of numeric values.
Borders Drops down a gallery of border effects from which you can choose to change the borders for the currently selected cells. Many worksheet examples in this text use one or more of these border effects: All Borders,
Outside Borders, and Top Border.
Fill Color and Font Color Drop down a gallery from which you can choose to change the cell background color and the type color for the currently selected cells. Many

worksheet examples in this text use the color Light
Turquoise to tint areas that contain user-changable data values and the color Light Yellow to tint areas that contain results. These colors are available in the Excel
2002 2003 galleries and by clicking More Colors in the
Excel 2007 gallery.
In addition to these operations, you may want to adjust the width of a worksheet column so that its column heading and all its values are clearly visible. To do this, select the entire column and then select Format *
Column * AutoFit Selection (Excel 97 2003) or Home
* Format (in the Cells group) * AutoFit Selection
(Excel 2007).

E1.5

COPY-AND-PASTE OPERATIONS

There will be times that you will want to copy cell entries to another part of the same worksheet or to another worksheet as well as copy an entire worksheet for insertion into another workbook. Copying and pasting cell entries entirely composed of numeric or text values is fairly straightforward. You select the cells to be copied, press
Ctrl+C, move to the first cell of the range in which you want to paste the copy, and press Ctrl+V. However, often you will copy cells that contain one or more formulas.
Copying cells that contain formulas is not necessarily as straightforward as you might expect. Likewise, copying cells or entire worksheets between workbooks raises some issues you need to understand in order to get the best results possible.

Copying Formulas
Copying entries that contain formulas requires extra attention because exact duplicates may or may not result, depending on how you have entered cell addresses. If you entered addresses using the Sheetname!ColumnRow or
ColumnRow form, as introduced in Section E1.3, Excel

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

E1.5: Copy-and-Paste Operations

considers them relative references that will change to reflect the difference, or offset, between the original
(source) cell and the cell into which you are pasting the formula (the target cell). For example, when you copy the formula =A2 + B2 in cell C2 down to cell C3, an offset of one row, Excel pastes the formula =A3 + B3 in C3 to reflect that one-row offset. Cell ranges also get changed, so if you copy the formula =SUM(A1:A4) from cell A5 to cell B5, the formula is changed to =SUM(B1:B4).
You typically want Excel to make these changes when you are copying a formula down a column or across a row.
This allows you to enter the formula once and then use copy-and-paste to fill in the similar formulas in the column or row. Sometimes, especially in cases in which you want to copy and paste a single formula, you may not want Excel to make a change. You can stop Excel from making changes in the column or row offset by inserting a dollar sign ($) before either the column letter or row number (or both) of a cell address. Addresses written with inserted dollar signs, such as $A$2, are called absolute references and do not change during a copy operation.
For example, if you copy the formula from one cell to another, the formula that appears in the target cell will be the same, =$A$2 + $B$2. You can also use addresses such as $A2 or A$2, if you want Excel to change only the row or column, respectively. For example, when you copy the cell C2 formula =A2/B$10 to cell C3, the formula that appears in C3 is =A3/B$10. However, if you copy the cell C2 formula to cell D3, the formula that would appear is =B3/C$10.
Do not confuse the use of the dollar sign with formatting cell values for currency display. To format cell values for currency display (in dollars and cents, in the U.S. version of Microsoft Excel), you use the Currency ($) format button, located to the left of the Percent button in either the Formatting toolbar or the Home tab group (see
Figure E1.9).

27

worksheet named Results to cell A5 in the worksheet named Summary, you can enter the formula =Results!B10 in cell A5 of the Summary sheet instead of copying the original formula. You can also use this technique to transfer column headings or other labeling information between worksheets in order to maintain consistency across worksheets. If you need to transfer information from a large range of cells, you should consider using the Paste
Special command. To use this command, you first select the cell range to be copied and press Ctrl+C. Then select the first cell of the cell range in the second worksheet that is your target and right-click. Next, select Paste
Special from the shortcut menu that appears. In the
Excel Paste Special dialog box (similar for all Excel versions; Figure E1.10 shows the Excel 2003 dialog box), you select the Values and number formats option and click OK. Selecting this option places the current values of all formulas in the second worksheet, so if you change the underlying data, you need to repeat this procedure to update the values shown on the second worksheet. Copying Formulas Between Worksheets
When you copy and paste formulas between worksheets, you generally want to make sure that all cell addresses contain a worksheet name and are absolute references for example, Data!$A$1:$A$12 in order to ensure consistent results. If you only need to transfer the calculated results from one sheet to another (for example, to create your own summary or report worksheet), you should consider using one of two methods to transfer information between worksheets. If you need to transfer only one or a few cells worth of information, consider entering formulas in the form =Sourcesheetname!Cellreference to transfer the information into the second worksheet. For example, if you want to transfer the results of a formula found in cell B10 of the

F IGU RE E 1 .10
Paste Special dialog box (Excel 2003 version)

Copying Worksheets
You can copy worksheets to a new workbook or to any opened workbook, including the source of the copy.
To copy a worksheet to a new workbook, you first select the worksheet by clicking its sheet tab. Then you rightclick the tab and select Move or Copy from the shortcut

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

28

EXCEL COMPANION to Chapter 1

menu that appears. In the Move or Copy dialog box
(similar for all Excel versions; Figure E1.11 shows the Excel 2007 dialog box), select (new book) from the To book drop-down, click Create a copy, and click
OK.

F IG UR E E1 .1 1
Move or Copy dialog box (Excel 2007 version)
You use the same procedure, changing only your selection in the To book drop-down list, to copy a worksheet to any opened workbook, including the source of the copy.
(You may want to duplicate a worksheet in the same workbook so that you can have a copy that shows the formulas contained in the worksheet or that presents the same information in an alternate format.) When you make a copy of a worksheet and place it in the same workbook, Excel assigns the copied worksheet the name of the original sheet, plus a number, in parentheses. For example, if you copy a sheet named Calculations, the copied sheet will be named Calculations (2). (You can and should consider renaming copied worksheets to give them more descriptive names.) E1.6

ADD-INS: MAKING THINGS
EASIER FOR YOU

Add-ins can simplify the task of creating something to add to a workbook. Add-ins are programming components not included in the main Excel program and may need to be installed, or added, to your computer system separately.
Add-ins are not always available for you to use because they can be disabled by other users or system security set-

tings. However, the little extra effort you need to ensure that the right add-ins are installed and enabled is well worth the features that add-ins bring to Excel.
In this book, you use the Analysis ToolPak add-in that is included with Microsoft Excel. You may make use of
PHStat2, a Prentice Hall add-in that is included on the
Student CD. The Analysis ToolPak add-in (which the book simply calls the ToolPak from this point forward) adds statistical procedures to Excel, but creates worksheets that contain only text and numbers and no formulas. This means that the results you create using the ToolPak will not change if you change the underlying data. (To get updated results, you would have to use the ToolPak a second time.
This is in contrast to worksheets that contain formulas which update themselves automatically when data are changed.) PHStat2 adds a PHStat menu of procedures to the Excel menu bar (Excel 97 2003) or the Add-ins tab (Excel 2007).
Unlike the ToolPak, PHStat2 usually creates worksheets that contain formulas and that will produce new results as the underlying data changes. Sometimes, though, PHStat2 asks the ToolPak to create sheets on its behalf, and the resulting sheets are similar to the no-formulas sheets that the ToolPak creates. In many such cases, PHStat2 enhances the sheets it asks the ToolPak to create, correcting errors the ToolPak makes or adding new formula-based calculations.
Before you continue, you should check to see if the
ToolPak is already installed and active in your copy of
Microsoft Excel. If you are using an Excel version other than Excel 2007, select Tools and then see if Data Analysis appears on the Tools menu. If you see Data Analysis, the
ToolPak is installed and enabled. If you are using Excel
2007, the best way to check the ToolPak status is to click the Office Button and then click Excel Options in the
Office menu. Finally, you click Add-Ins in the Excel
Options dialog box. If Analysis ToolPak appears in the
Active Applications Add-Ins list, the ToolPak is installed and enabled.
If the ToolPak is not installed and enabled, you may need to use your original Microsoft Office or Excel CD or
DVD and go through the Microsoft Office setup process to add the ToolPak add-in to your copy of Excel. If you plan to use the PHStat2 add in, you should review the appropriate part of Appendix F and read the PHStat2 readme file on the book s CD.

How to Use the ToolPak Add-in
Once you have determined that the ToolPak add-in is properly installed and active, you use the ToolPak by selecting
Tools * Data Analysis (Excel 97 2003) or Formulas *
Data Analysis (Excel 2007). In either case, you will see a
Data Analysis dialog box similar to the one shown in
Figure E1.12.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

E1.6: Add-ins: Making Things Easier for You

29

FIG UR E E1.1 2 Data Analysis dialog box
To use a ToolPak procedure, you select the name of the procedure that you want to use from the Analysis Tools list and then click OK. (The Histogram procedure has been selected in Figure E1.12.) A second dialog box then appears, in which you make entries and selections appropriate for the selected procedure. Click OK in this second dialog box to execute the procedure.

Macro Security Issues
In all Excel versions other than Excel 97, all add-ins that you open will be screened by Microsoft Office security components. If you use Excel 2000, 2002, or 2003, you can review and change the security settings by selecting Tools
* Macros * Security to display a Security dialog box
(similar to the one shown in Figure E1.13 for Excel 2003).
To use an add-in such as PHStat2 that is not signed, select the Medium option and click OK.

F IGU R E E1 .14 Excel Options and Trust Center panes (Excel 2007) issues. Then click Trust Center Settings (obscured in
Figure E1.14) to display the Trust Center dialog box. To use an add-in such as PHStat2 that is not signed, click Add-Ins in the left pane and then clear all checkboxes in the right pane (see Figure E1.15). Then click Macro Settings in the left pane and click Disable all macros with notification in the right pane (see Figure E1.14). (On some systems, you may also have to click Trusted Locations in the left pane and add the file location of the add-in.)
With the Security screen set to an appropriate level, you will see a macro virus warning dialog box when you open

F IG UR E E1 . 1 3 Security dialog box (Excel 2003 version) If you use Excel 2007, click the Office Button, then click Excel Options in the Office menu. In the Excel
Options dialog box (see Figure E1.14) click Trust Center in the left pane to display information about security

FIG UR E E1.1 5 Macro warning dialog boxes (Excel
2003 and 2007)

30

EXCEL COMPANION to Chapter 1

an add-in such as PHStat2. Figure E1.15 shows the Excel
2003 and Excel 2007 warning boxes. You click the Enable
Macros button to allow virus-free add-ins, such as PHStat2, to be opened and used.

KEY

Forum: Click the SETUP AND INSTALLATION ISSUES link if you need additional information setting up and using the add-ins.

TERMS

absolute reference 27 accelerator key 21 add-in 28
Cancel button 21 cell 26 cell pointer 24 cell range 24 check box 21 clear 18 click 18 combination keystroke 21 contextual tabs 21 dialog box 21 double-click 18 drag 18 drag-and-drop 18 drop-down list 21 edit box 21

ellipsis 21
Formatting toolbar 21 formula 24 formula bar 20 gallery 21
Help 21
Home tab 21 launch button 21 list box 21 menu bar 20 minimize, resize, and close buttons 19 mouse pointer 18
Office Button 21
OK button 21 option buttons 21 question-mark button 21 quick access tool bar 21 relative reference 27

ribbon 21 right-click 18 scroll bars 20 select 18 sheet tab 20 shortcut menus 18 slider 21 spinner button 21
Standard toolbar 21 tab 21 tab groups 21 task pane 21 title bar 20
Tool Tip 22 workbook 22 worksheet 24 workspace area 19

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

CHAPTER 2
Presenting Data in Tables and Charts
USING STATISTICS @ Choice Is Yours, Part I
2.1

TABLES AND CHARTS FOR CATEGORICAL
DATA
The Summary Table
The Bar Chart
The Pie Chart
The Pareto Diagram

2.2

ORGANIZING NUMERICAL DATA
The Ordered Array
The Stem-and-Leaf Display

2.3

TABLES AND CHARTS FOR NUMERICAL
DATA
The Frequency Distribution
The Relative Frequency Distribution and the
Percentage Distribution
The Cumulative Distribution
The Histogram
The Polygon
The Cumulative Percentage Polygon (Ogive)

2.4

CROSS TABULATIONS
The Contingency Table
The Side-by-Side Bar Chart

2.5

SCATTER PLOTS AND TIME-SERIES PLOTS
The Scatter Plot
The Time-Series Plot

2.6

MISUSING GRAPHS AND ETHICAL ISSUES
Microsoft Excel Graphs

EXCEL COMPANION TO CHAPTER 2
E2.1 Creating Summary Tables
E2.2 Creating Charts
E2.3 Creating Bar and Pie Charts from Summary
Tables
E2.4 Creating Pareto Diagrams from Summary
Tables
E2.5 Creating an Ordered Array
E2.6 Creating Stem-and-Leaf Displays
E2.7 Creating Frequency Distributions and
Histograms
E2.8 Creating a Histogram from Summarized Data
E2.9 Creating Polygons
E2.10 Creating Contingency Tables
E2.11 Creating Side-by-Side Charts
E2.12 Creating Scatter Plots
E2.13 Creating Time-Series Plots

LEARNING OBJECTIVES
In this chapter, you learn:
* To develop tables and charts for categorical data
* To develop tables and charts for numerical data
* The principles of properly presenting graphs

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

32

CHAPTER TWO Presenting Data in Tables and Charts

USING STATISTICS @ Choice Is Yours, Part I
Choice Is Yours is a service that helps customers make wise investment choices. You ve been hired to assist investors interested in mutual funds, a market basket of securities. According to investopedia.com, A mutual fund is nothing more than a collection of stocks and/or bonds. You can think of a mutual fund as a company that brings together a group of people and invests their money in stocks, bonds, and other securities. Each investor owns shares, which represent a portion of the holdings of the fund. (You can learn more about mutual funds at www.investopedia.com/ university/mutualfunds/.) The Choice Is Yours company previously selected a sample of 838 mutual funds that it believes might be of interest to its customers. You have been asked to present data about these funds in a way that will help customers make good investment choices. What facts about each mutual fund would you collect to help customers compare and contrast the many funds?
A good starting point would be to collect data that would help customers classify mutual funds into various categories. You could research such things as the amount of risk involved in a fund s investment strategy and whether the fund focuses on growth securities, those companies that are expected to grow quickly in the next year, or, on value securities, those companies whose stock prices are currently considered undervalued. You might also investigate whether a mutual fund specializes in a certain size of company and whether the fund charges management fees that would reduce the percentage return earned by an investor.
Of course, you would want to know how well the fund performed in the past. You would also want to supply the customer with several measures of each fund s past performance. While past performance is no assurance of future performance, past data could give customers insight into how well each mutual fund has been managed.
As you further think about your task, you realize that all these data for all 838 mutual funds would be a lot for anyone to review. How could you get your hands around such data and explore them in a comprehensible manner?

T

o get your hands around the data described in this chapter s Using Statistics scenario, you need to use methods of descriptive statistics, defined in Chapter 1 as the branch of statistics that collects, summarizes, and presents data. In this scenario, you need to use descriptive techniques for both categorical variables (to help investors classify the mutual funds) and numerical variables (to help show the return each fund has achieved). Reading this chapter will help you to prepare tables and charts that are appropriate for both types of variables. You ll also learn techniques to help answer questions that require two variables, such as Do growthoriented mutual funds have lower returns than value mutual funds? and Do growth funds tend to be riskier investments than value funds?
Many examples in this chapter use a sample of 838 real mutual funds, the data for which you can find in the Data worksheet of the Mutual Funds.xls file on the Student CD-ROM.

2.1

TABLES AND CHARTS FOR CATEGORICAL DATA
When you have categorical data, you tally responses into categories and then present the frequency or percentage in each category in tables and charts.

2.1: Tables and Charts for Categorical Data

33

The Summary Table
A summary table indicates the frequency, amount, or percentage of items in a set of categories so that you can see differences between categories. A summary table lists the categories in one column and the frequency, amount, or percentage in a different column or columns.
Table 2.1 illustrates a summary table based on a recent survey that asked people what they would do if they had an extra $1,000 to spend ( If You Had an Extra $1,000 to Spend, What
Would You Do with the Money? USA Today, January 11, 2006, p. A1). In Table 2.1, the most common choices are saving and paying debt, followed by buying a luxury item, vacation, or gift and spending on essentials. Very few respondents mentioned giving to charity or other uses. What You Would Do with the Money

TAB L E 2. 1
What People Would Do with an Extra $1,000

EX A MP LE 2 .1

Percentage (%)

Buy a luxury item, vacation, or gift
Give it to charity
Pay debt
Save
Spend on essentials
Other

20
2
24
31
16
7

SUMMARY TABLE OF LEVELS OF RISK OF MUTUAL FUNDS
The 838 mutual funds that are part of the Using Statistics scenario (see page 32) are classified according to their risk level, categorized as low, average, and high. Construct a summary table of the mutual funds, categorized by risk.
SOLUTION The mutual funds are fairly evenly divided by risk (see Table 2.2). There are more high-risk funds (346, or 41%) than low-risk or average-risk funds. There are about the same number of average-risk funds as low-risk funds (29%).

TA BL E 2.2
Frequency and
Percentage Summary
Table Pertaining to Risk
Level for 838 Mutual
Funds

Fund Risk Level
Low
Average
High
Total

Number of Funds

Percentage of Funds (%)

247
245
346
838

29.47
29.24
41.29
100.00

The Bar Chart
In a bar chart, a bar shows each category, the length of which represents the amount, frequency, or percentage of values falling into a category. Figure 2.1 on page 34 displays the bar chart for spending an extra $1,000 presented in Table 2.1.
Bar charts allow you to compare percentages in different categories. In Figure 2.1, respondents are most likely to save or pay debt, followed by buy a luxury item, vacation, or gift and spend on essentials. Very few respondents mentioned giving to charity or other uses. Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

34

CHAPTER TWO Presenting Data in Tables and Charts

FIGURE 2.1
Microsoft Excel bar chart for spending an extra $1,000

See Sections E2.2 and E2.3 to create this.

EX A MP LE 2 .2

BAR CHART OF LEVELS OF RISK OF MUTUAL FUNDS
Construct a bar chart for the levels of risk of mutual funds (based on the information in
Table 2.2) and interpret the results.
SOLUTION The mutual funds are fairly evenly divided by risk (see Figure 2.2 on page 33).
There are more high-risk funds (346, or 41%) than low-risk or average-risk funds. There are about the same number of average-risk funds as low-risk funds (29%).

FIGURE 2.2
Microsoft Excel bar chart of the levels of risk of mutual funds

The Pie Chart
The pie chart is a circle broken up into slices that represent categories. The size of each slice of the pie varies according to the percentage in each category. In Table 2.1 on page 33, for example, 31% of the respondents stated that they would save the $1,000. Thus, in constructing the pie chart, the 360 degrees that makes up a circle is multiplied by 0.31, resulting in a slice of the pie that takes up 111.6 degrees of the 360 degrees of the circle. From Figure 2.3, you can see that the pie chart lets you visualize the portion of the entire pie that is in each category. In this figure, saving takes 31% of the pie and giving to charity takes only 2%.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

2.1: Tables and Charts for Categorical Data

35

FIGURE 2.3
Microsoft Excel pie chart for spending an extra $1,000

See Sections E2.2 and E2.3 to create this.

Which chart should you use a bar chart or a pie chart? The selection of a particular chart often depends on your intention. If a comparison of categories is most important, you should use a bar chart. If observing the portion of the whole that is in a particular category is most important, you should use a pie chart.

EXA MPL E 2.3

PIE CHART OF LEVELS OF RISK OF MUTUAL FUNDS
Construct a pie chart for the levels of risk of mutual funds (see Table 2.2 on page 33) and interpret the results.
SOLUTION (See Figure 2.4.) The mutual funds are fairly evenly divided by risk. There are more high-risk funds (346, or 41%) than low-risk or average-risk funds. There are about the same number of average-risk funds as low-risk funds (29%).

FIGURE 2.4
Microsoft Excel pie chart of the levels of risk of mutual funds

The Pareto Diagram
In a Pareto diagram, the categorized responses are plotted in descending order, according to their frequencies, and are combined with a cumulative percentage line on the same chart. The
Pareto diagram can identify situations in which the Pareto principle occurs.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

36

CHAPTER TWO Presenting Data in Tables and Charts

PARETO PRINCIPLE
The Pareto principle exists when the majority of items in a set of data occur in a small number of categories and the few remaining items are spread out over a large number of categories. These two groups are often referred to as the vital few and the trivial many.

The Pareto diagram has the ability to separate the vital few from the trivial many, enabling you to focus on the important categories. In situations in which the data involved consist of defective or nonconforming items, the Pareto diagram is a powerful tool for prioritizing improvement efforts.
Table 2.3 presents data for a large injection-molding company that manufactures plastic molded components used in computer keyboards, washing machines, automobiles, and television sets (see the keyboarddefects.xls file). The data presented in Table 2.3 consist of all computer keyboards with defects produced during a three-month period.
Cause

TAB LE 2 .3
Summary Table of
Causes of Defects in
Computer Keyboards in a Three-Month Period

Frequency

Percentage

413
1,039
258
834
442
275
413
371
292
1,987
6,324

6.53
16.43
4.08
13.19
6.99
4.35
6.53
5.87
4.62
31.42
100.01*

Black spot
Damage
Jetting
Pin mark
Scratches
Shot mold
Silver streak
Sink mark
Spray mark
Warpage
Total

*Result differs slightly from 100.00 due to rounding.
Source: Extracted from U. H. Acharya and C. Mahesh, Winning Back the Customer s Confidence: A Case Study on the
Application of Design of Experiments to an Injection-Molding Process, Quality Engineering, 11, 1999, pp. 357 363.

Table 2.4 presents a summary table for the computer keyboard data in which the categories are ordered based on the percentage of defects present (rather than arranged alphabetically).
The cumulative percentages for the ordered categories are also included as part of the table.
TAB LE 2 .4
Ordered Summary
Table of Causes of
Defects in Computer
Keyboards in a ThreeMonth Period

Cause
Warpage
Damage
Pin mark
Scratches
Black spot
Silver streak
Sink mark
Spray mark
Shot mold
Jetting
Total

Frequency

Percentage

1,987
1,039
834
442
413
413
371
292
275
258
6,324

31.42
16.43
13.19
6.99
6.53
6.53
5.87
4.62
4.35
4.08
100.01*

Cumulative
Percentage
31.42
47.85
61.04
68.03
74.56
81.09
86.96
91.58
95.93
100.00

*Result differs slightly from 100.00 due to rounding.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

2.1: Tables and Charts for Categorical Data

37

In Table 2.4, the first category listed is warpage (with 31.42% of the defects), followed by damage (with 16.43%), followed by pin mark (with 13.19%). The two most frequently occurring categories warpage and damage account for 47.85% of the defects; the three most frequently occurring categories warpage, damage, and pin mark account for
61.04% of the defects, and so on. Figure 2.5 is a Pareto diagram based on the results displayed in Table 2.4.

FIGURE 2.5
Microsoft Excel
Pareto diagram for the keyboard defects data

See Sections E2.2 and E2.4 to create this.

Figure 2.5 presents the bars vertically, along with a cumulative percentage line. The cumulative line is plotted at the midpoint of each bar, at a height equal to the cumulative percentage. If you follow the line, you see that these first three categories account for about
60% of the defects. Because the categories in the Pareto diagram are ordered by the frequency of occurrences, decision makers can see where to concentrate efforts to improve the process. Attempts to reduce defects due to warpage, damage, and pin marks should produce the greatest payoff. Then efforts can be made to reduce scratches, black spots, and silver streaks. In order for a Pareto diagram to include all categories, even those with few defects, in some situations you need to include a category labeled Other or Miscellaneous. In these situations, the bar representing these categories is placed to the right of the other bars.

EXA MPL E 2.4

PARETO DIAGRAM OF SPENDING AN EXTRA $1,000
Construct a Pareto diagram of what respondents would do with an extra $1,000 (see Table 2.1 on page 33).
SOLUTION In Figure 2.6, saving and paying debt account for 55% of what respondents would do with the extra $1,000; 91% of the respondents would save; pay debt; buy a luxury item, vacation, or gift; or spend on essentials.

Statistics for Managers Using Microsoft Excel, Fifth Edition, by David M. Levine, Mark L. Berenson, and Timothy C. Krehbiel. Published by Prentice Hall.
Copyright 2008 by Pearson Education, Inc.

38

CHAPTER TWO Presenting Data in Tables and Charts

FIGURE 2.6
Microsoft Excel Pareto diagram for spending an extra $1,000

From the Authors Desktop

Using Microsoft Excel Properly

U

sually when you learn something, there is eventually some sort of feedback.
Providing feedback is one reason instructors give tests. When you get the results of a test, you can reflect on how well you have properly learned the subject matter. Unfortunately, feedback opportunities are rare when you are learning to use a computer program such as Microsoft Excel as you study statistics. You either get the worksheet to calculate what you want or get a worksheet with misleading or inaccurate results.
This good/bad, pass/fail type of feedback cannot help you refine your Microsoft Excel skills.
We hope the following list will.
You are using Microsoft Excel properly if you can:

1. Understand how to operate the program. Understanding how to operate the program does not mean that you need to know everything about Microsoft Excel. Don t waste your time teaching yourself every command or trying to memorize every function.
Use the Excel Companion to Chapter 1 as your guide for what you need to know to get started using Excel effectively. Learn about additional features only just-in-time when you need to use the feature. Excel features that you learn but don t immediately use are features that you will soon forget. However, if you learn an Excel feature when you need to

use it, such as discovering that you can use
Data * Sort to sort data in Excel 97 2003, you are more likely to remember the proper command. 2. Understand the underlying statistical concepts. Make sure you understand what a worksheet is doing as it produces its information. As a minimum, be informed about what each function you use calculates, or returns, as its value. Knowing this information will minimize your confusion and help you avoid making mistakes such as using the
NORMSDIST function described in Chapter 6 when you meant to use the similar looking but different NORMDIST function.
3. Understand how to format and present information in a worksheet.
Section 1.6 on page 11 has already discussed how to organize your worksheets. With that as a starting point, you should plan how you are going to use the rows and columns of a worksheet to enhance the contents of your worksheets. For almost every worksheet illustrated in this text, the authors changed column widths, book font characteristics, and background tints of selected cells and added borders to highlight various cell ranges. Use the author s techniques (reviewed in the
Excel Companion to Chapter 1) or invent your own, but always remember to have a plan that will present information effectively.

4. Know how to review formulas for errors. Never assume that any worksheet you create or that was created for you is free of error. Always examine all formulas that have been entered into a worksheet. (See the Excel
Companion to Chapter 1 for one way to do this.) You need to do this even if you use worksheets created by PHStat2 or retrieved from the Student CD-ROM.
5. Take control of your copy of
Microsoft Excel. Customize the settings of
Microsoft Excel (and Microsoft Windows) to best suit your style, abilities, and needs. For example, many overlook increasing the default 10-point Arial font to a more readable size when examining the contents of a worksheet. If you own or control the computer on which you use Excel, you can make such customizations permanent so that they are automatically available to you every time you use
Microsoft Excel.
In addition, if you own or control your own computer, you should regularly apply the security updates to Microsoft Office that
Microsoft puts on its Web site. And if you plan to use PHStat2 on your own computer, you should read all the technical documentation including the PHStat readme file on the
Student CD-ROM.
Forum > Visit the online forum to continue this discussion online.