In the Excel Lab #5 Data File, you will find raw data on sheet 1. However, to simplify your calculations, you have only been asked to test a random sample of 10 homes. So, your first step will be to randomly select data from 10 of the homes and paste them into sheet 2 called "sample data". This will be the data that you will analyze. (You can generate 10 random numbers between 5 and 30 however you want for your sample, but know that there is essentially no chance two students should have the same 10 samples).

Within the Excel spreadsheet, use the Excel tools/functions to calculate the correlation coefficient, test statistic, and p-value. You will also use Excel to generate a scatterplot of your sample data with a linear regression (line of best fit) model. All of these results should be shown on the Excel spreadsheet. Don't forget to put your name on the first sheet of the spreadsheet as this will be uploaded as an Excel file to Canvas.

As your boss is not interested in sorting through data on a spreadsheet, you will then write up a professional report summarizing your findings. This report should include your scatterplot graph and any necessary tables. Within your report, in addition to any appropriate introductions, conclusions, citations, etc. also discuss the following:

Interpret the correlation coefficient.

Discuss the hypothesis in order to determine whether the population correlation between the two variables differs from zero.

What are the results of the hypothesis test in the context of this task (at the 5% significance level)?

What is the sample regression equation that could enable your to predict property taxes on the basis of the size of a home?

Using this, predict the property taxes for a home that is 1,500 square ft.

Random Sample Property Taxes Size Use Excel functions/tools to calculate the correlation coefficient, the value of the test statistic, and p-value. Also use Excel to generate a scatterplot with a linear regression line. Paste these results on this sheet.

1 17339 2479

2 18229 1890 SUMMARY OUTPUT

3 15693 1000

4 43988 5665 Regression Statistics

5 15187 2200 Multiple R 0.740785562

6 42007 3334 R Square 0.548763249

7 38968 4000 Adjusted R Square 0.492358655

8 16200 3565 Standard Error 929.8657891

9 35478 2698 Observations 10

10 15333 2189

ANOVA

df SS MS F Significance F

Regression 1 8412228.915 8412228.915 9.729052406 0.014247134

Residual 8 6917203.085 864650.3857

Total 9 15329432

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%

Intercept 903.0415446 705.1075534 1.280714609 0.236173476 -722.9393892 2529.022478 -722.9393892 2529.022478

Property Taxes 0.077352488 0.024799277 3.119142896 0.014247134 0.020165253 0.134539722 0.020165253 0.134539722

RESIDUAL OUTPUT

Observation Predicted Size Residuals

1 2244.256327 234.7436727

2 2313.100041 -423.1000413

3 2116.934133 -1116.934133

4 4305.62277 1359.37723

5 2077.793774 122.206226

6 4152.387492 -818.3874918

7 3917.313282 82.68671809

8 2156.151844 1408.848156

9 3647.3531 -949.3531001

10 2089.087237 99.91276282