## Description

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

## Purchase Info

ISBN-10: 0-13-296726-X

ISBN-13: 978-0-13-296726-6

Format: Safari PTG

This publication is not currently for sale.