Predictive Analytics : Microsoft Excel

  • ISBN13:


  • ISBN10:


  • Edition: 1st
  • Format: Paperback
  • Copyright: 7/2/2012
  • Publisher: Que Publishing
  • Purchase Benefits
  • Free Shipping On Orders Over $59!
    Your order must be $59 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $39.99 Save up to $6.00
  • Buy New


Supplemental Materials

What is included with this book?

  • The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
  • The eBook copy of this book is not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.


"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

Introductionp. 1
Building a Collectorp. 7
Planning an Approachp. 8
A Meaningful Variablep. 8
Identifying Salesp. 8
Planning the Workbook Structurep. 9
Query Sheetsp. 9
Summary Sheetsp. 13
Snapshot Formulasp. 15
More Complicated Breakdownsp. 16
The VBA Codep. 18
The DoltAgain Subroutinep. 19
The GetNewData Subroutinep. 20
The GetRank Functionp. 24
The GetUnitsLeft Functionp. 26
The RefreshSheets Subroutinep. 27
The Analysis Sheetsp. 28
Defining a Dynamic Range Namep. 29
Using the Dynamic Range Namep. 30
Linear Regressionp. 35
Correlation and Regressionp. 35
Charting the Relationshipp. 36
Calculating Pearson's Correlation Coefficientp. 38
Correlation Is Not Causationp. 41
Simple Regressionp. 42
Array-Entering Formulasp. 44
Array-Entering LINEST()p. 44
Multiple Regressionp. 45
Creating the Composite Variablep. 45
Analyzing the Composite Variablep. 48
Assumptions Made in Regression Analysisp. 50
Variabilityp. 50
Using Excel's Regression Toolp. 54
Accessing the Data Analysis Add-Inp. 54
Running the Regression Toolp. 56
Forecasting with Moving Averagesp. 65
About Moving Averagesp. 65
Signal and Noisep. 66
Smoothing Versus Trackingp. 68
Weighted and Unweighted Moving Averagesp. 70
Criteria for Judging Moving Averagesp. 73
Mean Absolute Deviationp. 73
Least Squaresp. 74
Using Least Squares to Compare Moving Averagesp. 74
Getting Moving Averages Automaticallyp. 76
Using the Moving Average Toolp. 76
Forecasting a Time Series: Smoothingp. 83
Exponential Smoothing: The Basic Ideap. 84
Why "Exponential" Smoothing?p. 86
Using Excel's Exponential Smoothing Toolp. 89
Understanding the Exponential Smoothing Dialog Boxp. 90
Choosing the Smoothing Constantp. 96
Setting Up the Analysisp. 97
Using Solver to Find the Best Smoothing Constantp. 99
Understanding Solver's Requirementsp. 104
The Pointp. 107
Handling Linear Baselines with Trendp. 108
Characteristics of Trendp. 108
First Differencingp. 111
Holt's Linear Exponential Smoothingp. 115
About Terminology and Symbols in Handling Trended Seriesp. 115
Using Holt Linear Smoothingp. 116
Forecasting a Time Series: Regressionp. 123
Forecasting with Regressionp. 123
Linear Regression: An Examplep. 125
Using the LINEST() Functionp. 128
Forecasting with Autoregressionp. 133
Problems with Trendsp. 134
Correlating at Increasing Lagsp. 134
A Review: Linear Regression and Autoregressionp. 137
Adjusting the Autocorrelation Formulap. 139
Using ACFsp. 140
Understanding PACFsp. 142
Using the ARIMA Workbookp. 147
Logistic Regression: The Basicsp. 149
Traditional Approaches to the Analysisp. 149
Z-tests and the Central Limit Theoremp. 149
Using Chi-Squarep. 153
Preferring Chi-square to a Z-testp. 155
Regression Analysis on Dichotomiesp. 158
Homoscedasticityp. 158
Residuals Are Normally Distributedp. 161
Restriction of Predicted Rangep. 161
Ah, But You Can Get Odds Foreverp. 162
Probabilities and Oddsp. 163
How the Probabilities Shiftp. 164
Moving On to the Log Oddsp. 166
Logistic Regression: Further Issuesp. 169
An Example: Predicting Purchase Behaviorp. 170
Using Logistic Regressionp. 171
Calculation of Logit or Log Oddsp. 179
Comparing Excel with R: A Demonstrationp. 193
Getting Rp. 193
Running a Logistic Analysis in Rp. 194
The Purchase Data Setp. 195
Statistical Tests in Logistic Regressionp. 198
Models Comparison in Multiple Regressionp. 198
Calculating the Results of Different Modelsp. 199
Testing the Difference Between the Modelsp. 200
Models Comparison in Logistic Regressionp. 201
Principal Components Analysisp. 211
The Notion of a Principal Componentp. 211
Reducing Complexityp. 212
Understanding Relationships Among Measurable Variablesp. 213
Maximizing Variancep. 214
Components Are Mutually Orthogonalp. 215
Using the Principal Components Add-Inp. 216
The R Matrixp. 219
The Inverse of the R Matrixp. 220
Matrices, Matrix Inverses, and Identity Matricesp. 222
Features of the Correlation Matrix's Inversep. 223
Matrix Inverses and Beta Coefficientsp. 225
Singular Matricesp. 227
Testing for Uncorrelated Variablesp. 228
Using Eigenvaluesp. 229
Using Component Eigenvectorsp. 231
Factor Loadingsp. 233
Factor Score Coefficientsp. 233
Principal Components Distinguished from Factor Analysisp. 236
Distinguishing the Purposesp. 236
Distinguishing Unique from Shared Variancep. 237
Rotating Axesp. 238
Box-Jenkins ARIMA Modelsp. 241
The Rationale for ARIMAp. 241
Deciding to Use ARIMAp. 242
ARIMA Notationp. 242
Stages in ARIMA Analysisp. 244
The Identification Stagep. 244
Identifying an AR Processp. 244
Identifying an MA Processp. 248
Differencing in ARIMA Analysisp. 249
Using the ARIMA Workbookp. 252
Standard Errors in Correlogramsp. 253
White Noise and Diagnostic Checkingp. 254
Identifying Seasonal Modelsp. 255
The Estimation Stagep. 257
Estimating the Parameters for ARIMA(1,0,0)p. 257
Comparing Excel's Results to R'sp. 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 Stagesp. 264
Varimax Factor Rotation in Excelp. 267
Getting to a Simple Structurep. 267
Rotating Factors: The Rationalep. 268
Extraction and Rotation: An Examplep. 271
Showing Text Labels Next to Chart Markersp. 275
Structure of Principal Components and Factorsp. 276
Rotating Factors: The Resultsp. 277
Charting Records on Rotated Factorsp. 279
Using the Factor Workbook to Rotate Componentsp. 281
Indexp. 283
Table of Contents provided by Ingram. All Rights Reserved.

Rewards Program

Write a Review