Smart-Money magazine evaluated 65 metropolitan areas to determine where home values are headed. An ideal city would get a score of 100 if all factors measured were as favorable as possible. Areas with a score of 60 or greater are considered to be primed for price appreciation, and areas with a score of below 50 may see housing values erode. Two of the factors evaluated were the recession resistance of the area and its affordability. Both of these factors were rated using a scale ranging from 0 (low score) to 10 (high score). All this data is contained in the 'Home Values' dataset in the attached file below.
1. Develop an estimated regression equation that can be used to predict a metropolitan area's score given the recession resistance. Discuss your findings. Be sure to report on the regression equation, goodness of fit, and relationship between the independent and dependent variables. Does a significant regression relationship exist? Use ±=0.01 for your hypothesis test.
2. Construct a scatter plot showing the relationship between each independent variable and the dependent variable. Include the regression equation, r2 value, and trend line. Which independent variable explains the most variance? Describe the relation between each independent variable and the dependent variable.
3. Develop an estimated regression equation using both independent variables to predict score values. Is it a better fit compared to your answer in part (1)? Does any multicollinearity exist? What conclusions and recommendations can you derive from F and t tests? Use ±=0.01 for your hypothesis test.
4. With the multiple linear regression equation derived in part (3), what is the predicted score when recession resistance is equal to 5 and affordability is equal to 7? Interpret the meaning of each coefficient.
5. Are there any possible modifications you could suggest for a better-fit regression model? Adding/dropping of independent variables? Include at least two recommendations based on the results of your analysis. Justify your recommendations using the results of your statistical analysis.
This is a statistical report, not simply a collection of different types of Excel output. It is not necessary to include the formulas you used or a copy of the dataset. When answering the questions above, make sure to include any relevant statistics and/or the results of your calculations. Like any other written report, you will want to start with an introductory paragraph or problem statement, and finish with a conclusion that summarizes the information presented.
When answering the questions, make sure to double-check your calculations. It is also important to fully address the question being asked. Be sure to include your results as well as a detailed explanation/interpretation of the information. Statistical analysis includes not just the ability to obtain the correct results, but also the ability to describe and interpret what the results mean. Instead of simply saying, 'Variable X has more variation', use your statistics to support your conclusions, 'Variable X has more variation because...'
The solution provides step-by-step method of performing a Regression Analysis and Hypothesis Test in EXCEL. All the steps of hypothesis testing (formulation of null and alternate hypotheses, selection of significance level, choosing the appropriate test-statistic, decision rule, calculation of test-statistic and conclusion) have been explained and the Regression Analysis has been shown in details.