Predictive Analytics Microsoft Excel

by Carlberg, Conrad

• ISBN13: 9780789749413
• ISBN10: 0789749416
• Edition: 1st
• Format: Paperback
• Copyright: 2012-07-02
• Publisher: Que Publishing

Summary

"Moneyball" helped make predictive analytics famous, but companies of all types are discovering these techniques'immense value for improving decision-making and profitability. Not everyone has access to expensive predictive analytics tools such as SAS, but virtually every business professional does have software that can serve the purpose admirably: Microsoft Excel. In this complete, hands-on tutorial, Microsoft Excel MVP Conrad Carlberg shows business professionals exactly how to solve real-world business problems with Excel predictive analytics, in areas ranging from sales and marketing to operations. Building on skills they already have, experienced Excel users will master techniques ranging from least squares regression and moving averages through smoothing, ARIMA, and logistic regression. Carlberg helps Excel users avoid pitfalls associated with simply "plugging" numbers into Excel's Data Analysis add-in (formerly "Analysis ToolPak"), showing how to create more credible, reliable forecasts. His forecasting coverage is more thorough and sophisticated than that of any other book. Carlberg also provides downloadable Excel workbooks that can be easily adapted to readers'unique requirements. This book's techniques are highly prized by companies seeking to increase revenues, reduce costs, and improve productivity; businesspeople who master these skills will have a major competitive advantage.

Author Biography

Conrad Carlberg is a multiple recipient of Microsoft's most Valuable Professional (MVP) award for Microsoft Excel. He has written 11 books about quantitative analysis with Excel, including Statistical Analysis: Microsoft Excel 2010. His company (at conradcarlberg.com) specializes in custom statistical and analytical problem ranging form inventory control to real-estate market segmentation. Carlberg holds a Ph. D. in statistics from the University of Colorado and has 25 years of experience in applying advanced analytic techniques.

Table of Contents

Introduction	p. 1
Building a Collector	p. 7
Planning an Approach	p. 8
A Meaningful Variable	p. 8
Identifying Sales	p. 8
Planning the Workbook Structure	p. 9
Query Sheets	p. 9
Summary Sheets	p. 13
Snapshot Formulas	p. 15
More Complicated Breakdowns	p. 16
The VBA Code	p. 18
The DoltAgain Subroutine	p. 19
The GetNewData Subroutine	p. 20
The GetRank Function	p. 24
The GetUnitsLeft Function	p. 26
The RefreshSheets Subroutine	p. 27
The Analysis Sheets	p. 28
Defining a Dynamic Range Name	p. 29
Using the Dynamic Range Name	p. 30
Linear Regression	p. 35
Correlation and Regression	p. 35
Charting the Relationship	p. 36
Calculating Pearson's Correlation Coefficient	p. 38
Correlation Is Not Causation	p. 41
Simple Regression	p. 42
Array-Entering Formulas	p. 44
Array-Entering LINEST()	p. 44
Multiple Regression	p. 45
Creating the Composite Variable	p. 45
Analyzing the Composite Variable	p. 48
Assumptions Made in Regression Analysis	p. 50
Variability	p. 50
Using Excel's Regression Tool	p. 54
Accessing the Data Analysis Add-In	p. 54
Running the Regression Tool	p. 56
Forecasting with Moving Averages	p. 65
About Moving Averages	p. 65
Signal and Noise	p. 66
Smoothing Versus Tracking	p. 68
Weighted and Unweighted Moving Averages	p. 70
Criteria for Judging Moving Averages	p. 73
Mean Absolute Deviation	p. 73
Least Squares	p. 74
Using Least Squares to Compare Moving Averages	p. 74
Getting Moving Averages Automatically	p. 76
Using the Moving Average Tool	p. 76
Forecasting a Time Series: Smoothing	p. 83
Exponential Smoothing: The Basic Idea	p. 84
Why "Exponential" Smoothing?	p. 86
Using Excel's Exponential Smoothing Tool	p. 89
Understanding the Exponential Smoothing Dialog Box	p. 90
Choosing the Smoothing Constant	p. 96
Setting Up the Analysis	p. 97
Using Solver to Find the Best Smoothing Constant	p. 99
Understanding Solver's Requirements	p. 104
The Point	p. 107
Handling Linear Baselines with Trend	p. 108
Characteristics of Trend	p. 108
First Differencing	p. 111
Holt's Linear Exponential Smoothing	p. 115
About Terminology and Symbols in Handling Trended Series	p. 115
Using Holt Linear Smoothing	p. 116
Forecasting a Time Series: Regression	p. 123
Forecasting with Regression	p. 123
Linear Regression: An Example	p. 125
Using the LINEST() Function	p. 128
Forecasting with Autoregression	p. 133
Problems with Trends	p. 134
Correlating at Increasing Lags	p. 134
A Review: Linear Regression and Autoregression	p. 137
Adjusting the Autocorrelation Formula	p. 139
Using ACFs	p. 140
Understanding PACFs	p. 142
Using the ARIMA Workbook	p. 147
Logistic Regression: The Basics	p. 149
Traditional Approaches to the Analysis	p. 149
Z-tests and the Central Limit Theorem	p. 149
Using Chi-Square	p. 153
Preferring Chi-square to a Z-test	p. 155
Regression Analysis on Dichotomies	p. 158
Homoscedasticity	p. 158
Residuals Are Normally Distributed	p. 161
Restriction of Predicted Range	p. 161
Ah, But You Can Get Odds Forever	p. 162
Probabilities and Odds	p. 163
How the Probabilities Shift	p. 164
Moving On to the Log Odds	p. 166
Logistic Regression: Further Issues	p. 169
An Example: Predicting Purchase Behavior	p. 170
Using Logistic Regression	p. 171
Calculation of Logit or Log Odds	p. 179
Comparing Excel with R: A Demonstration	p. 193
Getting R	p. 193
Running a Logistic Analysis in R	p. 194
The Purchase Data Set	p. 195
Statistical Tests in Logistic Regression	p. 198
Models Comparison in Multiple Regression	p. 198
Calculating the Results of Different Models	p. 199
Testing the Difference Between the Models	p. 200
Models Comparison in Logistic Regression	p. 201
Principal Components Analysis	p. 211
The Notion of a Principal Component	p. 211
Reducing Complexity	p. 212
Understanding Relationships Among Measurable Variables	p. 213
Maximizing Variance	p. 214
Components Are Mutually Orthogonal	p. 215
Using the Principal Components Add-In	p. 216
The R Matrix	p. 219
The Inverse of the R Matrix	p. 220
Matrices, Matrix Inverses, and Identity Matrices	p. 222
Features of the Correlation Matrix's Inverse	p. 223
Matrix Inverses and Beta Coefficients	p. 225
Singular Matrices	p. 227
Testing for Uncorrelated Variables	p. 228
Using Eigenvalues	p. 229
Using Component Eigenvectors	p. 231
Factor Loadings	p. 233
Factor Score Coefficients	p. 233
Principal Components Distinguished from Factor Analysis	p. 236
Distinguishing the Purposes	p. 236
Distinguishing Unique from Shared Variance	p. 237
Rotating Axes	p. 238
Box-Jenkins ARIMA Models	p. 241
The Rationale for ARIMA	p. 241
Deciding to Use ARIMA	p. 242
ARIMA Notation	p. 242
Stages in ARIMA Analysis	p. 244
The Identification Stage	p. 244
Identifying an AR Process	p. 244
Identifying an MA Process	p. 248
Differencing in ARIMA Analysis	p. 249
Using the ARIMA Workbook	p. 252
Standard Errors in Correlograms	p. 253
White Noise and Diagnostic Checking	p. 254
Identifying Seasonal Models	p. 255
The Estimation Stage	p. 257
Estimating the Parameters for ARIMA(1,0,0)	p. 257
Comparing Excel's Results to R's	p. 259
Exponential Smoothing and ARIMA(0,0,1)	p. 261
Using ARIMA(0,1,1) in Place of ARIMA(0,0,1)	p. 263
The Diagnostic and Forecasting Stages	p. 264
Varimax Factor Rotation in Excel	p. 267
Getting to a Simple Structure	p. 267
Rotating Factors: The Rationale	p. 268
Extraction and Rotation: An Example	p. 271
Showing Text Labels Next to Chart Markers	p. 275
Structure of Principal Components and Factors	p. 276
Rotating Factors: The Results	p. 277
Charting Records on Rotated Factors	p. 279
Using the Factor Workbook to Rotate Components	p. 281
Index	p. 283
Table of Contents provided by Ingram. All Rights Reserved.