How to calculate wacc in excel

CASE STUDY AND EXCEL !

Case 3

Nike Inc.: Cost of Capital

BNFN 4304- Financial Policy

Mr. Masood Aijazi

Group members:

Nour Abdulaziz 1420149

Maryam Barifah 1420023

Balquis Mekhlafi 1420231

Shrouq Al-Jaaidi 1420072

Dar Al Hekma University: Business School

Spring Semester 2017-2018

25th February 2018

Introduction

The following case is about a portfolio manager who works at North Point Group named Kimi Ford who is trying to decide whether to buy Nike’s stock. However, Nike had a negative year which lead in a severe decline in sales growth, decline in profits and market share due to supply-chain issue and it was adverse effect of a strong dollar. A meeting was held to look at different strategies where Nike has revealed that it can boost revenues by having additional exposure in mid-price footwear and apparel lines as well as exerting more effort in controlling the expenses. Analysts had very different reactions to Nike’s changes.

Kimi Ford later created her own discounted cash flow forecast (DCF) to get a clearer conclusion, she asked her assistant Joanna Cohen to estimate the cost of capital. The aim of this case and analysis is to find and show the mistakes that are arising when estimating the cost of capital which was done by Cohen. When estimating the cost of capital she used a single cost instead of a multiple one and we agree with her. Even though there are different business segments for Nike, they all seem to have the same risk, thus using a single cost is more effective. This case shows the importance of weighing a company’s stock prior to buying them via using valuation models which is the WACC the importance of carefully selecting of carefully selecting the variables that are used in the WACC formula.

1. What is the WACC and why is it important to estimate a firm’s cost of capital? What does it represent? Is the WACC set by investors or by managers?

The weighted average cost of capital (WACC) is simply the cost of the individual sources of capital. Capital is almost usually comprised of the equity that shareholders decided to in a company, or the debt that the lenders decided to invest in a company, with each source being individually being proportionally weighted. Calculating the WACC is important as when using that capital in any way would be an opportunity cost to the investors as that any capital that is being invested can be used in any other investment. In calculating the WACC the shareholders or lenders would be able to estimate the return that they would be able to earn when they decide to the invest in this company.

WACC is set by investors not by the managers, as it assists when they choose their final decision in the whether to proceed with that particular investment or not. Having the calculated the WACC which is the minimum rate of return that the investor will accept, for the investor they can find the yield that they will receive as their return by deducting the WACC from the company’s returns. If the WACC > Company’s returns investors would go ahead with their decision to invest. However, if the WACC < Company’s returns then the investor will withhold his decision to invest.

2. Do you agree with Joanna Cohen’s WACC calculation? Why or why not?

No we don’t agree with Cohen’s Weighted Average Cost of Capital for these reasons:

1. She calculated the weights of debt and equity using book value rather than market value. Book value is the price paid for an asset that will never change as long as you own that asset. Therefore, she did a mistake by using historical data in estimating the cost of debt as it is a must for her to use the market value, based on current data. The reason behind this decision is it shows how much it will cost the firm to raise the capital today.

2. Using historical data doesn’t reflect Nike’s current or future cost of debt, therefore Cohen’s cost of debt calculation which was done by taking the total interest expense for 2001 and dividing it by the company’s average debt balance is wrong. She should have instead calculated the yield to maturity on a 20-year debt basis with a coupon rate that is paid semiannually.

3. Another error that was done was using the average beta (from 1996-2001), which is 0.80, this number doesn’t represent the future systematic risk, so it is better to use the most recent beta (0.69)

3. If you do not agree with Cohen’s analysis, calculate your own WACC for Nike and justify your assumptions?

Weights of Equity and Debt:

Market value of equity = (Current Share Price x Current Shares Outstanding)

= $42.09 x 271.5 million

= $11,427.44 million.

There is not enough information to find the market value of debt, therefore, we are going to use the book value for computing:

Market Value of Debt= Current portion of long-term debt + Notes Payable + Long-Term Debt

= $5.4 m + $855.3 m + $435.9 m

= $1,296.6 million

Therefore, = = 89.81 % whereas = = 10.19%

In order to find cost of debt, what we need to do is to calculate the YTM (yield to maturity) of Nike’s bonds so that we can be able to represent the most recent cost of debt.

Current price (Po)= $95.60, Issued date= 07/15/96. Maturity date= 07/15/21, Coupon Rate= 6.75% (Semiannually), Payment (PMT)= , PAR value = $100, a 25-year bond (year 1996 minus year 2021) and since the case is in 2001 the bond was issued 5 years ago therefore N= (25-5) x 2= 40 paid semiannually. This can be calculated by either using an excel worksheet or by using a financial calculator. To calculate the YTM, we can either use the financial calculator or the excel sheet and the result would be; r= 3.58% Semiannual. Rd= 3.58% x 2= 7.16%.

So the after tax cost of debt = 7.16%(1-38%) = 4.439%

To compute the Cost of Equity (CoE), we used the 20-year treasury bond rate (5.74%) to represent the risk free rate as this rate is considered the longest one available. Choosing the 20-year rate is the most applicable, since the CoE and the WACC are actually used to discount cash flows in the long-term as well as the WACC calculations below depend on a mix of debt and equity weights both being long-term. Another reason why choosing this maturity is that long-term is better than short-term as the cumulative from 20-years is accurate that a 1-year figure. So the Rf used is equal to 5.74%.

The next step is to find the market risk premium, we used from the historical equity risk premium. The geometric mean is (5.90%) as it is actually compounded the returns where as the arithmetic mean can actually overstate the return. So the risk premium = 5.90%.

The beta used is the most recent one for 6/30/01 which is equal to 0.69.

Re=Rf +B * (Market Risk Premium)

= 5.74%+0.69 (5.90%)

= 9.811%

The tax rate that was taken was the same as what Joanna Cohen took which was computed by adding the US statutory tax rate with the state taxes (35%+3% = 38%), so the tax rate taken is 38%.

Weighted Average Cost of Capital (WACC)= Wd x Rd x (1-T) + We x Re

=10.19% x 7.16% x (1-38%) + 89.81% x9.811%

= 9.264%.

4. Calculate the costs of equity using CAPM, the dividend discount model, and the earnings capitalization ratio. What are the advantages and disadvantages of each method? Which method is best for calculating the cost of equity? Which of these methods be most appropriate particularly for Nike and why?

Cost of Equity RE :-

a. Using Dividend Discount Model (DDM)

RE = D1/P0 + g D1 = D0 (1+g)

D0 = 0.48; g = 5.5%

D1 = 0.48 x (1+ 0.055)= 0.5064

P0 = 42.09 RE= D1/P0 + g = 0.5064/42.09 + 5.5%= 6.70%

Based on the above calculations, we can see that after June 30, 2001 the company did not pay any dividends to its shareholders, so this model (DDM) is not useful since it doesn’t reflect the intrinsic cost of capital.

Advantages of this model;

· There is a flexibility in forecasting the future dividends.

· It assists in the approximations, no matter the impact of the inputs.

· Helps in sensitivity analysis and analysis of markets variations to changing situations

· Method is very simple.

Disadvantages of this model;

· The model is not suitable to use in many cases so it will result in inaccurate answers.

· High sensitivity to minor variations in the inputs of the models and assumptions

· The model assumes that the company pays substantial dividends that will grow at a constant rate.

· It carries no consideration for systematic risk.

b. Using Earning Capitalization Model

ECM= E1 / P0 = 2.16 / 42.09 = 5.13%

E1: forecasted earnings, current diluted earnings per share P0: stock price today.

This model is not recommended due to that that it ignores the potential growth of the firm.

Advantages of this model;

· It helps in predicting the earnings

· It carries consideration for the forecasted earnings and the current price of the stock.

Disadvantages of this model;

· It ignores the growth of the company

· This model is not appropriate for the firms with no growth

· The estimates may not be accurate

· It has errors in the current capitalization rate.

c. Using Capital Asset Pricing Model(CAPM) to calculate RE:

RE = RRF + (RM – RRF) x Beta

→RE =5.74% + 5.9% x 0.69 = 9.811%

The model is the recommended way to calculate the cost of equity for Nike Inc., as it is very simple to apply, an addition to that it includes the most important variables for instance Beta (systematic risk), risk free rate and also market return.

Advantages of this model;

· Simple

· Applied in practice

· Adjusts for risk

· Can be used by companies that do not have steady dividend growth

Disadvantages of this model;

· Unrealistic assumptions specially in estimations of risk free rate

· Sometimes it fails in explaining the behaviors of the investors and the used beta will not be successful in capturing the risk of investment

· Difficult to validity

· Predicts future based on past (there is change in economic conditions)

· It is important to consider the market value, book values ratios, as it is highly relevant to return.

5. What should Kimi Ford recommend regarding an investment in Nike?

The calculated WACC is 9.27% and the present value per share is $58.13 (15,782.295/271.5). This shows that the present value is higher by 1.38 times than Nike’s current market price of $42.09. The shares price of Nike is undervalued by $16.04 (58.13-42.09) as Nike is presently trading in 2001. The current growth rate that is about 6 to 7% is much lower than the one estimated which was 9.27%. This value is considered majorly understated. Nike Changed their business technique by focusing in mid-priced segment, which for a long time was less concentrated. This means that there is a possibility for their sales total to increase that that will lead to an increase in revenues and profit. In addition to this Nike’s share prices and dividend will be increased in the long-term.

Based on these records, we recommend to the North Point Large-Cap Fund to buy Nike’s shares, because the stock is currently undervalued and it has a major growth potential that will be beneficial to the fund. In addition to this, the goals that were set by the management of Nike Inc. could be a great source of profit. Also by the past performance of Nike Inc. shares against the market index, technical analysis supports the buy decision. The past performance shows that Nike can out preform the current market returns and now that it has gone down, it is left with the hope for an increase based on the plans being set up.

Conclusion

In conclusion, before buying Nike Inc. shares, Kimi Ford must decide whether she wants the shares for long or short term. If it is for the long-term, then the decision to invest is a good one and if it is for the short-term she should be cautious about the fast changing industry the changes that Nike is doing and also changes in the footwear trends. However, based on historical, recent and future data the decision that Kimi should consider that is to buy Nike’s shares for the reason that it is quite safe, currently undervalued and has great potential.

References

Baker, H. and Martin, G. (2011). Capital structure & corporate financing decisions.

Hoboken, NJ.:John Wiley & Sons.

Brealey, R., Myers, S and Allen, F. (2017). Principles of corporate finance. New York, NY:

McGraw-Hill Education.