Predictive Analytics Microsoft Excel

Cover......Page 1
Title Page......Page 4
Copyright Page......Page 5
Contents......Page 6
Introduction to the 2013 Edition......Page 14
You, Analytics, and Excel......Page 15
What’s in This Book......Page 17
Introduction to this Edition......Page 20
Helping Out Your Colleagues......Page 21
1 Building a Collector......Page 24
A Meaningful Variable......Page 25
Query Sheets......Page 26
Summary Sheets......Page 31
Snapshot Formulas......Page 33
Customizing Your Formulas......Page 34
The VBA Code......Page 36
The DoItAgain Subroutine......Page 37
The PrepForAgain Subroutine......Page 38
The GetNewData Subroutine......Page 39
The GetRank Function......Page 43
The RefreshSheets Subroutine......Page 45
The Analysis Sheets......Page 46
Defining a Dynamic Range Name......Page 47
Using the Dynamic Range Name......Page 49
Correlation and Regression......Page 52
Charting the Relationship......Page 53
Calculating Pearson’s Correlation Coefficient......Page 56
Correlation Is Not Causation......Page 58
Simple Regression......Page 59
Array-Entering Formulas......Page 61
Multiple Regression......Page 62
Creating the Composite Variable......Page 63
Merging the Predictors......Page 64
Analyzing the Composite Variable......Page 66
Assumptions Made in Regression Analysis......Page 67
Variability......Page 68
Measures of Variability: Bartlett’s Test of Homogeneity of Variance......Page 70
Means of Residuals Are Zero......Page 71
Accessing the Data Analysis Add-ln......Page 72
Accessing an Installed Add-ln......Page 73
Running the Regression Tool......Page 74
Understanding the Regression Tool’s Dialog Box......Page 75
Understanding the Regression Tool’s Output......Page 77
About Moving Averages......Page 84
Signal and Noise......Page 85
Smoothing Out the Noise......Page 86
Smoothing Versus Tracking......Page 87
Weighted and Unweighted Moving Averages......Page 89
Total of Weights......Page 90
More Recent Weights Are Larger......Page 91
Least Squares......Page 93
Using Least Squares to Compare Moving Averages......Page 94
Getting Moving Averages Automatically......Page 95
Using the Moving Average Tool......Page 96
Actuals and Forecasts......Page 98
Interpreting the Standard Errors—Or Failing to Do So......Page 100
4 Forecasting a Time Series: Smoothing......Page 102
Exponential Smoothing: The Basic Idea......Page 103
Why “Exponential” Smoothing?......Page 105
Using Excel’s Exponential Smoothing Tool......Page 108
Understanding the Exponential Smoothing Dialog Box......Page 109
Choosing the Smoothing Constant......Page 115
Setting Up the Analysis......Page 116
Using Solver to Find the Best Smoothing Constant......Page 118
Understanding Solver’s Requirements......Page 123
The Point......Page 126
Characteristics of Trend......Page 127
First Differencing......Page 130
Holt’s Linear Exponential Smoothing......Page 136
Using Holt’s Linear Smoothing......Page 137
Holt’s Method and First Differences......Page 143
Seasonal Models......Page 146
Estimating Seasonal Indexes......Page 147
Estimating the Series Level and First Forecast......Page 148
Extending the Forecasts to Future Periods......Page 149
Finishing the One-Step-Ahead Forecasts......Page 150
Extending the Forecast Horizon......Page 151
Using Additive Holt-Winters Models......Page 153
Trend......Page 156
Season......Page 157
Formulas for the Holt-Winters Additive and Multiplicative Models......Page 158
Formulas for the Additive Model......Page 159
Formulas for the Multiplicative Model......Page 161
The Models Compared......Page 162
Damped Trend Forecasts......Page 164
Forecasting with Regression......Page 166
Linear Regression: An Example......Page 168
Using the LINEST( ) Function......Page 171
Problems with Trends......Page 177
Correlating at Increasing Lags......Page 178
A Review: Linear Regression and Autoregression......Page 181
Adjusting the Autocorrelation Formula......Page 182
Using ACFs......Page 184
Understanding PACFs......Page 185
Using the ARIMA Workbook......Page 191
Z-tests and the Central Limit Theorem......Page 194
Binomial Distribution......Page 196
Only One Comparison......Page 197
Using Chi-Square......Page 198
Preferring Chi-Square to a Z-test......Page 200
Homoscedasticity......Page 204
Restriction of Predicted Range......Page 207
Probabilities and Odds......Page 208
How the Probabilities Shift......Page 210
Moving On to the Log Odds......Page 213
8 Logistic Regression: Further Issues......Page 216
An Example: Predicting Purchase Behavior......Page 217
Using Logistic Regression......Page 218
Calculation of Logit or Log Odds......Page 226
Comparing Excel with R: A Demonstration......Page 241
Running a Logistic Analysis in R......Page 242
Importing a csv File into R......Page 243
Importing From an Open Workbook Into R......Page 246
Understanding the Long Versus Wide Shape......Page 247
Running Logistic Regression Using glm......Page 248
Models Comparison in Multiple Regression......Page 253
Calculating the Results of Different Models......Page 254
Testing the Difference Between the Models......Page 255
Models Comparison in Logistic Regression......Page 256
The Multinomial Problem......Page 266
Three Alternatives and Three Predictors......Page 267
Calculating the Logits......Page 269
Converting the Logits to Probabilities......Page 270
Understanding the Differences Between the Binomial and Multinomial Equations......Page 271
Optimizing the Equations......Page 273
Benchmarking the Excel Results Against R......Page 274
Converting the Raw Data Frame with mlogit.data......Page 275
Calling the mlogit Function......Page 277
Completing the mlogit Arguments......Page 279
Four Outcomes and One Predictor......Page 280
Multinomial Analysis with an Individual-Specific Predictor......Page 282
Multinomial Analysis with an Alternative-Specific Predictor......Page 285
The Notion of a Principal Component......Page 288
Reducing Complexity......Page 289
Understanding Relationships Among Measurable Variables......Page 290
Maximizing Variance......Page 291
Components Are Mutually Orthogonal......Page 293
Using the Principal Components Add-In......Page 294
The Inverse of the R Matrix......Page 297
Matrices, Matrix Inverses, and Identity Matrices......Page 300
Features of the Correlation Matrix’s Inverse......Page 301
Matrix Inverses and Beta Coefficients......Page 303
Testing for Uncorrelated Variables......Page 306
Using Eigenvalues......Page 308
Using Component Eigenvectors......Page 309
Factor Score Coefficients......Page 312
Distinguishing Unique from Shared Variance......Page 316
Rotating Axes......Page 318
The Rationale for ARIMA......Page 320
ARIMA Notation......Page 321
Identifying an AR Process......Page 323
Identifying an MA Process......Page 326
Differencing in ARIMA Analysis......Page 328
Using the ARIMA Workbook......Page 333
Standard Errors in Correlograms......Page 334
White Noise and Diagnostic Checking......Page 335
Identifying Seasonal Models......Page 336
Estimating the Parameters for ARIMA(1,0,0)......Page 337
Comparing Excel’s Results to R’s......Page 339
Exponential Smoothing and ARIMA(0,0,1)......Page 342
Using ARIMA(0,1,1) in Place of ARIMA(0,0,1)......Page 345
The Diagnostic and Forecasting Stages......Page 346
Getting to a Simple Structure......Page 348
Rotating Factors: The Rationale......Page 349
Extraction and Rotation: An Example......Page 352
Structure of Principal Components and Factors......Page 357
Rotating Factors: The Results......Page 358
Charting Records on Rotated Factors......Page 361
Using the Factor Workbook to Rotate Components......Page 363
A......Page 366
B......Page 367
C......Page 368
D......Page 369
E......Page 370
G......Page 371
L......Page 372
M......Page 374
P......Page 375
R......Page 377
S......Page 378
V......Page 380
Z......Page 381