Conrad Carlberg
©2018 |Que Publishing | Available
Introducing Pearson+ 1500+ eTexts and study tools, all in one place. Subscriptions starting at $9.99/month.
K-12 educators: This link is for individuals purchasing with credit cards or PayPal only. Contact your Savvas Learning Company Account General Manager for purchase options.
Microsoft Excel MVP Conrad Carlberg shows readers 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.
Readers will get an extensive collection of downloadable Excel workbooks that can be easily adapted to their own unique requirements, plus VBA code—much of it open-source—to streamline several of this book’s most complex techniques.
Step by step, readers build on Excel skills they already have, learning advanced techniques that can help them increase revenue, reduce costs, and improve productivity.
Chapter 1 in the first edition of the proposed book concerned itself with how to use
Excel and VBA to download periodically (e.g., by second, by minute, by hour, by day, etc.) sales and related information from web sites. This information did not get much comment in Amazon reviews (there are 42 reviews as of November 2016) and what comment there was split itself between those who like to know how to do that sort of data acquisition, and those whose background in VBA is far too sketchy to understand what was going on. In the interim, many such sites have instituted bot blockers that refuse to complete a query not sent by a browser. I think that it makes sense to drop this chapter but could be talked into retaining it.
The existing Chapter 4 contains information on two types of exponential smoothing: simple smoothing and Holt's linear smoothing, which takes account of trend in a time series. I would expect to expand the chapter to provide more information on Holt's method and add a discussion of Holt-Winters smoothing, which accounts for seasonality in a time series. I might add some material on damped trend forecasts, which help prevent trends from getting out of control beyond the normal forecast horizon.
I also intend to add material to the existing chapter 7 on more advanced issues in logistic regression. At present, the discussion covers situations in which the outcome variable has two values only. Logistic regression also deals with situations in which the outcome variable has 3 or more categorical values. That's a considerably more complex situation. It's difficult to illustrate in Excel and the R syntax to deal with it is esoteric. But the situation arises frequently, particularly in consumer choice situations, and it should get coverage in this book.
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 Regression and Autoregression ..............................168
Adjusting the Autocorrelation Formula ....169
Using ACFs .........171
Understanding PACFs ...............................172
Using the ARIMA Workbook .....................178
7 Logistic Regression: The Basics...................181
Traditional Approaches to the Analysis ..........181
Z-tests and the Central Limit Theorem .....181
Sample Size and Observed Rate ...............183
Binomial Distribution ..............................183
Only One Comparison ..............................184
Using Chi-Square .....................................185
Preferring Chi-Square to a Z-test .............187
Regression Analysis on Dichotomies .............191
Homoscedasticity ....................................191
Residuals Are Normally Distributed ........194
Restriction of Predicted Range ................194
Ah, But You Can Get Odds Forever .................195
Probabilities and Odds .............................195
How the Probabilities Shift .....................197
Moving On to the Log Odds ....................200
8 Logistic Regression: Further Issues .............203
An Example: Predicting Purchase Behavior ....204
Using Logistic Regression ........................205
Calculation of Logit or Log Odds ..............213
Comparing Excel with R: A Demonstration .....228
Getting R ...........229
Running a Logistic Analysis in R ..............229
Importing a csv File into R .......................230
Importing From an Open Workbook Into R .......................................233
Understanding the Long Versus Wide Shape ....................................234
Running Logistic Regression Using glm ...235
Statistical Tests in Logistic Regression ............240
Models Comparison in Multiple Regression ......................................240
Calculating the Results of Different Models ......................................241
Testing the Difference Between the Models .....................................242
Models Comparison in Logistic Regression .......................................243
9 Multinomial Logistic Regression ................253
The Multinomial Problem ..............................253
Three Alternatives and Three Predictors .........254
Three Intercepts and Three Sets of Coefficients .................................256
Dummy Coding to Represent the Outcome Value .............................256
Calculating the Logits ..............................256
Converting the Logits to Probabilities ......257
Calculating the Log Likelihoods ...............258
Understanding the Differences Between the Binomial and Multinomial Equations ...............258
Optimizing the Equations ........................260
Benchmarking the Excel Results Against R ....261
Converting the Raw Data Frame with mlogit.data ...................262
Calling the mlogit Function .................264
Completing the mlogit Arguments ......266
Four Outcomes and One Predictor ..................267
Multinomial Analysis with an Individual-Specific Predictor ..............269
Multinomial Analysis with an Alternative-Specific Predictor ............272
10 Principal Components Analysis ..................275
The Notion of a Principal Component ............275
Reducing Complexity ...............................276
Understanding Relationships Among Measurable Variables .............277
Maximizing Variance................................278
Components Are Mutually Orthogonal ....280
Using the Principal Components Add-In ........281
The R Matrix ......284
The Inverse of the R Matrix ......................284
Matrices, Matrix Inverses, and Identity Matrices ...............................287
Features of the Correlation Matrix’s Inverse ......................................288
Matrix Inverses and Beta Coefficients ......290
Singular Matrices .....................................293
Testing for Uncorrelated Variables ...........293
Using Eigenvalues ....................................295
Using Component Eigenvectors ...............296
Factor Loadings .299
Factor Score Coefficients ..........................299
Principal Components Distinguished from Factor Analysis ......................303
Distinguishing the Purposes ....................303
Distinguishing Unique from Shared Variance ....................................303
Rotating Axes ....305
11 Box-Jenkins ARIMA Models ........................307
The Rationale for ARIMA ................................307
Deciding to Use ARIMA ............................308
ARIMA Notation .308
Stages in ARIMA Analysis ...............................310
The Identification Stage .................................310
Identifying an AR Process ........................310
Identifying an MA Process .......................313
Differencing in ARIMA Analysis ................315
Using the ARIMA Workbook .....................320
Standard Errors in Correlograms ..............321
White Noise and Diagnostic Checking......322
Identifying Seasonal Models ....................323
The Estimation Stage .....................................324
Estimating the Parameters for ARIMA(1,0,0) ....................................324
Comparing Excel’s Results to R’s ...............326
Exponential Smoothing and ARIMA(0,0,1) .......................................329
Using ARIMA(0,1,1) in Place of ARIMA(0,0,1) ...................................332
The Diagnostic and Forecasting Stages ..........333
12 Varimax Factor Rotation in Excel ................335
Getting to a Simple Structure .......................335
Rotating Factors: The Rationale ...............336
Extraction and Rotation: An Example ......339
Structure of Principal Components and Factors ......................................344
Rotating Factors: The Results ..................345
Charting Records on Rotated Factors ......348
Using the Factor Workbook to Rotate Components ..........................350
9780789758354, ToC, 6/30/2017
Pearson offers affordable and accessible purchase options to meet the needs of your students. Connect with us to learn more.
K12 Educators: Contact your Savvas Learning Company Account General Manager for purchase options. Instant Access ISBNs are for individuals purchasing with credit cards or PayPal.
Savvas Learning Company is a trademark of Savvas Learning Company LLC.
Carlberg
©2018 | Que Publishing | 384 pp
Conrad Carlberg (www.conradcarlberg.com) is a nationally recognized expert on quantitative analysis and on data analysis and management applications such as Microsoft Excel, SAS, and Oracle. He holds a Ph.D. in statistics from the University of Colorado and is a many-time recipient of Microsoft’s Excel MVP designation.
Carlberg is a Southern California native. After college he moved to Colorado, where he worked for a succession of startups and attended graduate school. He spent two years in the Middle East, teaching computer science and dodging surly camels. After finishing graduate school, Carlberg worked at US West (a Baby Bell) in product management and at Motorola.
In 1995, he started a small consulting business that provides design and analysis services to companies that want to guide their business decisions by means of quantitative analysis—approaches that today we group under the term “analytics.” He enjoys writing about those techniques and, in particular, how to carry them out using the world’s most popular numeric analysis application, Microsoft Excel.
We're sorry! We don't recognize your username or password. Please try again.
The work is protected by local and international copyright laws and is provided solely for the use of instructors in teaching their courses and assessing student learning.
You have successfully signed out and will be required to sign back in should you need to download more resources.