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 Xplotted 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. Instructions for Non-Excel Assignment 6 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 Xin purple f) change the font colour for the value of SE(bı) 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 shade the cells containing the lower and upper limits of the confidence interval for B1 to grey change the font colour for the value of se to orange i) j) 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

(a) The scatter plot is:

(b) The output is:

Regression Statistics
Multiple R	0.95679
R Square	0.91544
Adjusted R Square	0.90487
Standard Error	4.48450
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1741.69772	1741.69772	86.60516	1.45E-05
Residual	8	160.88628	20.11078
Total	9	1902.58400
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	64.20610	3.52633	18.20761	0.00000	56.07436	72.33784	56.07436	72.33784
X	-1.28402	0.13797	-9.30619	0.00001	-1.60219	-0.96585	-1.60219	-0.96585

(c) 0.91544

(d) -1.28402