Can I get some explanation and send spreadsheet Describing a Categorical Relationship V4 update Project Description Data was collected from several classes of Business students enrolled in a private religious affiliated university during the fall semester of the 2016 presidential election year The variables collected included 1) Sex Female Male 2) HS Private Public 3) Party Democrat Republican Other (includes Independent, Libertarian, and Green) 4) Registered Yes No 5) Likely Vote 0 100 For the purpose of this project, you will only be using the variables, Party and Registered The purpose of this project is to describe graphically and numerically a categorical relationship and to understand the difference among joint, marginal, and conditional probabilities Use this summary analysis to help you determine whether the choice of a student's political party is independent or dependent of whether the student is registered to vote or not Steps to Perform Step Instructions Points Possible 1 Start Excel Download and open the workbook named Describing a Categorical Relationship Start 0 2 In cells C8 F13 , insert a pivot table putting political party affiliation on the rows and values areas and registered to vote on the columns area Go to the Voting Data worksheet Select the PivotTable in the Tables group of the Insert tab of the Ribbon Choose Select a table or range and select columns A F in the Voting Data worksheet Select Existing Worksheet and choose cell H2 in the Voting Data worksheet as the location for the pivot table Do not check any additional boxes in the Create PivotTable dialog In the PivotTable Fields list, drag and drop the Registered field into the columns area and Party field into the rows and values areas In the Fields list, open the drop down menu to the right of the Registered field and uncheck the (blank) box Then open the drop down menu for the Party field and uncheck the (blank) box Select design Pivot Style Light 20 in the PivotTable Styles group of the Design tab of the Ribbon In the Home tab of the ribbon, set the pivot table text to Times New Roman, size 14 pt font Change the name of the pivot table to Table1 in the PivotTable group of the Analyze tab on the Ribbon Select the pivot table Then choose Move PivotTable in the Actions group of the Analyze tab on the Ribbon In the Move PivotTable dialog, select cells C8 F13 in the Problem worksheet Note Filters should be set to both the Registered and Party fields Other options will not be graded 2 3 In cells C17 F22 , insert a pivot table putting political party affiliation on the rows and values areas and registered to vote on the columns area and showing the values as ' of Grand Total' Go to the Voting Data worksheet Select the PivotTable in the Tables group of the Insert tab of the Ribbon Choose Select a table or range and select columns A F in the Voting Data worksheet Select Existing Worksheet and choose cell H9 in the Voting Data worksheet as the location for the pivot table Do not check any additional boxes in the Create PivotTable dialog In the PivotTable Fields list, drag and drop the Registered field into the columns area and Party field into the rows and values areas In the Fields list, open the drop down menu to the right of the Registered field and uncheck the (blank) box Then open the drop down menu for the Party field and uncheck the (blank) box In the Values area, open the drop down list to the right of the field name and select the Value Field Settings option Choose of Grand Total in the Show Values As tab Click on the Number Format button and select Percentage and format to two decimal places Select design Pivot Style Light 20 in the PivotTable Styles group of the Design tab of the Ribbon In the Home tab of the ribbon, set the pivot table text to Times New Roman, size 14 pt font Change the name of the pivot table to Table2 in the PivotTable group of the Analyze tab on the Ribbon Select the pivot table Then choose Move PivotTable in the Actions group of the Analyze tab on the Ribbon In the Move PivotTable dialog, select cells C17 F22 in the Problem worksheet Note Filters should be set to both the Registered and Party fields Other options will not be graded 2 4 In cells C26 F31 , insert a pivot table putting political party affiliation on the rows and values areas and registered to vote on the columns area and showing the values as ' of Row Total' Use columns A F in the Voting Data worksheet Choose cell H16 in the Voting Data worksheet as the location for the pivot table In the PivotTable Fields list, drag and drop the Registered and Party fields into appropriate areas Select to show all values except the blanks for the Registered and Party fields Choose of Row Total and set percentage format that displays two decimal places in the Show Values As tab of the Value Field Settings Select design Pivot Style Light 20 Set to the text in the pivot table Times New Roman, size 14 pt font Change the name of the pivot table to Table3 Move the pivot table in cells C26 F31 in the Problem worksheet Note Filters should be set to both the Registered and Party fields Other options will not be graded 2 5 In cell J35 , identify the probability that the business student is a registered voter 1 6 In cell J36 , choose the type of probability in step 5) from the drop down menu 1 7 In cell J37 , identify the probability that the business student is registered and a Republican 1 8 In cell J38 , choose the type of probability in step 7) from the drop down menu 1 9 In cell J39 , identify the probability that the business student is registered to vote given they are Republicans 1 10 In cell J40 , choose the type of probability in step 9) from the drop down menu 1 11 In cell J41 , identify the probability that the business student is registered to vote if they are a Democrat 1 12 In cell J43 , identify the probability that the business student is not registered to vote and not affiliated with either the Democratic or the Republican party 1 13 In cells C47 G47 insert a Clustered Column Chart to show the distribution of the registered (Yes No) across the three political parties Select the Column Chart and then the Clustered Column Chart from the provided chart options in the Charts group of the Insert tab of the Ribbon Choose Select Data in the Design tab on the Ribbon Delete any series created automatically using the Remove button and add a new series using Add button Add a new series for the distribution of those not registered to vote across the three political parties using cells D28 D30 as the Series values Use cell D27 to set the series name Add new series for the distribution of those registered to vote across the three political parties using cells E28 E30 as the Series values Use cell E27 to set the series name Edit Category Labels using cells C28 C30 as the Axis label range Select design Style 1 in the Design tab of the Ribbon Then go to the Add Chart Elements drop down list in the Chart Layouts part of the Design tab of the Ribbon Add the legend and choose the Right option Add the data labels and choose the Outside End option Go to the Format tab on the Ribbon Set the chart height and width so the entire chart fits within cells C47 G47 2 14 In cells C51 G51 insert a Stacked Column Chart to show the distribution of the registered Yes No across the three political parties Select the Column Chart and then the Stacked Column Chart from the provided chart options in the Charts group of the Insert tab of the Ribbon Choose Select Data in the Design tab on the Ribbon Delete any series created automatically using the Remove button and add a new series using Add button Add a new series for the distribution of those not registered to vote across the three political parties using cells D28 D30 as the Series values Use cell D27 to set the series name Add a new series for the distribution of those registered to vote across the three political parties using cells E28 E30 as the Series values Use cell E27 to set the series name Edit Category Labels using cells C28 C30 as the Axis label range Select design Style 1 in the Design tab of the Ribbon Then go to the Add Chart Elements drop down list in the Chart Layouts part of the Design tab of the Ribbon Add the legend and choose the Right option Add the data labels and choose the Center option Go to the Format tab on the Ribbon Set the chart height and width so the entire chart fits within cells C51 G51 2 15 In cell C60 , based on steps 13 14, identify whether the distribution of being registered to vote or not appears to differ between the three party affiliations Choose from the drop down menu the letter from A to D corresponding to one of the options listed in cells C55 I58 1 16 Identify whether the analysis supports that being registered to vote is independent of party affiliation Choose the correct answer from the drop down menu in cell C64 1 17 Save the workbook Close the workbook and then exit Excel Submit the workbook as directed 0

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 11, 2024

Can I get some explanation and send spreadsheet Describing a Categorical Relationship V4 update Project Description: Data was collected from several classes of Business students

Can I get some explanation and send spreadsheet

Describing a Categorical Relationship V4 update

Project Description:

Data was collected from several classes of Business students enrolled in a private religious affiliated university during the fall semester of the 2016 presidential election year. The variables collected included: 1) Sex - Female/Male; 2) HS - Private/Public; 3) Party - Democrat/Republican/Other (includes Independent, Libertarian, and Green); 4) Registered - Yes/No; 5) Likely Vote - 0-100%. For the purpose of this project, you will only be using the variables, Party and Registered. The purpose of this project is to describe graphically and numerically a categorical relationship and to understand the difference among joint, marginal, and conditional probabilities. Use this summary analysis to help you determine whether the choice of a student's political party is independent or dependent of whether the student is registered to vote or not.

Steps to Perform:

Step	Instructions	Points Possible
1	Start Excel. Download and open the workbook named: Describing_a_Categorical_Relationship_Start.	0
2	In cellsC8-F13, insert a pivot table putting political party affiliation on the rows and values areas and registered to vote on the columns area. Go to theVoting Data worksheet. Select the PivotTable in the Tables group of the Insert tab of the Ribbon. Choose Select a table or range and select columnsA-F in theVoting Data worksheet. Select Existing Worksheet and choose cellH2 in theVoting Data worksheet as the location for the pivot table. Do not check any additional boxes in the Create PivotTable dialog. In the PivotTable Fields list, drag-and-drop the Registered field into the columns area and Party field into the rows and values areas. In the Fields list, open the drop-down menu to the right of the Registered field and uncheck the (blank) box. Then open the drop-down menu for the Party field and uncheck the (blank) box. Select design Pivot Style Light 20 in the PivotTable Styles group of the Design tab of the Ribbon. In the Home tab of the ribbon, set the pivot table text to Times New Roman, size 14 pt. font. Change the name of the pivot table toTable1in the PivotTable group of the Analyze tab on the Ribbon. Select the pivot table. Then choose Move PivotTable in the Actions group of the Analyze tab on the Ribbon. In the Move PivotTable dialog, select cellsC8-F13 in theProblem worksheet. Note:Filters should be set to both the Registered and Party fields. Other options will not be graded.	2
3	In cellsC17-F22, insert a pivot table putting political party affiliation on the rows and values areas and registered to vote on the columns area and showing the values as '% of Grand Total'. Go to theVoting Data worksheet. Select the PivotTable in the Tables group of the Insert tab of the Ribbon. Choose Select a table or range and select columnsA-F in theVoting Data worksheet. Select Existing Worksheet and choose cellH9 in theVoting Data worksheet as the location for the pivot table. Do not check any additional boxes in the Create PivotTable dialog. In the PivotTable Fields list, drag-and-drop the Registered field into the columns area and Party field into the rows and values areas. In the Fields list, open the drop-down menu to the right of the Registered field and uncheck the (blank) box. Then open the drop-down menu for the Party field and uncheck the (blank) box. In the Values area, open the drop-down list to the right of the field name and select the Value Field Settings option. Choose % of Grand Total in the Show Values As tab. Click on the Number Format button and select Percentage and format to two decimal places. Select design Pivot Style Light 20 in the PivotTable Styles group of the Design tab of the Ribbon. In the Home tab of the ribbon, set the pivot table text to Times New Roman, size 14 pt. font. Change the name of the pivot table toTable2in the PivotTable group of the Analyze tab on the Ribbon. Select the pivot table. Then choose Move PivotTable in the Actions group of the Analyze tab on the Ribbon. In the Move PivotTable dialog, select cellsC17-F22in theProblem worksheet. Note:Filters should be set to both the Registered and Party fields. Other options will not be graded.	2
4	In cellsC26-F31, insert a pivot table putting political party affiliation on the rows and values areas and registered to vote on the columns area and showing the values as '% of Row Total'. Use columnsA-F in theVoting Data worksheet. Choose cellH16 in theVoting Data worksheet as the location for the pivot table. In the PivotTable Fields list, drag-and-drop the Registered and Party fields into appropriate areas. Select to show all values except the blanks for the Registered and Party fields. Choose % of Row Total and set percentage format that displays two decimal places in the Show Values As tab of the Value Field Settings. Select design Pivot Style Light 20. Set to the text in the pivot table Times New Roman, size 14 pt. font. Change the name of the pivot table toTable3. Move the pivot table in cellsC26-F31in theProblem worksheet. Note:Filters should be set to both the Registered and Party fields. Other options will not be graded.	2
5	In cellJ35,identify the probability that the business student is a registered voter.	1
6	In cellJ36, choose the type of probability in step 5) from the drop-down menu.	1
7	In cellJ37, identify the probability that the business student is registered and a Republican.	1
8	In cellJ38, choose the type of probability in step 7) from the drop-down menu.	1
9	In cellJ39, identify the probability that the business student is registered to vote given they are Republicans.	1
10	In cellJ40, choose the type of probability in step 9) from the drop-down menu.	1
11	In cellJ41, identify the probability that the business student is registered to vote if they are a Democrat.	1
12	In cellJ43, identify the probability that the business student is not registered to vote and not affiliated with either the Democratic or the Republican party.	1
13	In cellsC47-G47 insert a Clustered Column Chart to show the distribution of the registered (Yes/No) across the three political parties. Select the Column Chart and then the Clustered Column Chart from the provided chart options in the Charts group of the Insert tab of the Ribbon. Choose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add a new series using Add button. Add a new series for the distribution of those not registered to vote across the three political parties using cellsD28-D30as the Series values. Use cellD27 to set the series name. Add new series for the distribution of those registered to vote across the three political parties using cellsE28-E30 as the Series values. Use cellE27 to set the series name. Edit Category Labels using cells C28-C30 as the Axis label range. Select design Style 1 in the Design tab of the Ribbon. Then go to the Add Chart Elements drop-down list in the Chart Layouts part of the Design tab of the Ribbon. Add the legend and choose the Right option. Add the data labels and choose the Outside End option. Go to the Format tab on the Ribbon. Set the chart height and width so the entire chart fits within cellsC47-G47.	2
14	In cellsC51-G51 insert a Stacked Column Chart to show the distribution of the registered Yes/No across the three political parties. Select the Column Chart and then the Stacked Column Chart from the provided chart options in the Charts group of the Insert tab of the Ribbon. Choose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add a new series using Add button. Add a new series for the distribution of those not registered to vote across the three political parties using cellsD28-D30as the Series values. Use cellD27 to set the series name. Add a new series for the distribution of those registered to vote across the three political parties using cellsE28-E30 as the Series values. Use cellE27 to set the series name. Edit Category Labels using cells C28-C30 as the Axis label range. Select design Style 1 in the Design tab of the Ribbon. Then go to the Add Chart Elements drop-down list in the Chart Layouts part of the Design tab of the Ribbon. Add the legend and choose the Right option. Add the data labels and choose the Center option. Go to the Format tab on the Ribbon. Set the chart height and width so the entire chart fits within cellsC51-G51.	2
15	In cellC60, based on steps 13-14, identify whether the distribution of being registered to vote or not appears to differ between the three party affiliations. Choose from the drop-down menu the letterfrom A to D corresponding to one of the options listed in cellsC55-I58.	1
16	Identify whether the analysis supports that being registered to vote is independent of party affiliation. Choose the correct answer from the drop-down menu in cellC64.	1
17	Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.	0