Assignment:  Correlation and Regression

• An instructor is interested in studying the relationship between the Quantitative SAT scores and the Final Exam Performance in the first semester math class.   If a meaningful relationship is to be found, the instructor would like to be able predict a student’s final exam score based on the Quantitative SAT score.  Towards this end, the instructor has collected the following data from a previous class.
  
  

 Enter the above data into a worksheet and name the sheet: Data.  (Check the ACCURACY of data you entered; format the table only after finishing all the analysis)

• Create a scatter plot for SAT Score and Final Exam Performance on a separate worksheet (call it: Scatter Plot).  The SAT score should be on the X-axis and the Final Exam Performance on the Y-axis.  Add a trendline to the chart.  Change the scales of the axes if necessary.  Title the chart “SAT Score Vs. Final Exam Performance.”  Increase the font size of the title to 16.  Add any formatting to improve the appearance of the chart.
  

• Calculate the Correlation between SAT Score and Final Exam Performance on a separate worksheet with tab name “Correlation.”  Adjust the column widths so that the labels are clearly seen. Add any formatting for better appearance.
  

• Perform a Regression analysis.  The output must be on a separate worksheet with tab name “Regression.” Adjust the column widths so that the labels are clearly seen.  Add any formatting to improve the appearance (The Intercept should be: 1.357023876).
  

• Insert a column between SAT Score and Final Exam Performance columns in the worksheet named “Data”.  Enter a column title called  “Prediction.”  Calculate the predicted values based on the regression coefficients in the “Regression” worksheet.  The instructor is interested in predicting the performance of students who might have the following SAT Scores: 715, 730, 770, 790.   Assume that the student numbers for these new students are as follows: 16, 17, 18, and 19.  Calculate the predicted final exam scores for these new students.
  

• Create a Line chart for the Predicted and Final Exam Performance variables.  Name the X-axis  “Student” and Y-axis  “Performance.”  Add a title “Projected Performance” to the chart.  Name the chart sheet  “Predictions.”  Increase the font size of the title to 16. Add any formatting to improve the appearance of the chart.
  

• Rearrange the worksheets in the following order: Data, Scatter Plot, Correlation, Regression, and Predictions. Add customized page header for all sheets. Print the worksheets and charts and arrange them the in sequence shown above, staple them together and hand them in.