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
About the Author :
Counting conservatively, this is Conrad Carlberg’s eleventh book about quantitative analysis using Microsoft Excel, which he still regards with a mix of awe and exasperation. A look back at the “About the Author” paragraph in Carlberg’s first book, published in 1995, shows that the only word that remains accurate is “He.” Scary.
