More Predictive Analytics: Microsoft Excel

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