Question

In: Statistics and Probability

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

Solutions

Expert Solution

i) Group A and Group B

Regression line equation: y=98.11304347828+0.013043478260679x

Correlation coefficient (r): 0.01130828

SUMMARY OUTPUT A&B
Regression Statistics
Multiple R 0.011308
R Square 0.000128
Adjusted R Square -0.19985
Standard Error 2.563316
Observations 7
ANOVA
df SS MS F Significance F
Regression 1 0.004202 0.004202 0.000639 0.980804
Residual 5 32.85294 6.570588
Total 6 32.85714
Coefficients Standard Error t Stat P-value Lower 95% Upper 95%
Intercept 99.88235 38.56015 2.5903 0.048819 0.760322 199.0044
X Variable 1 0.009804 0.387695 0.025288 0.980804 -0.9868 1.006406

ii)  Group A and Group C

Regression line equation: y=82.469565217373+0.16956521739148x

Correlation coefficient (r): 0.107523

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.107523
R Square 0.011561
Adjusted R Square -0.18613
Standard Error 2.548618
Observations 7
ANOVA
df SS MS F Significance F
Regression 1 0.37987 0.37987 0.058482 0.818516
Residual 5 32.47727 6.495455
Total 6 32.85714
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 5.0% Upper 5.0%
Intercept 94.06818 28.08963 3.348858 0.020354 21.86149 166.2749 92.21666 95.91971
X Variable 1 0.068182 0.281939 0.241831 0.818516 -0.65657 0.79293 0.049598 0.086766

iii) Group A and Group D

Correlation coefficient (r): -0.9339

Regression line equation: y=201.29565217391-1.0043478260869x

SUMMARY OUTPUT A&D
Regression Statistics
Multiple R 0.933915
R Square 0.872197
Adjusted R Square 0.846636
Standard Error 0.916433
Observations 7
ANOVA
df SS MS F Significance F
Regression 1 28.65789 28.65789 34.12265 0.00208
Residual 5 4.199248 0.83985
Total 6 32.85714
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 5.0% Upper 5.0%
Intercept 187.6992 14.87054 12.62222 5.54E-05 149.4733 225.9252 186.7191 188.6794
X Variable 1 -0.86842 0.148665 -5.84146 0.00208 -1.25058 -0.48627 -0.87822 -0.85862

iv) Group B and Group C

Regression line equation: y=110.29411764707-0.10784313725499x

Correlation coefficient (r): -0.0788778

SUMMARY OUTPUT b&C
Regression Statistics
Multiple R 0.078878
R Square 0.006222
Adjusted R Square -0.19253
Standard Error 2.94762
Observations 7
ANOVA
df SS MS F Significance F
Regression 1 0.271978 0.271978 0.031303 0.866509
Residual 5 43.44231 8.688462
Total 6 43.71429
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 5.0% Upper 5.0%
Intercept 105.1731 32.48723 3.237367 0.023017 21.66199 188.6842 103.0317 107.3145
X Variable 1 -0.05769 0.326079 -0.17693 0.866509 -0.8959 0.78052 -0.07919 -0.0362

v) Group B and Group D

Correlation coefficient (r): 0.2208203

Regression line equation: y=79.529411764708+0.20588235294116x

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.22082
R Square 0.048762
Adjusted R Square -0.14149
Standard Error 2.883841
Observations 7
ANOVA
df SS MS F Significance F
Regression 1 2.131579 2.131579 0.256306 0.634196
Residual 5 41.58271 8.316541
Total 6 43.71429
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 5.0% Upper 5.0%
Intercept 75.74436 46.79478 1.61865 0.166447 -44.5455 196.0342 72.65989 78.82883
X Variable 1 0.236842 0.467821 0.506267 0.634196 -0.96573 1.439414 0.206006 0.267678

vi) Group C and Group D

Regression line equation: y=113.40384615385-0.13461538461538x

Correlation coefficient (r): -0.19740215

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.197402
R Square 0.038968
Adjusted R Square -0.15324
Standard Error 3.963082
Observations 7
ANOVA
df SS MS F Significance F
Regression 1 3.184211 3.184211 0.202738 0.671371
Residual 5 78.53008 15.70602
Total 6 81.71429
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 5.0% Upper 5.0%
Intercept 128.5188 64.30712 1.998516 0.102133 -36.7879 293.8255 124.28 132.7576
X Variable 1 -0.28947 0.642897 -0.45026 0.671371 -1.94209 1.363145 -0.33185 -0.2471

a) we have to conclude that Relation between

i) Group A and Group B

ii)Group A and Group C

iii) Group B and Group D  this data has no correation between them beacause there correlation value is very low

b) Group A and Group D this two group is strong negative correlation

c) Group B and Group C is moderate neagative correlation between them

d) Group C and Group C is very low negative correlation between them


Related Solutions

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...
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 estimate a simple linear regression model for the following data (Y is a...
Use Excel to estimate a simple linear regression model for the following data (Y is a dependent variable and X is an independent variable): Y X 0 -2 0 -1 1 0 1 1 3 2 Fill in Multiple Blanks: What is the slope of the estimated line?  In your answer, show one (1) digit to the right of the decimal point, for example, 1.0, 1.2. Apply the appropriate rounding rule if necessary. What is the Y-intercept?
(20 pts) Use the “Distance.sav” (SPSS) data set (located below) to perform a linear regression analysis....
(20 pts) Use the “Distance.sav” (SPSS) data set (located below) to perform a linear regression analysis. This dataset shows how far on average a person in Illinois drives each year. Write your findings using the format presented in the class slides. (2 pts) How much of the variation in the dependent variable is explained by the variation in the independent variable? What statistic did you use? (2 pts) Is the linear model significantly different than zero? Why or why not?...
Run a linear regression using Excel’s Data Analysis regression tool. Construct the linear regression equation and...
Run a linear regression using Excel’s Data Analysis regression tool. Construct the linear regression equation and determine the predicted total sales value if the number of promotions is 6. Is there a significant relationship? Clearly explain your reasoning using the regression results. Number of Promotions Total Sales 3 2554 2 1746 11 2755 14 1935 15 2461 4 2727 5 2231 14 2791 12 2557 4 1897 2 2022 7 2673 11 2947 11 1573 14 2980
Use Simple Linear Regression (excel) on the table below. What is the MAD? Period Month Demand...
Use Simple Linear Regression (excel) on the table below. What is the MAD? Period Month Demand 37 January 7077 38 February 7050 39 March 5430 40 April 5475 41 May 5504 42 June 6246 43 July 6361 44 August 6358 45 September 6379 46 October 6430 47 November 6720 48 December 7107
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  ...
Enter the data from the table below into Microsoft Excel and determine the linear correlation coefficient,...
Enter the data from the table below into Microsoft Excel and determine the linear correlation coefficient, R2 for time versus position. Then, calculate y/t and determine the linear correlation coefficient, R2 for time versus y/t. Write in the calculated values in the table below (use two decimal places). Time, t (s) Position, y (cm) Velocity, y/t (cm/s) 0/60 0.00 1/60 2.41 2/60 5.09 3/60 8.08 4/60 11.30 5/60 14.79 6/60 18.56 7/60 22.52 8/60 26.96 9/60 31.56 Determine the linear...
From the table below, use a simple linear regression analysis to establish the relationship that may...
From the table below, use a simple linear regression analysis to establish the relationship that may exist between a) number of confirmed cases and deaths; b) number of confirmed cases and number of tests performed; c) number of confirmed cases and number of recoveries; and d) number of deaths and number of recoveries. Briefly discuss these relations. Date Total confirmed Death Recoveries Test 1-Apr 195 5 3 12046 2-Apr 204 5 3 12046 3-Apr 205 5 3 12046 4-Apr 214...
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...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT