For this Excel Lab, we will be using data related to auto fuel efficiency and emissions. After downloading the Excel data file, you’ll see the data for different variables on sheet 1. You’ll be performing analysis, generating graphs, and identify various test statistics, p-values, and regression equations using Excel.
For Sheet 1:
You’ll explore the correlation between the weight of a vehicle and its greenhouse gas emission.
Use Excel to generate the Scatterplot and regression line. Make sure your graph is formatted/labelled properly.
Make note of the regression equation, and use it to estimate the GHG for your personal car based on its weight (you can find this info online)
For Sheet 2:
Examine the relationship between the size of a car, and its greenhouse gas emissions. Does a relationship exist, or are they independent from one another?
Use a contingency table to run a chi-squared test for independence. Fill in the table with the frequency (counts) of each category. Then also calculate (using excel) what the expected frequencies. Use excel functions to calculate the test statistic, and p-value.
For Sheet 3:
Test the claim that the mean gas MPG (city) is equal for all three car sizes. This requires the use of one-way ANOVA (single factor) to test for equality of three or more means.
For the Report:
You’ll be writing up a report to summarize what you did for each of the analyses, and what your conclusions were.
You should embed your scatterplot graph into your report.
Within your report, you’ll make a single prediction for each of the three regression tests using the input values of your choice. Based on the data, discuss if your output result makes sense.
