Question

The company I am using is Nike, Inc for the year 2019.

Your task is to determine the WACC for a given firm using what you know about WACC, as well as data you can find through research. Your deliverable is a brief report in which you state your determination of WACC, describe and justify how you determined the number, and provide relevant information as to the sources of your data. Select a publicly traded company that has debt or bonds and common stock to calculate the current WACC. One good source for financial data for companies, as well as data about their equity, is Yahoo! Finance. By looking around this site, you should be able to find the market capitalization (E) as well as the β for any publicly traded company. There are not many places left where data about corporate bonds is still available. One of them is the Finra Bonds website. To find data for a particular company’s publicly traded bonds use the Quick Search feature, then be sure to specify corporate bonds and type in the name of the issuing company. This should give you a list of all of the company’s outstanding bond issues. Clicking on the symbol for a given bond issue will lead you to the current amount outstanding and the yield to maturity. You are interested in both. The total of all bonds outstanding is D in the above formula. If you like, you can use the YTM on a bond issue that is not callable as the pre-tax cost of debt for the company.

Assumptions: As you recall, the formula for WACC is: rWACC = (E/E+D) rE + D/(E+D) rD (1-TC) The formula for the required return on a given equity investment is: ri= rf + βi * (RMkt-rf) RMkt-rf is the Market Risk Premium. For this project, you may assume the Market Risk Premium is 5% unless you can develop a better number. rf is the risk-free rate. The risk-free rate is normally the yield on US Treasury securities such as a 10-year treasury. For this assignment, please use 3.5%. You may assume a corporate tax rate of 40%.

Submit the following: Write a 350- to 700-word report that contains the following elements: Your calculated WACC How data was used to calculate WACC (provide the formula and the formula with your values substituted) Sources for your data A discussion of how much confidence you have in your answer, including what the limiting assumptions you made were, if any Include a Microsoft® Excel® file showing your WACC calculations discussed above.

Answer 1

SOLUTION:-

We won't be able to attach any excel sheet for you. You will have to develop that yourself. However, a snapshot of my model is attached. In the last column, i have explained the how it has been calculated. Excel formula is also there. You can reproduce this table yourself at your end. The calculations are sequential.

N Value How it has been calculated? 3.50% Given 5% Given 1.62 Given in the Nasdaq data 11.60% CAPM formula; excel formula is EN425+N426 N427 424 Parameter 425 Risk free rate 426 Market risk premium 427 Beta 428 Cost of equity 429 430 Face value of bonds 431 Current price % 432 Current price 433 Coupon rate 434 Frequency 435 Years to maturity 436 PMT 437 Nper 438 Yield (Annual) = Pre tax cost of debt 439 440 Market Cap ($ bn) 441 Nos. of bonds (mn) 442 Market value of bonds ($bn) 443 Total Capital ($bn) 444 445 Proportion of debt 446 Proportion of equity 447 448 Tax rate 449 450 WACC 1,000.00 Given in the Nasdaq data 99.95 Given in the Nasdaq data 999.50 "=N431/100*1430 1.90% Given in the Nasdaq data 2 Given in the Nasdaq data 1 Bond is due for maturity in Aug next year 9.5 =N433/N434*N430 2 =N434*N435 1.95% "=N434*RATE(N437, N436,-N432,N430) 911.92 Given in the Nasdaq data 1,000.00 Given in the Nasdaq data 999.50=N432*N441/1000 1,911.42 =N440+N442 0.522909 '=N442/N443 0.477091 '=1-N445 40% 6.15% =N445* N438*(1-N448)+N446*N428

Calculated WACC = 6.15%

Data, formula - all are there in the table above. The formula is given in the text of the question itself. Please use some of them to prepare your report.

Sources of data: The Nasdaq sheet that you have attached, and the information given in the question itself.

How confident we are?

There are multiple theories and ways of calculating the cost of capital. We have used the most popular method of calculation of WACC and hence I am reasonably confident about the calculation of WACC. There is no dearth of assumptions that I have made to calculate WACC. If I start listing them one by one, I will probably end up exhausting all the available paper in the world. Hence, I am listing down the major assumptions:

• Risk free rate will remain constant in future at the value of 3.50%
• Market premium will remain constant in future at the value of 5%
• Beta as available on NASDAQ site is based on last three years trading price. I have assumed that it adequately captures the volatility of the return of the stock prices.
• All the assumptions of CAPM obviously becomes the assumption of my WACC calculation.
• Market capitalization reflects the intrinsic value of equity for the firm.
• Current price of the bond is sale as its issue price.