Posted: September 16th, 2017
You work for a major consultancy firms in corporate finance.
Case 1
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:
· Analyze the two capital structures for the three different EBIT-scenarios for the shareholder by calculating the EPS for the six possible outcomes.
· 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.
· 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 2
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%
|
|
|
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:
- What is the expected return of the assets?
- What is the risk (variance and volatility) for the assets?
- What is the correlation between the two asset’s returns?
- 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.
- What is the variance and volatilityfor this 50%/50%-portfolio?
- What is the efficient frontier for the two assets given the information in the table above? (Create a suitable graph depicting this.)
- 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.
Grading of the cases
The above two cases are made up by ten sub-questions.
· For an E: Solve any two of the ten sub-questions correctly
· For a D: Solve any four of the ten sub-questions correctly
· For a C: Solve any six of the ten sub-questions correctly
· For a B: Solve any eight of the ten sub-questions correctly
· For a A: Solve all ten sub-questions correctly
Expert paper writers are just a few clicks away
Place an order in 3 easy steps. Takes less than 5 mins.