On the data file find the first worksheet concert sales it

Question

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 Tool Pak. 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 (=2¹⁰-1) regression models should be investigated. When there are 100 variables, total 1267650600228230000000000000000-1 (=2¹⁰-1) models are possible. Furthermore, if we include interaction terms (e.g. X₁X₂) or nonlinear terms (e.g. X₁², 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 a=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. Analysis toolpak accepts data only in a single range. So you have to copy the data in a new worksheet and remove the chosen variable data by deleting its column. Then, you can choose all X variables in a range on 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 informationto the dependent variable in the model. It can generate unstable coefficients or an unexpected sign of coefficient b_j. For example, if diameter and height of trees are included in a regression model to predict age of trees, that is,

Tree Age = b₀ + b₁(Diameter) + b₂(Height),

we can easily expect that b₁ and b₂ 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 b₁ or b₂. 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 r². A good model has a high r². But a high r²does not guarantee a good model. As a remedy, we use the adjusted r² to compare the quality of linear regression models. Based on the adjusted r², 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?