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

418 Pages·2009·21 MB·English

by  Cynthia Fraser (auth.)

Business Statistics for Competitive Advantage
with Excel 2007
Business Statistics
for Competitive Advantage
with Excel 2007
Basics, Model Building,
and Cases
Cynthia Fraser
University of Virginia, McIntire School of Commerce
Cynthia Fraser
University of Virginia
Charlottesville, VA, USA
ISBN: 978-0-387-74402-4 e-ISBN: 978-0-387-74403-2
DOI: 10.1007/978-0-387-74403-2
Library of Congress Control Number: 2008939440
© Springer Science+Business Media, LLC 2009
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 dissimilar 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.
While th e advice and information in this book are belived to be true and accurate at the date of going to press, neither
the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may
be made. The publisher makes no warranty, express or implied, with respect to the material contained herein.

Printed on acid-free paper
springer.com
To Len Lodish, who introduced me to the competitive advantages
of modeling.
Contents

Preface xvii
Chapter 1 Statistics for Decision Making and Competitive
Advantage 1
1.1 Statistical Competences Translate Into Competitive Advantages 1
1.2 Attain Statistical Competences And Competitive Advantage
With This Text 1
1.3 Follow The Path Toward Statistical Competence and Competitive
Advantage 2
1.4 Use Excel for Competitive Advantage 3
1.5 Statistical Competence Is Satisfying 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 7
Example 2.2 Executive Compensation: Is the Board’s Offer
on Target? 7
2.3 Round Descriptive Statistics 10
2.4 Central Tendency and Dispersion Describe Data 11
2.5 Data Is Measured With Quantitative or Categorical Scales 11
2.6 Continuous Data Tend To Be Normal 12
Example 2.3 Normal SAT Scores 12
2.7 The Empirical Rule Simplifies Description 13
Example 2.4 Class of ’06 SATs: This Class is Normal
& Exceptional 13
2.8 Describe Categorical Variables Graphically: Column
and PivotCharts 15
Example 2.5 Who Is Honest & Ethical? 15
2.9 Descriptive Statistics Depend On The Data 16
Excel 2.1 Produce descriptive statistics and view distributions
with histograms 17
Excel 2.2 Sort to produce descriptives without outliers 20
Excel 2.3 Plot a cumulative distribution 23
viii Contents
Excel 2.4 Find and view distribution percentages with a PivotTable
and PivotChart 24
Excel 2.5 Produce a column chart from a PivotChart of a nominal variable 27
Excel Shortcuts at Your Fingertips 29
Lab 2 Descriptive Statistics 31
Assignment 2-1 Procter & Gamble’s Global Advertising 33
CASE 2-1 VW Backgrounds 34
Chapter 3 Hypothesis Tests, Confidence Intervals and Simulation
to Infer Population Characteristics and Differences 35
3.1 Sample Means Are Random Variables 35
Example 3.1 Thirsty on Campus: Is there Sufficient Demand? 35
3.2 Use Sample Data to Determine Whether Or Not µ Is Likely
To Exceed A Target 38
3.3 Confidence Intervals Estimate the Population Mean From A Sample 41
3.4 Round t to Calculate Approximate 95% Confidence Intervals
With Mental Math 43
3.5 Margin of Error Is Inversely Proportional To Sample Size 43
3.6 Samples Are Efficient 44
3.7 Use Monte Carlo Simulation with Sample Statistics To Incorporate
Uncertainty and Quantify Implications Of Assumptions 44
3.8 Determine Whether There Is a Difference Between Two Segments
With Student t 48
Example 3.2 Pampers Preemies: Is Income a Useful Base
for Segmentation? 48
3.9 Estimate the Extent of Difference between Two Segments
With Student t 49
3.10 Confidence Intervals Complement Hypothesis Tests 50
3.11 Estimation of a Population Proportion from a Sample Proportion 50
Example 3.3 Guinea Pigs 50
3.12 Conditions for Assuming Approximate Normality to Make
Confidence Intervals for Proportions 53
3.13 Conservative Confidence Intervals for a Proportion 53
3.14 Assess the Difference between Alternate Scenarios or Pairs
With Student t 54
Example 3.4 Are “Socially Desirable” Portfolios Undesirable? 55
3.15 Inference from Sample to Population 58
Excel 3.1 Test the level of a population mean with a one sample t test 59
Excel 3.2 Make a confidence interval for a population mean 60
Contents ix
Excel 3.3 Illustrate population confidence intervals with a clustered
column chart 61
Excel 3.4 Conduct a Monte Carlo simulation with Crystal Ball 65
Excel 3.5 Test the difference between two segments with a two sample t test 69
Excel 3.6 Construct a confidence interval for the difference between
two segments 70
Excel 3.7 Illustrate the difference between two segment means
with a column chart 71
Excel 3.8 Construct a pie chart of shares 72
Excel 3.9 Test the difference in levels between alternate scenarios
or pairs with a paired t test 74
Excel 3.10 Construct a confidence interval for the difference between
alternate scenarios or pairs 76
Excel Shortcuts at Your Fingertips 78
Lab Practice 3 Inference 80
Lab 3 Inference 82
Assignment 3-1 Bottled Water Possibilities 83
Assignment 3-2 Immigration in the U.S. 84
Assignment 3-3 McLattes 84
Assignment 3-4 A Barbie Duff in Stuff 85
CASE 3-1 Yankees v Marlins: The Value of a Yankee Uniform 85
CASE 3-2 Gender Pay 86
CASE 3-3 Polaski Vodka: Can a Polish Vodka Stand Up
to the Russians? 86
CASE 3-4 American Girl in Starbucks 88
Chapter 4 Quantifying the Influence of Performance Drivers
and Forecasting: Regression 91
4.1 The Simple Linear Regression Equation Describes the Line Relating
A Decision Variable to Performance 91
Example 4.1 HitFlix Movie Rentals 92
4.2 F Tests the Significance of the Hypothesized Linear Relationship,
RSquare Summarizes Its Strength and Standard Error Reflects
Forecasting Precision 93
4.3 The Population Slope Is Tested And Inferred From Our Sample 96
4.4 Analyze Residuals To Learn Whether Assumptions Have Been Met 98
4.5 95% Prediction Intervals Acknowledge That Individual
Elements Differ 99
4.6 Use Sensitivity Analysis to Explore Alternative Scenarios 101
x Contents
4.7 95% Conditional Mean Prediction Intervals Of Average
Performance Gauge Average Performance Response To A Driver 101
4.8 Explanation And Prediction Create A Complete Picture 102
4.9 Present Regression Results In Concise Format 103
4.10 We Make Assumptions When We Use Linear Regression 104
4.11 Correlation Is A Standardized Covariance 105
Example 4.2 HitFlix Movie Rentals 105
4.12 Correlation Coefficients Are Key Components Of Regression
Slopes 109
Example 4.3 Pampers 110
4.13 Correlation Summarizes Linear Association 113
4.14 Linear Regression Is Doubly Useful 113
Excel 4.1 Fit a simple linear regression model 114
Excel 4.2 Construct prediction and conditional mean prediction intervals 118
Excel 4.3 Find correlations between variable pairs 124
Excel Shortcuts at Your Fingertips 126
Lab 4 Regression 128
CASE 4-1 GenderPay (B) 130
CASE 4-2 GM Revenue Forecast 131
Assignment 4-1 Impact of Defense Spending on Economic Growth 133
Chapter 5 Marketing Segmentation with Descriptive Statistics,
Inference, Hypothesis Tests and Regression 135
CASE 5-1 Segmentation of the Market for Preemie Diapers 135
5.1 Guide to Effective PowerPoint Presentations and Writing
Memos that your Audience will Read 145
5.2 Write Memos that Encourage Your Audience to Read
and Use Results 147
MEMO Re: Importance of Fit Drives Trial Intention 148
Chapter 6 Finance Application: Portfolio Analysis
with a Market Index as a Leading Indicator
in Simple Linear Regression 149
6.1 Rates of Return Reflect Expected Growth of Stock Prices 149
Example 6.1 Goldman Sachs and Yahoo Returns 149
6.2 Investors Trade Off Risk And Return 152
6.3 Beta Measures Risk 152
Example 6.2 Four diverse stocks 153
Contents xi
6.4 A Portfolio’s Expected Return, Risk and Beta Are Weighted
Averages of Individual Stocks 158
Example 6.3 Four Alternate Portfolios 158
6.5 Better Portfolios Define The Efficient Frontier 161
MEMO Re: Recommended Portfolios Include Lockheed
Martin and Apple 162
6.6 Portfolio Risk Depends On the Covariances between Individual
Stocks’ Rates of Return and The Market Rate Of Return 163
Excel 6.1 Estimate portfolio expected rate of return and risk 164
Excel 6.2 Plot return by risk to identify dominant portfolios and the Efficient
Frontier 166
Assignment 6-1 Individual Stocks’ Beta Estimates 169
Assignment 6-2 Expected Returns and Beta Estimates of Alternate
Portfolios 169
Assignment 6-3 Portfolio Comparison 170
Chapter 7 Association between Two Categorical
Variables: Contingency Analysis with Chi Square 171
7.1 When Conditional Probabilities Differ From Joint Probabilities,
There Is Evidence of Association 171
Example 7.1 Recruiting Stars 172
7.2 Chi Square Tests Association between Two Categorical Variables 174
7.3 Chi Square Is Unreliable If Cell Counts Are Sparse 175
7.4 Simpson’s Paradox Can Mislead 177
Example 7.2 American Cars 177
MEMO Re: Country of Manufacture Does Not Affect Older
Buyers’ Choices 183
7.5 Contingency Analysis Is Demanding 184
7.6 Contingency Analysis Is Quick, Easy, and Readily Understood 184
Excel 7.1 Construct crosstabulations and assess association between
categorical variables with PivotTables and PivotCharts 185
Excel 7.2 Use chi square to test association 187
Excel 7.3 Conduct contingency analysis with summary data 190
Excel Shortcuts at Your Fingertips 193
Assignment 7-1 747s and Jets 195
Assignment 7-2 Fit Matters 195
Assignment 7-3 Allied Airlines 196
CASE 7-1 Hybrids for American Car 197
CASE 7-2 Tony’s GREAT Advertising 198