Simple Regression
Sales vs. Ads.
On the data file, find the first worksheet, “Concert Sales.” It contains data of Concert sales ($1000s), the number of radio & TV ads, and the number of newspaper ads.
1) Develop linear regression models enumerating all three possible combinations of two independent variables as listed below. Under each combination, report results. You may copy and paste the results from Analysis Toolpak. Then, briefly judge whether the models are significant using t-test. Note that we generally ignore the p-value of the intercept. The below first two models have example solutions. Refer to them for other similar types of questions.
a) Sales vs. Radio&TV
All variables are significant.
| Regression Statistics | ||||||
| Multiple R | 0.696608416 | |||||
| R Square | 0.485263285 | |||||
| Adjusted R Square | 0.459526449 | |||||
| Standard Error | 254.0524474 | |||||
| Observations | 22 | |||||
| ANOVA | ||||||
| df | SS | MS | F | Significance F | ||
| Regression | 1 | 1216939.671 | 1216939.671 | 18.85481532 | 0.000316108 | |
| Residual | 20 | 1290852.92 | 64542.64601 | |||
| Total | 21 | 2507792.591 | ||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
| Intercept | 699.9571823 | 132.5217655 | 5.281828082 | 3.60821E-05 | 423.5216244 | 976.3927402 |
| Radio&TV ads | 12.1620442 | 2.80088602 | 4.342213182 | 0.000316108 | 6.319498357 | 18.00459004 |
b) Sales vs. Newspaper
Newspaper is not significant.
| Regression Statistics | ||||||
| Multiple R | 0.264271891 | |||||
| R Square | 0.069839632 | |||||
| Adjusted R Square | 0.023331614 | |||||
| Standard Error | 341.5149542 | |||||
| Observations | 22 | |||||
| ANOVA | ||||||
| df | SS | MS | F | Significance F | ||
| Regression | 1 | 175143.3125 | 175143.3125 | 1.501668632 | 0.234650477 | |
| Residual | 20 | 2332649.278 | 116632.4639 | |||
| Total | 21 | 2507792.591 | ||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | |
| Intercept | 877.2432432 | 293.0840043 | 2.993146096 | 0.007185881 | 265.8807251 | 1488.605761 |
| Newspaper ads | 10.20486486 | 8.327606655 | 1.225425898 | 0.234650477 | -7.166218171 | 27.5759479 |
c) Sales vs. Radio&TV and Newspaper
2) Which model would like to choose? Answer with reasons.
3) Observe the data. The last two observations have zero advertisements on Radio&TV. Other values of Radio&TV are in a range between 25 and 70. So it may not be appropriate to include those two observations on regression models with Radio&TV because those outliers may distort the relationship between Sales and Radio&TV. As you did in question 1, develop linear regression models with the below two combinations. The model of (Sales vs. Newspaper) was considered with all available data in the previous question. Report and judge the model as question 1.
a) Sales vs. Radio&TV without the last two observations
b) Sales vs. Radio&TV and Newspaper without the last two observations
4) Which model would like to choose in the five models you made? Answer with reasons.
5) For the model you chose in question 4, make a more detailed report. Check assumptions. Interpret coefficients. Describe your findings.
Bank balances
It is ideal to choose the best model by enumerating all possible regression models. However, it is computationally heavy when there are many candidate independent variables with many observations. For example, when there are 10 candidate independent variables, total 1023 (=210-1) regression models should be investigated. When there are 100 variables, total 1267650600228230000000000000000-1 (=210-1) models are possible. Furthermore, if we include interaction terms (e.g. X1X2) or nonlinear terms (e.g. X12, sqrt(X1)), the number of models to be enumerated exponentially grows. In short, enumeration is ideal, but impossible or expensive in practice.
Consider this Bank balance case. The data on the second worksheet, “Banking Data” is acquired from banking and census records for different zip codes in the bank’s current market. Such information would be useful when advertising for new customers or choosing locations for new brank offices. The data show the medians of age of population, years of education, income, home value, household wealth, and average bank balance. Our goal is to a build a model that predicts the bank balance with proper variables.
1) Make a model that predicts the average bank balance with all available independent variables (Age, Education, Income, Home value, and Wealth). Is the model appropriate? Answer with reasons and regression model results.
2) In question 1, you should find that the model is not appropriate because two variables are not significant at α=5%. How do we make a better and significant model? Do we simply drop all those insignificant variables and make a new model? The answer is “no” simply because we do not know what will happen when a single or multiple variables are dropped or added until we actually try all those models. One simple idea is dropping the most insignificant variable and making a new model. Then, keep repeating the trials until a significant model is found. This is called the backward stepwise regression modeling.
Choose a variable that has the highest p-value of t-test in the question 1 model, and make a new model without it. When you select x variables in Analysis toolpak, all x variables should be together in a range. So, you need to copy and collect the selected x variables in a separate place on Excel. Then, you can select the selected variables in a range in Analysis toolpak.
Report whether the model is significant. If you found that the model is not significant, then drop a variable that has the highest p-value of t-test and make a new model. Repeat this until you find a significant model.
3) In multiple linear regression modeling, one issue that has to be carefully considered is multicollinearity. We did not consider this issue in the class.
A multiple linear regression model includes multiple independent variables. Two or multiple variables are sometimes correlated each other. For example, diameter and height of a tree may have a relationship. If those correlated variables are used for X variables in a regression model, those variables contribute redundant information to the dependent variable in the model. It can generate unstable coefficients or an unexpected sign of coefficient bj. For example, if diameter and height of trees are included in a regression model to predict age of trees, that is,
Tree Age = b0 + b1(Diameter) + b2(Height),
we can easily expect that b1 and b2 should be positive because trees grow as it gets more ages. However, when diameter and height have a significant relationship, it would be possible to have negative values of b1 or b2. Or the coefficients of the model drastically fluctuate when new observations or new independent variables are added. Thus, we have to drop one or multiple correlated variables in the model. You remember one of the assumption of regression modeling is independency of errors. It means this issue.
Then, how do we detect which variables are correlated each other? There are many techniques to reveal the multicollinearity, but an easiest way is checking the correlation. The correlation measures the linear relationship between two variables. Its value is between -1 and 1. As the value is closer to 1, two variables have a stronger positive linear relationship. Reversely, as the value is closer to -1, two variables have a stronger negative linear relationship. As the value is closer to zero, it indicates two variables have a weaker relationship, which is the case we want to have in a regression model.
Consider this correlation table of the variables. You can easily get it using Correlation in Analysis toolpak. Try if you want.
| Age | Education | Income | Home Value | Wealth | Bank Balance | |
| Age | 1 | |||||
| Education | 0.17 | 1 | ||||
| Income | 0.48 | 0.58 | 1 | |||
| Home Value | 0.39 | 0.75 | 0.80 | 1 | ||
| Wealth | 0.47 | 0.47 | 0.95 | 0.70 | 1 | |
| Bank Balance | 0.57 | 0.55 | 0.95 | 0.77 | 0.95 | 1 |
Bank Balance (the dependent variable Y) has high linear relationships with most variables. Especially, Income and Wealth have the highest linear relationship. This indicates that Income and Wealth would contribute to explain Bank Balance well.
To avoid problems from multicollinearity in linear regression modeling, all independent variables should have no or weak correlations (close to zero) each other. But look at the correlation between Income and Wealth. Those two variables have a very high correlation of 0.95. If those two variables were in a multiple linear regression model, those variables ruin the model having troubles discussed above with the tree example.
For the reason, we have to find models excluding one or both of Wealth and Income. Try to make linear regression models that do not include Income and Wealth together. Some models are suggested on the next question. Report all your trials and determine whether models are significant.
4) Summarize all the models you made for Bank balance models filling the below table. You do not have to make all the suggested models. You may try other models. Add more rows if you need.
| Adj-r2 | X variables | Problems in the model | |
| Model 1 | 0.944 | All variables | Education and Home value are not significant |
| Model 2 | 0.944 | Without Home value | Multicollinearity between Income and Wealth |
| Model 3 | Without Home value, Wealth | ||
| Model 4 | Without Home value, Income | ||
| Model 5 | Without Wealth | ||
| Model 6 | Without Income | ||
| Model 7 | Without Income and Wealth | ||
| Model 8 | Without Income, Wealth, Home value | ||
| Model 9 |
5) Probably, you have made a couple of significant models that do not have multicollinearity. Then, which model do we have to choose? As we discussed in the class, adding any good or bad variables in a model increases or do not change r2. A good model has a high r2. But a high r2 does not guarantee a good model. As a remedy, we use the adjusted r2 to compare the quality of linear regression models. Based on the adjusted r2, choose your best significant model and describe it.
6) In regression modeling, there is a principle known as parsimony; the simpler the model is, the better. If you found multiple models that provide adequate interpretations of the dependent variable at a certain quality, it would be better to choose a simpler model (few number of X variables). Taking into account the parsimony principle, choose your best model, make detailed report, check assumptions, and interpret coefficients. Finally, describe your findings and analysis about bank balances. How can the bank use the model and data?