Introduction to the 2013 Edition . 1 You, Analytics, and Excel .2 Excel as a Platform .4 What''s in This Book .4 Introduction to this Edition . 7 Inside the Black Box .8 Helping Out Your Colleagues .8 1 Building a Collector .
11 Planning an Approach .12 A Meaningful Variable .12 Identifying Sales .13 Planning the Workbook Structure .13 Query Sheets .13 Summary Sheets .18 Snapshot Formulas .20 Customizing Your Formulas .
21 The VBA Code .23 The DoItAgain Subroutine .24 The DontRepeat Subroutine .25 The PrepForAgain Subroutine .25 The GetNewData Subroutine .26 The GetRank Function.30 The RefreshSheets Subroutine .32 The Analysis Sheets.
33 Defining a Dynamic Range Name .34 Using the Dynamic Range Name .36 2 Linear Regression .39 Correlation and Regression .39 Charting the Relationship .40 Calculating Pearson''s Correlation Coefficient .43 Correlation Is Not Causation .45 Simple Regression .
46 Array-Entering Formulas .48 Array-Entering LINEST( ) .49 Multiple Regression .49 Creating the Composite Variable .50 Entering LINEST( ) with Multiple Predictors .51 Merging the Predictors .51 Analyzing the Composite Variable .53 Assumptions Made in Regression Analysis .
54 Variability .55 Measures of Variability: Bartlett''s Test of Homogeneity of Variance .57 Means of Residuals Are Zero .58 Normally Distributed Forecasts .59 Using Excel''s Regression Tool .59 Accessing the Data Analysis Add-ln .59 Accessing an Installed Add-ln .60 Running the Regression Tool .
61 Understanding the Regression Tool''s Dialog Box .62 Understanding the Regression Tool''s Output .64 3 Forecasting with Moving Averages .71 About Moving Averages .71 Signal and Noise .72 Smoothing Out the Noise .73 Lost Periods .74 Smoothing Versus Tracking .
74 Weighted and Unweighted Moving Averages .76 Total of Weights .77 Relative Size of Weights .78 More Recent Weights Are Larger .78 Criteria for Judging Moving Averages .80 Mean Absolute Deviation .80 Least Squares .80 Using Least Squares to Compare Moving Averages .
81 Getting Moving Averages Automatically .82 Using the Moving Average Tool .83 Labels .85 Output Range .85 Actuals and Forecasts .85 Interpreting the Standard Errors-Or Failing to Do So .87 4 Forecasting a Time Series: Smoothing .89 Exponential Smoothing: The Basic Idea.
90 Why "Exponential" Smoothing? .92 Using Excel''s Exponential Smoothing Tool .95 Understanding the Exponential Smoothing Dialog Box .96 Choosing the Smoothing Constant .102 Setting Up the Analysis .103 Using Solver to Find the Best Smoothing Constant .105 Understanding Solver''s Requirements .110 The Point .
113 Handling Linear Baselines with Trend .114 Characteristics of Trend .114 First Differencing .117 5 More Advanced Smoothing Models .123 Holt''s Linear Exponential Smoothing .123 About Terminology and Symbols in Handling Trended Series .124 Using Holt''s Linear Smoothing .124 Holt''s Method and First Differences .
130 Seasonal Models .133 Estimating Seasonal Indexes .134 Estimating the Series Level and First Forecast .135 Extending the Forecasts to Future Periods .136 Finishing the One-Step-Ahead Forecasts .137 Extending the Forecast Horizon .138 Using Additive Holt-Winters Models .140 Level .
143 Trend .143 Season .144 Formulas for the Holt-Winters Additive and Multiplicative Models.145 Formulas for the Additive Model .146 Formulas for the Multiplicative Model .148 The Models Compared .149 Damped Trend Forecasts .151 6 Forecasting a Time Series: Regression .
153 Forecasting with Regression .153 Linear Regression: An Example .155 Using the LINEST( ) Function .158 Forecasting with Autoregression.164 Problems with Trends .164 Correlating at Increasing Lags .165 A Review: Linear Regre.