Predictive Analytics: Microsoft Excel [NOOK Book]


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

See more details below
Predictive Analytics: Microsoft Excel

Available on NOOK devices and apps  
  • NOOK Devices
  • Samsung Galaxy Tab 4 NOOK 7.0
  • Samsung Galaxy Tab 4 NOOK 10.1
  • NOOK HD Tablet
  • NOOK HD+ Tablet
  • NOOK eReaders
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac

Want a NOOK? Explore Now

NOOK Book (eBook)
$17.99 price
(Save 43%)$31.99 List Price


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




Read More Show Less

Product Details

  • ISBN-13: 9780132967259
  • Publisher: Pearson Education
  • Publication date: 7/16/2012
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 336
  • Sales rank: 1,128,088
  • File size: 37 MB
  • Note: This product may take a few minutes to download.

Meet the Author

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.

Read More Show Less

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


Read More Show Less

Customer Reviews

Average Rating 5
( 2 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 2 Customer Reviews
  • Anonymous

    Posted May 9, 2013


    This is VERY gogd! But can you please put one of the following appsbon here? Subway surfers instagram minecraft microsoft type, or a texting site other than text free. Thank you very much! If you dont want to put those apps on, then ok, but youll use a coustumer. Thank you Barnes and Noble

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted March 15, 2013

    Leah to cali

    Next res

    Was this review helpful? Yes  No   Report this review
Sort by: Showing all of 2 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)