Safari, the world’s most comprehensive technology and business learning platform.

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required

O'Reilly logo
Predictive Analytics: Microsoft® Excel

Book 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

  1. Title Page
  2. Copyright Page
  3. Contents at a Glance
  4. Table of Contents
  5. About the Author
  6. Dedication
  7. Acknowledgments
  8. We Want to Hear from You!
  9. Reader Services
  10. Introduction
    1. You, Analytics, and Excel
    2. Excel as a Platform
    3. What’s in This Book
  11. 1. Building a Collector
    1. Planning an Approach
    2. Planning the Workbook Structure
    3. The VBA Code
    4. The Analysis Sheets
  12. 2. Linear Regression
    1. Correlation and Regression
    2. Simple Regression
    3. Multiple Regression
    4. Assumptions Made in Regression Analysis
    5. Using Excel’s Regression Tool
  13. 3. Forecasting with Moving Averages
    1. About Moving Averages
    2. Criteria for Judging Moving Averages
    3. Getting Moving Averages Automatically
  14. 4. Forecasting a Time Series: Smoothing
    1. Exponential Smoothing: The Basic Idea
    2. Why “Exponential” Smoothing?
    3. Using Excel’s Exponential Smoothing Tool
    4. Choosing the Smoothing Constant
    5. Handling Linear Baselines with Trend
    6. Holt’s Linear Exponential Smoothing
  15. 5. Forecasting a Time Series: Regression
    1. Forecasting with Regression
    2. Forecasting with Autoregression
  16. 6. Logistic Regression: The Basics
    1. Traditional Approaches to the Analysis
    2. Regression Analysis on Dichotomies
    3. Ah, But You Can Get Odds Forever
  17. 7. Logistic Regression: Further Issues
    1. An Example: Predicting Purchase Behavior
    2. Comparing Excel with R: A Demonstration
    3. Statistical Tests in Logistic Regression
  18. 8. Principal Components Analysis
    1. The Notion of a Principal Component
    2. Using the Principal Components Add-In
    3. Principal Components Distinguished from Factor Analysis
  19. 9. Box-Jenkins ARIMA Models
    1. The Rationale for ARIMA
    2. Stages in ARIMA Analysis
    3. The Identification Stage
    4. The Estimation Stage
    5. The Diagnostic and Forecasting Stages
  20. 10. Varimax Factor Rotation in Excel
    1. Getting to a Simple Structure
    2. Structure of Principal Components and Factors
  21. Index