Project Description: The present study investigates the relationship between living area as measured in square feet of a home and the sale price of the home. We will do this by providing a scatterplot of the relationship where living area may be used to estimate selling price. We will add the ‘least squares regression line (trendline) and the R2 value using Excel’s scatter graph option. The data we are using was collected from real estate values obtained in an upstate New York region in 2000. The variables collected are: 1) Price – Recorded in dollars; 2) Lot size – measured in acres; 3) Living Area – measured in square feet; 4) Pct College – percent of students going to college; 5) Central Air – No = 0, Yes = 1; 6) Bedrooms – Number of bedrooms; 7) Fireplace – Number of fireplaces; 7) Bathrooms – Number of bathrooms. For the purpose of this project, you will only be using the variables, Price and Living Area.
Grader – Instructions Excel 2016 Project
Linear Relationship V2
Project Description:
Project Description: The present study investigates the relationship between living area as measured in square feet of a home and the sale price of the home. We will do this by providing a scatterplot of the relationship where living area may be used to estimate selling price. We will add the ‘least squares regression line (trendline) and the R2 value using Excel’s scatter graph option. The data we are using was collected from real estate values obtained in an upstate New York region in 2000. The variables collected are: 1) Price – Recorded in dollars; 2) Lot size – measured in acres; 3) Living Area – measured in square feet; 4) Pct College – percent of students going to college; 5) Central Air – No = 0, Yes = 1; 6) Bedrooms – Number of bedrooms; 7) Fireplace – Number of fireplaces; 7) Bathrooms – Number of bathrooms. For the purpose of this project, you will only be using the variables, Price and Living Area.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Excel. Download and open the workbook named:
Linear_Relationship_Start.
0
2
In cell C6 insert a Scatter Chart for the Price versus Living Area data from the Data worksheet.
Inserting Chart
Select the Scatter chart from the provided chart options in the Charts group of the Insert tab of the Ribbon.
Selecting Data Series
Then choose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add new series using the Add button. Select the range of data on the Data worksheet. Note that Price should stand for the Y values and Living Area is the X values. Do not add the series name.
Note: Do not include the titles of the columns to the range of data.
Edit Chart Elements
Go to the Add Chart Elements dropdown list in the Design tab of the Ribbon. Delete the legend. Above the
chart, add the title Predicted sales price, y, for given living area, x, as the chart title. On the Ribbon, select design Style 1.
Chart Size and Position
Go to the Format tab on the Ribbon. Set the chart height and width, and then drag it to position th
e entire chart so that it fits within cell C6.
2
3
Add the trendline to the data on the chart.
Adding Linear Trendline
Select any point on the chart and right click on it. Select Add Trendline. In the Trendline Options window select Linear with automatic
trendline name.
Trendline Options
In the Trendline Options window check the
“Display equation on chart” and “Display R-squared value on chart” boxes. You can grab the added equation and R-squared value and drag it to any place on the chart so that it is more visible to read.
2
4
Use the regression model of Data Analysis for the data.
Adding Regression Model
Go to the
Data worksheet. Select the Data Analysis in the Analysis group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have in the Data tab already). Select Regression and click OK. Select the cells in the Price column as the Input Y Range and the cells in the Living Area column as the Input X Range. Choose cell J1 on the Data worksheet as the output range. Do not check any additional boxes in the regression model menu.
Note: Do not include the titles of the columns to the range of data.
On the
14-21 worksheet, identify the value of the correlation coefficient, r, between living area and selling price to four decimal places in cell D9.
4
5
Interpret the value of the correlation coefficient, r, in terms of direction and strength of the linear model. Choose the correct answers from the drop-down menus in cells D11 and D12.
1
6
In cells E20 and G20 complete the least squares equation. Round the y-intercept to the nearest integer and the slope to two decimal places.
2
7
Identify the value of the y-intercept, b0, in cell D22. Keep the same number of decimal places as the value in the equation above.
1
8
Interpret the y-intercept, b0, in terms of the data. Choose the correct answers from the drop-down menus in cells D24 and H24.
1
9
In cell D26 decide whether the interpretation of the y-intercept is a meaningful estimate in the context of this problem.
1
10
Identify the value of the slope, b1, in cell D28. Keep the same number of decimal places as the value in the equation above.
1
11
Interpret the slope, b1, in terms of the data. Choose the correct answers from the drop-down menu in cell D30. Round the amount in cell G30 to the nearest cent.
1
12
Identify the value of R2 in cell D32. Round the percent to two decimal places.
2
13
Calculate the expected sale price of a 1500 square foot house in cell D37 using the value(s) found in step 6 for the least squares regression equation. Round the value to the nearest dollar.
1
14
Calculate the expected price increase for an additional 200 square feet in cell D40 using the value(s) found in step 6 for the least squares regression equation. Round the value to the nearest dollar.
1
15
Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0
Total Points
20
Created On: 08/22/2019 1 Linear Relationship V2
