Predictive Analytics: Microsoft Excel

Paperback | July 2, 2012

byConrad Carlberg

not yet rated|write a review

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




Pricing and Purchase Info

$43.42 online
$49.99 list price (save 13%)
In stock online
Ships free on orders over $25
Prices may vary. why?
Please call ahead to confirm inventory.

From the Publisher

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

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.

other books by Conrad Carlberg

R For Microsoft Excel Users: Making The Transition For Statistical Analysis
R For Microsoft Excel Users: Making The Transition For ...

Paperback|Nov 15 2016

$43.43 online$49.99list price(save 13%)
Regression Analysis Microsoft Excel
Regression Analysis Microsoft Excel

Paperback|May 6 2016

$42.32 online$49.99list price(save 15%)
More Predictive Analytics: Microsoft Excel
More Predictive Analytics: Microsoft Excel

Paperback|Aug 20 2015

$39.95 online$45.99list price(save 13%)
see all books by Conrad Carlberg
Format:PaperbackDimensions:304 pages, 9.1 × 7 × 0.8 inPublished:July 2, 2012Publisher:Pearson EducationLanguage:English

The following ISBNs are associated with this title:

ISBN - 10:0789749416

ISBN - 13:9780789749413

Look for similar items by category:

Customer Reviews of Predictive Analytics: Microsoft Excel


Extra Content

Table of Contents


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


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


    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