1 b Data & Statistics Use the spreadsheet template posted on D2L with these instructions. (Save your Microsoft Excel spreadsheet by your first-last name and assignment number. (eg stacey- brookl.xlsx). (50 points) In the "Bar Chart" worksheet, copy and paste the Netflix subscriber data into cells B2 through E12 (1 posted the Netflix data (see Sheet 2) in Excel for this under Lecture07 on D2L). Create a Bar Chart in Excel (as shown in Lecture07-02) of Netflix Subscribers by Geographic area for all cleven quarters In the "Pie Chart" worksheet, copy and paste the video game market share data for the years 2015 to 2019 in cells B2 through E6, and create Pie Charts in Excel of the Video Game Console Market Share (as shown in Lecture07-03; you will have five different pie charts) In the "Line Chart" worksheet, copy and paste the Jan. to June 2019 and Jan. to June 2020 data in columns A through H (column E is blank). Create a Line Chart in Excel of CTA Rail Boarding's for the January 1 to June 30 time periods for the 2019 and 2020 years as shown in Lecture07-04. NOTE: The data is not always posted chronologically. If it is not, you will need to highlight the 5 columns and sort by service_date (Oldest to Newest) in the spreadsheet you download from D2L 1 provided information as to how to Sort data in Excel in Lecture 05-02 In the "Scatterplot Chart" worksheet, copy and paste the monthly gasoline prices and automobile sales from January 1995 to December 2019 in cells A2 through D301 (as shown in Lecture07-05), and create a Seatterplot Chart in Excel as shown in Lecture07-05 including the trend line and statistical results. NOTE: The gasoline price data have been revised, so your results may be slightly different than what is on Lecture07-05 In the "Per Capita Income" worksheet, copy and paste the US Per Capta Income (I posted this data in Lectures on D2L) data in columns A through D. PCI 1 - 2019 and PCI 2 - 2020. In the "Statistics" worksheet, calculate the mean, median and standard deviation for PCI 1 in cells B2 to B4, and calculate the mean, median and standard deviation for PCI 2 in cells C2 to CA (include all formulas in the spreadsheet). NOTE: The data is different from what is in the In the Statistics 2" worksheet, calculate the percentiles (each 104 percentile) for PCI 2 in cells F2 to F10 In the Statistics 3" worksheet, calculate the correlation between Per Capita Income 1 and Per Capita Income 2 in cell B2, calculate the value (probability) of the t-test in cell B3 (include all formulas in the spreadsheet). d lecture. E a. c. Written Explanation (Save the Microsoft Word file by your first-last name and assignment number (i.e. stacey-brook1.doe). (50 points) Based on the Bar Chart you created, which of the four geographic markets should Netflix focus most on to add new subscribers? Why? b. Based on Pie Charts you created, video game console market share has been changing. If you were asked to create a scatterplot (which you are not), what other yearly variable would you include to explain why video game console market share changes? Based on Line Chart you created, rail boardings have dramatically decreased with the shelter-in- place orders starting in March 2020. As Emergency Travel Orders are lifted, will rail boardings increase? Why or why not? d. Based on Scatterplot you created, what relationship exists between gasoline prices and automobile sales? Do you expect this relationship to change for 2020? Why or why not? Based on your center of data calculations, both the mean and median of Per Capita Income 2 increased in 2020. What implications does this have on those on a fixed income (either those who are retired or those working minimum wage jobs)? Given the change in the standard deviation of Per Capita Income over these two years and the percentiles of per capita income for Per Capita Income 2, what do you conclude about income inequality in the US economy? e. a 1 b Data & Statistics Use the spreadsheet template posted on D2L with these instructions. (Save your Microsoft Excel spreadsheet by your first-last name and assignment number. (eg stacey- brookl.xlsx). (50 points) In the "Bar Chart" worksheet, copy and paste the Netflix subscriber data into cells B2 through E12 (1 posted the Netflix data (see Sheet 2) in Excel for this under Lecture07 on D2L). Create a Bar Chart in Excel (as shown in Lecture07-02) of Netflix Subscribers by Geographic area for all cleven quarters In the "Pie Chart" worksheet, copy and paste the video game market share data for the years 2015 to 2019 in cells B2 through E6, and create Pie Charts in Excel of the Video Game Console Market Share (as shown in Lecture07-03; you will have five different pie charts) In the "Line Chart" worksheet, copy and paste the Jan. to June 2019 and Jan. to June 2020 data in columns A through H (column E is blank). Create a Line Chart in Excel of CTA Rail Boarding's for the January 1 to June 30 time periods for the 2019 and 2020 years as shown in Lecture07-04. NOTE: The data is not always posted chronologically. If it is not, you will need to highlight the 5 columns and sort by service_date (Oldest to Newest) in the spreadsheet you download from D2L 1 provided information as to how to Sort data in Excel in Lecture 05-02 In the "Scatterplot Chart" worksheet, copy and paste the monthly gasoline prices and automobile sales from January 1995 to December 2019 in cells A2 through D301 (as shown in Lecture07-05), and create a Seatterplot Chart in Excel as shown in Lecture07-05 including the trend line and statistical results. NOTE: The gasoline price data have been revised, so your results may be slightly different than what is on Lecture07-05 In the "Per Capita Income" worksheet, copy and paste the US Per Capta Income (I posted this data in Lectures on D2L) data in columns A through D. PCI 1 - 2019 and PCI 2 - 2020. In the "Statistics" worksheet, calculate the mean, median and standard deviation for PCI 1 in cells B2 to B4, and calculate the mean, median and standard deviation for PCI 2 in cells C2 to CA (include all formulas in the spreadsheet). NOTE: The data is different from what is in the In the Statistics 2" worksheet, calculate the percentiles (each 104 percentile) for PCI 2 in cells F2 to F10 In the Statistics 3" worksheet, calculate the correlation between Per Capita Income 1 and Per Capita Income 2 in cell B2, calculate the value (probability) of the t-test in cell B3 (include all formulas in the spreadsheet). d lecture. E a. c. Written Explanation (Save the Microsoft Word file by your first-last name and assignment number (i.e. stacey-brook1.doe). (50 points) Based on the Bar Chart you created, which of the four geographic markets should Netflix focus most on to add new subscribers? Why? b. Based on Pie Charts you created, video game console market share has been changing. If you were asked to create a scatterplot (which you are not), what other yearly variable would you include to explain why video game console market share changes? Based on Line Chart you created, rail boardings have dramatically decreased with the shelter-in- place orders starting in March 2020. As Emergency Travel Orders are lifted, will rail boardings increase? Why or why not? d. Based on Scatterplot you created, what relationship exists between gasoline prices and automobile sales? Do you expect this relationship to change for 2020? Why or why not? Based on your center of data calculations, both the mean and median of Per Capita Income 2 increased in 2020. What implications does this have on those on a fixed income (either those who are retired or those working minimum wage jobs)? Given the change in the standard deviation of Per Capita Income over these two years and the percentiles of per capita income for Per Capita Income 2, what do you conclude about income inequality in the US economy? e. a