Learning Goal: I’m working on a data analytics spreadsheet and need an explanati

Learning Goal: I’m working on a data analytics spreadsheet and need an explanation and answer to help me learn. I’m working on a data analytics spreadsheet and need an explanation and answer to help me learn.The Below Four Questions are Seperate Question with attached file below .Question-1). Danah is responsible for reporting the status of sales for his company. The following pie chart shows the percentages of closed sales in each of the top seven cities. Use a different type of chart to display the percentage of sales sold in each city that conveys the data better than the pie chart. Convert the pie chart to an Excel bar chart in order to improve readability.Question – 2).The attached shows the average monthly distance travelled (Billions of Miles) by vehicles on urban highways for five years.a. Use Excel to create sparklines for the average monthly vehicle distance travelled each year.b. Which year has decreasing trend of the average distance travelled? Which year has increasing trend of the average distance travelled?c. Use Excel to create a heat map for the average distance travelled by vehicles. Do you find the heat map or the sparklines to be better at communicating the trend of the average vehicle distance travelled over different years? Why?Question- 3).Below is a study on the number of accidents that occurred in ten U.S. states in different cities for three consecutive years.Create a PivotTable in Excel to answer the following questions. The PivotTable should group the number of accidentsinto yearly bins and display the sum of accidents that occurred each year in columns of Excel. Row labels should includethe accident locations and allow for grouping the locations into states or viewing by city. You should also sort thePivotTable so that the states with the greatest number of accidents between 2011 and 2013 appear at the top of the PivotTable. a. Which state had the greatest number of accidents between 2011 and 2013? Discuss in your Excel document uploaded.b. How many accidents occurred in the state of Colorado (CO) in 2012? In what cities did these accidents occur? Samec. Use the PivotTable’s filter capability to view only the accidents in Alabama (AL), Arizona (AZ), and Arkansas (AR) for the years 2011 through 2013. What is the total number of accidents in these states between 2011 and 2013? Discuss in Excel.d. Create a PivotChart to display a column chart that shows the total number of accidents in each year 2011 through 2013 in the state of California. Adjust the formatting of this column chart so that it best conveys the data. What does this column chart suggest about accidents between 2011 and 2013 in California? Discuss as above (your filename: AccidentsLastName.xlsx).Hint: You may have to switch the row and column labels in the PivotChart to get the best presentation for your PivotChart.Question – 4)Over the past few years the percentage of students who leave Dana College at the end of their first year has increased. Last year, Dana started voluntary one-credit hour-long seminars with faculty to help first-year students establish an on-campus connection. If Dana is able to show that the seminars have a positive effect on retention, college administrators will be convinced to continue funding this initiative. Dana’s administration also suspects that first-year students with lower high school GPAs have a higher probability of leaving Dana at the end of the first year. Data on the 500 first-year students from last year has been collected. Each observation consists of a first-year student’s high school GPA, whether they enrolled in a seminar, and whether they dropped out and did not return to Dana. Apply logistic regression to classify observations as dropped out or not dropped out by using GPA and Seminar as input variables and Dropped as the target (or response) variable.Download the file below and save locally as DroppedLastName.xlsx.
Clearly show in a Word document, how you obtained the glm-estimated coefficients of the estimated equation.
In your DroppedLastName.xlsx state in a column, the Estimated Probability of Dropped based these coefficients.
In your DroppedLastName.xlsx file, insert a diagram of Estimated Probability of Dropped against GPA.
Arbitrarily change some of the GPA values to illustrate that, indeed, your model follows Logistic regression.