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

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

orchestra answered 3 years ago

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...