Please help set up this problem and create the pivot table in Excel.
1. The attached file MBA.XLS contains the results of a study on the academic preparation of MBA students and their performance in a Quantitative Analysis Course. A group of 181 students, constituting a random sample, was
classified according to their latest academic degree program as well as their final grade in the QA course. The classifications for academic programs were: Arts and Science (A&S), Engineering (ENG), Law, Medicine (MED), and Other. The classifications for the final grades were A, B, and C. The admissions officers at the business college want to explore whether there is a quantifiable relationship between their academic performance in the quantitative analysis area and their focus of prior academic studies. Specifically, they want to test the null hypothesis that academic performance in the course and academic background are independent versus the alternative that these two attributes are dependent. A significance level of 0.05 will be used.
a. Using the p-value, perform the test providing a description of the test statistic, its value, and your conclusion. Translate the data set into a cross tabulation using the Pivot Tool in Excel.
As mentioned in your question, the first step to test this hypothesis is to set up a cross tabulation (or "contingency table") in Excel. I created it using the Data - Table function. This table (check the yellow table in the attached file) shows the number of students that fall in each category; that is, the number of students that with a degree in Med and grade A, with a degree in Med and grade B, etc. Totals are shown for each column and row.
Now, the appropriate test to use is the Chi-Square test. The formula is the following:
Sum (O - E)^2
(for all cells) -----------
This solution thoroughly explains the steps in a chi-square test to describe the test statistic, its value and the concluding statement. Attached is a cross tabulation using the Pivot Tool in Excel.