Excel data analysis

Steps to Perform: Step Instructions 1 Open the file named Excel Assignment #4. Save the file as Your last nameExcel Assignment #4. 2 Go to the Health worksheet and we want to create a frequency table to see the results of the Health Survey. Create a frequency distribution to determine how often respondents replied Poor Fair Good Excellent Use the method we reviewed in class copy the column of data to column D and go to Data/DataTools/Remove Duplicates 3 Add a sum to add up the total of Excellent, Fair, Poor, and Good, to confirm that the frequency numbers add to 150. 4 We want to create a Centering of the title Quality over the data columns, changing the title to Quality Survey Results, merging the title over cells D and E, increasing the font size, and applying a different font color will make it stand out as a title. Merge and center the title over the range D1:E1, change the font size to 20, and apply dark Blue Fill color and white font color. Make the title Bold. 5 We want to visually inspect the survey responses. Create a Clustered Column chart. Select the data in D2:E5 and insert a clustered column chart. 6 We want the clustered column chart to be more visually appealing. Add a title and make it Bold. Select the horizontal axis and make the labels Bold and size 14 font. Move the chart to a new worksheet called Charts/Graphs. 7 Open the Friends worksheet. We want to create a contingency table with Male or Female as the column headings and Feasible: Yes or No as the row heading. Insert a pivot chart and make Gender (Male or Female) as the columns and Feasible (Yes or No) as the row heading. We want to count how many students answered yes or no, so use Count of Students as the Value Field Setting. 8 Pivot tables are good, but we want a better visual! Create a stacked column chart using the data in the pivot table. Select the data in the body of the Pivot Table and insert Stacked Column Chart. 9 We want the chart to be more visually appealing. Add a Bold Centered Title of Friends or Not Just Friends. Using Chart Elements, Data Labels, add the number of respondents to the columns of the chart. Then move the Chart to the Charts/Graphs worksheet. 10 Open the Midterm Grades worksheet. We want to see if there is a relationship between Midterm and Final grades. Select the data and insert a scatter plot. Make the plot more appealing by changing the scale on both the horizontal and vertical axes to range from 25 100 for Midterm and 40-100 for Final Exam. Make the axes numbers Bold Add a title of Relationship of Midterm and Final and make it bold and size 20 font. Add a Gradient Fill to the chart (the background of the chart) (Use chart options, Format chart, Fill, Gradient) Move the chart to the Charts/Graphs worksheet. 11 Open the High School SAT worksheet. Construct a bubble plot that shows math score on the x-axis, writing score on the y-axis, and number of test takers as the size of the bubble. 12 We want to make the Bubble Chart more appealing. Change the horizontal and vertical axes to be scaled better minimum of 300 and maximum of 700. Add a title of Relationship of Size of School to SAT scores and make it BOLD and centered. Move the chart to the Charts/Graphs worksheet. 13 We want to see if there is a relationship between math and writing scores. Construct a scatterplot that shows math score on the x-axis and writing score on the y-axis. Use different colors or symbols to show whether the high school is a private or a public school. You should sort by Public or Private and then Insert Scatter Plot, then Select Data and call the series Public and select Math and Writing scores, then Private and select those Math and writing Scores 14 We want to make the Scatterplot more appealing. Change the range of numbers on the axes to 300-700 and make the numbers Bold. Add a title of Relationship of Math and Writing and make it Bold and centered. Move the chart to the Charts/Graphs worksheet. 15 Open the TShirts worksheet. Construct a contingency table that shows the total quantity sold for each color and size combination. Insert Pivot Table, move Size to Columns and Color to Rows and Quantity to Sum and change Value Field Settings to Count. 16 We still would like to make this more visually appealing! Create a heat map using the pivot table results. Select the data in the pivot table. Go to Home, Conditional Formatting, Color Scales, and choose one you like. Make the titles and labels more appealing and move the heat map to the Charts/Graphs worksheet. 17 Open the Country worksheet. We want to compare the average returns for Latin America and Canada. We want to also compare the riskiness of the funds for each country. Calculate the mean and standard deviation of the funds for each country and then compute the Sharpe Ratios. 21 Save and close file. Exit Excel. Submit the file as directed. 22 Have a great break this is your last Excel Assignment!