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 |
using Excel Question No 1. (Marks 10) i. Find the correlation coefficients between wheat and all...
1. GDP Population Inflation interest rate 78 14 111 0.09 88 15 112 0.1 45 25 113 0.12 65 32 114 0.14 98 25 112 0.06 88 45 112.5 0.05 85 47 114 0.111 68 85 107.5 0.124 92 66 107 0.123 81 63 106 0.147 79 54 105.5 0.159 77 87 105 0.25 73 25 104 0.16 76 45 110 0.12 66 30 102 0.08 80 50 100 0.09 Considering GDP as dependent variable and other variables as independent...
Using the Excel’s Regression Tool, develop the estimated regression equation to show how income (y annual income in $1000s) is related to the independent variables education (x1 level of education attained in number of years), age (x2 in years), and gender x3 dummy variable, 1= female, 0 = male. Develop the dummy variable for the gender variable first. Use the t test to test whether each of the coefficients obtained in part (a) are significant at .05 level of significance....
C2) The following data represent the age of the car and its price, in dollars, in one of the companies in some state. [ARI: 10 Marks] Price, in 1000 17.5 12 9.7 6.4 Car's age 10 (1 Marks) (4 Marks) (3 Marks) (1 Marks) (1 Marks) a) Identify the independent and dependent variables. b) Find the correlation coefficient. Interpret the result. c) Find the estimated regression equation. d) Interpret the coefficients e) Predict the price of a car if it...
Question 6 (10 marks) Finally, the researcher considers using regression analysis to establish a linear relationship between the two variables – hours worked per week and yearly income. a) What is the dependent variable and independent variable for this analysis? Why? (2 marks) b) Use an appropriate plot to investigate the relationship between the two variables. Display the plot. On the same plot, fit a linear trend line including the equation and the coefficient of determination R2 . (2 marks)...
Question 7 Solve the problem. Consider the data set shown below. Find the estimate of the y-intercept of the v 03 23 8 10 11 x -2 0 2 4 6 8 10 1.5 0.94643 0 1.49045 0.9003 n below. Find the estimate of the y-intercept of the least squares regression line 10 11 8 10 tion will save this response. Provide an appropriate response. In an area of the Midwest records were kept on the relationship between the rain...
The following exercise require a computer and software.Calculate the coefficients of correlation for each pair of independent variables in Exercise. What do these statistics tell you about the independent variables and the t-tests of the coefficients?ExerciseThe following exercises require the use of a computer and statistical software. Exercises below can be solved manually.A developer who specializes in summer cottage properties is considering purchasing a large tract of land adjoining a lake. The current owner of the tract has already subdivided...
Need to understand if I am evaluating this correctly. Also where a correlation evaluation would fit? We were unable to transcribe this imageInvestment Homework Saved ge Layout Formulas Data Review View Help Tell me what you want to do A" A" | Ξ Ξ 9-2ewrap Text General ·E Ξ Ξ 트ー園Merge & Certor . $ ·% , too 48 Conditional Format as CellIns Formatting, Table, Styles' Conditional Format as Styles ㄧ Alignment Number Styles Testing for Overall Significance Ha: Not...
Quiz: Quiz CH 10811 Submit Quiz This Question: 1 pt 9 of 15 (1 complete) This Quiz: 15 pts possibl The data show systolic and diastolic blood pressure of certain people. Find the regression equation, letting the systolic reading be the independent (x) variable. Find the best predicted diastolic pressure for a person with a systolic reading of 113. Is the predicted value close to 72.7, which was the actual diastolic reading? Use a significance level of 0.05. Systolic Diastolic...