Business Data analysis computer assignment

Instructions:
? All numerical calculations and graphs/plots should be done using EXCEL.
? Your assignment must be typed in a Word document in .doc or .docx format only!
? When answering questions, wherever required, you should cut and paste the Excel output (eg,
plots, regression output etc) to show your working/output on your assignment.
Many college instructors believe that students need to spend at least 2 hours studying outside of
class for every hour of lecture. They believe that the number of hours students study to prepare for
the exam affect students’ marks significantly. As opposed, some believe that the number of
preparation hours do not essentially affect students’ marks while some other factors are to be
considered. To study the relationship between the preparation time spent by each student (in hours)
for the exam and the reported mark, a sample of 100 students were selected randomly from a large
statistics class. The data are stored in the file named “ASSIGNMENTDATA.XLS” in the course
website. Using EXCEL, answer below 12 questions:
1. What type of survey method is used and why? (1 mark)
2. What sampling method could be used to select the sample and why? (1 mark)
3. What are the variables we should consider collecting data for the purpose of the analysis and
why? Identify the data type(s) for the variables. (1.5 marks)
4. What kind of issues we may face in this data collection? (1 mark)
5. Using intervals such as 0–11, 12–23, 24–35, … for the preparation time variable and class
intervals 0–12, 13–25, 26–38, … for marks and explaining how to decide on the number of
classes, use appropriate BIN values to draw a histogram for each variable. Then, comment
on the shape of the two distributions. (3.5 marks)
6. Use an appropriate plot to investigate the relationship between the two variables. Briefly
explain the selection of each variable on the X and Y axes and why? (2 marks)
7. Prepare a numerical summary report about the data on the two variables by including the
summary measures, mean, median, range, variance, standard deviation, smallest and largest
values and the three quartiles, for each variable. (3 marks)
8. Compute a numerical summary measure to measure the strength of the linear relationship
between the two variables. Interpret this value. (1.5 marks)
9. Construct a 90% confidence interval estimate for the population average time spent on
preparation. (1.5 marks)
10. Test the hypothesis that population average time spent on preparation is more than 65 hours
(use a 5% level of significance). (1 mark)
11. Estimate a simple linear regression model and present the estimated linear equation. Then,
interpret the coefficient estimates of the linear model. (2.5 marks)
12. Interpret the coefficient of determination, R-squared (R2
) value. (0.5 mark)