Describing a Categorical RelationshipProject 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.Instructions: Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. For the purpose of grading the project you are required to perform the following tasks:StepInstructions$1$Start Excel. Download and open the workbook named:Describing$\_$a$\_$Categorical$\_$Relationship$\_$Start$\mathrm{.}2$In cells C$8-$F$13,$ insert a pivot table putting political party affiliation on the rows and values areas and registered to vote on the columns area.Insert PivotTableGo to the Voting Data worksheet. Select the PivotTable in the Tables group of the Insert tab of the Ribbon. Selecting Data SeriesChoose Select a table or range and select columns A$-$F on the Voting Data worksheet. Select Existing Worksheet and choose cell H$2$ on the Voting Data worksheet as the location for the pivot table. Do not check any additional boxes in the Create PivotTable dialog. Edit PivotTable 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. Set Times New Roman, size $14$ pt$.$ font to the text in the pivot table in the Home tab of the Ribbon. Change the name of the pivot table to Table$1$ in the PivotTable group of the Analyze tab on the Ribbon.Moving PivotTableSelect 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 C$8-$F$13$ on the Problem worksheet. Note: Filters should be set to both the Registered and Party fields. Other options will not be graded. $3$In cells C$17-$F$22,$ 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.Insert PivotTableGo to the Voting Data worksheet. Select the PivotTable in the Tables group of the Insert tab of the Ribbon. Selecting Data SeriesChoose Select a table or range and select columns A$-$F on the Voting Data worksheet. Select Existing Worksheet and choose cell H$9$ on the Voting Data worksheet as the location for the pivot table. Do not check any additional boxes in the Create PivotTable dialog. Edit PivotTable 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 Number Format button and set percentage format that displays two decimal places. Select design Pivot Style Light $20$ in the PivotTable Styles group of the Design tab of the Ribbon. Set to the text in the pivot table Times New Roman, size $14$ pt$.$ font in the Home tab of the Ribbon. Change the name of the pivot table to Table$2$ in the PivotTable group of the Analyze tab on the Ribbon.Moving PivotTableSelect 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 C$17-$F$22$ on the Problem worksheet. Note: Filters should be set to both the Registered and Party fields. Other options will not be graded. $4"$In cells C$26-$F$31,$ 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 on the Voting Data worksheet. Choose cell H$16$ on 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 Table$3.$ Move the pivot table in cells C$26-$F$31$ on the Problem worksheet.Note: Filters should be set to both the Registered and Party fields. Other options will not be graded. $"5$In cell J$35,$ identify the probability that the business student is a registered voter$\mathrm{.}6$In cell J$36,$ choose the type of probability in step $5)$ from the drop$-$down menu$\mathrm{.}7$In cell J$37,$ identify the probability that the business student is registered and a Republican$\mathrm{.}8$In cell J$38,$ choose the type of probability in step $7)$ from the drop$-$down menu$\mathrm{.}9$In cell J$39,$ identify the probability that the business student is registered to vote given they are Republicans$\mathrm{.}10$In cell J$40,$ choose the type of probability in step $9)$ from the drop$-$down menu$\mathrm{.}11$In cell J$41,$ identify the probability that the business student is registered to vote if they are a Democrat$\mathrm{.}12$In cell J$43,$ identify the probability that the business student is not registered to vote and not affiliated with either the Democratic or the Republican party$\mathrm{.}13$In cells C$47-$G$47$ insert a Clustered Column Chart to show the distribution of the registered $($Yes$/$No$)$ across the three political parties.Inserting ChartSelect 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.Selecting Data SeriesChoose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add 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 D$28-$D$30$ as the Series values. Use cell D$27$ to set the series name.Add new series for the distribution of the registered to vote across the three political parties using cells E$28-$E$30$ as the Series values. Use cell E$27$ to set the series name.Edit Category Labels using cells C$28-$C$30$ as the Axis label range. Edit Chart ElementsSelect design Style $1$ in the Design tab of the Ribbon. Then go to the Add Chart Elements drop$-$down list. Add the legend and choose the Right option. Add the data labels and choose the Outside End option.Chart Size and PositionGo to the Format tab on the Ribbon. Set the chart height and width so the entire chart fits within cells C$47-$G$47\mathrm{.}14$In cells C$51-$G$51$ insert a Stacked Column Chart to show the distribution of the registered Yes$/$No across the three political parties.Inserting ChartSelect 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.Selecting Data SeriesChoose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add 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 D$28-$D$30$ as the Series values. Use cell D$27$ to set the series name.Add new series for the distribution of the registered to vote across the three political parties using cells E$28-$E$30$ as the Series values. Use cell E$27$ to set the series name.Edit Category Labels using cells C$28-$C$30$ as the Axis label range.Edit Chart ElementsSelect design Style $1$ in the Design tab of the Ribbon. Then go to the Add Chart Elements drop$-$down list. Add the legend and choose the Right option. Add the data labels and choose the Center option.Chart Size and PositionGo to the Format tab on the Ribbon. Set the chart height and width so the entire chart fits within cells C$51-$G$51\mathrm{.}15$In cell C$60,$ 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 C$55-$I$58\mathrm{.}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 C$64.17$Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.Total Score