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...'© BrainMass Inc. brainmass.com October 17, 2018, 1:57 am ad1c9bdddf
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.
Regression Analysis - Using PHStat or Excel
 A research analyst for an oil company wants to develop a model to predict miles per gallon based on highway speed. An experiment is designed in which a test car is driven at speeds ranging from 10 miles per hour to 75 miles per hour. The results are in the data set (SPEED.xls).
a) Set up a scatter diagram for speed and miles per gallon.
b) Apply simple regression analysis, and then interpret the meaning of the slope b1 in this problem.
c) Interpret the meaning of the regression coefficient b0 in this problem.
d) Determine the coefficient of determination, r2, and interpret its meaning.
e) How useful do you think this regression model is for predicting mileage?