Predictive Analytics: Microsoft Excel

"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.

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