Excel Assignment: Draw Scatter Diagram with SAT Scores Dataset

General Instruction: Each student is asked to submit only ONE Excel file named using the format of “LastNameFirstInitial_EA2”Benjamin submission file will be named “Kakavand to the eConestoga DropBox (created under eConestoga _Evaluations Excel Assignment

Draw Scatter Diagram with Excel (25 points) Download the “SATscore” Dataset from eConestoga_Evaluations – “Excel Assignment Open the Excel dataset and save it as a new file named ““LastNameFirstInitial

1. Highlight the two data columns of “Course” & “Teacher” without the top row of labels. 2. Click on Insert → Charts → All Charts → X Y (Scatter) → Select the leftmost diagram in the top menu row. ?
2. A “draft chart” should appear at this point. [5pt]
3. Click on the chart, a “+” sign should appear to the upper right corner of the chart. 4. Click on the “+” sign, in the drop-down menu, ensure the following boxes are “checked” – 1) Axes; 2) Axis Title; 3) Chart Title;
4. Gridlines; 5) Trendline
5. Click on the corresponding text box for (horizontal) X-axis, (vertical) Y-axis axis or the chart title, type in the text that you feel appropriate to describe the created chart, as well as the axis.
6. Click on the label line of the (horizontal) X-axis, right click the mouse, choose “format axis” from the drop-down menu, change the “minimum” under “bounds” from 0 to 55. Close the “format axis” window.
7. Repeat the above step #7 to let the (vertical) Y-axis start at 50 instead of 0.
8. Click on the Trendline on the chart, right click the mouse, choose “format trendline” from the drop-down menu. Ensure that the following boxes are “checked” – 1) linear; 2) display equation on chart; 3) display R-squared value on chart. Close the “format trendline” window.
9. Move the displayed equation & R-squared value to a proper place near the chart so that they won’t block the view of the chart.
10. Save your work on the Excel file before moving to the next part of this assignment. 
11. Correlation Analysis with Excel (25 points) (Scenario) A scientist is investigating the parental influence on girls regarding achievement in mathematics. Data were collected from 14 randomly selected families each has one 16-year old daughter. The father and daughter of the same family are asked to take a same math quiz separately, their scores are recorded for correlation analysis. Download the “MathScore” Dataset from eConestoga_ Evaluations _ “Excel Assignment 2” folder. Copy the dataset to a separate worksheet within your Excel Assignment 2 file created from Part A.
12. 1. Select Data → Data Analysis → Correlation → OK. A Correlation Windowwould appear on the computer screen. 2. Click on the box next to “Input Range”, highlight the two data columns including the labels on the first row (“DaughterScore” and “FatherScore”), click on the data box again and return to the Correlation Window.
13. Ensure that the “(group by) column” and “labels in first row” are checked on the Correlation Window
14. Click on the box next to “Output Range”, highlight an empty 3 (row) by 3 (column) space on the Excel sheet, click on the data box again and return to the Correlation Window. 5. Click “OK” on the Correlation Window. The Excel- computed sample correlation coefficient should appear in the output range you specified in the above Step
15. Format the result to be in 4-decimal place. [10pt]
16. Write in an empty cell of the Excel sheet your interpretation of this sample correlation coefficient between “DaughterScore” and “FatherScore” – in particular, the nature (positive or negative) and degree (strong or medium or weak) of the relationship. [5pt] 7. Using the remaining space on the Excel sheet, perform a statistical Test to see if the above sample relationship exists in the population. With 90% confidence, answer the following at empty cell spaces (with clear labels) of the same Excel sheet: [10pt] 1) R-square value 2) Computed test statistics 3) Degrees of freedom (DF) to look up critical t-value 4) Critical t-value looked up from textbook Appendix B.2 5) Your conclusion from this test. Save your work and rename the worksheet “PartB_Output” within the same Excel file. 
    
17. Regression Analysis with Excel (50 points) Download the “StudyEffort” Dataset from eConestoga_ Evaluations_ “Excel Assignment 2” folder. Copy the dataset to a separate worksheet within your Excel Assignment 2 file created from Part A. (Scenario) This dataset reports the final GRADE (out of 100) in a business course for a sample of 16 second-year undergraduate students. For each student in the sample, the dataset includes the number of classes attended (ATTEND) throughout the term, and the number of optional Cases (suggested by instructor, but not required) read by the student. 
18. Click on the box next to “Input Y Range”, highlight the data column of “GRADE” including the label in the first row, click on the data box again and return to the Regression Window. [5 pt]
19. Click on the box next to “Input X Range”, highlight the data column of “ATTEND” including the label in the first row, click on the data box again and return to the Regression Window. [5 pt]
20. Ensure the boxes next to “labels” & “New Worksheet Ply” are checked; the box next to “Constant is Zero” is UNchecked. [5 pt] Click OK and the regression output will appear on a separate worksheet. Rename that output worksheet as “PartC_Output1”. Format all numbers in the output table to be in 4-decimal places. [5 pt]
21. Using the empty cell space in the “PartC_Output1” worksheet, provide interpretation on the quantitative effect of “ATTEND” on a student’s course grade. [5 pt]
22. Using the empty cell space in the “PartC_Output1” worksheet, provide your answer – “Does the effect of class attendance on grade identified in the above Step 6 exist in the population?” Cite relevant information from “PartC_Output1
23. Following the similar steps as identified in the above C1 (Simple Linear Regression), but now selecting BOTH columns of “ATTEND” and “Case” as independent variables (Input X range), generate multiple linear regression output in a separate worksheet (rename the worksheet to “PartC_Output2”), format all numbers in the output table to be in 4-decimal places. [10 pt]. Using the empty cell space in the “PartC_Output2” worksheet, provide interpretation on the quantitative effect of “Case” on a student’s course grade. [5 pt] 9. Using the empty cell space in the “PartC_Output2” worksheet, provide your answer – “Does the effect of case reading on grade identified in the above Step 8 exist in the population?” Cite relevant information from “PartC_Output2” as supporting evidence. [5 pt] 
    Save your work to complete this Excel Assignment.