Question

Can you please show how you would solve the following using excel? Step by Step?

Kaelea, Inc., has no debt outstanding and a total market value of $117,000. Earnings before interest and taxes, EBIT, are projected to be $8,300 if economic conditions are normal. If there is strong expansion in the economy, then EBIT will be 23 percent higher. If there is a recession, then EBIT will be 32 percent lower. The company is considering a $41,700 debt issue with an interest rate of 5 percent. The proceeds will be used to repurchase shares of stock. There are currently 3,900 shares outstanding. Assume the company has a market-to-book ratio of 1.0.

a. Calculate return on equity, ROE, under each of the three economic scenarios before any debt is issued, assuming no taxes. (Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.)

	ROE
Recession	%
Normal	%
Expansion	%

b. Calculate the percentage changes in ROE when the economy expands or enters a recession, assuming no taxes. (A negative answer should be indicated by a minus sign. Do not round intermediate calculations and enter your answers as a percent rounded to the nearest whole number, e.g., 32.)

	%ΔROE
Recession	%
Expansion	%

  
Assume the firm goes through with the proposed recapitalization and no taxes.

c. Calculate return on equity, ROE, under each of the three economic scenarios after the recapitalization. (Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.)

	ROE
Recession	%
Normal	%
Expansion	%

d. Calculate the percentage changes in ROE for economic expansion and recession. (A negative answer should be indicated by a minus sign. Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.)

	%ΔROE
Recession	%
Expansion	%

Assume the firm has a tax rate of 40 percent.

e. Calculate return on equity, ROE, under each of the three economic scenarios before any debt is issued. Also, calculate the percentage changes in ROE for economic expansion and recession. (A negative answer should be indicated by a minus sign. Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.)

	ROE
Recession	%
Normal	%
Expansion	%

	%ΔROE
Recession	%
Expansion	%

f. Calculate return on equity, ROE, under each of the three economic scenarios after the recapitalization. Also, calculate the percentage changes in ROE for economic expansion and recession, assuming the firm goes through with the proposed recapitalization. (A negative answer should be indicated by a minus sign. Do not round intermediate calculations and enter your answers as a percent rounded to 2 decimal places, e.g., 32.16.)

	ROE
Recession	%
Normal	%
Expansion	%

	%ΔROE
Recession	%
Expansion	%

Answer 1

	Recession	Normal	Expansion
EBIT	5,644	8,300	10,209
Less: Interest	0	0	0
Earnings before taxes	5,644	8,300	10,209
Less: Taxes	0	0	0
Net Income	5,644	8,300	10,209
Equity	117,000	117,000	117,000
ROE	4.82%	7.09%	8.73%
% change
Recession	-2.27%
Expansion	1.63%
b-1
	Recession	Normal	Expansion
EBIT	5,644	8,300	10,209
Less: Interest	2085	2085	2085
Earnings before taxes	3,559	6,215	8,124
Less: Taxes	0	0	0
Net Income	3,559	6,215	8,124
Equity	75,300	75,300	75,300
ROE	4.73%	8.25%	10.79%
% change
Recession	-3.53%
Expansion	2.54%

With tax

	Recession	Normal	Expansion
EBIT	5,644	8,300	10,209
Less: Interest	0	0	0
Earnings before taxes	5,644	8,300	10,209
Less: Taxes	2,258	3,320	4,084
Net Income	3,386	4,980	6,125
Equity	117,000	117,000	117,000
ROE	2.89%	4.26%	5.24%
% change
Recession	-1.36%
Expansion	0.98%

	Recession	Normal	Expansion
EBIT	5,644	8,300	10,209
Less: Interest	2085	2085	2085
Earnings before taxes	3,559	6,215	8,124
Less: Taxes	1,424	2,486	3,250
Net Income	2,135	3,729	4,874
Equity	75,300	75,300	75,300
ROE	2.84%	4.95%	6.47%
% change
Recession	-2.12%
Expansion	1.52%