please help me with part C in the Wk 6 project instruction. I would like the calculation in for the all three part in excel. I am trying to do this on my own but I want to make sure that I do this correctly because its due tomorrow.

FIN515: Week 6 Project - Calculating the Weighted Average Cost of Capital Once again, your team is the key financial management team for your company. The company's CEO is now looking to expand its operations by investing in new property, plant, and equipment. In order to effectively evaluate the project's effectiveness, you have been asked to determine the firm's weighted average cost of capital. To determine the cost of capital, here is what you have been asked to do. 1. Go to Yahoo Finance (http://finance.yahoo.com) and capture the income statement information for the company you selected. (Be sure that your company has debt on their balance sheet. This will be required in your project.) a. Enter your company's name or ticker symbol. Your company's information should appear. b. Click on the Financials tab, and select the income statement option. Three years' worth of income statements should appear. Copy and paste this data into a spreadsheet. c. Repeat step b. above for the balance sheets of the company. d. Click on \"Historical Prices.\" Capture the closing price of the stock as of the balance sheet date for the three fiscal years used in steps b and c above. 2. Calculate the Weighted Average Cost of Capital (WACC) for the company: a. Cost of Debt i. Determine the market value of the firm's debt issues. Be sure to review the firm's 10-K. Also, the website http://finra-markets.morningstar.com/BondCenter may be of assistance. ii. You will need to calculate the firm's composite YTM on its bonds. This can be achieved by calculating a weighted-average YTM for its bond issues. iii. After calculating the YTM for the bond issues, calculate the firm's after-tax cost of debt. If the firm's marginal tax rate cannot be identified in its 10-K, assume that the tax rate will be 35%. b. Cost of Equity i. Calculate the firm's cost of equity using the capital asset pricing model (CAPM). The formula for the CAPM is ri = rf + i (RMkt - rf). ii. Assume the risk-free rate (rf) is the current rate of 10-year U.S. Treasury Bonds. iii. Calculate the market rate (RMkt) by calculating the market return on the Standard & Poor's 500 for the past 2 calendar years. iv. The beta for the firm can be obtained from Yahoo! Finance. Deliverable c. Calculate the WACC i. Determine the market capitalization of the firm's common equity and preferred equity, if any. ii. Determine the firm's capital structure based on the market value of the firm's equity and debt. The market value of the firm's debt can be obtained from the Morningstar website, listed in the Cost of Debt section above. iii. Calculate the WACC. As you recall, the formula for WACC is rWACC = E (E + D) rE + D (E + D) rD (1 - TC). 1 FIN515: Week 6 Project - Calculating the Weighted Average Cost of Capital Prepare a narrated PowerPoint presentation using VoiceThread or WebEx that shows the steps you performed to calculate the WACC for your firm. Feel free to embed your Excel spreadsheets in the presentation to demonstrate your calculations. Be sure to discuss how the values were obtained or derived to arrive at your WACC result. Finally, be sure to discuss any strengths or limitations in the calculations you performed, and discuss your analysis about the overall validity of your results. Both members of the team must be part of the narration in the presentation. Grading Rubric Possible Points Calculation of Cost of Debt Calculation of Cost of Equity WACC Calculation 12 12 8 Form 8 Criteria and Point Range 0-3 4-6 Incorrect data or no debt data provided. Questionable data used. Some errors in calculations presented. Data is mostly accurate. Correct calculations performed. Accurate debt data collected and correct cost of debt calculations made. 4-6 Questionable data used. Some errors in calculations presented. 7-9 Data is mostly accurate. Correct calculations performed. 10-12 Accurate equity data collected and correct cost of debt calculations made. 3-4 Two errors noted in the calculation relating to either cost of debt, cost of equity, or capital structure. 3-4 Several problems noted in regard to writing and presentation skills. 5-6 One error noted in the calculation relating to either cost of debt, cost of equity, or capital structure. 5-6 Writing and presentation done well with a few minor errors 7-8 WACC Calculation utilizes appropriate cost of debt and equity and capital structure to arrive at a solid result. 7-8 Virtually no errors in writing or presentation. Incorrect cost of debt calculations 0-3 Incorrect data or no equity data provided. Incorrect cost of equity calculations 0-2 All elements of the WACC calculation are incorrect, or calculation not performed. 0-2 Poor writing and presentation skills, or no presentation provided. 2 7-9 10-12 FIN515: Week 6 Project - Calculating the Weighted Average Cost of Capital 3 Revenue 1/30/2016 1/31/2015 Total Revenue 27,079,000 28,105,000 Cost of Revenue 16,496,000 16,863,000 Gross Profit 10,583,000 11,242,000 Operating Expenses Research Development - Selling General and Administrative Non Recurring - 8,256,000 8,355,000 288,000 87,000 Others - - Total Operating Expenses - - Operating Income or Loss 2,039,000 2,800,000 2,000 -15,000 2,041,000 363,000 1,678,000 608,000 3,000 1,072,000 2,785,000 395,000 2,390,000 864,000 3,000 1,509,000 Income from Continuing Operations Total Other Income/Expenses Net Earnings Before Interest and Taxes Interest Expense Income Before Tax Income Tax Expense Minority Interest Net Income From Continuing Ops Non-recurring Events Discontinued Operations Extraordinary Items Effect Of Accounting Changes Other Items Net Income - - Net Income 1,072,000 Preferred Stock And Other Adjustments Net Income Applicable To Common Shares 1,526,000 - 1,072,000 1,526,000 2/1/2014 27,931,000 16,725,000 11,206,000 - 8,440,000 88,000 - - 2,678,000 2,000 2,680,000 390,000 2,290,000 804,000 3,000 1,486,000 - 1,486,000 1,486,000 Balance Sheet All numbers in thousands Period Ending Current Assets 1/30/2016 Cash And Cash Equivalents Short Term Investments 1,109,000 - Net Receivables Inventory Other Current Assets Total Current Assets Long Term Investments 1/31/2015 2/1/2014 2,246,000 - 2,273,000 - 558,000 424,000 438,000 5,506,000 5,417,000 5,557,000 479,000 493,000 420,000 7,652,000 8,580,000 8,688,000 - - - Property Plant and Equipment 7,616,000 7,800,000 7,930,000 Goodwill 3,897,000 3,743,000 3,743,000 514,000 496,000 527,000 Intangible Assets Accumulated Amortization Other Assets Deferred Long Term Asset Charges Total Assets Current Liabilities Accounts Payable Short/Current Long Term Debt - 897,000 - 711,000 - 732,000 - 20,576,000 21,330,000 21,620,000 5,086,000 4,999,000 5,263,000 642,000 76,000 463,000 Other Current Liabilities Total Current Liabilities Long Term Debt Other Liabilities - Deferred Long Term Liability Charges Minority Interest 5,728,000 5,075,000 5,726,000 6,995,000 2,123,000 7,233,000 2,201,000 6,714,000 1,658,000 1,477,000 1,443,000 1,273,000 3,000 3,000 3,000 Negative Goodwill Total Liabilities Stockholders' Equity Misc. Stocks Options Warrants Redeemable Preferred Stock Preferred Stock Common Stock - 16,326,000 15,952,000 15,371,000 - - - - - - - - - 3,000 4,000 4,000 6,334,000 7,340,000 6,235,000 Treasury Stock Capital Surplus -1,665,000 621,000 -1,942,000 1,048,000 -1,847,000 2,522,000 Other Stockholder Equity -1,043,000 -1,072,000 -665,000 Total Stockholder Equity 4,250,000 5,378,000 6,249,000 -161,000 1,139,000 1,979,000 Retained Earnings Net Tangible Assets Date Open High Low Close Volume Adj Close 1/4/2016 34.45 41.8 34.33 40.41 10264100 38.83865 12/1/2015 39.38 39.94 34.05 34.98 8336600 33.6198 11/2/2015 51.02 52.48 37.75 39.08 11529300 37.20341 10/1/2015 51.18 52.2 47.1 50.98 5012900 48.53198 9/1/2015 57.61 60.13 49.72 51.32 5165500 48.85565 8/3/2015 69.11 70.12 55.16 58.61 5696200 55.45159 7/1/2015 67.82 73.61 65.53 69.06 4884700 65.33845 6/1/2015 67.03 71.1 66.5 67.47 4865300 63.83414 5/1/2015 64.16 69.22 62.8 66.95 6397000 63.01119 4/1/2015 64.52 69.98 64.3 64.63 4546500 60.82768 3/2/2015 63.4 66.14 61.85 64.91 3679200 61.09121 2/2/2015 64 66.09 61.1 63.72 4663900 59.67469 1/2/2015 66.33 68.3 62.23 63.88 4363800 59.82453 12/1/2014 64.34 66.59 60.22 65.75 4284300 61.57581 11/3/2014 57.96 65.74 55.64 64.91 6528400 60.47522 10/1/2014 58.34 59.92 54.84 57.82 5491100 53.86961 9/2/2014 62.37 62.97 57.86 58.18 4471800 54.20502 8/1/2014 57.65 63.1 55.81 62.29 5130000 57.73101 7/1/2014 58.2 60.25 56.37 57.79 3001000 53.56037 6/2/2014 60.09 60.34 57.03 58.02 2723100 53.77354 5/1/2014 57.69 59.93 54.82 59.89 4382000 55.2116 4/1/2014 59.19 61.26 56.47 57.43 3469000 52.94377 3/3/2014 57.19 59.95 56.7 59.29 3871800 54.65847 2/3/2014 53.12 58.66 50.05 57.86 4408300 53.11393 Prices as of 2/7/17 Bond Name YTM MA.B 11.48% M.AA 11.47% M.AJ 11.75% M.AL 10.77% M4047745 8.48% Avg YTM 10.79% Long Term Debt = times 10.79% tax savings net tax after tax cost of debt $ 6,995,000,000.00 $ 754,733,819.81 $ 264,156,836.93 $ 490,576,982.88 7.01% Cost of Equity CAPM = 0.02395 + 0.67(.1196-.02395) = CAPM = 8.80% https://dqydj.com/sp-500-return-calculator/ WACC Part C Macy's shares outstandy * Current Market price E E=D rwacc 1 1 1 rwacc VL RE FCF rwacc-g + 0.09 0.09 0 0.06 305.67 D E=D RD 0 1 0 31.2 (1-tc) 0.075 0.6 0.075 0.6 0 9536.904 E Debt-equity ratio Equity cost of capital debt cost of capital Tax rate Cash Flow Growing rate 0.09 1 0 8.8 0.09 0.075 0.4 0.03