1.2 HW - Plots for Analyses Submission: Completed Excel workbook via Canvas. Part 1 - Linear Regression Overview This problem extends the ideas from the notes '1.2 - Basic Charts in Excel', where we learned how to create summary tables, and from 'Lab 1.2 - More Charts in Excel', where we created scatterplots. The first tab of the accompanying workbook contains a dataset of public transformation information related to the metro for a sample of hypothetical cities; a subset is shown below: o The response variable Y is 'Number of weekly riders' and the other numeric variables are the explanatory X variables. Assignment The goal is to analyze the number of weekly riders by creating a summary statistics table for each state and determining the best predictor via simple linear regression. a) Complete the summary statistics table for each City and Overall. o Find the correct formulas to use and set them up so that they can be autofilled down easily. b) Now we want to determine which X variable has the most impact on Y, the number of weekly riders. Repeat the following steps for each of the three numeric X variables. o Create a scatterplot with the correct selection of the axes / variables. o Add informative chart title and axes labels so it is clear which variables are being plotted. o To add the line of best fit (regression line): Chart Design -> Add Chart Element -> Trendline -> Linear. o To add the regression information: Right click on the trend line -> Format Trendline -> check the boxes for 'Display Equation on chart' and 'Display R-squared value on chart'. o Format the points to be a neutral color and the line a brighter color so that the regression line has more emphasis. Change the font color of the regression info to visually 'link' it to the regression line. o After finishing all plots, add a text box to your workbook and include a quick write-up discussing which X variable is the best predictor of Y the number of weekly riders. How do you know? Describe the relationship (either visually or from the regression equation) between Y and the best X. What will be looked at for grading Completed summary statistics table with correct formulas, well-formatted plots with trendline information, sufficient write-up