Question

In: Statistics and Probability

Use the data set which is labeled below and answer using regression output in excel i....

Use the data set which is labeled below and answer using regression output in excel

i. Find the correlation coefficients between Y and X1, X2 and X3 and test the significance of population correlation coefficient using the value of r calculated for X2 and X3.

ii. Estimate the regression equation for Y and X1, Y and X2 and report the results and explain the intercept and slope coefficients.

iii. Check the significance or insignificance of the independent variables? and explain the coefficient of determination and standard error of estimate.

iv. Find the predicted value of Y when X1= 20, X2= 30 and X3= 125. Write down first five residuals of the estimated equation.

Y X1 X2 X3
33.4283 32.96268 16.92215 30.06201
28.97037 7.616413 56.9856 36.19529
14.42695 59.20676 38.73281 3.194128
68.30308 9.144719 71.65822 41.90527
36.60967 39.26304 49.15915 67.94662
34.50435 35.52693 20.51122 45.46315
16.17359 48.01627 61.08484 57.07025
12.12781 62.64879 17.2385 19.76266
41.56441 17.74422 49.82528 71.12354
57.93912 12.90088 16.6303 58.51167
30.42961 37.25575 4.753624 31.63265
8.420667 40.63094 53.26063 25.18525
52.69253 39.63063 63.03867 9.87991
26.43062 60.18702 51.28675 50.65404
5.769524 46.60604 68.49467 17.30534
61.96707 49.63369 8.369427 54.37901
28.9013 20.58473 15.58098 60.54347
52.05759 35.05463 42.73849 41.27033
50.60503 54.25425 64.52019 23.08661
56.78954 47.49495 11.34584 6.163854
2.251747 21.84793 54.51714 30.57219
39.44349 52.53212 13.18604 29.91498
40.79134 6.021271 48.14325 58.69213
20.77187 71.46217 25.71548 56.11673
50.56493 40.35691 8.810541 24.7174
23.3896 56.12342 61.39897 5.450941
30.27143 43.4915 71.39979 70.67351
25.29218 28.32875 41.38618 8.670186
51.05506 17.87121 18.974 16.11789
68.49913 45.93991 43.32441 28.17725
4.693472 2.131443 9.668264 24.55031
11.82482 68.55483 47.26548 36.2599
17.95587 69.09842 71.66713 74.44749
22.78585 14.81906 55.73354 19.93866
73.03726 4.417219 45.30497 59.92413
56.59795 11.38371 3.88699 31.4366
2.14481 52.81506 45.32948 10.02026
74.33164 30.45189 43.83236 65.27543
2.222785 30.96207 56.2504 61.61284
51.18427 10.98492 35.03903 53.99805
63.76717 18.99628 47.82244 39.93808
66.87503 25.50829 65.897 55.5219
65.7767 2.634938 26.67122 33.77139

Solutions

Expert Solution

1. The correlation coefficient is not significant.

Y X1 X2 X3
Y 1.000
X1 -.121 1.000
X2 -.049 -.230 1.000
X3 .063 .063 .036 1.000

2. The regression equation is:

Y = 59.3685 - 0.1341*X1

The relationship is insignificant. (p > .05)

For every increase in X1, the value of Y will decrease by 0.1341.

When X1 is constant, the value of Y will increase by 59.3685.

The regression equation is:

Y = 56.0864 - 0.0560*X2

The relationship is insignificant. (p > .05)

For every increase in X2, the value of Y will decrease by 0.0560.

When X2 is constant, the value of Y will increase by 56.0864.

3. X1 is not significant. (p > .05)

X2 is not significant. (p > .05)

X3 is not significant. (p > .05)

The coefficient of determination is 0.026.

The standard error of the estimate is 28.826.

4. The predicted value of Y is 57.82.

Observation Y Predicted Residual
1 21.87838 48.76989 -26.89151
2 93.77804 56.24570 37.53234
3 72.82208 53.22351 19.59857
4 9.28010 58.60905 -49.32895
5 31.10620 54.08195 -22.97575

The Excel output is:

0.026
Adjusted R² 0.000
R   0.162
Std. Error   28.826
n   42
k   3
Dep. Var. Y
ANOVA table
Source SS   df   MS F p-value
Regression 854.3926 3   284.7975 0.34 .7945
Residual 31,575.9299 38   830.9455
Total 32,430.3225 41  
Regression output confidence interval
variables coefficients std. error    t (df=38) p-value 95% lower 95% upper
Intercept 62.0474
X1 -0.1610 0.1830 -0.880 .3846 -0.5314 0.2095
X2 -0.0973 0.1892 -0.514 .6099 -0.4803 0.2856
X3 0.0764 0.1639 0.466 .6440 -0.2555 0.4083
0.002
r   -0.049
Std. Error   28.440
n   42
k   1
Dep. Var. Y
ANOVA table
Source SS   df   MS F p-value
Regression 77.0951 1   77.0951 0.10 .7591
Residual 32,353.2274 40   808.8307
Total 32,430.3225 41  
Regression output confidence interval
variables coefficients std. error    t (df=40) p-value 95% lower 95% upper
Intercept 56.0864
X2 -0.0560 0.1814 -0.309 .7591 -0.4227 0.3106
0.015
r   -0.121
Std. Error   28.265
n   42
k   1
Dep. Var. Y
ANOVA table
Source SS   df   MS F p-value
Regression 473.7874 1   473.7874 0.59 .4458
Residual 31,956.5350 40   798.9134
Total 32,430.3225 41  
Regression output confidence interval
variables coefficients std. error    t (df=40) p-value 95% lower 95% upper
Intercept 59.3685
X1 -0.1341 0.1742 -0.770

Related Solutions

3.Below is the regression input and output (from Excel) for the regression in which the percentage...
3.Below is the regression input and output (from Excel) for the regression in which the percentage change in the exchange rate is the dependent variable (Y) and the inflation differential (inflation rate home – inflation rate foreign) is the independent variable (X). According to the Purchasing Power Parity theory, the hypothesized coefficients (null hypothesis) in the regression are 0 (intercept coefficient) and 1 (inflation differential coefficient).   The directions for Problem Set 1 instruct you to first read sections of the...
Please show all steps using data set below and excel. By using a bivariate regression trend...
Please show all steps using data set below and excel. By using a bivariate regression trend line model, forecast total houses sold for the next five years. Prepare a time series plot of these data (years are on the x axis, THS is on the y axis) that shows both actual and forecast THS for the next five years. Total Houses Sold Year Northeast 1996         74.00 1997         78.00 1998         81.00 1999         76.00 2000         71.00 2001...
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...
Please answer each question set on a separate Excel worksheet, labeled appropriately and include excel Formulas...
Please answer each question set on a separate Excel worksheet, labeled appropriately and include excel Formulas Question Set 1. You are in charge of quality control for computer monitors at Dell. You have data on twenty-five batches of monitors, tracking five types of defects: brightness, color, contrast, dead pixels, and stuck pixels. These data are given in the table below. 1. For each defect type, find the average number of defects per batch. So, you should have an average defect...
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
The multiple regression model is estimated in Excel and part of the output is provided below....
The multiple regression model is estimated in Excel and part of the output is provided below. ANOVA df SS MS F Significance F Regression 3 3.39E+08 1.13E+08 1.327997 0.27152899 Residual 76 6.46E+09 85052151 Total 79 6.8E+09 Question 8 (1 point) Use the information from the ANOVA table to complete the following statement. To test the overall significance of this estimated regression model, the hypotheses would state there is    between attendance and the group of all explanatory variables, jointly. there is...
Use the given data set to answer parts​ (a) and​ (b). a. Find the regression equation...
Use the given data set to answer parts​ (a) and​ (b). a. Find the regression equation for the data points. b. Graph the regression equation and the data points. x 5 4 33 1 2 y 0 2 0 4 6 a. Find the regression equation for the data points.
2. Solve using Microsoft Excel: Use the following data to find the equation of the regression...
2. Solve using Microsoft Excel: Use the following data to find the equation of the regression line. X-Bar 2 4 5 6 Y-Bar 7 11 13 20
The summary output of Paradise Retreats' model in Excel is in the tables below: Regression Statistics...
The summary output of Paradise Retreats' model in Excel is in the tables below: Regression Statistics Multiple R 0.764437898 R Square 0.5843653 Adjusted R Square 0.555700838 Standard Error 315.8931794 Observations 60 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Intercept 413 317.1379944 0.110883586 0.912472524 -613.4546286 683.785425 x1 15 7.187801286 1.304664049 0.202266328 -5.323038326 24.07837018 x2 5 9.990263453 3.509123492 0.001488311 14.62468522 55.48945112 According to the tables, which of the following statements about this regression model are true? Select all correct...
Use the data below to solve the following problem using excel: 1 a) Import the data...
Use the data below to solve the following problem using excel: 1 a) Import the data into an Excel file. Done! b) Create a new column in the spreadsheet to assign the category of each car according to the engine horsepower. For this exercise use IF statements in each cell to determine the class for each vehicle. i. Class 1 if the vehicle horsepower is less than 80 HP. ii. Class 2 if the vehicle horsepower is between 81 and...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT