O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

More Predictive Analytics: Microsoft Excel

Book Description

Accurate, practical Excel predictive analysis: powerful smoothing techniques for serious data crunchers!

In More Predictive Analytics, Microsoft Excel® MVP Conrad Carlberg shows how to use intuitive smoothing techniques to make remarkably accurate predictions. You won’t have to write a line of code--all you need is Excel and this all-new, crystal-clear tutorial.

Carlberg goes beyond his highly-praised Predictive Analytics, introducing proven methods for creating more specific, actionable forecasts. You’ll learn how to predict what customers will spend on a given product next year… project how many patients your hospital will admit next quarter… tease out the effects of seasonality (or patterns that recur over a day, year, or any other period)… distinguish real trends from mere “noise.”

Drawing on more than 20 years of experience, Carlberg helps you master powerful techniques such as autocorrelation, differencing, Holt-Winters, backcasting, polynomial regression, exponential smoothing, and multiplicative modeling.

Step by step, you’ll learn how to make the most of built-in Excel tools to gain far deeper insights from your data. To help you get better results faster, Carlberg provides downloadable Excel workbooks you can easily adapt for your own projects.

If you’re ready to make better forecasts for better decision-making, you’re ready for More Predictive Analytics.

  • Discover when and how to use smoothing instead of regression

  • Test your data for trends and seasonality

  • Compare sets of observations with the autocorrelation function

  • Analyze trended time series with Excel’s Solver and Analysis ToolPak

  • Use Holt's linear exponential smoothing to forecast the next level and trend, and extend forecasts further into the future

  • Initialize your forecasts with a solid baseline

  • Improve your initial forecasts with backcasting and optimization

  • Fully reflect simple or complex seasonal patterns in your forecasts

  • Account for sudden, unexpected changes in trends, from fads to new viral infections

  • Use range names to control complex forecasting models more easily

  • Compare additive and multiplicative models, and use the right model for each task

  • Table of Contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Contents at a Glance
    5. Contents
    6. About the Author
    7. Dedication
    8. We Want to Hear from You!
    9. Reader Services
    10. Introduction
    11. 1. Smoothing and Its Alternatives
      1. Regression in Forecasting
        1. Curvilinear Relationships
        2. Piecewise Regression
        3. Polynomial Regression
        4. TREND() and LINEST()
        5. Using the Chart’s Trendline
      2. Regression or Smoothing?
        1. Comparing Smoothing with Regression with Forecasting Errors
        2. Additional Rationales for Smoothing
        3. Pushing the Forecast Horizon
    12. 2. Diagnosing Trend and Seasonality
      1. Inside the Autocorrelation
        1. The Magnitude and the Direction of a Correlation
        2. The Definitional Formula for Pearson’s Correlation
        3. Autocorrelation and Averages
        4. Looking at Individual Cases
      2. Testing for Trend in the Observations
        1. Understanding the Meaning of ACFs
        2. Interpreting the ACFs
        3. Using the ACF Add-in
        4. Testing All the ACFs at Once
      3. Testing for Seasonality in a Time Series
        1. Removing Trend
        2. Independent Residuals
        3. Analyzing the Regression with LINEST()
        4. Applying the Models Comparison Approach
        5. Time Series Diagnostics and the Analysis of Covariance
    13. 3. Working with Trended Time Series
      1. Smoothing: The Basic Idea
        1. The Smoothing Equation’s Error Correction Form
        2. Using Excel’s Solver to Choose Alpha
        3. The Equation’s Smoothing Form
      2. About the Smoothing Approaches to Forecasting
        1. A Time Series’ Pattern
        2. Additive and Multiplicative Models
        3. Initializing Values
        4. Initializing Versus Validation
        5. Terminology Problems
        6. More Smoothing Terminology
      3. Dealing with Trend: Holt’s Linear Exponential Smoothing
        1. Dealing with Trend by Differencing
        2. Forecasting the Differences
      4. Using Holt’s Linear Exponential Smoothing
        1. Forecasting a Level
        2. Forecasting a Trend
        3. Forecasting the Next Level and the Next Trend
        4. Extending the Forecast
        5. Optimizing the Constants
        6. Going Beyond the Next-Step-Ahead Forecast
        7. Validating the Results
    14. 4. Initializing Forecasts
      1. Setting Initial Values
      2. Getting an Initial Forecast with Backcasting
      3. Getting an Initial Forecast with Optimization
        1. Initial Forecast in Simple Exponential Smoothing
        2. Initial Forecasts with Holt’s Method
        3. Understanding the Relationship Between Holt’s Method and Linear Regression
        4. About Multistart and Solver
      4. Initializing with Regression
        1. Arranging for Curvilinear Regression
        2. Deriving the Regression Equation
        3. Confirming the Regression Equation
      5. Deciding to Use Polynomial Regression
    15. 5. Working with Seasonal Time Series
      1. Simple Seasonal Averages
        1. Identifying a Seasonal Pattern
        2. Calculating Seasonal Indexes
        3. Forecasting from Simple Seasonal Averages: No Trend
        4. Simple Seasonal Averages with Trend
        5. Evaluating Simple Averages
      2. Moving Averages and Centered Moving Averages
        1. Using Moving Averages Instead of Simple Averages
        2. Understanding Specific Seasonals
        3. Aligning the Moving Averages
        4. Centered Moving Averages with Even Numbers of Seasons
        5. Detrending the Series with Moving Averages
      3. Linear Regression with Coded Vectors
        1. About Effect Coding and Regression
        2. Effect Coding with Seasons
        3. Setting Up the Coded Vectors
        4. Dummy Coding
      4. Simple Seasonal Exponential Smoothing
        1. About the Level Smoothing Formulas
        2. About the Season Smoothing Formulas
        3. Dealing with the End of the Time Series
      5. Holt-Winters Models
        1. Initializing the Forecasts
        2. Starting the Forecasts Earlier
    16. 6. Names, Addresses, and Formulas
      1. We Interrupt This Program...
        1. Implicit Intersections
        2. The Relative Range Name
      2. Establishing Names for Forecasting Formulas
        1. Establishing the Fixed Range Names
        2. Establishing the Mixed Range Names
      3. Using the Names in Forecasting Formulas
        1. Interpreting the Formula for Series Level
        2. Interpreting the Formula for the Seasonal Effect
        3. Interpreting the Forecast Formula
      4. Deriving the Error Correction Formulas
        1. The Level Equation
        2. The Trend Equation
        3. The Season Equation
        4. Demonstrating the Formulas on the Worksheet
      5. Deriving the Formulas on the Worksheet
        1. Deriving Error Correction of Level and Season Effects
        2. Deriving Error Correction of Level and Trend
      6. Named Ranges for Holt-Winters Models
        1. Estimating the Series Level in Holt-Winters Models
        2. Estimating the Current Trend
        3. Estimating the Current Seasonal Effect
      7. Error Correction Formulas for Holt-Winters Models
    17. 7. Multiplicative and Damped Trend Models
      1. About Multiplicative Models
        1. Additive Models and Multiplicative Models
        2. Why Use an Additive Model?
      2. Multiplicative and Additive Models Compared
        1. Comparing the Level Estimates
        2. Comparing the Estimates of Seasonal Effects
        3. Producing the Next Forecast
        4. Using the Error Correction Formulas
      3. Additive and Multiplicative Seasonal Models in Holt-Winters Analysis
        1. Holt-Winters Additive Models Revisited
        2. Multiplicative Holt-Winters Models
        3. Changing the Initial Seasonal Estimates
        4. Modifying the Level and Seasonal Smoothing Formulas
        5. Modifying the Trend Smoothing Formula
        6. The Multiplicative Model with Error Correction Formulas
      4. Damped Trend Forecasts
        1. Formulas for the Damped Trend
        2. Implementing the Damped Trend on the Worksheet
    18. Index
    19. Code Snippets