NOTE: You need to use the Excel functions/tools for the test rather than just typing numbers into the cells. Along with the Excel tutorial videos presented each week, examples in Chapter 14 work through similar problems, so please review these before attempting the lab. Example 14.1 on page 511 shows Excel formulas for finding the test statistic and p-value for a two tailed hypothesis test for correlation.
After being hired as a financial advisor for a large real estate company, you have been tasked with exploring trends between home size (square ft) and property taxes to see if a relationship exists and if that trend can be applied outside of just the sample data
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.