Week$4$ Assignment: H$.$J$.$ Heinz: Estimating the Cost of Capital in Uncertain Times

DUE DATE: October $1$th$,$ midnight

In this assignment, we will cover a case from $$Case Studies in Finance$$ by Bruner et$.$ al$.$

For this assignment, you will need to complete the excel sheet provided under the assignment in $$what$$s due$$ box. Also, you may want to read the case which is uploaded at the same place.

The excel sheet will make much sense after you watched the video as I covered all the things you need to complete in the excel sheet in my videos with examples.

In the excel sheet, you will see the blue highlighted cells which must be completed. There are some cells with comments which you can read when you hold the cursor on the cell which have small red triangle on the corner of the cells.

Excel sheet is pretty self$-$explanatory, however, to make sure you do not miss any one of the required calculations, you can follow this list below:

In the worksheet, titled $$Beta & Cost of Equity$$

$1.$ Columns E$-$H: Calculate HNZ Return, S&P $500$ Return, HNZ Risk Premium, S&P $500$ Risk Premium.

$2.$ Run a regression where the dependent variable is HNZ Risk Premium $($not return even though it may be used in real world since it is easier$)$ and independent variable is S&P $500$ Risk Premium $($not return even though it may be used in real world since it is easier$).$ Your regression result will pop up in a new worksheet which I want to see for grading purposes.

$3.$ BETA: in the regression output, the coefficient of the S&P $500$ Risk Premium is the beta. Please copy and paste it to the cell K$4.$ Or you can you this: in K$4,$ you can put $=$ then go the regression output tab and click on the coefficient.

$4.$ Market Risk Premium and Risk$-$free rate are already given to you and you calculated the beta. Now, you need to solve for Cost of Equity. Follow the CAPM formula. This is K$5.$

In the worksheet, titled $$Cost of Debt$$

$1.$ Price $($cell B$3)$: Remember, price is quoted in percentage of the par value. You still need to calculate the $value. $($Please read the comment on B$1$ and B$3.)$

$2.$ N $($cell C$2)$: Number of expected coupon payments. This bond matures in $3/15/32.$ You need to count number of expected semiannual coupon payments till $3/15/32,$ where the first expected coupon payment will be in September, $2010.($Please read the comment on C$2.)$

$3.$ Coupon $($cell F$2)$: Coupon Rate x Face $($Please read the comment on F$1)$

$4.$ YTM $($cell G$2)$: Use the $=$rate$()$ function. This is from last week$$s topic, bond valuation.

$5.$ After$-$tax cost of debt $($cell G$5)$: YTM x $(1-$tax rate$)$

In the worksheet, titled $$Balance Sheet & BV$-$MV of Debt$$

$1.$ Book Value of Debt $($cell E$3)$: Short term debt $+$ Current Portion of LTD $+$ LTD$.$ The information is presented in the same worksheet, please make sure that you add all three of them.

$2.$ Market Value of Debt $($cell E$4)$: Book Value of Debt x Price$\%($Price info $($not the $ value$)$ is given in Cost of Debt Worksheet in CELL B$2)$

In the worksheet, titled $$Weights & WACC$$

In this worksheet except for $$Market Value of Equity$$ calculation $($cells C$2$ and C$8$ which are the same info$),$ you do not have to do anything else. I already wrote the formulas in the cells highlighted in blue. If you complete the worksheet as described above and fill all the blue highlighted cells in other worksheets, information will auto$-$populate itself and you will have your final answer.