5. Copy the first sheet in QMB3200-Homework#8Data.xlex called "HomePrices" to your file. This sheet has some data on some homes' appraised values and selling prices and some other fields. a. Find the correlation coefficient for Selling Price and Appraised Value, Selling Price and Square Feet, Selling Price and Bedrooms and Selling Price and Bathrooms Which independent variable is most strongly related to selling price. (Use the Excel function =CORREL() as shown in the chapter word document to find correlation coefficient between two variables). b. Create a scatter plot of Selling Price (on y axis) and Appraised Value (x axis). Interpret the chart. Talk about whether the relationship is linear or not, strong or not. c. Mentally fit a straight line through the above scatter plot and guess the y-intercept and the slope. d. Add a trendline to the scatterplot. Also, display the equation of the trendline. e. How close was your guess in part d above. f. Using the data Analysis Toolpak, run Regression between Appraised Value (independent variable) and Selling price (dependent variable). Use the default 95% confidence interval. What is the R-square value? Interpret this R-square value. h. Predict the selling price of a house whose appraised value is 150,000. Predict the selling price of a house whose appraised value is 125,000 j. Create a model using Num of Bedrooms as the independent variable and predict the value of a house with 4 bedrooms. 6. Copy the second sheet in QMB3200-Homework#8Data.xlex called "Top 50 MBA Programs" to your file. This data is according to US News and World Report, 2009 survey. a. Which of the variables in columns E through J are most highly correlated with Overall Rating? (Use the Excel function =CORREL( ) as shown in the chapter word document to find correlation coefficient between two variables). b. Run a simple regression with the variable identified above and Overall Rating. c. What percent of variation in overall rating explained by the variable identified in a? 1. Which of the remaining variables in columns E through J is most highly correlated to Overall Rating? e. Run a regression with the second most correlated variable as independent variable to predict the Overall rating. f. What percent variation is explained by the variable identified above? 7. An important application of regression analysis in accounting is in the estimation of cost. By collecting data on volume and cost and using the least squares method to develop an estimated regression equation relating volume and cost, an accountant can estimate the cost associated with a particular manufacturing volume. Sample data on production volumes and total cost data for a