Financial Management Excel

profilerobin65
CapitalStructureWACC.xlsx
Home>Business & Finance homework help>Management homework help>Financial Management Excel

CAPITAL STRUCTURE

(3) CAPITAL STRUCTURE
TJX Companies, Inc.: Capital Structure Data (USD ($) $ in Thousands, Feb. 02, 2019)
Based on: 10-K (filing date: 2019-04-03) (source: https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0000109198&owner=exclude&count=40&hidefilings=0)
Investor-Supplied Capital
Book Market
Percent of Total Book Value Percent of Total Market Value Percent of Total
Liabilities and Equity
Accounts payable $?2,6,4,4,1,4,3?? 18.5%
Notes payable $?0?? 0.0% $?0?? 0.0% $?0?? 0.0%
Accrued expenses and other current liabilities $?2,7,3,3,0,7,6?? 19.1%
Federal, state and foreign income taxes payable $?1,5,4,1,5,5?? ??1.1%
Total C.L. $?5,5,3,1,3,7,4?? 38.6%
Long-term debt $?2,2,3,3,6,1,6?? 15.6% ?2,2,3,3,6,1,6?? 30.7% ?2,2,3,3,6,1,6?? 3.1%
Other long-term liabilities $?1,3,5,4,2,4,2?? 9.5%
Non-current deferred income taxes, net $?1,5,8,1,9,1?? ??1.1%
Total liabilities $?9,2,7,7,4,2,3?? 64.8%
Preferred stock $?0?? 0.0% ?0?? 0.0% ?0?? 0.0%
Common stock $?1,2,1,7,1,8,3?? 8.5%
Accumulated other comprehensive income (loss) $?6,3,0,3,2,1? -4.4%
Retained earnings $?4,4,6,1,7,4,4?? ??31.1%
Total common equity $?5,0,4,8,6,0,6?? ??35.2% $?5,0,4,8,6,0,6?? ??69.3% $68,746,149 ??96.9%
Total L&E $14,326,029 100.0% $7,282,222 100.0% $70,979,765 100.0%
Other Data: (source: https://www.nasdaq.com/symbol/tjx/stock-report)(data retrieved: 7/13/2019)
Number of common shares outstanding = 1,212,668,000
Price per share of common stock = $56.69
Number of preferred shares outstanding = 0
Price per share of preferred stock = $0.00

WACC

(4) WEIGHTED AVERAGE COST OF CAPITAL
(4.1) COST OF DEBT
Short-Term Debt
TJX doesn't have any notes payable per Feb.02, 2019 Balance Sheet. The cost of short-Term Debt is estimated as zero.
Long-Term Debt
Cash paid for interest on debt* $?6,4,0,0,7??
Long Term Debt $?2,2,3,3,6,1,6??
Estimated cost of dept 2.87%
* Based on: 10-K -Supplemental Cash Flows Informa (filing date: 2019-04-03) (source: https://www.sec.gov/cgi-bin/browse-edgar?action=getcompany&CIK=0000109198&owner=exclude&count=40&hidefilings=0)
The After-Tax Cost of Debt
Effective income tax rate was 26.7% per Annual Report 10-K
Tax rate = 26.7%
Short-term debt: rstd = 0.0%
Long-term debt: rd = 2.87%
After-tax cost of debt = r (1 – T)
Short-term debt: rstd (1-T) = 0.00%
Long-term debt: rd (1-T) = 2.10%
(4.2) COST OF PREFERRED STOCK
TJX has not issued any preferred stock. The cost for preferred stock is zero.
(4.3) COST OF COMMON EQUITY, rs
(4.3.1) CAPM Approach
rs = risk-free rate + (Market risk premium) (Beta) - 0 $22.73 3.1236
= rRF + (RPM) bi - 0 $26.43 3.2743
- 0 $31.25 3.4420
The Risk-Free Rate - 0 $39.44 3.6749
The risk-free rate is estimated by using the yield on a 10-year T-bond:
(Source:https://www.cnbc.com/quotes/?symbol=US10Y) (Date of retrieval: 07/13/2019)
Yield on 10-year T-bond = rRF = 2.21%
The Market Risk Premium
We used 6% as market risk premium as suggested by the textbook
Assumed market risk premium = RPM = 6.00%
Beta
(Source:https://www.reuters.com/finance/stocks/overview/TJX) (Date of retrieval: 07/13/2019)
bi = 0.73
Cost of common equity, rs = 6.6%
(4.3.2) Discounted Cash Flow (DCF) Approach
rs = D1 + P0 +g
P0 = $56.69
D1 = $0.23 TJX's next dividend is expected to be $0.23 (source: https://www.dividend.com/dividend-stocks/services/department-stores/tjx-tjx-companies/) (data retrieved:07/13/2019)
g = 24.6% TJX's expected griwth rate for current year is 24.64% (https://www.zacks.com/stock/quote/TJX/detailed-estimates) (data retrieved:07/24/2019)
rs = D1 ÷ P0 + g
rs = $0.23 ÷ $56.69 + 24.6%
Cost of common equity, rs = 25.0%
(4.3.3) Bond-Yield-Plus- Risk-Premium (BYPRM) Approach
rs = Bond yield + Bond risk premium
Bond yield = cost of debt = 2.1% (see 4.1)
Estimated bond risk premium = 4% (as suggested by the textbook
rs = Bond yield + Bond risk premium
rs = 2.10% + 4.00%
Cost of common equity, rs = 6.1%
(4.3.4) Average Cost of Common Equity
CAPM Approach rs = 6.6%
DFC Approach rs = 25.0%
(BYPRM) Approach rs = 6.1%
Average Cost of common equity, rs = 12.6%
(4.4) The Weighted Average Cost of Capital (WACC)
The weighted average cost of capital (WACC) is calculated using the firm's market capital structure together with its after-tax cost of long-term debt and cost of common equity.
wd = 3% rd = 2.87%
wstd = 0% rstd = 0.00%
wps = 0% rps = 0.00%
ws = 97% rs = 12.58%
Tax rate = 26.70%
WACC = wd rd(1 – T) + wstd(1 – T)rstd + wps rps + ws rs
WACC = 12.25%

Sheet3