Using the instructions and Excel spreadsheet below, calculate the weighted average cost of capital (WACC). Write a two -page executive summary describing your results make sure you comment on the considerations for risk.

I have attached both the Excel and Instructions via Word

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% 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\