Question

James Jesse, the head of the bureau of the animal husbandry, perceives that several agencies received large increases in appropriations last year because they encouraged sample data for five agencies similar to jesse's were gathered. Using regression analysis, calculate a regression equation. Does a relationship exist? The bureau of animal husbandry could pressure 15 groups to testify for it. What percentage increase in appropriation would this number of groups predict? Place a 90% confidence interval around that estimate. How large an increase is each additional interest group worth? Interest groups testifying 25,14,7,18,10. % increase in appropriation 22,17,8,19,12

How do you calculate all of this in excel

Answer 1

The regression analysis is done in excel by following these steps,

Step 1: Write the data values in excel. The screenshot is shown below,

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Y' column, Input X Range: 'X' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

The regression equation is,

The standard error of the regression is,

For Xpredictor = 15,

Now, the confidence interval for x = 15 is obtained using the formula,

From the data values,

	Y	X	X^2
	25	22	484
	14	17	289
	7	8	64
	18	19	361
	10	12	144
Sum	74	78	1342

The t-critical value is obtained from t-distribution table for significance level = 0.10 and degree of freedom = n - 2 = 3. In excel use function =T.INV.2T(0.1,3)