Problem #1 - Everything WACC

Everything Corp. has operations in many different businesses, including the manufacturing of toasters.The goal in this problem is to find the WACC appropriate for discounting the cash flows associated with an expansion in Everything's toaster manufacturing business.Because Everything Corp. is in multiple lines of business, its overall cost of capital is not appropriate for this purpose.Instead, you will need to find the betas of some comparable firms in order to calculate the WACC.Enter formulas in the appropriate cells in order to complete the five steps outlined.

Step 1: Unlever Beta.Equity (levered) betas for three comparable firms are given.Unlever each beta to get the asset beta for each comp.Debt ratios are given for the first two comps, the third can be found using the financial statements for Superior Toasters on the second tab.Average the asset betas to get an estimate of the appropriate asset beta for Everything's investment in toaster manufacturing.Hint:be careful when entering your formulas for levering and unlevering, making sure that you use the right ratios (remember that D/V + E/V = 1 and that V = D + E).

Step 2:Relever Beta.Now relever the asset beta according to the appropriate capital structure for Everything.Note that when available, the target debt ratio is more appropriate than the current debt ratio, because it is more representative of what Everything's capital structure will be over the long run.Here the target debt ratio is given.

Step 3:Cost of Equity.Find the appropriate cost of equity (using the relevered beta) according to the CAPM.

Step 4:Cost of Debt.For the cost of debt, we'd like to use the yield to maturity on Everything's outstanding bonds.In this case, we don't know that information, so we need to estimate the cost of debt using data from comparable firms.This data is provided in the worksheet that says "Bond data".Estimate Everything's bond rating assuming that Everything has a debt ratio (TD/TA) of 18% and a times-interest-earned ratio (TIE) of 7.7.Observe the premium (over 10-year treasurys) at which bonds of similar risk are trading, and use this premium to estimate Everything's cost of debt if it were to issue bonds to help finance this project.

Step 5:WACC.We now have all the pieces available to put together the appropriate WACC for Everything's investment in toaster manufacturing.

Problem #2 - Nike Cola WACC

Analyze the hypothetical problem of Nike considering investing in the production of "Nike Cola", a soft drink for active lifestyles.You will proceed in calculating the WACC as in the first problem, but now you will look up the necessary data on line.Some suggested on-line sources are given, but you may use others if you wish.Hint:it will take a bit of searching around to find the information you need at these sites, but the information is there, and you'll get quicker at finding the information as you practice searching around.Note that in this problem your answers will vary depending on when data is retrieved because data (e.g. stock prices, risk-free rates) change in real time.

Step 1: Unlever Beta.The comparable firms will be Coca-Cola Co. (KO) and Pepsico Inc. (PEP).Find betas for these companies on line.Also calculate their debt ratios (D/V) from on-line data.Use their most recent financial statements available.When finding D use book values (we can't figure out market values) and use long-term sources of debt (i.e., total liabilities minus current liabilities.)The table to the right on the spreadsheet might be helpful for keeping track of the information you look up.

Q3:What is the average unlevered beta for Pepsico and Coca-Cola?__________

Step 2:Relever Beta.Now relever the asset beta according to Nike's (NKE) capital structure.You will not find their target debt ratio on line, so focus on their actual debt ratio from their most recent financial statements.

Step 3:Cost of Equity.For the risk-free rate (i_g), use the yield currently available on treasury securities.You can find this rate on line.The only question is what maturity of treasurys to use.While opinions differ on this issue, my feeling is that you should use a maturity that is close to the time horizon of the investment considered.I'll suggest using a 10-year rate here.

Step 4:Cost of Debt.To keep this step simple, let me just tell you that Nike's credit rating right now is A.Now you can look up a representative yield for bonds of this rating on line.(For example, on Yahoo finance, try Bonds under the Investing tab, and look up Composite Bond Rates.)Assume Nike would issue 10-year bonds.(Note that in this case you are entering the yield on the bonds, not a premium over treasurys.)

Step 5:WACC.First, calculate Nike's tax rate from its most recent annual financial statements.Then you have all the pieces available to put together the appropriate WACC for Nike's investment in soft drinks.

Problem #3 - Look up values in a table

In this problem, the calculations are mostly filled in.Your job is to fill in the cost of debt by creating a lookup formula in Excel.To the right in the worksheet is a table of rates applicable to corporate bonds of different credit ratings.The formula in C25 needs to be such that it will adjust automatically when the bond rating is changed in C24.This is done with the VLOOKUP function, which "looks up" a value in a table and then returns a corresponding value.Find VLOOKUP under Excel's list of functions.VLOOKUP is structured like this:VLOOKUP(cell reference of value to be looked up, cell references of complete table to look up value in, number of column in table corresponding to value you want returned, enter "FALSE" here).When you have entered the function correctly, you should be able to change the bond rating and see the cost of debt adjust automatically.