Decision Analytics: Microsoft Excel / Edition 1

Decision Analytics: Microsoft Excel / Edition 1

by Conrad Carlberg
4.0 1
Pub. Date:


View All Available Formats & Editions
Current price is , Original price is $39.99. You
Select a Purchase Option
  • purchase options


Decision Analytics: Microsoft Excel / Edition 1

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

Product Details

ISBN-13: 9780789751683
Publisher: Que
Publication date: 11/19/2013
Pages: 288
Product dimensions: 9.20(w) x 11.90(h) x 1.10(d)

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


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

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews

Decision Analytics: Microsoft Excel 4 out of 5 based on 0 ratings. 1 reviews.
Anonymous More than 1 year ago
Pheonix walks in with a messanger bag. She sits down and pulls out a sketchpad with the framework of a pheonix. She draws in the basic lines and getscarrieed away. She drew in all of the deatails. It was a beautiful firebird with green eyes rising out of flame. She leaned back and put her stuff away, falling asleep.