Decision Analytics

Conrad Carlberg has written eleven books about quantitative analysis with Excel, including Statistical Analysis: Microsoft® Excel 2010 and Predictive Analytics: Microsoft® Excel. His company, found at www.conradcarlberg.com, focuses on the quantitative analysis of data that companies routinely collect in their sales, employee, customer management and other operations database systems. Carlberg holds a Ph.D. in statistics from the University of Colorado and has 25 years’ experience applying advanced analytical techniques.   Conrad Carlberg lives near San Diego with his wife, not too far from the beach, but high enough that the rise in the sea level is unlikely to convert their home to waterfront property. Two cats round out the indoor menagerie; the three rabbits are required to stay outside.

Introduction   1

What’s in the Book   1

Why Use Excel?   3

 

1  Components of Decision Analytics   5

Classifying According to Existing Categories   5

  Using a Two-Step Approach   6

  Multiple Regression and Decision Analytics   6

  Access to a Reference Sample   8

  Multivariate Analysis of Variance   9

  Discriminant Function Analysis   10

  Logistic Regression   12

Classifying According to Naturally Occurring Clusters    13

  Principal Components Analysis   13

  Cluster Analysis   14

Some Terminology Problems   16

  The Design Sets the Terms   17

  Causation Versus Prediction   18

  Why the Terms Matter   18

 

2  Logistic Regression   21

The Rationale for Logistic Regression   22

  The Scaling Problem   24

  About Underlying Assumptions   25

  Equal Spread   25

  Equal Variances with Dichotomies   27

  Equal Spread and the Range   28

The Distribution of the Residuals   29

  Calculating the Residuals   30

  The Residuals of a Dichotomy   30

Using Logistic Regression   31

  Using Odds Rather Than Probabilities   32

  Using Log Odds   33

  Using Maximum Likelihood Instead of Least Squares   34

Maximizing the Log Likelihood   35

  Setting Up the Data   35

  Setting Up the Logistic Regression Equation   36

  Getting the Odds   38

  Getting the Probabilities   39

  Calculating the Log Likelihood   40

  Finding and Installing Solver   41

  Running Solver   41

The Rationale for Log Likelihood   43

  The Probability of a Correct Classification   44

  Using the Log Likelihood   45

The Statistical Significance of the Log Likelihood   48

  Setting Up the Reduced Model   50

  Setting Up the Full Model   51

 

3  Univariate Analysis of Variance (ANOVA)  53

The Logic of ANOVA   54

  Using Variance   54

  Partitioning Variance   55

  Expected Values of Variances (Within Groups)   56

  Expected Values of Variances (Between Groups)   58

  The F-Ratio   61

  The Noncentral F Distribution   64

Single Factor ANOVA   66

  Adopting an Error Rate   66

  Computing the Statistics   67

  Deriving the Standard Error of the Mean   70

Using the Data Analysis Add-In   72

  Installing the Data Analysis Add-In   73

  Using the ANOVA: Single Factor Tool   73

Understanding the ANOVA Output   75

  Using the Descriptive Statistics   75

  Using the Inferential Statistics   76

The Regression Approach   79

  Using Effect Coding   80

  The LINEST() Formula   82

  The LINEST() Results   83

  LINEST() Inferential Statistics   85

 

4  Multivariate Analysis of Variance (MANOVA)   89

The Rationale for MANOVA   89

  Correlated Variables   90

  Correlated Variables in ANOVA   91

Visualizing Multivariate ANOVA   92

  Univariate ANOVA Results   93

  Multivariate ANOVA Results   93

  Means and Centroids   95

From ANOVA to MANOVA   96

  Using SSCP Instead of SS   98

  Getting the Among and the Within SSCP Matrices   102

  Sums of Squares and SSCP Matrices   104

Getting to a Multivariate F-Ratio   105

Wilks’ Lambda and the F-Ratio   107

  Converting Wilks’ Lambda to an F Value   108

Running a MANOVA in Excel   110

  Laying Out the Data   110

  Running the MANOVA Code   111

  Descriptive Statistics   112

  Equality of the Dispersion Matrices   113

  The Univariate and Multivariate F-Tests   115

After the Multivariate Test   116

 

5  Discriminant Function Analysis: The Basics   119

Treating a Category as a Number   120

The Rationale for Discriminant Analysis   122

  Multiple Regression and Discriminant Analysis   122

  Adjusting Your Viewpoint   123

Discriminant Analysis and Multiple Regression   125

  Regression, Discriminant Analysis, and Canonical Correlation   125

  Coding and Multiple Regression   127

The Discriminant Function and the Regression Equation   129

  From Discriminant Weights to Regression Coefficients   130

  Eigenstructures from Regression and Discriminant Analysis   133

  Structure Coefficients Can Mislead   136

Wrapping It Up   137

 

6  Discriminant Function Analysis: Further Issues   139

Using the Discriminant Workbook   139

  Opening the Discriminant Workbook   140

  Using the Discriminant Dialog Box   141

Why Run a Discriminant Analysis on Irises?   144

  Evaluating the Original Measures 144

  Discriminant Analysis and Investment   145

Benchmarking with R   147

  Downloading R   147

  Arranging the Data File   148

  Running the Analysis   149

The Results of the Discrim Add-In   152

  The Discriminant Results   153

  Interpreting the Structure Coefficients   155

  Eigenstructures and Coefficients   156

  Other Uses for the Coefficients   159

Classifying the Cases   162

  Distance from the Centroids   163

  Correcting for the Means   164

  Adjusting for the Variance-Covariance Matrix   167

  Assigning a Classification   169

  Creating the Classification Table   170

Training Samples: The Classification Is Known Beforehand   171

 

7  Principal Components Analysis   173

Establishing a Conceptual Framework for Principal Components Analysis   174

  Principal Components and Tests   174

  PCA’s Ground Rules   175

  Correlation and Oblique Factor Rotation   176

Using the Principal Components Add-In   177

  The Correlation Matrix   179

  The Inverse of the R Matrix   179

  The Sphericity Test   182

Counting Eigenvalues, Calculating Coefficients and Understanding Communalities   183

  How Many Components?   184

  Factor Score Coefficients   186

  Communalities   186

Relationships Between the Individual Results   187

  Using the Eigenvalues and Eigenvectors   187

  Eigenvalues, Eigenvectors, and Loadings   188

  Eigenvalues, Eigenvectors, and Factor Coefficients   190

  Getting the Eigenvalues Directly from the Factor Scores   191

Getting the Eigenvalues and Eigenvectors   192

  Iteration and Exhaustion   193

Rotating Factors to a Meaningful Solution   196

  Identifying the Factors   197

  The Varimax Rotation   200

Classification Examples   202

  State Crime Rates   202

  Physical Measurements of Aphids   206

 

8  Cluster Analysis: The Basics   209

Cluster Analysis, Discriminant Analysis, and Logistic Regression   209

Euclidean Distance   211

  Mahalanobis’ D2 and Cluster Analysis   214

Finding Clusters: The Single Linkage Method   215

The Self-Selecting Nature of Cluster Analysis   220

Finding Clusters: The Complete Linkage Method   223

  Complete Linkage: An Example   224

  Other Linkage Methods   227

Finding Clusters: The K-means Method   228

  Characteristics of K-means Analysis   228

  A K-means Example   229

Benchmarking K-means with R   233

 

9  Cluster Analysis: Further Issues   235

Using the K-means Workbook   235

  Deciding on the Number of Clusters   237

  The Cluster Members Worksheet   239

  The Cluster Centroids Worksheet   241

  The Cluster Variances Worksheet   242

  The F-Ratios Worksheet   244

  Reporting Process Statistics   247

Cluster Analysis Using Principal Components   248

  Principal Components Revisited   249

  Clustering Wines   253

  Cross-Validating the Results   256

 

Index   259