Question

Instructions for Excel Assignment 2 a) Using the data in the file (i.e., the values of Y and values of X), create a scatter plot with the variable Y plotted vertically and the variable X plotted horizontally. You are to add a trendline, the equation of the line and R to this scatter plot. You do not need to add a title to this plot. b) Run the 'Regression' analysis tool (found under DATA/Data Analysis). The output using this tool should result in 3 tables. The values in the table are to be formatted to 5 decimal places except for number of observations and df's. Make certain that when your columns are wide enough so that no descriptions in your output are cut-off.

Answer 1

Question (a)

The scatter plot is as follows

х Y 60 25 15.7 50 22 22.6 10.7 32 40 8 31.3 14 23.7 > 30 12 45.1 y = -1.5315x + 55.705 R2 = 0.6875 20 16 42 le 9 41.4 10 25 8.6 10 51 0 5 10 15 20 25 30 35 X

Question (b)

The regression output is as follows

х Y SUMMARY OUTPUT 25 15.7 22.6 22 32 10.7 31.3 8 Regression Statistics Multiple R 0.82914 R Square 0.68747 Adjusted R Square 0.64841 Standard Error 8.98779 Observations 10 14 23.7 45.1 12 16 9 42 41.4 8.6 25 ANOVA 10 51 df SS MS F Significance F 0.00302 1 17.59790 Regression Residual Total 1421.56564 646.24336 2067.80900 1421.56564 80.78042 8 9 t Stat Lower 95% Coefficients 55.70460 -1.53148 Standard Error 6.92583 0.36507 P-value 0.00004 Upper 95% 71.67560 Lower 95.0% 39.73361 Intercept X Variable 1 8.04302 39.73361 Upper 95.0% 71.67560 -0.68962 -4.19498 0.00302 -2.37334 -0.68962 -2.37334