Question

This is an open ended question.

Could someone run a bivarate and multivatrate regression analysis on either Excel Data Analysis or Excel QM. PLEASE ATTACH A COPY OF THE ORIGINAL EXCEL DATA ANALYS OR EXCEL QM TO YOUR ANSWER, I.E. NO SCREEN SHOTS.

Any data can be used, but the data used in the bivarate must be expanded upon in the multivriate analysis. PLEASE USE REAL DATA AND CITE THE SOURCE OF THE DATA.

The analysis must include the following:

Evaluation using the F and t statistics

Scattergram analysis, residual chart analysis

Discussion and analysis of the slope, y intercept, and regression equation

Discussion of the hypothesis and conclusions based on your analysis.

Thank you so much!

Answer 1

The~following table shows, for each of 18 cinchona plants, the yield of dry bark (in oz.), the height (in inches) and the girth (in inches) at a height of 6" from the ground (Source: Fundamentals of Statistics, Volume 1: Gun, Gupta, Dasgupta)

Here Y=Yield of dry bark, X1= Height, X2=Girth at a height.

First we find the linear regression of Y on X1 using excel. The outputs are as follows:

From the above analysis, we see that correlation coefficient of Y and X1 is 0.7679 which implies Y and X1 are highly +vely correlated and the predicted Y=8.0952+2.4318X1.

Since R-square=0.5896 means this regression line explains 58.96% of total variation of data. We also see that Y-intercept is insignificant since the corresponding p-value=0.1619>0.05 and slope is significantly different from zero since its p-value=0.0002<0.05 and the regression equation is also significant since its p-value=0.0002<0.05.  

From the above scatter plot and fitted line, we observe that the fitting is not highly satisfactory since there is a significant difference between observed and fitted values and this fact is also shown by the following table by computing their residuals:

Since Girth at a height of 6' (in.) (=X2, say) also influences Y so if we add X2 and perform multiple regression analysis then we get followings, where Y=Yield of dry bark (Dependent variable), X1=Height (Independent variable 1) and X2=Girth at a height of 6" (in.):

Here we see that R square=0.7679 which is greater than R-square of bivariate analysis and adjusted R square is closed to R square hence the new variable has significant effect. Here the modl is:

Y=-0.6507+1.7098X1+4.3432X2

Here we see that Y-intercept i.e. constant term is insignifiacnt however regression coefficients corresponding to X1 and X2 are significant and multiple regression is also significant (see ANOVA table). This fact is also observed from the following plot. Hence we conclude that addition of new variable improves the prediction of Y.