Question

Profitability remains a challenge for banks and thrifts with less than​ $2 billion of assets. The business problem facing a bank analyst relates to the factors that affect return on assets​ (ROA), an indicator of how profitable a company is relative to its total assets. Data collected from a sample of 20 community banks include the ROA​ (%), the efficiency ratio​ (%), as a measure of bank productivity​ (the lower the efficiency​ ratio, the​ better), and total​ risk-based capital​ (%), as a measure of capital adequacy. Complete parts​ (a) through​ (g) below.

a. State the multiple regression equation.

Let X1i represent the efficiency ratio​ (%) and let X2i represent the total​ risk-based capital​ (%).

Yi=( )+(    ) X1i+ ( ) X2i

​(Round the constant to two decimal places as needed.)

ROA	Efficiency Ratio	Total RiskBased Capital
0.54	74.69	15.33
4.41	86.31	10.96
1.55	71.04	14.3
1.29	54.81	19.19
1.07	70.15	16.61
4.55	86.21	20.78
1.3	51.32	24.04
1.1	79.47	10.53
0.73	61.73	12.5
1.17	62.47	11.67
0.6	63.31	13.94
1.23	58.77	14.92
0.9	72.43	15.52
0.8	72.42	17.97
1.06	57.09	14.62
1.06	59.37	14.06
0.9	64.53	13.64
1.75	55.68	17.42
7.27	81.4	27.77
0.97	55.14	15.44
1.7125	66.917	16.0605

How is this solved in excel? thanks

Answer 1

first enter data into excel then click on data tab

Then click on Data Analysis and select Regression

In Input range Y ROA

and in Input range X select Efficiency Ratio & Total RiskBased Capital

Click on labels if your selecting the data with label

finally click on ok

Output

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.807251393
R Square	0.651654812
Adjusted R Square	0.612949791
Standard Error	1.032055458
Observations	21
ANOVA
	df	SS	MS	F	Significance F
Regression	2	35.86628256	17.93314128	16.83644127	7.55246E-05
Residual	18	19.17249244	1.065138469
Total	20	55.038775
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-8.001097255	1.725301449	-4.63750683	0.000204667	-11.62582109	-4.37637342
Efficiency Ratio	0.093132631	0.021983043	4.236566849	0.000496241	0.046947972	0.139317291
Total RiskBased Capital	0.216770398	0.0552276	3.925037458	0.000992471	0.100741516	0.332799279

so from last table we can write the regression equation as

ROA =-8.00+0.09*Efficiency Ratio + 0.22*Total RiskBased Capital