Predictive Analytics: Microsoft® Excel

Table of Contents

Introduction

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

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