I am looking for some help with the following question.

Spreadsheet Tutorial

Chapter 8

Cost of Capital

Introduction:This spreadsheet tutorial will give you practice with the functions and tools in Excel that allow you to calculate the weighted-average cost of capital (WACC).Follow the instructions on this handout using the accompanying Excel spreadsheet.Use your spreadsheet to answer the questions in each of the problems below.The instructions are based on Excel 2013 for a PC, so you may need to make some adjustments if you are using a different spreadsheet, such as Calc or Excel for a Mac.

Review:In addition to the primary material in Chapter 8, this tutorial requires an understanding of the Appendix to Chapter 8.

Instructions:

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.

Q1:Under the assumptions given, what is the appropriate WACC for Everything's investment in toaster manufacturing?__________

Q2:Suppose that Everything determined that the appropriate target debt ratio for their toaster division would really be 0.4.What would be the WACC?__________

Problem #2 - Nike Cola WACC

In this problem, you will 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.

Q4:What is the appropriate WACC for Nike's investment?__________

Q5:Suppose Nike estimated that the IRR of their soft-drink investment was 5.0%.How low would the market risk premium have to be in order for their investment to be justified?__________(You can solve this with Goal Seek.)

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.

Q6:What is the WACC assuming a bond rating of AAA-?__________

Q7:What is the WACC assuming a bond rating of B?__________

Calculating WACC Everything Corp. has operations in many different businesses, including the manufacturing of toasters. Find the WACC appropriate for discounting the cash flows associated with an expansion in its toaster manufacturing business. (See the handout for additional instructions.) 1. Unlever Beta Comparable Franklin Toaster Ohio Toaster Superior Toasters AVERAGE A Levered E 0.86 1.15 0.79 Unlevered A D/V 0.33 0.59 NOTES: A=(E/V)*E 2. Relever Beta Target D/V Relevered E 0.60 E=A/(E/V) 3. Cost of Equity ig Rp KE 5.45% 6.90% 10-year treasury rate market risk premium KE=ig+(Rp) 4. Cost of Debt Bond rating Premium KD estimated from comparable data premium over treasurys 5. WACC (KW) t KW 44% from last year's operating results KW=(D/V)KD(1-t) + (E/V)KE Data on Superior Toasters Balance Sheet (millions) 100 Current Liabilities 200 Long-term Debt Equity 300 Total Liab. & Equity Current Assets Fixed Assets Total Assets Stock price Shares outstanding $ 50 5,000,000 20 150 130 300 Average characteristics of bond issuers by rating category Based on bonds with maturity in 10 years. Rating AAA AA A BBB BB B Avg. Avg. Risk TD/TA TIE Premium* 9% 9.8 0.50% 16% 8.2 1.00% 25% 5.6 1.40% 29% 2.9 1.90% 37% 2.1 2.60% 48% 1.2 4.20% *Risk premium is the premium over comparable U.S. Treasurys. Calculating WACC Nike Corp. is considering producing "Nike Cola", a soft drink for active lifestyles. Find the WACC appropriate for Nike to discount this investment. (See the handout for additional instructions.) 1. Unlever Beta Comparable Pepsico Inc. Coca-Cola Co. AVERAGE A Levered E D/V Unlevered A POSSIBLE SOURCES: http://finance.yahoo.com/ 2. Relever Beta D/V Relevered E http://finance.google.com http://finance.yahoo.com/ 3. Cost of Equity ig Rp KE www.cnnmoney.com 6.70% 4. Cost of Debt Bond rating KD A www.moodys.com http://finance.yahoo.com/ 5. WACC (KW) t KW http://finance.google.com http://finance.yahoo.com/ WACC appropriate for Financial Statement Data PEP D in Billions E in Billions KO NKE Calculating WACC Complete the WACC calculations by putting in a lookup formula for the cost of debt. (See the handout for additional instructions.) 1. Unlever Beta Comparable Comp One Comp Two AVERAGE A Levered E 1.25 1.66 Unlevered A D/V 0.34 0.40 0.83 1.00 0.91 2. Relever Beta Target D/V Relevered E 0.70 3.04 Corporate Bonds Rates 3. Cost of Equity ig Rp KE 4.20% 6.90% 25.14% 4. Cost of Debt Bond rating KD B 5. WACC (KW) t KW 42% 7.5% AAA AAAAA+ AA AAA+ A ABBB+ BBB BBBBB+ BB BBB+ B BCCC CC 4.51% 4.58% 4.62% 4.66% 4.69% 4.82% 4.88% 4.93% 5.12% 5.19% 5.21% 5.44% 5.50% 5.59% 5.89% 5.95% 6.08% 6.35% 6.69% Answers Q1 Q2 Q3 Q4 Q5 Q6 Q7 7.9% 8.3% Answer varies based on when data is retrieved. Answer varies based on when data is retrieved. Answer varies based on when data is retrieved. 9.4% 10.0%