Business Statistics for Competitive Advantage with Excel 2010: Basics, Model Building, and Cases

469 Pages·2012·27.61 MB·English

by  Cynthia Fraser (auth.)

B usiness Statistics for Competitive Advantage with Excel 2010

Cynthia Fraser
Business Statistics for Competitive
Advantage with Excel 2010
Basics, Model Building, and Cases
Second Edition
Cynthia Fraser
University of Virginia
Charlottesville, VA, USA
[email protected]
ISBN 978-1-4419-9856-9 e-ISBN 978-1-4419-9857-6
DOI 10.1007/978-1-4419-9857-6
SpringerNew York Dordrecht Heidelberg London
Library of Congress Control Number: 2011938464
© Springer Science+Business Media, LLC 2012
All rights reserved. This work may not be translated or copied in whole or in part without the written permission of the publisher (Springer
Science+Business Media, LLC, 233 Spring Street, New York, NY 10013, USA), except for brief excerpts in connection with reviews or scholarly
analysis. Use in connection with any form of information storage and retrieval, electronic adaptation, computer software, or by similar or
dissimiliar methodology now known or hereafter developed is forbidden.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be
taken as an expression of opinion as to whether or not they are subject to proprietary rights.
Printed on acid-free paper
Springer is part of Springer Science+Business Media (www.springer.com)
To Len Lodish who taught me to include decision makers in the model
building process

Contents
Preface...........................................................................................................................................xv
Chapter 1 Statistics for Decision Making and Competitive Advantage................................1
1.1 Statistical Competences Translate into Competitive Advantages .........................................1
1.2 The Path toward Statistical Competence and Competitive Advantage .................................2
1.3 Use Excel for Competitive Advantage ..................................................................................2
14 StatisticalCompetenceIsPowerfulandYours .....................................................................3
Chapter 2 Describing Your Data..............................................................................................5
2.1 Describe Data with Summary Statistics and Histograms ......................................................5
Example 2.1 Yankees’ Salaries: Is It a Winning Offer? .......................................................5
2.2 Outliers Can Distort the Picture ............................................................................................8
Example 2.2 Executive Compensation: Is the Board’s Offer on Target? .............................8
2.3 Round Descriptive Statistics ...............................................................................................11
2.4 Share the Story that Your Graphics Illustrate .....................................................................11
2.5 Central Tendency, Dispersion, and Skewness Describe Data .............................................11
2.6 Data Are Measured with Quantitative or Categorical Scales ..............................................11
2.7 Continuous Data Are Sometimes Normal ..........................................................................13
Example 2.3 Normal SAT Scores ........................................................................................13
2.8 The Empirical Rule Simplifies Description ........................................................................13
’
Example 2.4 Class of 10 SATs: This Class Is Normal and Exceptional ............................14
2.9 Describe Categorical Variables Graphically: Column and PivotCharts ............................15
Example 2.5 Who Is Honest and Ethical? ..........................................................................15
2.10 Descriptive Statistics Depend on the Data and Your Packaging ........................................16
Excel 2.1 Produce Descriptive Statistics and Histograms ..................................................................17
Executive Compensation .....................................................................................................17
Excel 2.2 Sort to Produce Descriptives Without Outliers ...................................................................21
Excel 2.3 Plot a Cumulative Distribution ...........................................................................................23
Excel 2.4 Find and View Distribution Percentages with a PivotChart ...............................................25
Class of ’10 Math SATs ......................................................................................................25
Excel 2.5 Produce a Column Chart of a Nominal Variable ................................................................29
Excel Shortcuts at Your Fingertips by Shortcut Key ..........................................................33
Significant Digits Guidelines ..............................................................................................39
Lab 2 Descriptive Statistics ................................................................................................41
A Typical Executive’s Compensation ................................................................................41
Hollywood Politics .............................................................................................................42
Assignment 2-1 Procter & Gamble’s Global Advertising ..................................................43
Assignment 2-2 Best Practices Survey ...............................................................................43
vii
viii Contents
Assignment 2-3 Shortcut Challenge ...................................................................................44
CASE 2-1 VW Backgrounds ..............................................................................................44
Chapter 3 Hypothesis Tests, Confidence Intervals, and Simulation to Infer
Population Characteristics and Differences .......................................................45
3.1 Sample Means Are Random Variables ...............................................................................45
Example 3.1 Thirsty on Campus: Is There Sufficient Demand? .........................................45
3.2 Infer Whether a Population Mean Exceeds a Target ..........................................................50
3.3 Confidence Intervals Estimate the Population Mean ..........................................................52
3.4 Calculate Approximate Confidence Intervals with Mental Math .......................................54
3.5 Margin of Error Is Inversely Proportional To Sample Size ................................................55
3.6 Samples Are Efficient .........................................................................................................56
3.7 Use Monte Carlo Simulation Samples to Incorporate Uncertainty
and Quantify Implications of Assumptions ........................................................................57
3.8 Determine Whether Two Segments Differ with Student t .................................................60
Example 3.2 SmartScribe: Is Income a Useful Base for Segmentation? ............................60
3.9 Estimate the Extent of Difference Between Two Segments ...............................................62
3.10 Confidence Intervals Complement Hypothesis Tests .........................................................64
3.11 Estimate a Population Proportion from a Sample Proportion .............................................64
Example 3.3 Guinea Pigs ....................................................................................................64
3.12 Conditions for Assuming Approximate Normality .............................................................66
3.13 Conservative Confidence Intervals for a Proportion ...........................................................66
3.14 Assess the Difference Between Alternate Scenarios or Pairs .............................................68
Example 3.4 Are “Socially Desirable” Portfolios Undesirable? .......................................69
3.15 Inference from Sample to Population .................................................................................72
Excel 3.1 Test the Level of a Population Mean with a One Sample t Test .........................................74
Thirsty on Campus ..............................................................................................................74
Excel 3.2 Make a Confidence Interval for a Population Mean ...........................................................75
Excel 3.3 Illustrate Confidence Intervals with Column Charts ...........................................................76
T-Mobile’s Service .............................................................................................................76
Excel 3.4 Conduct a Monte Carlo Simulation ....................................................................................80
Excel 3.5 Test the Difference Between Two Segment Means with a Two Sample t Test ..................86
Pampers Preemies ...............................................................................................................86
Excel 3.6 Construct a Confidence Interval for the Difference Between Two Segments ....................87
Excel 3.7 Illustrate the Difference Between Two Segment Means with a Column Chart ..................88
Excel 3.8 Construct a Pie Chart of Shares ..........................................................................................90
Moral Acceptance of Medical Testing on Animals ............................................................90
Excel 3.9 Test the Difference in Between Alternate Scenarios or Pairs with a Paired t Test..............93
Difference Between Conventional and Socially Desirable Portfolio Ratings.....................93
Excel 3.10 Construct a Confidence Interval for the Difference Between Alternate
Scenarios or Pairs.................................................................................................................94
Lab Practice 3 Inference......................................................................................................97
Cingular’s Position in the Cell Phone Service Market........................................................97
Contents i x
Value of a Nationals Uniform..............................................................................................97
Extra Value of a Phillies Uniform.......................................................................................98
Confidence in Chinese Imports ...........................................................................................98
Lab 3 Inference: Dell PDA Plans.........................................................................................99
Assignment 3-1The Marriott Difference............................................................................101
Assignment 3-2 Bottled Water Possibilities......................................................................101
Assignment 3-3 Immigration in the United States.............................................................102
Assignment 3-4 McLattes..................................................................................................103
Assignment 3-5 A Barbie Duff in Stuff.............................................................................103
CASE 3-1 Yankees Versus Marlins: The Value of a Yankee Uniform.............................103
CASE 3-2 Gender Pay.......................................................................................................104
CASE 3-3 Polaski Vodka: Can a Polish Vodka Stand Up to the Russians?......................105
CASE 3-4 American Girl in Starbucks..............................................................................107
Chapter 4 Quantifying the Influence of Performance Drivers
and Forecasting: Regression...............................................................................109
4.1 The Simple Linear Regression Equation Describes the Line...................................................
Relating a Decision Variable to Performance....................................................................109
Example 4.1 HitFlix Movie Rentals...................................................................................110
4.2 F Tests Significance of the Hypothesized Linear Relationship R Square
Summarizes Its Strength and Standard Error Reflects Forecasting Precision...................111
4.3 Test and Infer the Slope.....................................................................................................116
4.4 Analyze Residuals to Learn Whether Assumptions Are Met............................................118
4.5 Prediction Intervals Estimate Average Response..............................................................120
4.6 Use Sensitivity Analysis to Explore Alternative Scenarios...............................................121
4.7 Explanation and Prediction Create a Complete Picture.....................................................122
4.8 Present Regression Results in Concise Format..................................................................123
4.9 Assumptions We Make When We Use Linear Regression................................................123
4.10 Correlation Reflects Linear Association............................................................................124
Example 4.2 HitFlix Movie Rentals...................................................................................124
4.11 Correlation Coefficients Are Key Components of Regression Slopes..............................128
Example 4.3 Pampers........................................................................................................129
4.12 Correlation Complements Regression...............................................................................131
4.13 Linear Regression Is Doubly Useful..................................................................................132
Excel 4.1 Build a Simple Linear Regression Model: Impact of Titles Offered on HitFlix
Movie Rental Revenues.....................................................................................................133
Excel 4.2 Construct Prediction Intervals............................................................................................135
Excel 4.3 Find Correlations Between Variable Pairs.........................................................................142
Lab Practice 4 Oil Price Forecast.......................................................................................145
Lab 4 Simple Regression Dell Slimmer PDA....................................................................147
CASE 4-1 GenderPay (B) .................................................................................................149
CASE 4-2 GM Revenue Forecast......................................................................................149
Assignment 4-1 Impact of Defense Spending on Economic Growth................................151
x Contents
Chapter 5 Market Simulation and Segmentation with Descriptive Statistics,
Inference, Hypothesis Tests, and Regression.....................................................153
5.1 CASE 5-1 Simulation and Segmentation of the Market for Preemie Diapers...................153
5.2 Use PowerPoints to Present Statistical Results for Competitive Advantage.....................164
5.3 Write Memos That Encourage Your Audience to Read and Use Results..........................171
MEMO Re: Importance of Fit Drives Trial Intention........................................................173
Chapter 6 Finance Application: Portfolio Analysis with a Market Index
as a Leading Indicator in Simple Linear Regression........................................175
6.1 Rates of Return Reflect Expected Growth of Stock Prices................................................175
Example 6.1 General Electric and Apple Returns.............................................................175
6.2 Investors Trade Off Risk and Return.................................................................................177
6.3 Beta Measures Risk...........................................................................................................177
6.4 A Portfolio Expected Return, Risk, and Beta Are Weighted Averages
of Individual Stocks...........................................................................................................180
Example 6.2 Three Alternate Portfolios............................................................................181
6.5 Better Portfolios Define The Efficient Frontier.................................................................182
MEMO Re: Recommended Portfolios are Diversified......................................................184
6.6 Portfolio Risk Depends on Correlations with the Market and Stock Variability...............185
Excel 6.1 Estimate Portfolio Expected Rate of Return and Risk.......................................................186
Three Portfolios with Exxon Mobil, IBM, and Apple.......................................................186
Correlations between stocks and the market .....................................................................186
Excel 6.2 Plot Return by Risk to Identify Dominant Portfolios and the Efficient Frontier...............188
Assignment 6-1 Individual Stocks’ Beta Estimates...........................................................192
Assignment 6-2 Expected Returns and Beta Estimates of Alternate Portfolios................192
Chapter 7 Association between Two Categorical Variables: Contingency Analysis
with Chi Square....................................................................................................193
7.1 Evidence of Association when Conditional Probabilities Differ from Joint
Probabilities.......................................................................................................................193
Example 7.1 Recruiting Stars............................................................................................194
7.2 Chi Square Tests Association Between Two Categorical Variables.................................195
7.3 Chi Square Is Unreliable If Cell Counts Are Sparse..........................................................197
7.4 Simpson’s Paradox Can Mislead.......................................................................................199
Example 7.2 American Cars..............................................................................................199
MEMO Re.: Country of Assembly Does Not Affect Older Buyers’ Choices...................204
7.5 Contingency Analysis Is Demanding.................................................................................205
7.6 Contingency Analysis Is Quick, Easy, and Readily Understood.......................................205
Excel 7.1 Construct Crosstabulations and Assess Association Between Categorical Variables
with PivotTables and PivotCharts......................................................................................206
American Cars...................................................................................................................206