Question

3. HOMEWORK. The annual energy consumption in billions of Btu for both natural gas and coal is shown for a random selection of states. Gas 223 474 377 289 747 146 Coal 478 631 413 156 736 474 Source Time Almata Use Excel: a) Create a scatter plot. b) Find the linear regression equation for data. c) Find the linear correlation coefficient (7) d) Find the coefficient of determination () e) If 500 billion Btu of natural gas is used then what is the projected amount of coal that is used?

Answer 1

For the given data set the scatter-plot in excel is plotted as:

a) Scatter-plot

Scatter-plot 0 100 200 300 400 500 600 700 800

The following regression equation, Correlation, and coefficient of determination are calculated and given in the excel sheet below as:

Gas	Coal	Formula used
223	478
474	631
377	413
289	356
747	736	Slope(b)=	0.5286	=LINEST(B2:B7,A2:A7,TRUE,TRUE)'
146	474	Intercept(C)=	315.8987	=INTERCEPT(B2:B7,A2:A7)'
		b) Regreesion Equation=	yˆ=0.5286*X + 315.8987	yˆ=b*X+C
		c) Correlation,r=	0.7996	=CORREL(A2:A7,B2:B7)'
		d) Coefficient of determination ,r2=	0.6393	=POWER(D8,2)'

e) At X=500 based on the linear regression equation calculated above the predicted value is:

yˆ=0.5286*X + 315.8987

=> yˆ=0.5286*500 + 315.8987

=>yˆ=580.1987

Note : The values can be calculated manually but since it is asked by using the excel hence i am giving the excel formula and values