Problem

The student news service at Clear Mountain State University (CMSU) has decided to gather d...

The student news service at Clear Mountain State University (CMSU) has decided to gather data about the undergraduate students that attend CMSU. They create and distribute a survey of 14 questions and receive responses from 62 undergraduates (stored in ) For each question asked in the survey, construct all the appropriate tables and charts and write a report summarizing your conclusions.

Step-by-Step Solution

Solution 1

To construct all the appropriate tables and chart, consider all variables one by one:

Step 1: Consider the Gender variable combination with Class variable

Use contingency tables of row percentage, column percentage, and total percentages for class with Gender.

(a) The contingency table based on total percentage can be constructed in following manner:

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

14

17

2

33

Male

11

14

4

29

Grand Total

25

31

6

62

Divide each given value of class and total by the grand total value i.e. 62

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

14/62

17/62

2/62

33/62

Male

11/62

14/62

4/62

29/62

Grand Total

25/62

31/62

6/62

62/62

Then multiply each value by 100 to get total percentage.

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

22.58%

27.42%

3.23%

53.23%

Male

17.74%

22.58%

6.45%

46.77%

Grand Total

40.32%

50.00%

9.68%

100.00%

(b) The contingency table based on row percentage can be constructed in following manner:

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

14

17

2

33

Male

11

14

4

29

Grand Total

25

31

6

62

Divide each given value of class and total by total value of each row

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

14/33

17/33

2/33

33/33

Male

11/29

14/29

4/29

29/29

Grand Total

25/62

31/62

6/62

62/62

Then multiply each value by 100 to get row percentage.

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

42.42%

51.52%

6.06%

100.00%

Male

37.93%

48.28%

13.79%

100.00%

Grand Total

40.32%

50.00%

9.68%

100.00%

(c) The contingency table based on column percentage can be constructed in following manner:

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

14

17

2

33

Male

11

14

4

29

Grand Total

25

31

6

62

Divide each given value of Class and total by total value of each column

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

14/25

17/31

2/6

33/62

Male

11/25

14/31

4/6

29/62

Grand Total

25/25

31/31

6/6

62/62

Then multiply each value by 100 to get column percentage.

 

Class

 

Gender

Junior

Senior

Sophomore

Grand Total

Female

56.00%

54.84%

33.33%

53.23%

Male

44.00%

45.16%

66.67%

46.77%

Grand Total

100.00%

100.00%

100.00%

100.00%

To construct the bar chart of total percentages follow below steps:

1) Select cell range A2:D4.

2) In Excel 2013, select Insert, then the Bar icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Bar).

3) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

4) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

5) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 2

Step 2: Consider the Gender variable with Major variable

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Major from Choose fields to add to report and Drop in the Column box.

4) Drag Gender from Choose fields to add to report and Drop in the Row box.

5) Drag Major from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Count of Type).

To construct the bar chart of Majors follow below steps:

1) Select cell range A2:I4.

2) In Excel 2013, select Insert, then the Bar icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Bar).

3) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

4) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

5) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 3

Step 3: Consider the Gender variable with Grad Intention

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Grad Intention from Choose fields to add to report and Drop in the Column box.

4) Drag Gender from Choose fields to add to report and Drop in the Row box.

5) Drag Grad Intention from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Count of Type).

To construct the bar chart of Majors follow below steps:

6) Select cell range A2:D4.

7) In Excel 2013, select Insert, then the Bar icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Bar).

8)

Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

9) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

10) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 4

Step 4: Consider the Gender variable with GPA

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag GPA from Choose fields to add to report and Drop in the Column box.

4) Drag Gender from Choose fields to add to report and Drop in the Row box.

5) Drag GPA from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Average of Type).

To construct the column chart of GPA follow below steps:

6) Select cell range A2:B4.

7) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

8) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

9) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

10) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 5

Step 4: Consider the Gender variable with Employment

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Employment from Choose fields to add to report and Drop in the Column box.

4) Drag Gender from Choose fields to add to report and Drop in the Row box.

5) Drag Employment from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Count of Type).

6) Divide the cell range A3 by E4 using =A3/$E$4 copy it to column 4.

To construct the Column chart of Employment follow below steps:

7) Select cell range A2:D4.

8) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

9) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

10) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

11) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Step 5: Consider the Gender variable with Salary

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Gender from Choose fields to add to report and Drop in the Row box.

4) Drag Salary from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Average of Type).

To construct the Column chart of Salary follow below steps:

5) Select cell range A2:B4.

6) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

7) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

8) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

9) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 8

Step 6: Consider the Gender variable with Social networking

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Social Networking from Choose fields to add to report and Drop in the Column box.

4) Drag Gender from Choose fields to add to report and Drop in the Row box.

5) Drag Social Networking from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Count of Type).

6) Divide the cell range A3 by E4 using =A3/$E$4 copy it to column 6.

To construct the Column chart of Social Networking follow below steps:

7) Select cell range A2:F4.

8) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

9) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

10) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

11) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Step 7: Consider the Gender variable with Satisfaction

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Satisfaction from Choose fields to add to report and Drop in the Column box.

4) Drag Gender from Choose fields to add to report and Drop in the Row box.

5) Drag Social Networking from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Count of Type).

6) Divide the cell range A3 by E3 using =A3/$E$3 copy it to column 6.

7) Divide the cell range A4 by H4 using =A4/$H$4 copy it to column 6.

To construct the Column chart of Satisfaction follow below steps:

8) Select cell range A2:G4.

9) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

10) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

11) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

12) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 10

Step 8: Consider the Gender variable with Spending

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

5) Click range and enter A1:O63 as the Table/range cell range.

6) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

7) Drag Gender from Choose fields to add to report and Drop in the Row box.

8) Drag Spending from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Average of Type).

To construct the Column chart of Spending follow below steps:

10) Select cell range A2:B4.

11) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

12) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

13) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

14) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Step 9: Consider the Gender variable with Computer

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Computer from Choose fields to add to report and Drop in the Column box.

4) Drag Gender from Choose fields to add to report and Drop in the Row box.

5) Drag Computer from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Count of Type).

6) Divide the cell range B3 by E3 using =A3/$E$3 copy it to column 4.

7) Divide the cell range B4 by E4 using =A4/$E$4 copy it to column 4.

To construct the Column chart of Computer follow below steps:

8) Select cell range A2:D4.

9) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

10) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

11) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

12) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 12

Step 10: Consider the Gender variable with Text Message

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

1) Click range and enter A1:O63 as the Table/range cell range.

2) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

3) Drag Gender from Choose fields to add to report and Drop in the Row box.

4) Drag Text Message from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Average of Type).

To construct the Column chart of Text Message follow below steps:

5) Select cell range A2:B4.

6) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

7) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

8) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

9) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Step 10: Consider the Gender variable with wealth

Open to the Data worksheet of the UndergradSurvey workbook. Select Insert ? PivotTable. In the create Pivot Table dialog box:

5) Click range and enter A1:O63 as the Table/range cell range.

6) Click New Worksheet and the click OK.

In the Pivot Table Fields task pane:

7) Drag Gender from Choose fields to add to report and Drop in the Row box.

8) Drag Wealth from Choose fields to add to report and Drop in the ∑ value box.(Type changes to Average of Type).

To construct the Column chart of Text Message follow below steps:

10) Select cell range A2:B4.

11) In Excel 2013, select Insert, then the Column icon in the Charts group, and then select the first 2-D Bar gallery item (Clustered Column).

12) Right-click the Percentage drop down bottom in the chart and click Hide All Field Buttons on Chart.

13) Select Design ? Add Chart element ?Axis Titles ? Primary Horizontal.

14) Relocate the chart to a chart sheet and turn off the chart legend and gridlines.

Picture 14

Step 11: Study the relation between GPA and Salary

To construct salary with GPA on the X axis and Salary on the Y axis:

1) Select the cell range G1:G63, while holding ctrl key select I1:I63.

2) Select Insert ? Scatter and select the first Scatter gallery item (Scatter with only Marker).

3) Relocate the chart to a chart sheet, turn off the chart legend and gridlines, add axis titles, and modify the chart title.

Picture 15

Step 12: Study the relation between GPA and Social Networking

To construct salary with GPA on the X axis and Salary on the Y axis:

1) Select the cell range G1:G63, while holding ctrl key select J1:J63.

2) Select Insert ? Scatter and select the first Scatter gallery item (Scatter with only Marker).

3) Relocate the chart to a chart sheet, turn off the chart legend and gridlines, add axis titles, and modify the chart title.

Step 12: Study the relation between GPA and Satisfaction.

To construct salary with GPA on the X axis and Satisfaction on the Y axis:

1) Select the cell range G1:G63, while holding ctrl key select J1:J63.

2) Select Insert ? Scatter and select the first Scatter gallery item (Scatter with only Marker).

3) Relocate the chart to a chart sheet, turn off the chart legend and gridlines, add axis titles, and modify the chart title.

Picture 17

Step 13: Study the relation between GPA and Spending.

To construct salary with GPA on the X axis and Spending on the Y axis:

1) Select the cell range G1:G63, while holding ctrl key select L1:L63.

2) Select Insert ? Scatter and select the first Scatter gallery item (Scatter with only Marker).

3) Relocate the chart to a chart sheet, turn off the chart legend and gridlines, add axis titles, and modify the chart title.

Picture 18

Step 13: Study the relation between GPA and Text Message.

To construct salary with GPA on the X axis and Text Message on the Y axis:

1) Select the cell range G1:G63, while holding ctrl key select N1:N63.

2) Select Insert ? Scatter and select the first Scatter gallery item (Scatter with only Marker).

3) Relocate the chart to a chart sheet, turn off the chart legend and gridlines, add axis titles, and modify the chart title.

Picture 19

Step 14: Study the relation between GPA and Wealth

To construct salary with GPA on the X axis and Text Message on the Y axis:

1) Select the cell range G1:G63, while holding ctrl key select O1:O63.

2) Select Insert ? Scatter and select the first Scatter gallery item (Scatter with only Marker).

3) Relocate the chart to a chart sheet, turn off the chart legend and gridlines, add axis titles, and modify the chart title.

Picture 20

From above finding, we can conclude that

1) Females are majority in junior and senior class and Males are majority in sophomore.

2) Males are more in management, Accounting and others as compared to Females whereas Females are more in Retailing/Marketing, Economic/Finance, CIS and International Business.

3) Male students are more incline to complete their graduation than their counterparts.

4) Female students score better than Males students.

5) Male students have better employment rate than Female students.

6) Females’ students are having higher salary than males’ students.

7) Male students are more socially active.

8) Male students are more satisfy.

9) Male students spend more than Female students.

10) Female students are having different type of computers than Male student.

11) Male student do more texting than Female student.

12) Students are having higher GPA have higher salary.

13) Students who are less social active have higher GPA.

14) Students having higher GPA are more satisfy.

15) Students who spend less have higher GPA.

16) Students who do less texting have higher GPA.

17) Less well to do student are having higher GPA.

Add your Solution
Textbook Solutions and Answers Search