Question

In: Statistics and Probability

What are influential observations and outliers in regression and how to determine them? Use Excel Analysis...

What are influential observations and outliers in regression and how to determine them? Use Excel Analysis ToolPak to solve the following problem.

The following data show the annual revenue ($ millions) and the estimated team value
($ millions) for the 32 teams in the National Football League (Forbes website, February
2009).

Team   Revenue   Value
Arizona Cardinals   203   914
Atlanta Falcons   203   872
Baltimore Ravens   226   1062
Buffalo Bills   206   885
Carolina Panthers   221   1040
Chicago Bears   226   1064
Cincinnati Bengals   205   941
Cleveland Browns   220   1035
Dallas Cowboys   269   1612
Denver Broncos   226   1061
Detroit Lions   204   917
Green Bay Packers   218   1023
Houston Texans   239   1125
Indianapolis Colts   203   1076
Jacksonville Jaguars   204   876
Kansas City Chiefs   214   1016
Miami Dolphins   232   1044
Minnesota Vikings   195   839
New England Patriots   282   1324
New Orleans Saints   213   937
New York Giants   214   1178
New York Jets   213   1170
Oakland Raiders   205   861
Philadelphia Eagles   237   1116
Pittsburgh Steelers   216   1015
San Diego Chargers   207   888
San Francisco 49ers   201   865
Seattle Seahawks   215   1010
St Louis Rams   206   929
Tampa Bay Buccaneers   224   1053
Tennessee Titans   216   994
Washington Redskins   327   1538

a.   Develop a scatter diagram with Revenue on the horizontal axis and Value on the vertical axis.
b.   Develop the estimated regression equation that can be used to predict team value given the value of annual revenue.
c. Construct a residual plot of the standardized residuals against the independent variable.
d. Do the assumptions about the error term and model form seem reasonable in light of the residual plot?

Solutions

Expert Solution

Soln

a)

Steps for Scatterplot in Excel

  • Import the data in Excel
  • In Insert Tab Click on scatterplot and select the Y values and X values. Click OK.

b)

Steps for Regression in Excel

  • Import the data in Excel
  • In Data Analysis tool from Data Tab.
  • Click on regression and select the Y values and X values. Select Labels and click OK.

Regression Output

Regression Equation

Value = 5.83 Revenue – 252.08

c)

d)

From the plot in part c we can conclude that the assumptions of about error terms are not satisfied and they suffer from heteroskedasticity. (Absence of constant variance leads to heteroskedestacity)


Related Solutions

Regression Analysis (use Excel) Need to determine if there is a relationship in the amount a...
Regression Analysis (use Excel) Need to determine if there is a relationship in the amount a household spends on prepared foods to family size and income. Need to be able to answer if the data is a good fit and what the exact relationship is between the dependent variable and the independent variables. Please use the below data and Excel to determine the equation that represents the relationship and explain the goodness of fit. Based on the data, how might...
Use Excel to prepare a Linear Regression Analysis. Use data samples below for populations and determine...
Use Excel to prepare a Linear Regression Analysis. Use data samples below for populations and determine if the selected independent variable is affecting the dependent variable. Use an alpha of 5% for ANOVA and Correlation Coefficient. Explain the results. Data samples Group A 104,103,101,99,97,101,101 Group B 101,100,95,99,101,103,97 Group C 100,96,99,95,99,102,106 Group D 97,99,99,101,105,100,99
Give three ways to check for outliers in a regression analysis.
Give three ways to check for outliers in a regression analysis.
Excel Lab 2: Regression and Goal Seek In this lab, you will use Excel to determine...
Excel Lab 2: Regression and Goal Seek In this lab, you will use Excel to determine the equation of the model which best fits a set of ordered pairs obtained from data sets. You will enter data, graph the data, find the equation for the regression model, and then use that equation to make predictions for the dependent variable. You will use the goal seek to make predictions for the independent variable. Then you will consider how accurate your predictions...
what is regression analysis?what are the major challenges associated with the use of regression analysis to...
what is regression analysis?what are the major challenges associated with the use of regression analysis to estimate the demand for a firm.
A regression analysis is conducted with 13 observations. a. What is the df value for inference...
A regression analysis is conducted with 13 observations. a. What is the df value for inference about the slope betaβ​? b. Which two t test statistic values would give a​ P-value of 0.05 for testing H0​:β =0 against Ha​: β ≠​0? c. Which​ t-score would you multiply the standard error by in order to find the margin of error for a 95%confidence interval for betaβ​?
how can regression analysis be used to determine causality?
how can regression analysis be used to determine causality?
i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as...
i. Use MS Excel Data Analysis ToolPak to perform a multiple regression analysis using Quality as the response variable and Helpfulness and Clarity as the explanatory variables. Write down the corresponding coefficient estimates and provide the regression output. j. Perform an F-test for the overall usefulness of the model in part i) using a 5% significance level. Make sure you follow all the steps for hypothesis testing indicated in the Instructions section and clearly state your conclusion. k. Test manually...
You will need to use Excel Regression Data Analysis to estimate the following linear model of...
You will need to use Excel Regression Data Analysis to estimate the following linear model of Texas Natural Gas Utility Residential Demand: (1) QRES = α + β1*PRES + β2*RES + β3*INCOME where QRES = Quantity demand (mcfs) of residential customers PRES = Price per mcf, RES = number of residential customers, INCOME = per capita income, The data below are actual publicly available residential demand data for city/town natural gas distribution utilities in Texas. You can copy and paste...
How can you perform simple linear regression analysis using Excel?
How can you perform simple linear regression analysis using Excel?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT