Supply Chain Management 200
Excel Homework 6
Fall 2020
Using the Homework 6 data set in Canvas and the Excel Homework 6 Tutorial or any other sources, answer all of the questions below.
Terms:
Batting Average (BA) number of hits/number of at-bats
On Base Percentage (OBP) number of times a player gets on base/number of plate appearances
Problem: We would like to see if there is a relationship between the Batting Average (BA) of players in the National League Central with the On Base Percentage (OBP) of the players. If we conclude there is a relationship, then we can use Batting Average to predict On Base Percentage.
Follow all the steps in the tutorial to make a scatterplot of Batting Average (BA) and On Base Percentage (OBP). BA will be your independent variable and OBP will be your dependent variable. You do not need to include the Scatterplot with your homework.
1. Write a short description of what kind of relationship, if any, you see in your scatterplot. (.1)
Using Data Analysis in Excel, find the correlation coefficient for BA and OBP by creating a correlation matrix. (If Data Analysis is not loaded in your Excel, follow the instructions in Excel Tutorial 1 to install Data Analysis Toolpak.) This correlation matrix may not be able to be done on a Mac. You do not need to include the correlation matrix with your homework.
2. What is the value of the correlation coefficient found in your correlation matrix? (.1)
3. Using the value of the correlation coefficient found in Question 2, write a statement about the strength and direction of the data set. (.1)
(Questions 1-3 can be completed after Lecture 27.)
Use Data Analysis to run a regression of BA (independent variable) and OBP (dependent variable). (If Data Analysis is not loaded in your Excel, follow the instructions in Excel Tutorial 1 to install Data Analysis Toolpak.) This regression may not be able to be run on a Mac. You will be using this output for the remainder of the questions. You do not need to turn the output in with your homework.
4. Using the appropriate functions, find the sample standard deviation of both BA and OBP. You must handwrite or type the entire function equations (including the equals signs, the function names, and the arguments) and the answers. No credit will be given without the entire equations and the answers. (.1 for BA sample standard deviation, .1 for OBP sample standard deviation)
5. Using the value of r found in Question 2, hand calculate b1. You must show all work. (.1)
6. Write an interpretation of the slope beginning with the phrase On average . You must use the phrase on average and follow the pattern given in class in order to get credit. (.1)
7. How does the slope you calculated by hand in Question 5 compare to the slope found in the regression output? (.1)
8. Using the regression output, write the regression equation. (.1)
9. What On Base Percentage would you predict if the Batting Average was .206? As always, you must show all work. (.1)
10. Is Batting Average a significant predictor of On Base Percentage? Why or why not? Alpha for this problem is .05. (.1 for answer, .1 for why)
11. What is the value of R-Square? (.1)
12. Write a statement to interpret the R-square value. (.1)
(Questions 4-12 can be completed after Lecture 28.)
