Decision Analytics: Microsoft Excel

by Conrad Carlberg

Overview

Crunch Big Data to optimize marketing and more!

 

Overwhelmed by all the Big Data now available to you? Not sure what questions to ask or how to ask them? Using Microsoft Excel and proven decision analytics techniques, you can distill all that data into manageable sets—and use them to optimize a wide variety of business and investment decisions. In Decision Analytics: Microsoft Excel, best selling statistics expert and consultant Conrad Carlberg will show you how—hands-on and step-by-step.

 

Carlberg guides you through using decision analytics to segment customers (or anything else) into sensible and actionable groups and clusters. Next, you’ll learn practical ways to optimize a wide spectrum of decisions in business and beyond—from pricing to cross-selling, hiring to investments—even facial recognition software uses the techniques discussed in this book!

 

Through realistic examples, Carlberg helps you understand the techniques and assumptions that underlie decision analytics and use simple Excel charts to intuitively grasp the results. With this foundation in place, you can perform your own analyses in Excel and work with results produced by advanced stats packages such as SAS and SPSS.

 

This book comes with an extensive collection of downloadable Excel workbooks you can easily adapt to your own unique requirements, plus VBA code to streamline several of its most complex techniques.

• Classify data according to existing categories or naturally occurring clusters of predictor variables
• Cut massive numbers of variables and records down to size, so you can get the answers you really need
• Utilize cluster analysis to find patterns of similarity for market research and many other applications
• Learn how multiple discriminant analysis helps you classify cases
• Use MANOVA to decide whether groups differ on multivariate centroids
• Use principal components to explore data, find patterns, and identify latent factors

Register your book for access to all sample workbooks, updates, and corrections as they become available at quepublishing.com/title/9780789751683.

Product Details

ISBN-13:	9780133481686
Publisher:	Pearson Education
Publication date:	10/31/2013
Sold by:	Barnes & Noble
Format:	eBook
Pages:	336
File size:	21 MB
Note:	This product may take a few minutes to download.
Age Range:	18 Years

About the Author

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.

Table of Contents

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