Question

Assume for a moment that these 20 houses made up the entire population of houses in San Antonio. Use the Data Analysis Sampling function to choose a random sample of 7 house prices from the population. Put a label called "Sample of 7" over the list you create. 4. 3. Highlight all the data, including both Square Footage and Price, and use the Insert Scatter function to create a Scatter Diagram. Change the title and add a linear trend line to the diagram. S. Use the Data Analysis Correlation tool to find the correlation coefficient between Square Footage and Price. What is the correlation coefficient? 6. Use the Data Analysis Regression tool to run a regression. First, determine which variable is the Independent Variable, x, and which is the Dependent Variable, y. There is only one logical answer for this problem. (Think about this carefully. A common mistake students make is to get this backwards, which is called Reverse Causality.) Remember that the Excel tool requires you to enter the y variable first and then the x variable. Check the box to calculate residuals. Write the estimated regression equation in your spreadsheet and highligh i. (Extra Credit: Try to figure out how to use the Insert Equation function in Excel to get the y-hat to look like 9) 7. What is the value of r for this regression? 8. Comment on the residual plot. Do the regression assumptions appear to be satisfied? 9. What is the result of the hypothesis test for Ho: B0? Is there a statistically significant relationship between the variables? 10. Give the 95% confidence interval for the value of β. 11. Give the 95% confidence interval for the value of β: in business terms. Square Footage Price (S) 1580 142,500 1572 145,000 1352 115,000 2224 155,900 1556 95,000 1435 128,000 1438 100,000 1089 55.000 1941 142,000 1698 115,000 1539 115,000 1364 105.000 1979 155.000 2183 132,000 2096 140,000 1400 85,000 2372 145,000 1752 155,000 1386 80,000 1163100,000

Answer 1

(3) following 7 unit is selected using sampling function in excel data analysis tool kit

S.N.	square footage	price
1	1580	142500
16	1400	85000
12	1364	105000
3	1352	115000
9	1941	142000
2	1572	145000
11	1539	115000

(4) the scatter plot is given as

160000 140000 y76.44x 3979 R2-0.469 120000 100000 80000 60000 40000 20000 500 1000 1500 2500 square footage

(5) the correlation between square footage and price is =r=0.6851

(6) using price as dependent variable (y) and square footage (x) as independent varaibale

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.685149
R Square	0.469429
Adjusted R Square	0.363315
Standard Error	18143.98
Observations	7
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1456337259	1.46E+09	4.423814	0.08938
Residual	5	1646019884	3.29E+08
Total	6	3102357143
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	3979.11	56226.7391	0.070769	0.946325	-140556	148514.5
X Variable 1	76.44643	36.34617945	2.103287	0.08938	-16.9844	169.8773

(7)r2=0.4694

(8)residual plot showed , assumption is satisfied as the points are scattered

X Variable1 Residual Plot 30000 20000 10000 1590 2000 2500 500 100015002000 2500 10000 20000 X Variable 1

(9) since the p-value =0.0894 of variable x is more than typical level of significance alpha=0.05, so we accept the null hypothesis H0:$\beta =0$ and conclude that there is not statistically significant relationship

(10) the 95% confidence interval is = (-16.98, 169.88)

(11) this interval says that if one unit in square footage(x)  changes there will be changes of price (y) between -16.98 to 169.88 unit