Account and Finance 3 statement model valuation

profiledanishsiddiquie
Homework9withSolutionsandRubrics1.xlsx
Home>Business & Finance homework help>Accounting homework help>Account and Finance 3 statement model valuation

Assignment

VALUATION - NIKE
NOTE: All information has been provided as part of the homework. No need to look up any additional information.
Your boss has asked you to do a valuation of NIKE.
You have previously put together a forecast for the company.
You decide to approach the valuation using 2 methods:
1. Comparable companies analysis
2. Unlevered DCF analysis
Part 1. Comparable Companies analysis
You've reviewed the competior universe and selected comps for NIKE (see NIKE Comps).
You believe that P/E is the most relevant multiple. You will use the average comps P/E multiple to calculate the implied valuation for NIKE.
You've collected the following information about NIKE:
Most recent share price ($/share) 93.34
Most recent shares outstanding (mm) 1,568
LTM Net Income 4,029
Q1. What's the share price implied by the comps-average P/E multiple? Is it higher/ lower / in line with the current price?
Q2. Based solely on this analysis, would your stock recommendation be "Buy", "Sell", or "Hold"?
2. Discounted Cash Flows Analysis
Q3. Calculate NIKE's cost of equity using CAPM based on the following:
Comparable companies' Betas (see NIKE comps). Hint: you've also been provided capital structure for each of the comps
You believe that in the long term, NIKE will have a debt / market equity ratio of: 5.0%
Treasury Yield is: 1.77%
You've been asked to use the following market risk premium: 5.50%
NIKE's Effective Tax Rate is: 16.08%
Q4. Calculate NIKE's WACC based on the following:
NIKE's cost of debt: 3.0%
You believe that in the long term, NIKE will have a debt / (debt + market equity) ratio of: 4.76%
You're putting together unlevered DCF. You will use the 5-year forecast that you've created and then a terminal multiple.
For the next 5 years, calculate:
Q5. NOPAT. Hint: Other income is really small. We will disregard it in our calculations
Q6. Unlevered Free Cash Flow
Q7. Calculate terminal value assuming the following:
Use 2024 net income and average P/E derived from the comp set to calculate terminal equity value
Assume that terminal Net Debt = 2024 Net debt to calculate terminal enterprise value
Q8. Calculate NPV of the cash flows including terminal value at the WACC. Your valuation date is year-end 2019. What does this NPV represent?
Q9. Based on your analysis, what's NIKE's equity value?
Hint: your valuation date is as of year-end 2019.
Q10. What is the price/share implied by your DCF valuation? Based on this valuation only, would your recommendation be "Buy", "Sell", or "Hold"?
Q11. You would like to use an alternative terminal value analysis and choose the perpetuity growth rate approach
Calculate the price / share implied by the terminal FCF growth rate of: 2.50%
Hint: use the 2024 FCF to calculate terminal value
Part 3. - Output
Q12. Create a valuation summary output for the comparables valuation (Q1), DCF with terminal multiple (Q8), and DCF with perpetuity growth rate approach (Q11)
Include the following stats: Share price, Equity Value, Enterprise Value, 2020 P/E, 2020 EV / EBITDA; share price premium to most recent close
Q13. Calculate the average valuation and mutiples. What is the premium to the recent close implied by your average valuaiton?
Which approach has produced the most conservative valuation? Which approach - the most aggressive?
Is this surprising? Why or why not?

Breakdowns_Rubrics

Questions Credits Comments Solutions
Q1 and Q2 2 COMP's PE Name Market Cap Debt Cash Beta Tax Rate LTM NI LTM P/E Debt/ Equity Unlevered Beta
2 COMP's D/E Skechers 6,250 123 824 0.79 18.0% 334 18.7 2.0% 0.78
3 COMP's Unlev Beta Adidas 59,422 2,214 2,584 0.75 25.0% 2,013 29.5 3.7% 0.73
1 Avg PE Puma 11,000 985 492 0.37 26.0% 282 39.1 9.0% 0.35
2 Implied Stock Value Fila 785 676 53 0.78 29.0% 26 29.9 86.0% 0.48
2 Implied Price/share Under Armour 7,420 592 417 0.60 22.0% 112 66.5 8.0% 0.56
1 Correct Price Conclusion 36.7 21.7% 0.58
1 Correct Investment Conclusion NIKE'P/E 36.3
If use comp's P/E
14 Tol P/E 36.7
Shares Outstanding 1568
LTM NI of NIKE 4029
Implied Value of Stocks 147,973.09
Implied Stock Price 94.37
Current Price 93.34
Q3 and Q4 4 Leverage Beta CAPM
4 WACC Rf 1.77%
WACC is 4.98% 3 Re MKT Risk Premium 5.50%
Re is 5.1% Unleveraged Beta 0.58
11 Tol D/E 0.05
EF Tax 16.08%
D/(D+E) 4.76%
Debt Cost 0.03
Leveraged Beta 0.605
Re 5.10%
WACC 4.98%
Q5 and Q6 1 EBIT 2020 2021 2022 2023 2024
2 Tax
2 Correct NOPAT EBIT 5,533 5,948 6,394 6,713 6,915
1 D&A (Taxes) 890 956 1,028 1,080 1,112
1 Delta WC NOPAT 4,643 4,991 5,366 5,634 5,803
1 Capex D&A 873 964 1,064 1,175 1,298
2 Correct FCF Changes in Working Capital (473) (582) (625) (448) (282)
Capex (1,367) (1,509) (1,666) (1,840) (2,032)
10 Tol FCF 3,676 3,864 4,138 4,521 4,787
Q7 1 NI Terminal NI 5,830
2 Terminal P Terminal P/E 37
2 Terminal Debt Terminal P 214,116
2 Terminal Cash Terminal Debt 3,479
1 Correct EV Terminal Cash 2,877
8 Tol Terminal EV 214,718
Q8 4 Correct value, npv function is required NPV 178,512
3 Correct Intepretation of NPV Debt 3,479
Cash 4466
7 Tol Equity 179,499
# of Shares 1568
Shares Price 114.48
Q9 and Q10 2 Debt Current Price 93.34
The Share Price is 114.48$ 2 Cash
We should buy NIKE 2 Equity Value
2 Price/Share
2 Investment conclusion
10 Tol
Q11 4 Correct Terminal Value g 2.50%
Price/Share is 106.6$ 3 Correct NPV, npv function is required Terminal Value 198,185.36
2 Debt NPV 166,158.31
2 Cash Debt 3,479
2 Equity Value Cash 4466
2 Price/Share Equity 167,145.31
15 Tol Shares Price 106.60
Current Price 93.34
Q12 and Q13 1 Share price summary Method Share Price EV Equity Value EV/2020EBITDA 2020 PE Premium Rate
2 Equity Value summary COMP 94.37 146,986.09 147,973.09 22.95 31.69 1.10%
4 EV summary NPV - PE Approach 114.48 178,512.11 179,499.11 27.87 38.44 22.64%
2 EV/2020EBITDA NPV - Perpetual Approach 106.60 166,158.31 167,145.31 25.94 35.79 14.20%
2 2020 PE
2 Premium Rate AVG 105.15 163,885.51 164,872.51 25.58 35.30 12.65%
2 Avg
3 Premium Rate implication
3 Aggressive and conservative conclusion
4 Analysis of the suprising or not surprising
25 Tol
100 Tol

Nike is currently traded at 93.34, with a P/E of 36.3. From Comps, the average P/E is 36.7>36.3, and by the average P/E, the implied price is 94.37>93.34. From both implied P/E and price, we should buy or hold NIKE

The NPV from P/E approach is 178,512MM. This is the enterprise value discounted back to present.

Implication: By all three methods, there will be a positive premium rate, which is a signal that NIKE current price still has room to go up, ranging from 1.1% to 22.64%. The COMP method is most conservative while PE Approach is the most aggressive. This is not surprising. Share price implied by COMPs is almost the same as the trading price because this method produces a valuation that is in line with current stock price, i.e the price is in line with the indsutry. And DCF method produces the intrinsic value of the company and often varies because of different assumptions.

NIKE Comps

NIKE Comparable Companies
Solution
($mm) except for Beta and Tax Rate
Name Market Cap Debt Cash Beta Tax Rate LTM NI LTM P/E Debt/ Equity Unlevered Beta
Skechers 6,250 123 824 0.79 18.0% 334 18.7 2.0% 0.78
Adidas 59,422 2,214 2,584 0.75 25.0% 2,013 29.5 3.7% 0.73
Puma 11,000 985 492 0.37 26.0% 282 39.1 9.0% 0.35
Fila 785 676 53 0.78 29.0% 26 29.9 86.0% 0.48
Under Armour 7,420 592 417 0.60 22.0% 112 66.5 8.0% 0.56
Average 36.7 21.7% 0.58
Solution
Comp Analysis
LTM NI 4,029
(x) Comps Average P/E 36.7
Implied Equity Value 147,973
(/) Shares Outstanding 1,568
Price/ Share 94.37
WACC Analysis
Market Risk Premium 5.50%
Risk Free Rate 1.77%
Debt/ Market Equity 5.00%
Debt/ (Debt + Market Equity) 4.76%
Tax Rate 16.08%
Unlevered Beta 0.581
Levered Beta 0.605
Cost of Debt 3.00%
Re 5.10%
WACC 4.98%

DCF

x Free Cash Flow Buildup
($mm)
2020 2021 2022 2023 2024
EBIT 5,533 5,948 6,394 6,713 6,915
(Taxes) (890) (956) (1,028) (1,080) (1,112) 16.1%
NOPAT 4,643 4,991 5,366 5,634 5,803
D&A 873 964 1,064 1,175 1,298
Changes in Working Capital (473) (582) (625) (448) (282)
Capex (1,367) (1,509) (1,666) (1,840) (2,032)
FCF 3,676 3,864 4,138 4,521 4,787
x Terminal Value Calculations
Terminal P/E Multiple Approach Terminal Growth Rate Approach
Terminal Net Income 5,830 2024 FCF 4,787
(x) Terminal Multiple 36.73 WACC 4.98%
Terminal Equity Value 214,116 g 2.50%
(+) Terminal Debt 3,479
(-) Terminal Cash (2,877)
Terminal Value 214,718 Terminal Value 198,185
x Valuation
($mm)
2020 2021 2022 2023 2024 Terminal
With Terminal P/E Multiple
Cash Flows (Terminal P/E Mult) 3,676 3,864 4,138 4,521 4,787 214,718
Cash Flows (Perp. Growth Rate) 3,676 3,864 4,138 4,521 4,787 198,185
WACC 4.98%
Terminal P/E Perp. G
Enterprise Value 178,512 166,158
(Debt) (3,479) (3,479)
(+) Cash 4,466 4,466
Equity Value 179,499 167,145
(/) Shares Out 1,568 1,568
Price / Share 114.48 106.60

NIKE Forecast

NIKE FORECAST
(In millions, except per share data) Year Ended May 31,
Historic Average Growth CAGR 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024
Income Statement
Revenues 30,601 32,376 34,350 36,397 39,117 42,051 45,205 48,595 51,025 52,555
Cost of sales (16,534) (17,405) (19,038) (20,441) (21,643) (23,103) (24,836) (26,698) (28,033) (28,874)
Gross profit 14,067 14,971 15,312 15,956 17,474 18,948 20,369 21,897 22,991 23,681
% Revenue Growth 6.3% 5.8% 6.1% 6.0% 7.5% 7.5% 7.5% 7.5% 5.0% 3.0%
Gross Margin 45.1% 46.0% 46.2% 44.6% 43.8% 44.7% 45.1% 45.1% 45.1% 45.1% 45.1%
COGS Margin 54.9% 54.0% 53.8% 55.4% 56.2% 55.3% 54.9% 54.9% 54.9% 54.9% 54.9%
Demand creation expense (3,213) (3,278) (3,341) (3,577) (3,753)
Operating overhead expense (6,679) (7,191) (7,222) (7,934) (8,949)
Total selling and administrative expense (9,892) (10,469) (10,563) (11,511) (12,702) (13,415) (14,421) (15,503) (16,278) (16,766)
SG&A Margin 31.9% 32.3% 32.3% 30.8% 31.6% 32.5% 31.9% 31.9% 31.9% 31.9% 31.9%
Interest (expense) / income, net (28) (19) (59) (54) (49) (49) (49) (49) (49) (49)
Other income / (expense), net 81 58 140 196 (66) 78 81 81 81 81 81
Income before income taxes 4,205 4,623 4,886 4,325 4,801 5,565 5,980 6,426 6,746 6,947
Income tax expense (932) (863) (646) (2,392) (772) (895) (962) (1,033) (1,085) (1,117)
NET INCOME 3,273 3,760 4,240 1,933 4,029 4,670 5,018 5,393 5,661 5,830
Earnings per common share: 1.90 2.21 2.56 1.19 2.55 3.01 3.31 3.63 3.87 4.03
Weighted average common shares outstanding: 1,724 1,698 1,658 1,624 1,580 1,550 1,517 1,487 1,465 1,448
Dividends declared per common share 0.54 0.62 0.70 0.78 0.86 1.02 1.12 1.22 1.30 1.36
Non-GAAP Metrics
EBIT 3.4% 4,175 4,502 4,749 4,445 4,772 5,533 5,948 6,394 6,713 6,915
Depreciation 606 649 706 747 705 873 964 1,064 1,175 1,298
EBITDA 3.5% 4,781 5,151 5,455 5,192 5,477 6,406 6,912 7,458 7,889 8,212
Margins
EBIT Margin 13.2% 13.6% 13.9% 13.8% 12.2% 12.2% 13.2% 13.2% 13.2% 13.2% 13.2%
EBITDA Margin 15.1% 15.6% 15.9% 15.9% 14.3% 14.0% 15.2% 15.3% 15.3% 15.5% 15.6%
Other Metrics
Effective Tax Rate 22.2% 18.7% 13.2% 55.3% 16.1% 16.1% 16.1% 16.1% 16.1% 16.1%
Dividend Payout 36.6% 28.4% 28.0% 27.4% 65.5% 33.7% 33.7% 33.7% 33.7% 33.7% 33.7%
Balance Sheet
ASSETS
Current assets:
Cash and equivalents 3,852 3,138 3,808 4,249 4,466 3,344 2,293 1,390 2,029 2,877
Short-term investments 2,072 2,319 2,371 996 197 197 197 197 197 197
Accounts receivable, net 3,358 3,241 3,677 3,498 4,272 4,392 4,721 5,075 5,329 5,489
Inventories 4,337 4,838 5,055 5,261 5,622 6,111 6,569 7,062 7,415 7,637
Prepaid expenses and other current assets 1,968 1,489 1,150 1,130 1,968 1,968 1,968 1,968 1,968 1,968
Total current assets 15,587 15,025 16,061 15,134 16,525 16,012 15,748 15,692 16,938 18,168
Property, plant and equipment, net 3,011 3,520 3,989 4,454 4,744 5,238 5,783 6,385 7,050 7,784
Identifiable intangible assets, net 281 281 283 285 283 283 283 283 283 283
Goodwill 131 131 139 154 154 154 154 154 154 154
Deferred income taxes and other assets 2,587 2,422 2,787 2,509 2,011 2,011 2,011 2,011 2,011 2,011
Total Non-Current Assets 6,010 6,354 7,198 7,402 7,192 7,686 8,231 8,833 9,498 10,232
TOTAL ASSETS 21,597 21,379 23,259 22,536 23,717 23,697 23,980 24,525 26,436 28,400
LIABILITIES AND SHAREHOLDERS' EQUITY
Current liabilities:
Current portion of long-term debt 107 44 6 6 6 6 6 6 6 6
Notes payable 74 1 325 336 9 9 9 9 9 9
Accounts payable 2,131 2,191 2,048 2,279 2,612 2,747 2,953 3,175 3,333 3,433
Accrued liabilities 3,949 3,037 3,011 3,269 5,010 5,010 5,010 5,010 5,010 5,010
Income taxes payable 71 85 84 150 229 229 229 229 229 229
Total current liabilities 6,332 5,358 5,474 6,040 7,866 8,001 8,207 8,429 8,587 8,687
Long-term debt 1,079 1,993 3,471 3,468 3,464 3,464 3,464 3,464 3,464 3,464
Deferred income taxes and other liabilities 1,479 1,770 1,907 3,216 3,347 3,347 3,347 3,347 3,347 3,347
Total non-current liabilities 2,558 3,763 5,378 6,684 6,811 6,811 6,811 6,811 6,811 6,811
Shareholders' equity:
PIC 6,776 7,789 5,713 6,387 7,166 3,916 666 (2,584) (4,584) (6,584)
Retained earnings 5,931 4,469 6,694 3,425 1,874 4,969 8,296 11,870 15,622 19,486
Total shareholders' equity 12,707 12,258 12,407 9,812 9,040 8,885 8,962 9,286 11,038 12,902
TOTAL LIABILITIES AND SHAREHOLDERS' EQUITY 21,597 21,379 23,259 22,536 23,717 23,697 23,980 24,525 26,436 28,400
Check - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
Cash Flow Statement
Net Income 4,670 5,018 5,393 5,661 5,830
Depreciation 606 649 706 747 705 873 964 1,064 1,175 1,298
Change in A/R (120) (329) (354) (254) (160)
Change in Inventories (489) (458) (493) (353) (222)
Change in Payables 135 206 221 159 100
Cash From Operations 5,070 5,401 5,832 6,388 6,845
Capital Expenditures (1,113) (993) (1,105) (1,028) (1,119) (1,367) (1,509) (1,666) (1,840) (2,032)
Free Cash Flow 3,703 3,891 4,165 4,548 4,814
Debt Repayment - 0 - 0 - 0 - 0 - 0
Share Repurchases (3,250) (3,250) (3,250) (2,000) (2,000)
Dividends Paid (1,575) (1,692) (1,818) (1,909) (1,966)
Cash From Financing (4,825) (4,942) (5,068) (3,909) (3,966)
Net Cash Flow (1,122) (1,051) (903) 639 848
Beginning Cash Balance 4,466 3,344 2,293 1,390 2,029
Ending Cash Balance 3,344 2,293 1,390 2,029 2,877
Capex as % of PPE 28.8% 33.0% 31.4% 25.8% 25.1% 28.8% 28.8% 28.8% 28.8% 28.8%
Depreciation as % of Capex 63.9% 54.4% 65.4% 63.9% 72.7% 63.0% 63.9% 63.9% 63.9% 63.9% 63.9%
Financial Ratios
Turnover Ratios
EBIT / Assets 20.8% 22.2% 19.1% 21.2% 23.3% 25.1% 26.7% 27.4% 26.2%
ROE 29.6% 34.6% 15.6% 41.1% 51.7% 56.5% 60.2% 61.0% 52.8%
Efficiency Ratios
A/R Days 38 40 37 39 35 40 38 38 38 38 38
Inventory Days 53 52 55 54 53 52 53 53 53 53 53
A/P Days 43 47 46 39 41 44 43 43 43 43 43
Leverage Ratios
Total Debt 1,260 2,038 3,802 3,810 3,479
Debt/ EBITDA 0.26 0.40 0.70 0.73 0.64
Liquidity Ratios
Current Ratio 2.56 2.46 2.80 2.93 2.51 2.10
PP&E Schedule
BB 4,744 5,238 5,783 6,385 7,050
Additions 1,367 1,509 1,666 1,840 2,032
(Depreciation) (873) (964) (1,064) (1,175) (1,298)
EB 5,238 5,783 6,385 7,050 7,784
Capex as % of PPE 33.0% 31.4% 25.8% 25.1% 28.8% 28.8% 28.8% 28.8% 28.8%
D&A ad % of Capex 65.4% 63.9% 72.7% 63.0% 63.9% 63.9% 63.9% 63.9% 63.9%
Share Repurchases
Share Price @ which repurchased 90.0 106.0 115.0 120.0 122.5
Share repurchases ($mm) 3,250 3,250 3,250 2,000 2,000
Shares BB 1,568 1,532 1,501 1,473 1,456
(Shares Repurchased) (36) (31) (28) (17) (16)
Shares EB 1,532 1,501 1,473 1,456 1,440
WASO 1,550 1,517 1,487 1,465 1,448

Summary Valuation Output

($mm except price/share and multiples and as otherwise indicated) Memo items
Methodology Share Price Equity Value Enterprise Value EV/ 2020 EBITDA 2020 P/E Premium / (Discount) to Recent Close Shares out Net Debt 2020 EBITDA 2020 Net Income Closing Share Price
Comparable Companies 94.37 147,973 146,986 22.95x 31.69x 1.10% 1,568.0 (987.0) 6,405.9 4,670.1 93.34
DCF (Terminal P/E Approach) 114.48 179,499 178,512 27.87x 38.44x 22.64%
DCF (Perp. Growth Rate Approach) 106.60 167,145 166,158 25.94x 35.79x 14.20%
Average 105.15 164,873 163,886 25.58x 35.30x 12.65%