Predictive Analytics: Microsoft Excel

Excel predictive analytics for serious data crunchers!   The movie Moneyball made predictive analytics famous: Now you can apply the same techniques to help your business win. You don’t need multimillion-dollar software: All the tools you need are available in Microsoft Excel, and all the knowledge and skills are right here, in this book!   Microsoft Excel MVP Conrad Carlberg shows you how to use Excel predictive analytics to solve real-world problems in areas ranging from sales and marketing to operations. Carlberg offers unprecedented insight into building powerful, credible, and reliable forecasts, showing how to gain deep insights from Excel that would be difficult to uncover with costly tools such as SAS or SPSS.   You’ll get an extensive collection of downloadable Excel workbooks you can easily adapt to your own unique requirements, plus VBA code—much of it open-source—to streamline several of this book’s most complex techniques.   Step by step, you’ll build on Excel skills you already have, learning advanced techniques that can help you increase revenue, reduce costs, and improve productivity. By mastering predictive analytics, you’ll gain a powerful competitive advantage for your company and yourself.      •   Learn both the “how” and “why” of using data to make better tactical decisions    •   Choose the right analytics technique for each problem    •   Use Excel to capture live real-time data from diverse sources, including third-party websites    •   Use logistic regression to predict behaviors such as “will buy” versus “won’t buy”    •   Distinguish random data bounces from real, fundamental changes    •   Forecast time series with smoothing and regression    •   Construct more accurate predictions by using Solver to find maximum likelihood estimates    •   Manage huge numbers of variables and enormous datasets with principal components analysis and Varimax factor rotation    •   Apply ARIMA (Box-Jenkins) techniques to build better forecasts and understand their meaning       

Table of Contents:
Introduction Chapter 1 Building a Collector Planning an Approach     A Meaningful Variable     Identifying Sales Planning the Workbook Structure     Query Sheets     Summary Sheets     Snapshot Formulas     More Complicated Breakdowns The VBA Code     The DoItAgain Subroutine     The GetNewData Subroutine     The GetRank Function     The GetUnitsLeft Function     The RefreshSheets Subroutine The Analysis Sheets     Defining a Dynamic Range Name     Using the Dynamic Range Name Chapter 2 Linear Regression Correlation and Regression     Charting the Relationship     Calculating Pearson’s Correlation Coefficient     Correlation Is Not Causation Simple Regression     Array-Entering Formulas     Array-Entering LINEST() Multiple Regression     Creating the Composite Variable     Analyzing the Composite Variable Assumptions Made in Regression Analysis     Variability Using Excel’s Regression Tool     Accessing the Data Analysis Add-In     Running the Regression Tool Chapter 3 Forecasting with Moving Averages About Moving Averages     Signal and Noise     Smoothing Versus Tracking     Weighted and Unweighted Moving Averages Criteria for Judging Moving Averages     Mean Absolute Deviation     Least Squares     Using Least Squares to Compare Moving Averages Getting Moving Averages Automatically     Using the Moving Average Tool Chapter 4 Forecasting a Time Series: Smoothing Exponential Smoothing: The Basic Idea Why “Exponential” Smoothing? Using Excel’s Exponential Smoothing Tool     Understanding the Exponential Smoothing Dialog Box Choosing the Smoothing Constant     Setting Up the Analysis     Using Solver to Find the Best Smoothing Constant     Understanding Solver’s Requirements     The Point Handling Linear Baselines with Trend     Characteristics of Trend     First Differencing Holt’s Linear Exponential Smoothing     About Terminology and Symbols in Handling Trended Series     Using Holt Linear Smoothing Chapter 5 Forecasting a Time Series: Regression Forecasting with Regression     Linear Regression: An Example     Using the LINEST() Function Forecasting with Autoregression     Problems with Trends     Correlating at Increasing Lags     A Review: Linear Regression and Autoregression     Adjusting the Autocorrelation Formula     Using ACFs     Understanding PACFs     Using the ARIMA Workbook Chapter 6 Logistic Regression: The Basics Traditional Approaches to the Analysis     Z-tests and the Central Limit Theorem     Using Chi-Square     Preferring Chi-square to a Z-test Regression Analysis on Dichotomies     Homoscedasticity     Residuals Are Normally Distributed     Restriction of Predicted Range Ah, But You Can Get Odds Forever     Probabilities and Odds     How the Probabilities Shift     Moving On to the Log Odds Chapter 7 Logistic Regression: Further Issues An Example: Predicting Purchase Behavior     Using Logistic Regression     Calculation of Logit or Log Odds Comparing Excel with R: A Demonstration     Getting R     Running a Logistic Analysis in R     The Purchase Data Set Statistical Tests in Logistic Regression     Models Comparison in Multiple Regression     Calculating the Results of Different Models     Testing the Difference Between the Models     Models Comparison in Logistic Regression Chapter 8 Principal Components Analysis The Notion of a Principal Component     Reducing Complexity     Understanding Relationships Among Measurable Variables     Maximizing Variance     Components Are Mutually Orthogonal Using the Principal Components Add-In     The R Matrix     The Inverse of the R Matrix     Matrices, Matrix Inverses, and Identity Matrices     Features of the Correlation Matrix’s Inverse     Matrix Inverses and Beta Coefficients     Singular Matrices     Testing for Uncorrelated Variables     Using Eigenvalues     Using Component Eigenvectors     Factor Loadings     Factor Score Coefficients Principal Components Distinguished from Factor Analysis     Distinguishing the Purposes     Distinguishing Unique from Shared Variance     Rotating Axes Chapter 9 Box-Jenkins ARIMA Models The Rationale for ARIMA     Deciding to Use ARIMA     ARIMA Notation Stages in ARIMA Analysis The Identification Stage     Identifying an AR Process     Identifying an MA Process     Differencing in ARIMA Analysis     Using the ARIMA Workbook     Standard Errors in Correlograms     White Noise and Diagnostic Checking     Identifying Seasonal Models The Estimation Stage     Estimating the Parameters for ARIMA(1,0,0)     Comparing Excel’s Results to R’s     Exponential Smoothing and ARIMA(0,0,1)     Using ARIMA(0,1,1) in Place of ARIMA(0,0,1) The Diagnostic and Forecasting Stages Chapter 10 Varimax Factor Rotation in Excel Getting to a Simple Structure     Rotating Factors: The Rationale     Extraction and Rotation: An Example     Showing Text Labels Next to Chart Markers Structure of Principal Components and Factors     Rotating Factors: The Results     Charting Records on Rotated Factors     Using the Factor Workbook to Rotate Components   9780789749413    TOC    6/18/2012