Predictive Analytics: Microsoft Excel

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

Category: Spreadsheets

Covers: Microsoft Office Excel

Downloads

Download the accompanying workbook files here

Related Articles

Calculating the Power of the F Test

Excel's LINEST() Function Deconstructed

Forcing the Constant in Regression to Zero: Understanding Excel's LINEST() Error

How Excel's LINEST() Handles Collinearity

Rotating Factors with Excel using Varimax

The Concept of Statistical Power

The Noncentrality Parameter in the F Distribution

The Statistical Power of t-Tests

Using Principal Components in Excel

Online Sample Chapter

Predictive Analytics in Microsoft Excel: Building Your Own Data Collector

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

Sample Pages

Download the sample pages (includes Chapter 1 and Index)