| |
| |
| Introduction | |
| |
| |
| |
| 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 DoltAgain 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 | |
| |
| |
| |
| 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 | |
| |
| |
| Variability | |
| |
| |
| Using Excel's Regression Tool | |
| |
| |
| Accessing the Data Analysis Add-In | |
| |
| |
| Running the Regression Tool | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| Homoscedasticity | |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| |
| 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 | |
| |
| |
| Index | |