Account question

MEM-1

ThirdCapitalStructure.xlsx

Home >Business & Finance homework help >Accounting homework help >Account question

Instructions

Instructions/reminders

1. Only use named cells if I ask you to.

2. Always use cell references when possible. Avoid hard coding numbers into functions.

3. If you are confused about the instructions, please ask for clarification on the discussion board.

4. Don't wait until the last minute, and take advantage of all available attempts.

5. You will be asked to use the SUMPRODUCT function and Goal Seek (found in the Data tab).

6. If you think ExPrep graded something incorrectly, send me an email and I will be happy to look into it.

74540

Portfolio Returns

You work as an economic analyst for an investment firm. You believe there are four possible states for the economy over the next year: Boom, Good, Poor, Bust. Your colleague Tosha has estimated the returns for three stocks based on those four scenarios. One of your clients has a portfolio that is 30.00% invested in Stock A and 30.00% invested in Stock C. The rest of your client's portfolio is invested in Stock B. Use the information below to calculate the expected return, variance, and standard deviation on your client's portfolio.

1. First fill in the missing probability and portfolio weight (cells D19 and F22).

2. Calculate the actual return for the portfolio in each state.

3. Use those portfolio returns and the probabilities to calulcate the portfolio's expected return using the SUMPRODUCT function.

4. Calculate the squared deviation from the mean for each state of the economy.

5. Use the SUMPRODUCT function to get the variance (the probability weighted average of the squared deviations).

6. Use the POWER function to convert the variance into the standard deviation.

19 points

State

Probability

Stock A

Stock B

Stock C

Boom

0.15

35%

40%

28%

Good

0.45

16%

17%

Poor

0.30

-1%

-3%

Bust

-10%

-12%

-9%

Stock A

Stock B

Stock C

portfolio weights

30%

Probability

Portfolio Return

Squared Deviation

Boom

0.15

Good

0.45

Poor

0.30

Bust

Portfolio E(R)

Portfolio Variance

Portfolio Standard Deviation

74540

Portfolio Weights

You have another client that is invested in three stocks: Stock X, Stock Y, Stock Z. She currently has invested 20.00% invested in Stock X and 60.00% invested in Stock Z, but she would like her target expected return to be 10.00%. Use Excel's Goal Seek (or Solver) to calculate how much she needs to shift from Stock Z into Stock X to hit her target expected return (the allocation to Stock Y will stay the same). Note: You can work this out by hand, but using Excel is quicker.
11 points
State	Probability	Stock X	Stock Y	Stock Z
Boom	0.15	50%	40%	15%
Good	0.35	20%	12%	12%
Poor	0.30	-1%	-3%	1%
Bust	0.20	-15%	-9%	-9%
	E(R) on the stock	11.20%	7.50%	4.95%
	Currrent portfolio weights	0.20	0.20	0.60
	Current portfolio return	6.71%
	Target portfolio return	10.00%
HINT: In the yellow area below, write the current portfolio weights and then write the formula to calculate the portfolio return by referencing the weights in the yellow section. If done correctly, you should get the same expected return as listed above. Next, re-write the portfolio wieght on Stock Z as one minus the portfolio weights on X and Y (your answer should not change if done correctly). Re-writing the portfolio weight this way will guarantee that Excel returns portfolio weights that add to one. In addition, as the weight on X changes, so will the weight on Z but not the weight on Y. When you use Goal Seek (or Solver), cell E31 will be the only cell that Goal Seek should change.
	New Portfolio Return
	New portfolio weights
					74540

WACC

Your boss needs you to calculate the weighted average cost of capital for Spacely Sprockets. To this end, she has provided you information about the firm's capital structure. Use the information to:

1. Cacluate the market value of debt and equity.

2. Use the YIELD function to calculate the cost of debt.

Note about the YIELD function's arguments: This function uses information you could find in the Wall Street Journal about a bond. "Settlement date" should reference today's date (it represents when you will receive the bond, and we want the yield for someone that currently owns it). "Rate" is the coupon rate (the yield to maturity is what the function returns). "Pr" is the price quoted as a percent of par. "Redemption" is the percent of par paid at maturty (typically 100%). "Frequency" is the number of coupons paid each year. Leave "Basis" blank. Reminder about bond terminology: Par value represents the amount bondholder lent the company. Maturity is when the loan must b repaid. Coupons are the interest payments made on the loan. Bond prices are typically quoted as a percent of par.

3. Calculate the cost of common and preferred equity.

4. Calcuate the WACC.

18 points

Tax rate

21%

Debt

Bonds outstanding

50,000

Current date

01/01/22

Maturity date

01/01/45

Annual coupon rate

5.00%

Coupons per year

Bond price (% of par)

105

Redemption (% of par)

100

Par value ($)

$ 1,000

Common stock

Shares outstanding

600,000

Beta

1.30

Share price

$ 81

Preferred stock outstanding

Shares outstanding

100,000

Coupon rate

4.00%

Par value

$ 100

Share price

$ 94

Market

Market risk premium

8.00%

Risk-free rate

3.20%

Market value of debt

Market value of common equity

Market value of preferred

Market value of firm

Pretax cost of debt

Aftertax cost of debt

Cost of common equity

Cost of preferred

Debt's capital structure weight

Preferred equity's weight

Common equity's weight

WACC

74540

Float

Latte Larry has a target debt-to-equity ratio of 0.6 and is planning to expand into new markets. The expansion requires initial net capital spending of $73,000,000, but you estimate the expansion will generate after-tax cashflows of $7,000,000 in the first year, and the cash flows will grow at 3.0% in perpetuity. The company plans to use all external financing. The floatation cost of debt is 3.0% and the floatation cost of equity is 6.0%.

1. Calculate the firm's weighted average float (as a percentage).

2. Calculate the total cost of the project, including floatation)

3. Calculate the project's NPV given the WACC is 12.0%.

13 points

Debt-equity ratio

0.60

Projected cost

$ 73,000,000

Aftertax cash flow (year 1)

$ 7,000,000

Cashflow growth rate

3.0%

Equity floatation costs (%)

6.0%

Debt floatation costs (%)

3.0%

WACC

12.0%

Debt-to-Asset ratio

Equity-to-Asset ratio

Floatation cost (%)

Project cost ($)

NPV

74540

Leverage

Usher Housing is considering two different capital strucutres, the first of which is an all-equity plan with 200,000 shares outstanding. Under the second option the firm would have 125,000 shares outstanding along with $800,000 debt outstanding.

a. For both capital structures, calculate net income and earnings per share if EBIT is $300,000.

b. For both capital structures, calculate net income and earnings per share if EBIT is $600,000.

c. Calculate the breakeven EBIT.

14 points

Unlevered

Shares outstanding

200,000

Levered

Shares outstanding

125,000

Debt outstanding

$ 800,000

Interest rate

EBIT

$ 300,000

EBIT

$ 600,000

Net income

EPS

Plan I

Plan II

Plan I

Plan II

Breakeven EBIT

74540

Homemade Leverage

Iranzo Ammunition is an all-equity firm that currently has 6,000,000 shares outstanding worth $50 per share. The company's considering converting to a capital structure that is 50.0% debt. The firm expects EBIT to remain at $40,000,000 in perpetuity, and the interest rate on the debt would be 8.0%. Ignore taxes.

a. If Javier owns 100 shares of stock, calculate his annual cash flow under the current capital structure.

b. Calculate Javier's annual cash flow under the proposed capital structure.

c. Assuming the firm decides to change the capital structure, calculate the number of shares Javier would need to sell to replicate

the current all-equity capital structure. Show that by undoing the leverage his cash flow would be the same as in part a.

13 points

Company:

Percent debt under proposal

50%

Shares currently outstanding

6,000,000

Current Stock Price

$ 50

EBIT

$ 40,000,000

Interest rate

Shareholder:

Shares owned

100

EPS (unlevered firm)

Shareholder's cash flow

Value Unlevered Firm

Value of Debt (proposed)

Shares bought back with debt

Net Income (levered firm)

EPS (levered firm)

Shareholder's cash flow

Javier should sell

lend the proceeds at

Annual interest Javier would receive

Cash flow from shares Javier holds

Total cash flow to Javier

74540

M&M Props

The Great Indoors is an all-equity firm and expects EBIT to be $1,400,000 in perpetuity. The firm's tax rate is 21.0% and the cost of unlevered equity is 14.0%.

The firm is considering changing capital structure by issuing $2,000,000 in debt at 6.0% in interest.

a. Calculate the value of the unlevered and levered firm.

b. Calculate the WACC for the levered firm.

12 points

EBIT

$ 1,400,000

Interest rate

Cost of equity

14%

Tax rate

21%

Amount borrowed

$ 2,000,000

Debt-Equity ratio

WACC

74540