A Model for Analysis of Bond Valuation By Using Microsoft Excel plus VBA

Thursday, June 16, 2011

Case Analysis of Nike, Inc.: Cost of Capital


Apparently, the issue of Nike’s case is to control and check the calculation cost of capital done by Joanna Cohen who is the assistant of a portfolio manager at NorthPoint Group. But I am willing to tell you that it can be a complex case in which we can doubt about sensitivity analysis done by Kimi Ford (portfolio manager) because her assumptions such as Revenue Growth Rate, COGS / Sales, S &A / Sales, Current Assets / Sales, and Current Liability / Sales have been adopted from previous income statements and balance sheets from 1995 to 2001. Perhaps, we can take new assumptions. Generally, the case issue is to examine if the share price of Nike is undervalue or overvalue and the common stock of Nike Inc should be added to the North Point Group’s Mutual Fund Portfolio or not.
Now, let me approve Kimi Ford’s analysis and tell you only the mistakes of Joanna Cohen.
What is the cost of capital?
The cost of capital is the rate of return that a firm must earn on the projects in which it invests to maintain the market value of its stock. Cohen calculated a weighted average cost of capital (WACC) of 8.4 percent by using the Capital Asset Pricing Model (CAPM) for Nike Inc. I do not agree with Joanna Cohen because of below mentioned:
-In the field of Equity’s Cost:
Ø She should use current yields on US Treasuries 3 to 12 months at 3.59% because the yield curve is upward sloping.  Upward sloping yield curve means that North Point Group should rely to short-term financing instead of long term financing.  In fact, by short term financing, the manager can use cheaper cost of equity. It means that North Point Group should sell the purchased shares of Nike during the period of one year.
Ø In the case of value of equity, Cohen’s should use liquidation value in calculating value of equity.  Liquidation value per share is more realistic than book value because it is based on the current market value of the firm’s assets by using of balance sheet data. 
Market Value of Equity (E) Calculation:
E = Stock Price  x  Number of Shares Outstanding
= $42.09 X 271.5
= $11,427.44

This figure is should be used for market value of equity (E) rather than Joanna Cohen figure ($3,494.50).

-In the field of Debt’s Cost:
Ø In calculating value of debt, Cohen should have discounted the value of long-term debt that appears on the balance sheet. It means she should also consider the future value of total long term debt base on coupon rate.

To calculate total value of debt, the steps are as follows:

Market Value of Debt (D) Calculation:

I considered the total amount of Debt for all items which are included by a interest rate as follows:

-Current portion of long -term debt
-Notes payable
-Long - term debt
-Redeemable preferred stock

D = Current LT + Notes Payable + LT Debt (discounted) 
 = $5.40 + $855.30 + $435.9 + 0.3 
 = $1296.9

Using these figures, we can now find the market value of Nike Inc., and the company’s capital structure.

The Calculation of Weighs:

The weights of debt and equity are calculated using the market values of debt and equity as follows:

Weight of Debt (WD)


D + E = 1296.9 + 11,427.44 = 12724.34

WD = D/ D+E

WD = $ 1296.9 /$12724.34
= 10.2%

Weight of Equity (WE)
WE = E/ D +E
WE = 11,427.44/ 12724.34
     =89.8%

 Cost of Debt

There are two types of interest rate for Nike, Inc. as follows:


1) For Notes payable, Current portion of long - term debt and Redeemable preferred stock, all these debts should be cleared during the period of maximum 12 months. Therefore, I calculated the interest rate in accordance with Exhibit 1(Income Statement) for 2001 year as follows:


Interest rate = Interest payment / Operating income
Cost of Debt = Interest rate = (58.7 / 1014.2) * 100 = 5.78%
You can see this interest rate is approximately equal to 20 year yields on U.S Treasuries  
(Exhibit 4).


2) For Long - term debt, Nike, Inc. had issued the Bonds in which the Cost of debt was calculated by finding the yield to maturity on 20-year Nike Inc. debt with a 6.75% coupon semi-annually. I assumed Nike Inc. to have a single cost of capital since its multiple business segments (shoes, apparel, sports equipment, etc.) are not very different and would experience similar risks and betas.

Before-Tax Cost of Debt

I used three (3) methods as follows:
-Method (1):  Using Cost Quotations Based on Coupon Interest Rate and Yield to Maturity (YTM)

Cost of Debt = 14.14%

-Method (2): Based on calculating the IRR
Cost of Debt = 14.15%

-Method (3): Approximating the Cost Based on the Value Bond and Coupon Rate

Cost of Debt = 14%
All of the calculations have been included in my spreadsheet.
 
 

Note:  “All spreadsheets and calculation notes are available. The people, who are interested in having my spreadsheets of this case analysis as a template for further practice, do not hesitate to ask me by sending an email to: soleimani_gh@hotmail.com or call me on my cellphone: +989109250225. Please be informed these spreadsheets are not free of charge.”

 

 
As we can see, all three methods present us approximately the same amount of the cost of debt. I have chosen 14.15% for the cost of debt. 

It is important to find the relationship between the required return and the coupon interest rate. When the required return is greater than the coupon interest rate, the bond value will be less than its par value. We choose cost of debt as 14.15% because it is rational (coupon value annually is 13.50%).  When current value of bond is less than par, required return will be more than coupon rate.


Weight Average of Cost of Debt:


As I mentioned, there are two types of debt and consequently we have two types of Cost of Debt. I calculated the weight average for Cost of debt as follows:


Total debt type 1 = $5.40 + $855.30 + 0.3 = $861 
Total debt type 2 = $435.9 
Total debt = $1296.9


W (type 1) = (861 / 1296.9) * 100 = 66.4% , Cost of Debt (type 1) = 5.78%
W (type 2) = (435.9 / 1296.9) * 100 = 33.6% , Cost of debt (type 2) = 14.15%


Weight Average of Cost of Debt = (66.4% * 5.78) + (33.6% * 14.15) = 


3.84 + 4.75 = 8.59%


Therefore, the Cost of Debt is equal 8.6%

After-Tax Cost of Debt
Cost of financing must be stated on an after-tax basis.  Because interest on debt is tax deductible, it reduces the firm’s taxable income.



ri =rd x (1 –T)
  =8.6% x (1 – 38%)
  =5.33%

Cost of Equity

I have calculated the cost of equity by using of two methods as follows:
Ø Capital Asset Pricing Model (CAPM)

Ø Constant-Growth Valuation (Gordon) Model
 
 To be continued…….