USING EXCEL
Copy the first sheet in QMB3200-Homework#8Data.xlsx 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.
g. 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.
i. 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.
