Case.

You work for a major consultancy firms in corporate finance. Your firm has been approached by one

of its major clients to assist them in solving a problem that they have. You have been assigned the

task to solve the client’s problem.

A shareholder of a firm is wondering what to do in the following situation. The firm is considering an

investment that will have one of three EBIT next year: $1,000,000; $2,000,000 or $3,000,000. The

issue that the shareholder wants your help with is concerning how to finance the investment. The

firm has two possible ways of financing the investment; either by issuing new shares or by a

combination of issuing shares and issuing bonds. If fully equity funded the firm will issue 100,000

shares and sell them at $100 a share. If the firm chooses to use debt as part of the financing it will

only issue 60,000 shares at $100 and raise $10,000,000 by selling bonds at a yield of 15%.

You are asked to do the following by the shareholder using a spreadsheet program:

x Analyze the two capital structures for the three different EBIT-scenarios for the shareholder

by calculating the EPS for the six possible outcomes.

x Use the above to create a graph showing EBIT on the x-axis and EPS on the y-axis for the two

capital structure alternatives so the shareholder can see how they relate to each other.

x Solve for the break-even EBIT so the shareholder knows above what EBIT leverage is

preferable.

N.B. Since the client wants to be able to use this spreadsheet in the future for other projects as well

you need to use cell references in the formulas. Case ʹ

You have also been approached by a friend who is evaluating an investment problem. Knowing that

you have studied finance, you are asked to help him answering the below questions.

Assume the following expected data on two assets:

Return

State Probability state Bond Stock

Recession 25% 20% -15%

Normal 50% 10% 0%

Boom 25% 5% 20%

The stated probabilities shall be changeable as well as the returns in the different states. Build a

spreadsheet model to answer the following question:

x What is the expected return of the assets?

x What is the risk (variance and volatility) for the assets?

x What is the correlation between the two asset’s returns?

x Assume a portfolio of the two assets. What is the expected return on this portfolio (the user

shall be able to change the percentage weight of the assets in the portfolio)? Use a 50%/50%

weights to answer this question.

x What is the variance and volatility for this 50%/50%-portfolio?

x What is the efficient frontier for the two assets given the information in the table above?

(Create a suitable graph depicting this.)

x What is the minimum variance portfolio and what is the return of this portfolio, i.e. what

fraction of the stock and the bond is it made up of? Use the information in the table above

for this question. Hint: Use the solver in excel (include solver results) if you cannot solve this

algebraically.

N.B. Since the client wants to be able to use this spreadsheet in the future for other projects as well

you need to use cell references in the formulas.