# Statistics: Reasoning Problems and Excel Scatter plots

Topic 2, Problem 5 - The following table gives information from a sample of college students: gender, number of children in family of origin, and number of children in their ideal family, in which they may someday be a parent ...

Topic 2, Problem 6 - The following table of data gives hockey star Eric Lindros' career numbers for the eight seasons he played with the Philadelphia Flyers ...

Topic 4 Problem 3 - One way to find out whether your weight is "reasonable" for your height is to calculate your body mass index (BMI). A BMI of 20 to 25 is normal. BMI is a function of height and weight and is determined as follows: To calculate a person's BMI, divide his or her weight in kilograms by the ...

Topic 4 Problem 6 - A student's final grade in a course is often a function of various student performance measures such as homework, class participation, hour exams, and final exam, as well as the weights the professor assigns to each of these measures ...

Topic 10, Problem 2 - The following table gives the percentage of the U.S. population living below the poverty level for the period from 1990 to 2002 ...

(See attachment).

© BrainMass Inc. brainmass.com October 17, 2018, 12:07 am ad1c9bdddfhttps://brainmass.com/statistics/scatter-plot/statistics-reasoning-problems-and-excel-scatter-plots-281941

#### Solution Summary

Complete, Neat and Step-by-step Solution s are provided in the attached Zip file.

Multiple Regression Analysis & Scatter Plots in Excel - Traffic Fatalities

A group of legislators wanted to look at factors that affect the number of traffic fatalities. They collected some data for 1994 from the National Transportation Safety Board on the number of fatalities for 50 states and the District of Columbia (DC), the number of licensed drivers, the number of registered vehicles, and the number of vehicle miles traveled.

See attachment for data. Source: Statistical Abstract of the United States 1996

(Reference: Pelosi and Sadifer, Doing Statistics for Business with Excel, Second Edition)

(a) In Excel, select the Number of Traffic Fatalities as the dependent variable. Do a scatter plot between the Number of Traffic Fatalities and Number of licensed drivers (in thousands), a second scatter plot between the Number of Traffic Fatalities and the Number of Registered Vehicles (in thousands) and a third scatter plot between the Number of Traffic Fatalities and the Number of Vehicle Miles travelled (in millions), Paste the scatter plots below and discuss the nature of the relationship based on the scatter plots. Note: Follow the instructions given in module 5 to do the scatter plots.

(b) Select the Number of Traffic Fatalities as the dependent variable and the Number of licensed drivers (in thousands), the Number of Registered Vehicles (in thousands) and the Number of Vehicle Miles travelled (in millions) as independent variables. Conduct multiple regression using Excel. Paste the output report below. Note: Follow the instructions given in module 5 to conduct simple regression. At the step where you specify the input data range, instead of selecting the data for one independent variable, select data for all the independent variables.

(c) Write the equation from the regression output report. If you are using symbols in the equation for the variables, do define the symbols before using the symbols in the equation. Provide clear and complete interpretation of the coefficients b1, b2 and b3 in the equation. There is no need to interpret b0.

(d) What is the value of R2 for this model? Do you think that the model does a good job of explaining the variation in the number of traffic fatalities? Why or why not?

(e) Set up the hypotheses to test whether the model is significant. Is the regression model significant at 0.01 as the level of significance? What does this mean?

(f) Set up the hypotheses to test for each of the regression coefficients individually and perform the test at the 0.05 level of significance.

(g) What are your conclusions from the tests on individual coefficients? Do any variables need to be dropped? If so, rerun the regression and determine the final regression equation. Note: drop the variables one at a time starting with the variable with the largest p-value (least significant), rerun the regression without the data for the dropped variable, check the p-values again and continue the process until all p-values (other than for the intercept term) are less than the level of significance.

(h) Compare the final equation with the first regression equation. What recommendations do you have about using the final equation? Give reasons for your answer.

(i) Suppose there was a state with the following values of the independent variables: Number of Licensed Drivers (in thousands) = 3,500, Number of Registered Vehicles (in thousands) = 4,000 and Number of Vehicle Miles Travelled (in millions) = 45,000 Determine and interpret the predicted value of the number of traffic fatalities.

View Full Posting Details