Exercise #8

October 3, 2013

Due Thu, Oct 10

Step 1.

Using the same set of data from Exercise #7 (i.e., 30+ property records), calculate the following. Use standard Excel functionality as you did in Exercise 6 and 7:

• Sum of Squares Error (SSE) – see equation on p 490
• Standard Error of the Estimate – see equation on p. 491
• Coefficient of Determination (r-squared) – see equation on p. 494

Step 2.

Use the “Data Analysis” ToolPak in Excel to run a regression analysis. You will be prompted to enter your y values (dependent variable) and your x values (independent variable). If you select these two columns correctly and run the regression it will produce a new worksheet with regression output. Check to see if your Coefficient of Determination from Step 1 matches the r-squared value reported in the regression output. It should be very close but perhaps not exactly the same due to rounding error. If the two values are far apart it would likely indicate an error along the way. In that case, go back and check your calculations.

Step 3.

Provide 4 value estimates for an average home in your assigned neighborhood using various sizes (in square feet) including:

• 1000 sq ft
• 1500 sq ft
• 2000 sq ft
• 2500 sq ft

If you did not use square footage then simply generate estimated values over a range of numbers relevant for you independent variable. For example, if you used total number of rooms then use 4, 5, 6, 7 rooms or something similar representing the range of likely numbers.

Step 4.

Write a 1-page analysis explaining what you’ve done since the beginning of the Pueblo Real Estate project. Write a report that you might submit to a group of local real estate professionals. Use your own words to write a business memo that answers the following questions:

1. Describe the analysis completed using easy to understand non-technical terms.
2. What will this analysis allow you to do? How might you be able to help a real estate agent or someone looking to purchase a single-family home?
3. What observations can you make about your assigned Pueblo neighborhood?

Step 5.

• Print a 1 page worksheet with all calculations from steps 1-3.
• Print your 1-page analysis written in Step 4.
• Be sure your name is on both pages and keep them separate (don’t staple).
• Turn in these 2 pages on Thursday, Oct 10

Video Help:

I’m intentionally not providing a step-by-step video guide to this exercise. I want you to think about these calculations and not just copy what I do. Instead please use these two video resources that explain how to calculate the Standard Error of the Estimate and the Coefficient of Determination (or r-squared). With these videos, the textbook and the previous exercises and accompanying videos you should have what you need to complete the assignment. If you are confused after reviewing all these materials, simply do the best you can – I will grade based on effort more than numerical accuracy.

Standard Error of the Estimate Video: 

R-Squared Video: 

It wouldn’t hurt to watch all of the videos, including these two, associated with the Linear Regression playlist on the same YouTube channel.