1. There are two excel files that you need for this assignment

File A. excel file for relevant Data:

?Assignment_2 data? for the data tables.

File B. excel file for your answer. You need to use this file for assignment submission:

?Name_section_Assignment_2? for answers.

2. Download these files. 2. Write all the group members Name and ID in the first worksheet (top left corner) of the file: ?Name_section_Assignment_2? 3. Present your answers to the designated excel worksheets (i.e. Problem 1 and Problem 2). 4. While saving your work/file, PLEASE save one of your group member?s name and section (M or P) in the file name.

e.g. (PeterNorm_P_Assignment2) 5. Upload your completed assignment using Blackboard system (i.e. in the designated area), before the deadline.

6. You have till 11:59 p.m. (March 26) to submit your work. 7. Make necessary assumptions (if needed). If your assumption is reasonable, I will consider it.

8. Important: There will be marks for presenting your work neatly. It should be easy to follow.

Problem 1: Refer to worksheet ?Cost of capital? (11 + 2 + 4 + 4 + 4 = 25 Marks)

Tom Hanks has been appointed as the CFO (Chief Financial Officer) of Holiwood Corporation. As he needs to take some important decisions on future investment projects, he is concerned about the firm's Cost of Capital. He has been looking at the firm's financial information and market data in order to figure out the firm's Cost of Capital.

Holiwood Inc. has three series of Bonds (long-term debt) Its first series bonds are currently selling at $1009.00 in the market. There are currently 11.5 million such bonds in the market. These are semi- annual interest paying bonds, with an annual coupon rate of 8%. These bonds will mature in 10 years. The series is quite old and hence the issuing cost is irrelevant.

Its second series bonds are currently selling at $1059.00 in the market. There are currently 9 million such bonds in the market. These are quarterly interest paying bonds, with an annual coupon rate of 9%. These bonds will mature in 12 years. The series is quite old and hence the issuing cost is irrelevant.

For the third series, the Yield to Maturity is 9.1% and the estimated issuing cost is 0.8%. Total market value of this bond series is $6.2 billion. Holiwood Inc.'s effective tax rate is 30%. Interest rate for the short term debt is 4.2%.

The firm has been paying dividends over the last fifteen years. Last six years? dividend information is presented in Table 1. (See the excel worksheet ?Tables?).

Holiwood Inc. has currently 165 million common shares outstanding. Current share price is $62 in the market. Estimated flotation cost is negligible. Tom recalls that there are two commonly used methods to calculate the cost of equity - Dividend Growth Model and CAPM.

Tom decides to use the compound growth rate concept, to calculate dividend growth rate (Hint: FV = PV(1+g)^n)

In order to use CAPM, Tom looks for other market data (see Table 2, as presented in excel worksheet ?Tables?).

He projects that a reasonable estimate for future monthly risk-free rate will be 0.40%. Further, for beta estimation Tom Hanks plans to use a 48 month estimation window.

Tom also feels that for the future equity source, 70% of equity fund will come from retained earnings and 30% will be raised by issuing new shares in the market. Tom plans to use market value weights for cost of equity calculations.

Question 1.1

What is the cost of common equity - (i) using DGM (Dividend Growth Model) (ii) using CAPM (Capital asset pricing model)?

Question 1.2

What is the cost of retained earnings?

Question 1.3

What is the after-tax cost of debt? (Show your calculation for each series separately and combined)

Question 1.4

What are the weights for different components of cost of capital?

Question 1.5

What is the Holiwood Inc.'s overall cost of capital? Use market value weights.

Problem 2: Refer to worksheet ?Cash Flow Forecasting? (13 + 5 + 4 + 3 = 25 Marks)

Question 2.1

Using the assumptions, income statement (year 2012) and balance sheet (as of 2012) data, develop the forecasted balance sheet, income statement and cash-flow statements till 2017.

Other information:

The firm will repay $375,000 of long-term debt each year starting 2013. The firm has currently 2,000,000 shares outstanding. The firm?s current share price in the market is $15. The firm?s cost of capital (WACC) is 11%.

Question 2.2

What is the enterprise value of the firm?

Question 2.3

What is the estimated stock price of the firm (based on forecasted cash-flow)?

Question 2.4

Do you think that it is a good idea to invest in this stock, based on the assumptions and data given to you?

Assignment 2: Some Tips Problem 1: Cost of Capital Look at the following example file, it is quite similar: Go to: Cost of Capital (Week 1 and 2) -> Cost of Capital Excel Models -> Example 3 DGM and CAPM: These are two different methods to calculate the cost of equity; the results could vary significantly; it is normal. Cost of Retained Earnings: It is same as the cost of common equity without flotation cost. Again, in this question, it is given the flotation cost for the common equity is zero. Do we need a Balance Sheet for this question? No. Because we need only market value weight. What is the combined cost of debt? (Question 1.3) It is the weighted average cost of debt. First, calculate the cost of each debt series; then combine them with respect to their market value weight (similar concept is followed in WACC calculation). See the Example 3 closely to find out: how to calculate the cost of debt. Beta Calculation: Remember to use 48 month window (as opposed to 60 month window in the example) as given in the question. For market risk premium use (Rm - Rf) values as shown in the example. Problem 2: Cash Flow Forecasting Look at the following example file, it is quite similar: Go to: Cash Flow Forecasting -> Excel Files -> Example 3 Assumptions: Pay close attention to the assumptions. Some assumptions are slightly different from the Example problem. For example, while calculating depreciation, it is based on only the past year's Fixed Asset at Cost. (In the example, it is based on the average value of last and current year). Therefore, use an appropriate formula to take this into account. The assumptions are arrived at based on the past few years' observations and the future outlook of the firm. It is out of scope for this course. Hence, the assumptions are directly given to you. Long-term growth rate: As given in the question, it is 5%. Terminal Value: Do not forget to consider the Terminal Value in your calculation. Debt Repayment: It is a yearly reduction of $375,000. Market Value of equity: 2,000,000 shares * Price per share ($15) = $30,000,000. Remember that it is unlikely that book value of equity (as sown in the balance sheet) will be same as the market value of equity. Writing Proper Formula: Be very careful when you are writing the excel formula. Use \"$\" sign properly to lock the reference. See the Example 3 closely. Decision Rule: Once you complete your calculations, you will find a 'stock price'. If your stock price is lower than the current market stock price (i.e. $15), you should not invest in this firm, because it is overvalued