Question

Instructions for Excel

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

х Y 25 22 32 15.7 22.6 8 10.7 31.3 23.7 14 12 16 9 25 10 45.1 42 41.4 8.6 51

Instructions for Non-Excel

Using the tables produced by ‘Regression’:

a) shade the cell containing the proportion of the total variation in Y explained by the model in orange

b) shade the cell containing the best estimate of the effect that x has on y in yellow

c) shade the cell containing the best estimate of the y-intercept in blue

d) shade the cell containing the test statistic for testing whether there is a negative linear relationship between y and x in green

e) shade the cell containing the p-value for testing whether there is a linear relationship between Y and X in purple

f) change the font colour for the value of SE(b1) to blue

g) change the font colour for the value of SSM to green

h) change the font colour for the value of the total variation in y to red

i) shade the cells containing the lower and upper limits of the confidence interval for β1 to grey

j) change the font colour for the value of se to orange

Notes:

1) Do not shade the cells in such dark colours making reading the values in the cells difficult!

2) Bold the font colours that were changed making it easier to read the values.

Answer 1

We have to use Excel and solve: y = dependent variable and x = independent variable ##a) scatter plot : Scatter plot y = -1.5315x + 55.705 R2 = 0.6875 60 50 40 > 30 20 10 0 0 5 10 15 20 25 30 35 х

##b) fit regression model: DATA > Data Analysis > Regression > choose y range as y and x range as x SUMMARY OUTPUT Regression Statistics Multiple R 0.829140727 R Square 0.687474345 Adjusted R Squa 0.648408638 Standard Error 8.98779285 Observations 10 ANOVA df SS MS F Significance F 0.003017123 1 17.5978985 Regression Residual Total 8 1421.565638 1421.566 646.2433625 80.78042 2067.809 9 Coefficients 55.7046032 Intercept Standard Error t Stat 6.925830133 8.043022 0.365074007 -4.19498 P-value Lower 95% Upper 95% ower 95.0%pper 95.0% 4.20136E-05 39.73361027 71.6755961 39.73361 71.6756 0.003017123 -2.373342123 -0.6896178 -2.37334 -0.68962 х -1.531479954