Question

using Excel

Question No 1. (Marks 10) i. Find the correlation coefficients between wheat and all other independent variables and test the significance of population correlation coefficient using the value of r calculated for wheat and rainfall and alpha value of 0.02. ii. Estimate the multiple regression equation and report and interpret the results. iii. Check the significance or insignificance of the independent variables using alpha 0.01 and state whether the regression model is overall fit or not? iv. Find the predicted value of wheat for water=111, fertilizer = 113 and pesticide= 12. Write down first five residuals of the estimated equation.

Answer 1

Excel > Data > Data Analysis > Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.281179532
R Square	0.079061929
Adjusted R Square	0.011676217
Standard Error	23.46976611
Observations	45
ANOVA
	df	SS	MS	F	Significance F
Regression	3	1938.82388	646.2746268	1.173274367	0.331641182
Residual	41	22584.02676	550.829921
Total	44	24522.85064
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	156.9759072	57.47053394	2.731415501	0.009257536	40.91180936	273.0400051	40.91180936	273.0400051
Rainfall	0.10225057	0.196587703	0.52012699	0.605772383	-0.29476635	0.499267489	-0.29476635	0.499267489
Pesticide	-0.232545938	0.190739308	-1.21918204	0.229743027	-0.617751785	0.152659908	-0.617751785	0.152659908
Fertilizer	0.089801399	0.169024693	0.531291596	0.598083496	-0.251550894	0.431153692	-0.251550894	0.431153692

a)

Correlation coefficient (r) = 0.2812

Hypothesis:

H0​: ρ = 0

HA​: ρ not = 0

df = n−2 = 45−2 = 43, α = 0.02

Test:

t = r*SQRT((n-2)/(1-r^2)) = 0.2812*SQRT((45-2)/(1-0.2812^2)) = 1.9215

P value = 0.0613

P value > 0.02, Do not reject H0

There is not enough evidence to conclude that significant relationship between dependent and independent variables

Correlation coefficient between wheat and rainfall is 0.1925

Hypothesis:

H0​: ρ = 0

HA​: ρ not = 0

df = n−2 = 45−2 = 43, α = 0.02

Test:

t = r*SQRT((n-2)/(1-r^2)) = 0.1925*SQRT((45-2)/(1-0.1925^2)) = 1.2864

P = 0.2052 ≥ 0.02

P value > 0.02, Do not reject H0

There is not enough evidence to conclude that significant relationship between wheat and rainfall

b)

Y = 156.9759+0.1023*Rainfall-0.2325*Pesticide+0.0898*Fertilizer

c)

IV	Coefficients	Standard Error	t Stat	P-value		alpha	Significance
Rainfall	0.10225057	0.196587703	0.52012699	0.605772383	>	0.01	No
Pesticide	-0.23254594	0.190739308	-1.21918204	0.229743027	>	0.01	No
Fertilicer	0.089801399	0.169024693	0.531291596	0.598083496	>	0.01	No

Overall F test

F stat = 1.1733

P value = 0.3316

P value > 0.01, Overall model is not significant

R^2 = 0.0791 and it close to 0, which means model does not fit

d)

Water = 111, Fertilizer = 113 and pesticide = 12

Y = 156.9759+0.1023*Rainfall-0.2325*Pesticide+0.0898*Fertilizer

Y = 156.9759+0.1023*111-0.2325*12+0.0898*113 = 175.6886

Observation	Predicted Wheat	Residuals
1	145.2255817	18.75073825
2	150.386875	22.90631504
3	155.6244229	5.326347117
4	143.0967454	-15.26429536
5	143.3028411	-20.87583111