Question

A study tried to relate the number of calories in beer to the percentage of alcohol and number of carbohydrates. Use the accompanying data table to perform a natural logarithmic transformation of the dependent variable​ (calories). Using the transformed dependent variable and the percentage of alcohol and the number of carbohydrates as the independent​ variables, perform a multiple regression analysis. Then answer parts​ (a) through​ (f). a. State the regression equation, Fstat, and critical value

how do you find each variable in the regression equation? please show steps in excel.

Calories (Y)	Alcohol % (X1)	Carbohydrates (X2)
94	4.1	3.2
153	4.8	12.5
147	5.4	8.8
144	4.9	10.6
168	5.8	12.5
174	6.2	11.1
147	4.5	13.4
147	4.8	13.9
149	5.5	9.8
109	4.1	7.2
143	5.8	6.6
152	4.7	13.8
104	4.1	5.7
144	4.5	12.9
151	4.6	14.1
112	4.2	6.7
143	4.7	13.1
109	4.1	6.9
98	4.4	3.4
113	3.7	8.2
146	4.7	11.8
179	5.7	14.2
159	4.7	17.9
159	4.9	19.9
165	5.2	16.9
162	4.8	19.6

Answer 1

Excel Goto Data > Data analysis > Regression > select y and then in x select both x1 and x2

> ok

================================

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9761
R Square	0.9527
Adjusted R Square	0.9486
Standard Error	5.4806
Observations	26
ANOVA
	df	SS	MS	F	Significance F
Regression	2	13923.17641	6961.58821	231.76339	5.72984E-16
Residual	23	690.8620476	30.0374803
Total	25	14614.03846
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-4.8713	8.5026	-0.5729	0.5723	-22.4602	12.7177	-22.4602	12.7177
Alcohol % (X1)	22.5361	1.8404	12.2450	0.0000	18.7289	26.3434	18.7289	26.3434
Carbohydrates (X2)	3.3352	0.2525	13.2063	0.0000	2.8128	3.8577	2.8128	3.8577

================================

Regression equation

==================================

F_stat = 231.76339

==================================

F_crit = F.INV.RT(0.05,2,23) = 3.4221

==================================

x₁= It is positive and statistically significant , because p-value is less than 0.05

same for x₂