Statistics for Managers Using Microsoft Excel

Cover......Page 1
A Roadmap for Selectinga Statistical Method......Page 2
Title Page......Page 4
Copyright Page......Page 5
About the Authors......Page 7
Brief Contents......Page 8
Contents......Page 9
Preface......Page 18
Resources for Success......Page 24
 Using Statistics: “The Price of Admission”......Page 26
  Statistics: A Way of Thinking......Page 27
  Statistics: An Important Part of Your Business Education......Page 28
  Structured Versus Unstructured Data......Page 29
  Statistic......Page 30
 FTF.4 Preparing to Use Microsoft Excel for Statistics......Page 31
  Ways of Working with Excel......Page 32
  Conventions Used......Page 33
 Key Terms......Page 34
  EG.2 Reviewing Worksheets......Page 35
  EG.3 If You Plan to Use the Workbook Instructions......Page 36
 Using Statistics: Defining Moments......Page 37
 1.1 Defining Variables......Page 38
  Measurement Scales......Page 39
 1.2 Collecting Data......Page 40
  Data Sources......Page 41
 1.3 Types of Sampling Methods......Page 42
  Systematic Sample......Page 43
  Cluster Sample......Page 44
  Data Cleaning......Page 45
  Stacked and Unstacked Variables......Page 46
  Recoding Variables......Page 47
  Sampling Error......Page 48
 Consider This: New Media Surveys/Old Survey Errors......Page 49
 Key Terms......Page 51
 Chapter Review Problems......Page 52
  CardioGood Fitness......Page 53
  Learning with the Digital Cases......Page 54
  EG1.3 Types of Sampling Methods......Page 55
  EG1.4 Data Preparation......Page 56
 Using Statistics: “The Choice Is Yours”......Page 57
  The Summary Table......Page 58
  The Contingency Table......Page 59
 2.2 Organizing Numerical Variables......Page 62
  The Frequency Distribution......Page 63
  Classes and Excel Bins......Page 65
  The Relative Frequency Distribution and the Percentage Distribution......Page 66
  The Cumulative Distribution......Page 68
  The Bar Chart......Page 71
  The Pie Chart and the Doughnut Chart......Page 72
  The Pareto Chart......Page 73
  Visualizing Two Categorical Variables......Page 75
 2.4 Visualizing Numerical Variables......Page 77
  The Stem-and-Leaf Display......Page 78
  The Histogram......Page 79
  The Percentage Polygon......Page 80
  The Cumulative Percentage Polygon (Ogive)......Page 81
  The Scatter Plot......Page 84
  The Time-Series Plot......Page 86
  Multidimensional Contingency Table......Page 88
  Drill Down......Page 89
  Excel Slicers......Page 90
  Sparklines......Page 91
  Obscuring Data......Page 93
  Creating False Impressions......Page 94
  Chartjunk......Page 95
  EXHIBIT: Best Practices for Creating Visualizations......Page 97
 Summary......Page 98
 Key Equations......Page 99
 Chapter Review Problems......Page 100
  Digital Case......Page 105
  Clear Mountain State Student Survey......Page 106
  EG2.1 Organizing Categorical Variables......Page 107
  EG2.2 Organizing Numerical Variables......Page 109
  EG2.3 Visualizing Categorical Variables......Page 111
  EG2.4 Visualizing Numerical Variables......Page 113
  EG2.6 Organizing and Visualizing a Set of Variables......Page 117
 Using Statistics: More Descriptive Choices......Page 120
  The Mean......Page 121
  The Median......Page 123
  The Mode......Page 124
  The Geometric Mean......Page 125
  The Range......Page 126
  The Variance and the Standard Deviation......Page 127
  EXHIBIT: Manually Calculating the Sample Variance, S², and Sample Standard Deviation, S......Page 128
  The Coefficient of Variation......Page 130
  Z Scores......Page 131
  Shape: Kurtosis......Page 133
  EXHIBIT: Rules for Calculating the Quartiles from a Set of Ranked Values......Page 138
  The Five-Number Summary......Page 140
  The Boxplot......Page 142
 3.4 Numerical Descriptive Measures for a Population......Page 144
  The Population Variance and Standard Deviation......Page 145
  The Empirical Rule......Page 146
  Chebyshev’s Theorem......Page 147
  The Covariance......Page 149
  The Coefficient of Correlation......Page 150
 Summary......Page 155
 Key Equations......Page 156
 Checking Your Understanding......Page 157
 Chapter Review Problems......Page 158
  Clear Mountain State Student Survey......Page 161
  EG3.1 Central Tendency......Page 162
  EG3.3 Exploring Numerical Data......Page 163
  EG3.5 The Covariance and the Coefficient of Correlation......Page 164
 Using Statistics: Possibilities at M&R Electronics World......Page 166
 4.1 Basic Probability Concepts......Page 167
  Events and Sample Spaces......Page 168
  Simple Probability......Page 170
  Joint Probability......Page 171
  General Addition Rule......Page 172
  Computing Conditional Probabilities......Page 176
  Decision Trees......Page 177
  Independence......Page 179
  Multiplication Rules......Page 180
  Marginal Probability Using the General Multiplication Rule......Page 181
 4.3 Ethical Issues and Probability......Page 183
 Consider This: Divine Providence and Spam......Page 184
 4.5 Counting Rules......Page 185
 Key Equations......Page 186
 Chapter Review Problems......Page 187
  Clear Mountain State Student Survey......Page 189
  EG4.4 Bayes’ Theorem......Page 190
 Using Statistics: Events of Interest at Ricknel Home Centers......Page 191
  Expected Value of a Discrete Variable......Page 192
  Variance and Standard Deviation of a Discrete Variable......Page 193
 5.2 Binomial Distribution......Page 196
 5.3 Poisson Distribution......Page 203
 5.4 Covariance of a Probability Distribution and its Application in Finance......Page 206
 Key Equations......Page 207
 Chapter Review Problems......Page 208
  Managing Ashland MultiComm Services......Page 210
  Digital Case......Page 211
  EG5.2 Binomial Distribution......Page 212
  EG5.3 Poisson Distribution......Page 213
 Using Statistics: Normal Load Times at MyTVLab......Page 214
 6.1 Continuous Probability Distributions......Page 215
  EXHIBIT: Normal Distribution Important Theoretical Properties......Page 216
  Computing Normal Probabilities......Page 217
  Finding X Values......Page 223
 Consider This: What Is Normal?......Page 227
 6.3 Evaluating Normality......Page 228
  Comparing Data Characteristics to Theoretical Properties......Page 229
  Constructing the Normal Probability Plot......Page 230
 6.4 The Uniform Distribution......Page 232
 6.6 The Normal Approximation to the Binomial Distribution......Page 234
 References......Page 235
 Chapter Review Problems......Page 236
  Digital Case......Page 238
  EG6.3 Evaluating Normality......Page 239
 Using Statistics: Sampling Oxford Cereals......Page 241
  The Unbiased Property of the Sample Mean......Page 242
  Standard Error of the Mean......Page 244
  Sampling from Normally Distributed Populations......Page 245
  Sampling from Non-normally Distributed Populations— The Central Limit Theorem......Page 248
  EXHIBIT: Normality and the Sampling Distribution of the Mean......Page 249
  VISUAL EXPLORATIONS: Exploring Sampling Distributions......Page 252
 7.3 Sampling Distribution of the Proportion......Page 253
 Using Statistics: Sampling Oxford Cereals, Revisited......Page 256
 Key Terms......Page 257
 Chapter Review Problems......Page 258
  Digital Case......Page 260
  EG7.2 Sampling Distribution of the Mean......Page 261
 Using Statistics: Getting Estimates at Ricknel Home Centers......Page 262
 8.1 Confidence Interval Estimate for the Mean (σ Known)......Page 263
  Can You Ever Know the Population Standard Deviation?......Page 268
  Student’s t Distribution......Page 269
  Properties of the t Distribution......Page 270
  The Concept of Degrees of Freedom......Page 271
  The Confidence Interval Statement......Page 272
 8.3 Confidence Interval Estimate for the Proportion......Page 277
  Sample Size Determination for the Mean......Page 280
  Sample Size Determination for the Proportion......Page 282
 8.5 Confidence Interval Estimation and Ethical Issues......Page 285
 Using Statistics: Getting Estimates. . ., Revisited......Page 286
 Key Equations......Page 287
 Chapter Review Problems......Page 288
  Managing Ashland MultiComm Services......Page 291
  Clear Mountain State Student Survey......Page 292
  EG8.2 Confidence Interval Estimate for the Mean (σ Unknown)......Page 293
  EG8.4 Determining Sample Size......Page 294
 Using Statistics: Significant Testing at Oxford Cereals......Page 295
  The Null and Alternative Hypotheses......Page 296
  The Critical Value of the Test Statistic......Page 297
  Risks in Decision Making Using Hypothesis Testing......Page 298
  Hypothesis Testing Using the Critical Value Approach......Page 301
  EXHIBIT: The Critical Value Approach to Hypothesis Testing......Page 302
  Hypothesis Testing Using the p-Value Approach......Page 304
  EXHIBIT: The p-Value Approach to Hypothesis Testing......Page 305
  A Connection Between Confidence Interval Estimation and Hypothesis Testing......Page 306
  Can You Ever Know the Population Standard Deviation?......Page 307
  The Critical Value Approach......Page 309
  Checking the Normality Assumption......Page 311
  The Critical Value Approach......Page 315
  The p-Value Approach......Page 316
  EXHIBIT: The Null and Alternative Hypotheses in One-Tail Tests......Page 318
 9.4 Z Test of Hypothesis for the Proportion......Page 319
  The Critical Value Approach......Page 320
  The p-Value Approach......Page 321
  EXHIBIT: Questions for the Planning Stage of Hypothesis Testing......Page 323
  Ethical Issues......Page 324
 Summary......Page 325
 Checking Your Understanding......Page 326
 Chapter Review Problems......Page 327
  Sure Value Convenience Stores......Page 329
  EG9.2 t Test of Hypothesis for the Mean (σ Unknown)......Page 330
  EG9.4 Z Test of Hypothesis for the Proportion......Page 331
 Using Statistics: Differing Means for Selling Streaming Media Players at Arlingtons?......Page 332
  Pooled-Variance t Test for the Difference Between Two Means......Page 333
  Confidence Interval Estimate for the Difference Between Two Means......Page 338
  t Test for the Difference Between Two Means, Assuming Unequal Variances......Page 339
 Consider This: Do People Really Do This?......Page 340
 10.2 Comparing the Means of Two Related Populations......Page 342
  Paired t Test......Page 343
  Confidence Interval Estimate for the Mean Difference......Page 348
 10.3 Comparing the Proportions of Two Independent Populations......Page 350
  Z Test for the Difference Between Two Proportions......Page 351
  Confidence Interval Estimate for the Difference Between Two Proportions......Page 355
 10.4 F Test for the Ratio of Two Variances......Page 357
 10.5 Effect Size......Page 361
 Summary......Page 362
 Key Equations......Page 363
 Chapter Review Problems......Page 364
  Managing Ashland MultiComm Services......Page 366
  Clear Mountain State Student Survey......Page 367
  EG10.1 Comparing The Means of Two Independent Populations......Page 368
  EG10.2 Comparing the Means of Two Related Populations......Page 370
  EG10.3 Comparing the Proportions of Two Independent Populations......Page 371
  EG10.4 F Test for the Ratio of Two Variances......Page 372
 Using Statistics: The Means to Find Differences at Arlingtons......Page 373
 11.1 The Completely Randomized Design: One-Way ANOVA......Page 374
  Analyzing Variation in One-Way ANOVA......Page 375
  F Test for Differences Among More Than Two Means......Page 377
  One-Way ANOVA F Test Assumptions......Page 381
  Levene Test for Homogeneity of Variance......Page 382
  Multiple Comparisons: The Tukey-Kramer Procedure......Page 383
  The Analysis of Means (ANOM)......Page 385
 11.2 The Factorial Design: Two-Way ANOVA......Page 388
  Factor and Interaction Effects......Page 389
  Testing for Factor and Interaction Effects......Page 391
  Multiple Comparisons: The Tukey Procedure......Page 394
  Interpreting Interaction Effects......Page 396
 Using Statistics: The Means to Find Differences at Arlingtons Revisited......Page 400
 Key Equations......Page 401
 Key Terms......Page 402
 Chapter Review Problems......Page 403
  PHASE 2......Page 405
  Clear Mountain State Student Survey......Page 406
  EG11.1 The Completely Randomized Design: One-Way ANOVA......Page 407
  EG11.2 The Factorial Design: Two-Way ANOVA......Page 409
 Using Statistics: Avoiding Guesswork about Resort Guests......Page 411
 12.1 Chi-Square Test for the Difference Between Two Proportions......Page 412
 12.2 Chi-Square Test for Differences Among More Than Two Proportions......Page 419
  The Marascuilo Procedure......Page 422
  The Analysis of Proportions (ANOP)......Page 424
 12.3 Chi-Square Test of Independence......Page 425
 12.4 Wilcoxon Rank Sum Test: A Nonparametric Method for Two Independent Populations......Page 431
 12.5 Kruskal-Wallis Rank Test: A Nonparametric Method for the One-Way ANOVA......Page 437
  Assumptions......Page 440
 12.7 Chi-Square Test for the Variance or Standard Deviation......Page 442
 Summary......Page 443
 Key Equations......Page 444
 Chapter Review Problems......Page 445
  PHASE 2......Page 447
  Clear Mountain State Student Survey......Page 448
  EG12.2 Chi-Square Test for Differences Among More Than Two Proportions......Page 449
  EG12.4 Wilcoxon Rank Sum Test: a Nonparametric Method for Two Independent Populations......Page 450
  EG12.5 Kruskal-Wallis Rank Test: a Nonparametric Method for the One-Way ANOVA......Page 451
 Using Statistics: Knowing Customers at Sunflowers Apparel......Page 452
 13.1 Types of Regression Models......Page 453
  Simple Linear Regression Models......Page 454
  The Least-Squares Method......Page 455
  Computing the Y Intercept, b0 and the Slope, b₁......Page 458
  VISUAL EXPLORATIONS: Exploring Simple Linear Regression Coefficients......Page 461
  Computing the Sum of Squares......Page 463
  The Coefficient of Determination......Page 464
  Standard Error of the Estimate......Page 466
  Evaluating the Assumptions......Page 468
  Residual Plots to Detect Autocorrelation......Page 472
  The Durbin-Watson Statistic......Page 473
  t Test for the Slope......Page 476
  F Test for the Slope......Page 478
  Confidence Interval Estimate for the Slope......Page 479
  t Test for the Correlation Coefficient......Page 480
  The Confidence Interval Estimate for the Mean Response......Page 483
  The Prediction Interval for an Individual Response......Page 484
  EXHIBIT: Six Steps for Avoiding the Potential Pitfalls......Page 487
 Summary......Page 489
 References......Page 490
 Key Equations......Page 491
 Chapter Review Problems......Page 492
  Brynne Packaging......Page 496
  EG13.2 Determining the Simple Linear Regression Equation......Page 497
  EG13.5 Residual Analysis......Page 498
  EG13.8 Estimation of Mean Values and Prediction of Individual Values......Page 499
 Using Statistics: The Multiple Effects of OmniPower Bars......Page 500
  Interpreting the Regression Coefficients......Page 501
  Predicting the Dependent Variable Y......Page 504
  Adjusted r²......Page 506
  Test for the Significance of the Overall Multiple Regression Model......Page 507
 14.3 Residual Analysis for the Multiple Regression Model......Page 509
  Tests of Hypothesis......Page 511
  Confidence Interval Estimation......Page 512
 14.5 Testing Portions of the Multiple Regression Model......Page 514
  Coefficients of Partial Determination......Page 518
 14.6 Using Dummy Variables and Interaction Terms in Regression Models......Page 520
  Interactions......Page 522
 14.7 Logistic Regression......Page 529
 Summary......Page 534
 Key Equations......Page 536
 Chapter Review Problems......Page 537
  Digital Case......Page 540
  EG14.1 Developing a Multiple Regression Model......Page 542
  EG14.3 Residual Analysis for the Multiple Regression Model......Page 543
  EG14.6 Using Dummy Variables and Interaction Terms in Regression Models......Page 544
  EG14.7 Logistic Regression......Page 545
 Using Statistics: Valuing Parsimony at WSTA-TV......Page 546
  Finding the Regression Coefficients and Predicting Y......Page 547
  Testing the Quadratic Effect......Page 550
  The Coefficient of Multiple Determination......Page 552
  The Square-Root Transformation......Page 554
  The Log Transformation......Page 556
 15.3 Collinearity......Page 559
 15.4 Model Building......Page 560
  The Stepwise Regression Approach to Model Building......Page 562
  The Best Subsets Approach to Model Building......Page 563
  Model Validation......Page 566
  EXHIBIT: Steps for Successful Model Building......Page 567
 Using Statistics: Valuing Parsimony…, Revisited......Page 569
 Summary......Page 570
 Chapter Review Problems......Page 571
  The Mountain States Potato Company......Page 573
  The Craybill Instrumentation Company Case......Page 574
  More Descriptive Choices Follow-Up......Page 575
  Eg15.2 Using Transformations In Regression Models......Page 576
  Eg15.4 Model Building......Page 577
 Using Statistics: Principled Forecasting......Page 578
 16.2 Component Factors of Time-Series Models......Page 579
 16.3 Smoothing an Annual Time Series......Page 580
  Moving Averages......Page 581
  Exponential Smoothing......Page 583
  The Linear Trend Model......Page 586
  The Quadratic Trend Model......Page 588
  The Exponential Trend Model......Page 589
  Model Selection Using First, Second, and Percentage Differences......Page 591
 16.5 Autoregressive Modeling for Trend Fitting and Forecasting......Page 596
  Selecting an Appropriate Autoregressive Model......Page 597
  Determining the Appropriateness of a Selected Model......Page 598
  EXHIBIT: Autoregressive Modeling Steps......Page 600
  Performing a Residual Analysis......Page 605
  A Comparison of Four Forecasting Methods......Page 606
 16.7 Time-Series Forecasting of Seasonal Data......Page 608
  Least-Squares Forecasting with Monthly or Quarterly Data......Page 609
 Using Statistics: Principled Forecasting, Revisited......Page 614
 Summary......Page 615
 Key Equations......Page 616
 Chapter Review Problems......Page 617
  Digital Case......Page 618
  Eg16.3 Smoothing an Annual Time Series......Page 619
  Eg16.4 Least-Squares Trend Fitting and Forecasting......Page 620
  Eg16.6 Choosing an Appropriate Forecasting Model......Page 621
  Eg16.7 Time-Series Forecasting of Seasonal Data......Page 622
 Using Statistics: Mounting Future Analyses......Page 623
  Reach Conclusions about the Population Mean or the Standard Deviation?......Page 624
  Determine Which Factors Affect the Value of a Variable?......Page 625
  Reach Conclusions about the Proportion of Items of Interest?......Page 626
 Using Statistics: Back to Arlingtons for the Future......Page 627
  Power Pivot......Page 628
 17.4 Descriptive Analytics......Page 629
  Dashboard Elements......Page 630
 17.5 Predictive Analytics......Page 631
  Classification and Regression Trees......Page 632
 Chapter Review Problems......Page 633
  EG17.4 Descriptive Analytics......Page 636
 Using Statistics: Finding Quality at the Beachcomber......Page 638
 18.1 The Theory of Control Charts......Page 639
 18.2 Control Chart for the Proportion: The p Chart......Page 641
 18.3 The Red Bead Experiment: Understanding Process Variability......Page 647
 18.4 Control Chart for an Area of Opportunity: The c Chart......Page 649
 18.5 Control Charts for the Range and the Mean......Page 652
  The R Chart......Page 653
  The X Chart......Page 655
  Customer Satisfaction and Specification Limits......Page 658
  Capability Indices......Page 660
  CPL, CPU, and Cpk......Page 661
 18.7 Total Quality Management......Page 663
 18.8 Six Sigma......Page 665
  The DMAIC Model......Page 666
  Lean Six Sigma......Page 667
 Summary......Page 668
 Key Equations......Page 669
 Key Terms......Page 670
 Chapter Review Problems......Page 671
  The Harnswell Sewing Machine Company Case......Page 672
  Managing Ashland Multicomm Services......Page 675
  EG18.2 Control Chart for the Proportion: The p Chart......Page 676
  EG18.4 Control Chart for an Area of Opportunity: The c Chart......Page 677
  EG18.5 Control Charts for the Range and the Mean......Page 678
  EG18.6 Process Capability......Page 679
 Using Statistics: Reliable Decision Making......Page 680
 19.1 Payoff Tables and Decision Trees......Page 681
  Maximax Payoff......Page 685
  Expected Monetary Value......Page 686
  Expected Opportunity Loss......Page 688
  Return-to-Risk Ratio......Page 690
 19.3 Decision Making with Sample Information......Page 695
 19.4 Utility......Page 700
 Using Statistics: Reliable Decision-Making, Revisited......Page 701
 Chapter Review Problems......Page 702
  Digital Case......Page 705
  EG19.2 Criteria for Decision Making......Page 706
Appendices......Page 708
  A.2 Rules for Algebra: Exponents and Square Roots......Page 709
  A.3 Rules for Logarithms......Page 710
  A.4 Summation Notation......Page 711
  A.6 Greek Alphabet......Page 714
  B.1 Which Excel Do You Use?......Page 715
  B.3 Formulas and Cell References......Page 716
  B.4 Entering a Formula......Page 718
  B.5 Formatting Cell Contents......Page 719
  B.6 Formatting Charts......Page 720
  B.7 Selecting Cell Ranges for Charts......Page 721
  B.9 Creating Histograms for Discrete Probability Distributions......Page 722
  C.3 Details of Online Resources......Page 723
  C.4 PHStat......Page 730
  D.3 Configuring Microsoft Windows Excel Security Settings......Page 731
  D.4 Opening Pearson-Supplied Add-Ins......Page 732
  E.1 Table of Random Numbers......Page 733
  E.2 The Cumulative Standardized Normal Distribution......Page 735
  E.3 Critical Values of t......Page 737
  E.4 Critical Values of X²......Page 739
  E.5 Critical Values of F......Page 740
  E.6 Lower and Upper Critical Values, T₁, of the Wilcoxon Rank Sum Test......Page 744
  E.7 Critical Values of the Studentized Range, Q......Page 745
  E.8 Critical Values, dL and dU, of the Durbin–Watson Statistic, D (Critical Values Are One-Sided)......Page 747
  E.9 Control Chart Factors......Page 748
  E.10 The Standardized Normal Distribution......Page 749
  F.3 New Function Names......Page 750
  F.4 Understanding the Nonstatistical Functions......Page 752
  G.2 Microsoft Excel FAQs......Page 754
Self-Test Solutions and Answers to Selected Even-Numbered Problems......Page 756
 C......Page 785
 K......Page 786
 M......Page 787
 P......Page 788
 S......Page 789
 V......Page 790
 Z......Page 791
Credits......Page 792