I need to create a 5 year 3 statement model (Income statement, balance sheet, Cash Flow statement, DCF) for Walmart, using annual statement SEC filings by Walmart online. I will also need to forecast for the next 4 years using assumptions based on how Walmart will perform during and after the coronavirus pandemic. Things that I will be calculating are:
- Sales growth
- Profitability
- Efficiency
- Returns
- Liquidity
- Leverage
- WACC
Finally, based on all the information, I need to create a report suggesting whether someone should buy, hold, or sell the stock, carefully explaining the assumptions I made, and analyzing the calculations.
I need this assignement by the 6th of May. Attached are the detailed requirements of the assignment (Please note I only have to do it for Walmart). Also attached are examples of vaguely how the 3 statement model should look like, and how the analysis should be
Accounting
and Finance – Final Exam (Take Home) ‐ Due May 8
Spring 2020
The current coronavirus pandemic has reinforced division between “haves” and the “have nots”, not
only for the world population (rich vs. poor, young vs. old, etc.), but also between different companies.
Some companies (like cruise lines), have had demand for their services completely destroyed, and
others (like Gilead) have had their stock rise dramatically in hopes of being able to use their Ebola
medication Remdesivir against the virus.
In this final, you are asked to analyze one of the companies on the below list (classified either as a
“have” or a “have not”). Please put together a detailed analysis of the historical performance as well as
the forecast and valuation for your company (assignments of particular companies posted on
CourseWorks).
You will be evaluated both on quality of analysis (in excel) and thoughtfulness of your write‐up on each
company. Think about yourself as an investment banker who needs to analyze and summarize their
views on the company / explain them to the Managing Director. Has this company performed well /
poorly over the last several years? Is it solvent? What are the prospects for the company going forward?
Is the current market valuation fair? Should an investor buy or sell the stock at current price?
We are asking you to analyze the following companies (please see CourseWorks for the specific
company you’ve been assigned:
Haves
Amazon
Walmart
Chewy
Have Nots
Disney
Norwegian
If you have any questions as you work through the exam, please ask your CAs as follows:
Amazon Nick
Walmart Rohan
Chewy Cindy
Disney Mohnish
Norwegian Anna
Part 1
Calculate key historic ratios over the last 3 years.
How has the company been performing in terms of:
A. Sales growth
B. Profitability
C. Efficiency
D. Returns
E. Liquidity
F. Leverage
Are there any areas that cause concern?
Have there been any non‐recurring events that you had to adjust for? If so which ones?
Part 2
Which companies do you consider to be good comps for your company? Why?
Calculate key trading multiples for the comps on the LTM basis
What is your company’s valuation if you were to take the comps average trading multiples to come up
with your valuation?
Does this method produce a valuation that’s at a premium or at a discount to the company’s most
recent closing stock price?
What does this tell you about whether your stock is more expensive or cheaper than comps?
Part 3
Put together a 5‐year 3‐statement model for your company
Review call transcripts and financial statements to find any assumptions on areas like a share repurchase
program and future capex spend
Make your own assumptions on topline growth and margins based on what you believe will happen to
the company in the future.
Use Thomson one (see Columbia Business School Electronic Resources: note, you will need Internet
Explorer) to pull 3‐5 most recent equity research reports.
What’s the EPS average based on these reports for year 1, 2, and 3 of the forecast?
Is your EPS forecast in line with this average / is it very different? Based on reading the reports, what do
you think are the key drivers for differences?
Part 4
Based on the same comps as in Part 2, calculate the WACC for your company (assume that your
company’s unlevered Beta will equal to the average unlevered Beta for your comps)
For capital structure for your company, assume that the historic average will be an accurate
approximation for the future
Assume 6.5% for market risk premium and use a 10‐year treasury yield
For cost of debt, use the weighted average coupon rate for the debt based on the debt schedule; for
simplicity, assume no difference between coupon rate and YTM.
Part 5
Based on your forecast in Part 3 and WACC calculation in Part 4, put together an unlevered DCF
valuation for your company
Use a multiples method for terminal value. Pick the multiple that you think is the most relevant
Part 6
Based on your valuation in parts 2 and 5, what’s your stock recommendation – “Buy”, “Sell”, or “Hold”?
Explain.
>Assi ment
tail group at the Best Investment Bank. One of the companies you help cover is .
-statement model and fill out the corresponding schedules provided
– Performance
Growth.
-20 revenue growth CAGR. Calculate 20 -201 revenue growth CAGR as well. Based on this information, has the revenue growth been slowing or increasing?
, and ,24
goes up, will increase, decrease, or remain the same?
0-on
and for 20 ,2018, and 2019. Use beginning balance of the balance sheet account to calculate the ratios.
-on 5-on
)
)
Days (of Sales)
and Interest
2-on
Debt and Interest 142-on and to decline compared to 2019, but you believe that Apple has a good product pipeline and you expect revenues to grow meaninfully going forward.
is expected to be the same in the projected years as in 2019.
averages for all working capital accounts mentioned in question 9.
Flow Statement
2.8 / share
ratio and assume that it remains constant across the forecast period for the purposes of calculation of the share repurchase price
EPS
, and 2020 for you to perfectly match the equity research average on EPS?
2016-19 Historical 2020 2021 2022 2024 ,174
%
10.0% 10.0% .8%
4,625 4,625 4,625 4,625 4,625 15.94% 15.94% 15.94% 15.94% 15.94% 1
21.2% standing (Thousands)
157,054 157,054 157,054 157,054 157,054 45,330 45,330 45,330 45,330 45,330 $ 202,384 $ 202,384 $ 202,384 $ 202,384 88,223 88,223 88,223 88,223 88,223 , $ 88,223 $ 88,223 $ 88,223 $ 88,223 $ 321,686 $ 375,319 $ 365,725 $ 338,516 $ 88,223 $ 88,223 $ 88,223 $ 88,223 $ 88,223 – 0 – 0 – 0 Depreciation and Amortization 10,505 10,157 10,903 12,547 Cash From Operations )
)
)
s
Inventory Days (of Sales) Re-Purchased @
60,000 50,000 50,000 Beginning Balance Shares Repurchased Beginning Balance Ending Balance Growth CAGR 2016-18 2016-19 38.4% 38.4% 38.4% 38.4% 38.4% 10.0% 10.0% 10.0% 10.0% 10.0% 26.8% 26.7% 24.6% 23.7% 23.7% 23.7% )
)
)
)
)
19.1% 19.1% 19.1% (3,468) (3,468) (3,468) (3,468) 19.7% 19.8% 19.8% 5,126 4,755 4,443 22.6% 24.9% 24.9% 24.9% 24.9% 24.9% 2020 2021 2022 2023 2024 Marketable Securities 217,101 248,606 211,187 157,054 157,054 157,054 157,054 157,054 157,054 2,132 4,855 3,956 4,106 Other Assets (current and non-current) 25,660 32,113 34,370 45,330 45,330 45,330 45,330 45,330 45,330 2020 2021 2022 Average 10.92 13.64 15.76 Liabilities Other Liabilities (Current and Non-Current) 58,101 70,966 77,867 88,223 88,223 88,223 88,223 88,223 88,223 108,047 108,047 108,047 108,047 108,047 108,047 Cash From Operations Changes in Working Capital 260 899 43 (3,047) (2,336) (2,570) (2,827) 483 )
)
)
)
)
Cash From Investing Activities (60,000) (50,000) (50,000) Debt Repayment 32,983 15,621 11,989 13,037 19,230 Balance Sheet Items Working Capital Liabilities Income Statement Items WC Asset Ratios WC Liability Ratios 114 104 112 112 112 112 112 Turnover Ratios 25.9% 27.8% Beginning Balance 108,047 108,047 108,047 108,047 108,047 3.21% 3.21% 3.21% 3.21% 3.21% Total Interest Expense 23.63x 23.63x 23.63x 23.63x Share Repurchase Amount ($mm) 60,000 60,000 50,000 50,000 50,000 # of Shares Outstanding (mm) 5.1% 5.1% 5.1% 5.1% 5.1% 91.4% 91.4% 91.4% 91.4% 91.4% Net PP&E 37,378 38,462 39,709 41,080 42,589 38,462 39,709 41,080 42,589 44,248 Value Selection
s
ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 1 ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 2 ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! (Per prior calc) g 3.0% (Assumption) ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Equity Value ERROR:#REF! Shares Out ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Terminal ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! WACC Tax Rate ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 8.596% 1.50 ERROR:#REF! ERROR:#REF! ERROR:#REF! 1.776% ERROR:#REF! 5.50% Share Price Equity Value Enterprise Value Net Debt 2020 EBITDA ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 262.00 ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Capex 2,000 2,000 2,000 1,000 3,000 3,000 3,000 1,500 5,000 5,000 5,000 5,000 2,500 6,000 6,000 6,000 3,000 1,000 3,000 >Assi nment
KE
. Comparable companies analysis
2. Unlevered analysis analysis
You’ve reviewed the competior universe and selected comps for NIKE (see ). is the most relevant multiple. You will use the average comps P/E multiple to calculate the implied valuation for NIKE.
.3 Flows Analysis
based on the following:
s (see NIKE comps). Hint: you’ve also been provided capital structure for each of the comps
7%
Rate
is:
based on the following:
. Hint: Other income is really small. We will disregard it in our calculations
.
net income and average P/E derived from the comp set to calculate terminal equity value
= 2024 Net debt to calculate terminal enterprise value
.
of the cash flows including terminal value at the WACC. Your valuation date is year-end . What does this NPV represent?
.
.
growth rate of:
to calculate terminal value
)
Value, , P/E, 2020 / IT
DA; share price premium to most recent close
s
2 ‘s PE
Debt Cash Beta 0
4
PE
.0%
.1
26 7
8.0% P/E 36.7 68
29
,973
.09
4 CAPM 3 5.50% 0.58 16.08% 4.76% 1 EBIT 2024 3
8)
)
)
)
0)
)
1
NI
37 Terminal P Terminal Debt Terminal Cash NPV Debt 3,479 6
1568 2 Debt Current Price 93.34 is 114.48$
2 Cash 2 g 2.50% 3 Terminal Value .36
.31
.31
Current Price 93.34 1 Share Price EV Equity Value COMP 94.37 .09
.09 114.48 106.60 166,158.31 167,145.31 Tol Nike is currently traded at 93.34, with a P/E of 36.3. 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%. NIKE Comps except for Beta and Tax Rate
Name Market Cap Debt Cash Beta Tax Rate LTM NI LTM P/E Debt/ Equity Unlevered Beta 123 824 0.79 334 18.7 2.0% 0.78 2,584 0.75 25.0% 2,013 29.5 3.7% 0.73 282 39.1 9.0% 0.35 36.7 21.7% 0.58 Solution 36.7 147,973 standing
94.37 5.50% 1.77% 4.76% 0.605 Re 5.10% DCF 2020 2021 2022 2023 2024 EBIT 5,533 5,948 6,394 6,713 6,915 NOPAT 4,643 4,991 5,366 5,634 5,803 )
)
(2,032) 5,830 2024 FCF 4,787 WACC 4.98% 214,116 g 2.50% 3,479 ($mm) 3,676 3,864 4,138 4,521 4,787 214,718 rowth Rate)
3,676 3,864 4,138 4,521 4,787 198,185 WACC 4.98% (3,479) 4,466 114.48 106.60 2019 2020 2021 2022 2023 2024 5
)
6.1% 6.0% 7.5% 7.5% 7.5% 5.0% 3.0% 45.1% 45.1% 45.1% 45.1% 45.1% .9%
54.9% 54.9% 54.9% 54.9% 54.9% 32.3% 31.9% 31.9% 31.9% 31.9% 31.9% (49) (49) (49) (49) (49) 81 58 78 81 81 81 81 81 4,029 5,830 0.78 5,533 5,948 6,394 6,713 6,915 873 964 1,064 1,175 1,298 12.2% 13.2% 13.2% 13.2% 13.2% 13.2% 15.9% 15.3% 15.6% 13.2% 55.3% 16.1% 16.1% 16.1% 16.1% 16.1% 33.7% 33.7% 33.7% 33.7% 33.7% 4,466 2,877 197 197 197 197 197 1,968 1,968 1,968 1,968 1,968 1,968 281 283 283 283 283 283 283 131 154 154 154 154 154 154 2,011 2,011 2,011 2,011 2,011 44 6 6 6 6 6 6 6 6 74 1 325 9 9 9 9 9 9 3,011 5,010 5,010 5,010 5,010 5,010 71 85 84 150 229 229 229 229 229 3,464 3,464 3,464 3,464 3,464 3,347 3,347 3,347 3,347 3,347 6,811 6,811 6,811 6,811 6,811 666 21,597 21,379 23,259 22,536 23,717 23,697 23,980 24,525 26,436 28,400 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 Depreciation 606 649 706 747 705 873 964 1,064 1,175 1,298 100 (1,028) (1,367) (1,509) (1,666) (1,840) (2,032) – 0 – 0 – 0 – 0 – 0 )
(3,250) (3,250) )
(2,000) 4,466 3,344 2,293 1,390 2,029 3,344 2,293 1,390 2,029 2,877 28.8% 28.8% 28.8% 28.8% 28.8% 63.9% 63.9% 63.9% 63.9% 63.9% 63.9% 22.2% 25.1% 27.4% 15.6% 38 40 37 39 35 40 38 38 38 38 38 53 52 55 54 53 52 53 53 53 53 53 43 47 46 39 41 44 43 43 43 43 43 3,479 0.70 0.73 2.56 2.51 4,744 5,238 5,783 6,385 7,050 1,367 1,509 1,666 1,840 2,032 65.4% 63.9% 72.7% 63.0% 63.9% 63.9% 63.9% 63.9% 63.9% 3,250 3,250 3,250 2,000 2,000 1,568 (28) 1,532 1,501 1,473 1,456 1,550 1,517 1,487 1,465 1,448 Share Price Equity Value Enterprise Value 2020 EBITDA 1.10% 93.34 114.48 179,499 178,512 22.64% 106.60 167,145 166,158 14.20% 12.65%
2
g
x
You work as an analyst at
Re
Apple
Given recent events, you’re asked to evaluate Apple’s historical financial performance and update your in-house forecast for Apple.
Please answer the questions below given assumptions provided in this sheet and the model tab. As part of the analysis, please put together a full
3
x
Part
1
Historical
Item
Model Line Reference
1
Revenue
9
Calculate 201
6
19
16
8
2
Gross Margin
1
4
Calculate gross margin % for the business for each historic year provided. Compute the average
3
O&M Margin
1
7
18
Calculate O&M margin and O&M growth rate. Compute the historic average
4
EBIT
EBITDA
21,
23
Calculate EBIT and EBITDA margin. Based in these metrics, has profitability for the business been increasing or declining?
5
Other IS Metrics
33,36
Caclulate effective tax rate and profit margin. What do you notice about profit margin for Apple?
6
Dividend Payout
44
Calculate dividend payout ratio for Apple for 2016-2019. What trend do you see? Does this mean that as / if
EPS
DPS
7
Returns
13
Calculate EBIT /
Assets
ROE
17
8 Returns 1
30
Which ratio from the question above is most probably used by equity investors? Which one by debt investors? What trends do you see in the two ratios? What could be the reason for the divergence?
9
Efficiency Ratios
10
Calculate the following ratios for 2016-2019:
A/R Days (of
Sales
Inventory Days (of Sales)
Non-Trade Receivable Days (of Sales)
A/P Days (of
COGS
Deferred Revenue
10 Efficiency Ratios
105-on
If you look at accounts receiable days, has the situation been getting worse / better over time? Why? What about account payable days?
11
Debt
14
Calculate an implied interest rate for 2019 based on average debt balance
12
Calculate Debt / EBITDA and EBITDA / Interest for 2016-2019. What can you tell about Apple’s leverage and ability to cover interest?
13
Capex
Depreciation
178-on
Calculate Capex as a % of sales for 2016-2019. Calculate Depreciation as % of capex for 2016-2019
14
Conclusion (Fill in the blanks)
Revenue growth for Apple has been [ ] lately. At the same time, margins for the business (such as EBITDA margin and profit margin) have been [ ].
Return on equity have been [ ] while EBIT / Assets has been [ ]. The key reason for the trend in ROE is [ ].
Leverage and coverage are [ ]. Overall, the company is in [stable / unstable] position. That conclusion is supported by its [high / low] cost of debt.
x
Part 2 – Projections
Put tohether a full 3-statement dynamic model for Apple using the assumptions below.
15
Income Statement
Because of the impact of coronavirus, you expect revenue in
2020
Based on assumptions in Line 9, please forecast revenues in 2020-
2024
Please put together the income statement forecast based on the following assumptions:
Gross margin % is expected to remain in line with the 2016-2019 historic average
Operating expenses are projected to grow by 10%/year every year
Assume that the company’s debt balance and interest rate are expected to remain constant.
Other income is flatlined to historic average
Effective
Tax Rate
You also believe that Apple will keep the same dividend payout ratio as the 2016-2019 average
16
Balance Sheet
You’re forecasting working capital days to remain at
2016-19
Marketable securities,other assets and other liabilities are flatlined.
17
Cash
Capex as % of sales and Depreciation as % of capex are expected to equal the 2016-2019 historic average
168
You’ve read about Apple’s share repurchase program and you expect Apple to continue repurchasing shares. Your assumptions on $ amount of share repurchases are in the share repurchase schedule
In 2020, you assume that all the shares are repurchased at the recent stock price of $
28
Based on the recent stock price and 2019 EPS, calculate the
LTM P/E
18
Free cash flow
86
Calculate free cash flow for the business.
In 2020, does Apple has sufficient free cash flow to both pay the dividends and execute on the planned share repurchases without dipping into the cash on balance sheet?
Do you believe this is a concern / not a concern given historical cash balances?
Would Apple be able to increase share repurchases by $10bn/year?
19
Projected
You’ve reviewed several recent equity research reports, and you find the below forecasted EPS averages. Is your forecast more / less conservative?
In which years do you see the most difference? What do you think may be the likely reason for the difference?
How would this one assumption need to change in 2020,
2021
2020 2021
2022
DB
12.81
14.29
JPM
11.60
16.80
WF
11.93
13.36
MS
12.83
15.24
16.69
CS
12.41
13.99
16.50
Average
12.
32
14.74
16.60
MODEL
Apple Financial Model
Growth CAGR
2016-18
($ in mm except per/share items and as otherwise indicated)
23.2%
20.7%
Hist. Avg
Forecasted
2016A
2017A
2018A
2019A
2023
x Income Statement
Revenues
215,639
229,234
265,595
260
% Growth
6.3%
15.9%
-2.0%
–
5.0
15.0%
10.0%
COGS
(131,376)
(141,048)
(163,756)
(161,782)
Gross Margin
84,263
88,186
101,839
98,392
% Margin
61.6%
60.9%
61.5%
61.7%
62.2%
Operating Expenses
(24,239)
(26,842)
(30,941)
(34,462)
% Growth 10.0% 10.0% 10.0% 10.0% 10.0%
% Margin
Operating Income / EBIT
60,024
61,344
70,898
63,930
% Margin
27
26.8%
26.7%
24.6%
Depreciation and Amortization
10,505
10,157
10,903
12,547
EBITDA
70,529
71,501
81,801
76,477
% Margin
32.7%
31.2%
30.8%
29.4%
Interest Expense
(1,456)
(2,323)
(3,240)
(3,576)
Other Income
4,625
2,804
5,068
5,245
5,383
EBT
61,372
64,089
72,903
65,737
Income Taxes
(15,685)
(15,738)
(13,372)
(10,481)
Effective Tax Rate
25.56%
24.56%
18.34%
15.94%
Net Income
45,687
48,
35
59,531
55,256
Profit Margin
21.2%
21.1%
22.4%
Weighted Average
Shares Out
5,471
5,217
4,955
4,618
Shares
Beginning Balance
5,126
Shares
Ending Balance
4,755
4,4
43
EPS
8.35
9.27
12.01
11.97
DPS
2.18
2.40
2.72
3.00
Dividend Payout
73.9%
74.1%
77.4%
74.9%
x Balance Sheet
Assets
Cash And Cash Equivalents
$
20,484
$
20,289
$
25,913
$
48,844
Marketable Securities
217,101
248,606
211,187
157,054
Accounts Receivable
15,754
17,874
23,186
22,926
Inventories
2,132
4,855
3,956
4,106
Vendor Non-trade Receivables
13,545
17,799
25,809
22,878
Other Assets (current and non-current)
25,660
32,113
34,370
45,330
Net PP&E
27,010
33,783
41,304
37,378
Total Assets
$ 321,686
$ 375,319
$ 365,725
$ 338,516
$ 202,384
Liabilities
Accounts Payable
$ 37,294
$ 44,242
$ 55,888
$ 46,236
Deferred Revenue
11,010
10,384
10,340
5,522
Other Liabilities (Current and Non-Current)
58,101
70,966
77,867
88,223
Debt
87,032
115,680
114
483
108,047
Total Liabilities
$ 193,437
$ 241,272
$ 258,578
$ 248,028
$ 88,223
Shareholders Equity
128,249
134,047
107,147
90,488
Total Liabilities and Shareholders Equity
Check
– 0
Cash Flow Statement
Cash From Operations
Net Income
Changes in Working Capital
Changes in A/R
Changes in Inventories
Changes in Non-Vendor Receivables
Changes in A/P
Chandes in Deferred Revenues
Capital Expenditures
(12,734)
(
12,451
(
13,313
(
10,495
Cash From Investing Activities
Free Cash Flow
Shares
Issued
Shares Repurchased
Dividends paid
(11,926.39)
(12,521.38)
(13,478.63)
(13,853.50)
Debt
Issuance
Debt Repayment
Cash Flow From Financing Activities
Net Cash Flow
Beginning Cash Balance
Net Cash Flow
Ending Cash Balance
x
Working Capital Schedule
Balance Sheet Items
Working Capital Assets
Accounts Receivable
Inventories
Non-Trade Receivables
Working Capital Liabilities
Accounts Payable
Deferred Revenue
Income Statement Items
Revenue
COGS
WC Asset Ratios
A/R Days (of Sales)
Non-Trade Receivable Days (of Sales)
WC Liability Ratios
A/P Days (of COGS)
Deferred Revenue Days (of Sales)
x
Returns Schedule
Turnover Ratios
x
Debt and Interest Schedule
Term Debt
Beginning Balance
Issuance
(Repayment)
Ending Balance
Average Balance
Interest Rate
Interest Expense
Total Interest Expense
EBITDA
Debt/ EBITDA
EBITDA/ Interest
x
Share Repurchase Schedule
LTM EPS
LTM P/E
Share Price
282.8
Nadia Zaets: Nadia Zaets:
Stock Price as of 4/17/20
Share Repurchase Amount ($mm)
60,000
50,000
Repurchase # of Shares (mm)
# of Shares Outstanding (mm)
Issued
Ending Balance x
PP&E and Capex Schedule
Sales
Capex
Depreciation
Capex / Sales %
Depreciation / Capex %
Net PP&E
Capex
(Depreciation)
Sheet2
Apple Financial Model
Q1
($ in mm except per/share items and as otherwise indicated) 23.2% 20.7%
Hist. Avg Historical Forecasted 2016A 2017A 2018A 2019A 2020 2021 2022 2023 2024
Revenue growth slowing slightly in the last year 2019
Income Statement
Q4
Revenues 215,639 229,234 265,595
260,174
247,165
284,240
312,664
343,931
378,324
Hard to tell, since no trend to be seen
% Growth 6.3% 15.9% -2.0% -5.0% 15.0% 10.0% 10.0% 10.0%
Decline in 2019
Margins therefore profitability has been falling
COGS (131,376) (141,048) (163,756) (161,782)
(152,254)
(175,092)
(192,601)
(211,861)
(233,047)
Gross Margin 84,263 88,186 101,839 98,392
94,911
109,148
120,063
132,069
145,276
Q5
% Margin
38.4%
39.1%
38.5%
38.3%
37.8%
Stready profit margin
Operating Expenses (24,239) (26,842) (30,941) (34,462)
(37,908)
(41,699)
(45,869)
(50,456)
(55,501)
Q6
% Growth
12.5%
10.7%
15.3%
11.4%
DPS increasing steadily
% Margin
12.0%
11.2%
11.7%
11.6%
13.2%
DPS ratio decreased in 2018 but on an increase in 2019
if EPS increases, DPS will increase
Operating Income / EBIT 60,024 61,344 70,898 63,930
57,003
67,449
74,194
81,614
89,775
% Margin
27.8%
23.1%
23.7%
Q8
Use ROE ratio as equity investers
Depreciation and Amortization
(10,505)
(10,157)
(10,903)
(12,547)
(
11,521
(
13,250
(
14,575
(
16,032
(
17,635
Use EBIT/asset as debt investors
EBITDA
49,519
51,187
59,995
51,383
45,482
54,200
59,620
65,582
72,140
EBIT/assets decreasing
% Margin
2
3.0%
22.3%
22.6%
19.7%
18.4%
19.1%
ROE increasing
Increase in ROE due to high level of debt and low shareholder’s equity (Share repurchase?)
Interest Expense (1,456) (2,323) (3,240) (3,576)
(3,468)
High level of debt can artificially boost ROE
Decrease in EBIT/assets due to low efficiency of utilization of assets
Other Income 4,625 2,804 5,068 5,245 5,383 2,804 2,804 2,804 2,804 2,804
Q10
EBT 61,372 64,089 72,903 65,737
56,339
66,785
73,530
80,949
89,111
A/R Days measures how much credit sales in given period is uncollected at EOP. The smaller, the better
A/P Days measures how much of period’s inventory purchases are unpaid at EOP. The bigger, the better
Income Taxes (15,685) (15,738) (13,372) (10,481)
(8,983)
(10,648)
(11,723)
(12,906)
(14,208)
Receivable days have been slightly getting worse (increasing) over time
Effective Tax Rate 25.56% 24.56% 18.34% 15.94% 15.94% 15.94% 15.94% 15.94% 15.94%
A/P days were improving (decreasing) but got worse (decreased) in 2019
Net Income 45,687 48,351 59,531 55,256
47,356
56,137
61,806
68,043
74,903
Q12
Profit Margin 21.2% 21.1% 22.4% 21.2%
19.2%
19.8%
Debt/EBIDTA is How many years it would take to amortize debt, if 100% of EBITDA was dedicated to this.
The ratio implies that it will take approx 2 years. -> high leverage
Weighted Average Shares Outstanding (Thousands) 5,471 5,217 4,955 4,618
4,337
4,115
3,921
3,776
3,650
Shares Beginning Balance
Shares Ending Balance
4,231
3,999
3,843
3,709
3,592
Q14
EPS 8.35 9.27 12.01 11.97
10.92
13.64
15.76
18.02
20.52
Revenue growth for Apple has been [slightly slowing] lately. At the same time, margins for the business (such as EBITDA margin and profit margin) have been [falling.]
DPS 2.18 2.40 2.72 3.00 2.72
3.40
3.92
4.49
5.11
Return on equity have been [increasing] while EBIT / Assets has been [decreasing]. The key reason for the trend in ROE is [increasing debt level].
Dividend Payout
24.9%
26.1%
25.9%
2
5.1%
Leverage and coverage are [decreasing]. Overall, the company is in [unstable] position. That conclusion is supported by its [high] cost of debt.
Balance Sheet 2016A 2017A 2018A
2019P
Q18
Apple does not have sufficient funds to both pay dividends and repurchase share without having to dip into previous year’s cash balance
Assets
This is definitely a concern, taking into consideration that previous year’s cash balance was almost 3x the forecasted balances
Cash And Cash Equivalents $ 20,484 $ 20,289 $ 25,913 $ 48,844
32,983
15,621
11,989
13,037
19,230
Apple will not be able to increase share repurchase by 10 bn/year otherwise will go into significant negative cash balance
Accounts Receivable 15,754 17,874 23,186 22,926
20,315
23,362
25,698
28,268
31,095
Q19
Inventories
4,063
4,672
5,140
5,654
6,219
Comparing with forecasted averages, we can say that our EPS forecasts are more conservative
Vendor Non-trade Receivables 13,545 17,799 25,809 22,878 20,315 23,362 25,698 28,268 31,095
in year 2020, but still towards the lower side for other years
Net PP&E 27,010 33,783 41,304 37,378
38,462
39,709
41,080
42,589
44,248
Year
Total Assets $ 321,686 $ 375,319 $ 365,725 $ 338,516
$ 318,522
$ 309,111
$ 311,990
$ 320,199
$ 334,272
12.316
14.736
16.595
Apple EPS
Accounts Payable $ 37,294 $ 44,242 $ 55,888 $ 46,236
$ 46,719
$ 53,727
$ 59,100
$ 65,009
$ 71,510
We see the most difference in the year 2020. This could likely be because of the Pandemic that hit us,
Deferred Revenue 11,010 10,384 10,340 5,522
9,480
10,902
11,993
13,192
14,511
forcing us to assume a decline in growth in the year 2020
Debt 87,032 115,680
114,483
We can change the EPS if we change the assumption of share repurchase amount
Total Liabilities $ 193,437 $ 241,272 $ 258,578 $ 248,028
$ 252,469
$ 260,
899
$ 267,362
$ 274,471
$ 282,291
Shareholders Equity 128,249 134,047 107,147 90,488
66,053
48,211
44,628
45,728
51,980
Total Liabilities and Shareholders Equity $ 321,686 $ 375,319 $ 365,725 $ 338,516 $ 318,522 $ 309,111 $ 311,990 $ 320,199 $ 334,272
Check – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0 – 0
Cash Flow Statement 2016A 2017A 2018A 2019P 2020 2021 2022 2023 2024
Net Income 45,687 48,351 59,531 55,256 47,356 56,137 61,806 68,043 74,903
Depreciation and Amortization 10,505 10,157 10,903 12,547 11,521 13,250 14,575 16,032 17,635
Changes in A/R
(2,120)
(5,312)
2,611
(3,047)
(2,336)
(2,570)
(2,827)
Changes in Inventories
(2,723)
(150)
(609)
(467)
(514)
(565)
Changes in Non-Vendor Receivables
(4,254)
(8,010)
2,931
2,563
Changes in A/P
6,948
11,646
(9,652)
7,008
5,373
5,910
6,501
Chandes in Deferred Revenues
(626)
(44)
(4,818)
3,958
1,422
1,090
1,199
1,319
Cash From Operations
55,733
69,613
56,374
68,536
71,
112
77,704
85,530
94,139
Capital Expenditures (12,734) (12,451) (13,313) (10,495)
(
12,605
(
14,496
(
15,946
(
17,540
(
19,295
Free Cash Flow
43,282
56,300
45,879
55,931
56,616
61,758
67,990
74,845
Shares Issued
Shares Repurchased
(60,000)
(50,000)
Dividends paid
(11,926)
(12,521)
(13,479)
(13,854)
(11,792)
(13,978)
(15,390)
(16,943)
(18,651)
Debt Issuance
Cash Flow From Financing Activities (12,521) (13,479) (13,854)
(71,792)
(73,978)
(65,390)
(66,943)
(68,651)
Net Cash Flows
30,761
42,821
32,025
(15,861)
(17,362)
(3,632)
1,047
6,194
Beginning Cash Balance 20,484 20,289 25,913 48,844 32,983 15,621 11,989 13,037
Net Cash Flow 30,761 42,821 32,025 (15,861) (17,362) (3,632) 1,047 6,194
Ending Cash Balance
51,245
63,110
57,938
Working Capital Schedule 2016A 2017A 2018A 2019P 2020 2021 2022 2023 2024
Working Capital Assets
Accounts Receivable
Inventories
Non-Trade Receivables
Accounts Payable
Deferred Revenue
Revenue
COGS A/R Days (of Sales) 30 27 28 32 32 30 30 30 30 30
Inventory Days (of Sales) 6 4 8 5 6 6 6 6 6 6
Non-Trade Receivable Days (of Sales) 30 23 28 35 32 30 30 30 30 30
A/P Days (of COGS) 112
104
125
Deferred Revenue Days (of Sales) 14 19 17 14 8 14 14 14 14 14
Returns Schedule 2016A 2017A 2018A 2019P 2020 2021 2022 2023 2024
EBIT/Assets
19.9%
19.4%
18.0%
16.6%
21.0%
23.8%
ROE
37.7%
44.4%
51.6%
52.3%
85.0%
128.2%
152.5%
163.8%
Debt and Interest Schedule 2016A 2017A 2018A 2019P 2020 2021 2022 2023 2024
Term Debt 87,032 115,680 114,483 108,047 108,047 108,047 108,047 108,047 108,047
Issuance
(Repayment)
Ending Balance Average Balance
101,356
115,082
111,265
Interest Rate
2.29%
2.82%
3.21%
Interest Expense (1,456) (2,323) (3,240) (3,576) (3,468) (3,468) (3,468) (3,468) (3,468)
EBITDA 49,519 51,187 59,995 51,383 45,482 54,200 59,620 65,582 72,140
Debt/ EBITDA
1.76
2.26
1.91
2.10
2.38
1.99
1.81
1.65
1.50
EBITDA/ Interest
34.01
22.03
18.52
14.37
13.11
15.63
17.19
18.91
20.80
Share Repurchase Schedule 2016A 2017A 2018A 2019P 2020 2021 2022 2023 2024
LTM EPS 11.97 10.92 13.64 15.76 18.02
LTM P/E
23.63x
Share Price Re-Purchased @ 282.8
Nadia Zaets: Nadia Zaets:
Stock Price as of 4/17/20
258.01
322.38
372.48
425.77
Repurchase # of Shares (mm)
212.16
232.55
155.10
134.24
117.43
Beginning Balance
Issued
Shares Repurchased
Ending Balance PP&E and Capex Schedule 2016A 2017A 2018A 2019P 2020 2021 2022 2023 2024
Sales 215,639 229,234 265,595 260,174 247,165 284,240 312,664 343,931 378,324
Capex (12,734) (12,451) (13,313) (10,495) 12,605 14,496 15,946 17,540 19,295
Depreciation 10,505 10,157 10,903 12,547 11,521 13,250 14,575 16,032 17,635
Capex / Sales % 5.1%
5.91%
5.43%
5.01%
4.03%
Depreciation / Capex %
91.4%
82.50%
81.58%
81.90%
119.55%
Beginning Balance 27,010
29,304
31,714
Capex 12,451 13,313 10,495 12,605 14,496 15,946 17,540 19,295
(Depreciation) (10,157) (10,903) (12,547)
(11,521)
(13,250)
(14,575)
(16,032)
(17,635)
Ending Balance 29,304 31,714
29,662
Sheet1
Unlevered DCF Valuation
2
Terminal
EV/EBITDA
15.00
(Assumption)
WACC
ERROR:#REF!
(Per prior calc)
g 3.0% (Assumption)
ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Terminal
Comment
EBITDA ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
EBIT ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Unlevered Taxable Income
ERROR:#REF!
Unlevered Taxes
Unlevered Taxes at ETR
NOPAT
Net Operating Profit After Tax
(+) D&A
Non-Cash Item
Changes in Working Capital ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
(-) Capex
111,644 x 15x OR
Unlevered Free Cash Flow
76,659 x (1+3%)/ (8.04%- 3%)
Enterprise Value
(Debt)
Cash ERROR:#REF!
Equity Value
Shares Out ERROR:#REF!
Price/ Share
Trading price
262.00
(Google Finance)
Conclusion? -> BUY
Levered DCF Valuation
EV/EBITDA 15.00 (Assumption)
Terminal
Net Debt
Re
8.60%
ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Terminal Comments
EBITDA ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Free Cash Flow ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Changes in Debt
Cash Flow to Equity
111,644 x 15 – 75,781
Note: Includes CF for Dividends and Share repurchases.
Price/ Share ERROR:#REF!
Trading price 262.00 (Google Finance)
Conclusion? -> BUY
Today
Cash Flows to Equity
(Net Ouflow)
Net Cash Flow ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
NPV at
8.596%
IRR
ERROR:#VALUE!
WACC Comment
Company Name
Ticker Symbol
Beta
Total Debt
Market Cap.
Debt/Equity
Unlevered Beta
D/ (D+E)
Cost of Debt
2.500%
Provided
Apple Inc.
NASDAQ: AAPL
1.24
1,18
4,000
Tax Rate ERROR:#REF! Projected
HP Inc.
NYSE:HPQ
25.44%
1.410
5,047
30,111
16.76%
1.253
D/(D+E)
Based on Debt and MV Equity
Hewlett Packard Enterprise Company
NYSE:HPE
21.35%
1.550
12,581
19,217
65.47%
1.023
Debt Portion
Intel Corporation
NasdaqGS:INTC
25.67%
0.810
28,487
209,076
13.63%
0.736
Cost of Equity
Use CAPM
NetApp, Inc.
NasdaqGS:NTAP
37.18%
1,793
15,352
11.68%
1.397
E/(D+E)
1- D/(D+E)
Samsung Electronics Co., Ltd.
KOSE:A005930
24.22%
0.840
11,241
247,705
4.54%
0.812
Equity Portion
Western Digital Corporation
NasdaqGS:WDC
32.70%
1.670
10,585
10,941
96.75%
1.011
WACC ERROR:#REF!
Broadcom Inc.
NasdaqGS:AVGO
5.58%
0.880
37,548
111,474
33.68%
0.668
Average
1.237
Re Apple
Other Private Company
Target Company D/E
15.00%
Beta 1.24 ERROR:#REF!
Target Company Tax Rate
25.00%
Rf
1.776%
Target Company Levered Beta
MRP
5.50%
Re 8.596% ERROR:#REF!
Sample Output Summary
($mm except price/share and multiples and as otherwise indicated)
Memo items
Methodology
EV/
2020 EBITDA
2020 P/E
Premium / (Discount) to Recent Close
Shares out
2020 Net Income
Closing Share Price
Unlevered DCF (Terminal EV/ EBITDA Approach)
284.82
8.71%
DCF (Perp. Growth Rate Approach)
Levered DCF (Terminal EV/EBITDA Approach)
Average ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Sample Discrete Depreciation
Year Year Year Year Year Year Year Year Year Year
1 2 3 4 5 6 7 8 9 10
Capex
10,000
1
5,000
20,000
25,000
30,000
5.0 Depreciation
Yrs
1 10,000
1,000
2,000
2 15,000
1,500
3,000
3 20,000 2,000 4,000 4,000 4,000 4,000 2,000
4 25,000
2,500
5 30,000 3,000
6,000
Total Depreciation
3,500
7,000
11,500
17,000
19,000
16,500
13,000
8,500
2
g
VALUATION –
NI
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
DCF
Part 1.
Comparable Companies
NIKE Comps
You believe that
P/E
You’ve collected the following information about NIKE:
Most recent share price ($/share)
9
3
4
Most recent shares outstanding (mm)
1,5
6
8
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
Q3.
Calculate NIKE’s cost of equity using
CAPM
Comparable companies’
Beta
You believe that in the long term, NIKE will have a debt / market equity ratio of:
5.0%
Treasury Yield is:
1.
7
You’ve been asked to use the following market risk premium:
5.50%
NIKE’s Effective
Ta
x
16.08%
Q4.
Calculate NIKE’s
WACC
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
Q6.
Unlevered
Free Cash Flow
Q7
Calculate terminal value assuming the following:
Use
2024
Assume that terminal Net
Debt
Q8
Calculate
NPV
2019
Q9.
Based on your analysis, what’s NIKE’s equity value?
Hint: your valuation date is as of year-end 2019.
Q
10
What is the price/share implied by your DCF valuation? Based on this valuation only, would your recommendation be “Buy”, “Sell”, or “Hold”?
Q
11
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
2.50%
Hint: use the
2024 FCF
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
Enterprise Value
2020
EV
EB
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
Solution
Q1 and Q2
COMP
Name
Market Cap
Tax Rate
LTM NI
LTM P/E
Debt/ Equity
Unlevered Beta
2
COMP’s
D/E
Skechers
6,
25
123
824
0.79
1
8.0%
334
18.7
2.0%
0.
78
3
COMP’s Unlev Beta
Adidas
59,422
2,2
14
2,
58
0.75
25.0%
2,013
29.5
3.7%
0.73
1
Avg
Puma
11,000
9
85
492
0.
37
26
282
39
9.0%
0.
35
2
Implied Stock Value
Fila
785
676
53
0.78
29.0%
29.9
8
6.0%
0.48
2
Implied Price/share
Under Armour
7,420
592
41
0.60
22.0%
112
66.5
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
Shares Outstanding
15
LTM NI of NIKE
40
Implied Value of Stocks
1
47
Implied Stock Price
94.37
Current Price
93.34
Q3 and Q4
Leverage Beta
4 WACC
Rf
1.77%
WACC is
4.98%
Re
MKT Risk Premium
Re is 5.1%
Unleveraged Beta
11 Tol D/E
0.05
EF Tax
D/(D+E)
Debt Cost
0.03
Leveraged Beta
0.605
Re
5.10%
WACC 4.98%
Q5 and Q6
2020
2021
2022
2023
2 Tax
2
Correct NOPAT
EBIT
5,533
5,948
6,394
6,
71
6,915
1
D&A
(Taxes)
890
956
1,028
1,080
1,112
1
Delta WC
NOPAT
4,6
43
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)
(
44
(282)
Capex
(
1,367
(
1,509
(1,
666
(1,
84
(
2,032
10 Tol
FCF
3,676
3,864
4,1
38
4,
52
4,787
Q7 1 NI
Terminal
5,830
2
Terminal P
Terminal P/E
2
Terminal Debt
214,116
2
Terminal Cash
3,479
1
Correct EV
2,877
8 Tol
Terminal EV
214,718
Q8 4
Correct value, npv function is required
178,512
3
Correct Intepretation of NPV
Cash
4
46
7 Tol Equity
179,499
# of Shares
Shares Price
114.48
Q9 and Q10
The
Share Price
We should buy NIKE
Equity Value
2
Price/Share
2
Investment conclusion
10 Tol
Q11 4
Correct
Terminal Value
Price/Share is 106.6$
Correct NPV, npv function is required
198,185
2 Debt NPV
166,158
2 Cash Debt 3,479
2 Equity Value Cash 4466
2 Price/Share Equity
167,145
15 Tol Shares Price
106.60
Q12 and Q13
Share price summary
Method
EV/2020
EBITDA
2020 PE
Premium Rate
2
Equity Value summary
146,986
147,973
22.95
31.69
1.10%
4
EV summary
NPV – PE Approach
178,512.11
179,499.11
27.87
38.44
22.64%
2 EV/2020EBITDA
NPV – Perpetual Approach
25.94
35.79
14.20%
2 2020 PE
2 Premium Rate
AVG
105.15
163,885.51
164,87
2.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
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 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 Comparable Companies
Solution
($mm)
Skechers 6,250
18.0%
Adidas 59,422 2,214
Puma 11,000 985 492 0.37
26.0%
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
Comp Analysis
LTM NI 4,029
(x) Comps Average P/E
Implied Equity Value
(/) Shares Out
1,568
Price/ Share
WACC Analysis
Market Risk Premium
Risk Free Rate
Debt/ Market Equity
5.00%
Debt/ (Debt + Market Equity)
Tax Rate 16.08%
Unlevered Beta
0.5
81
Levered Beta
Cost of Debt
3.00%
WACC 4.98%
x
Free Cash Flow Buildup
($mm)
(Taxes)
(890)
(956)
(1,028)
(1,080)
(1,112)
1
6.1%
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
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
(x) Terminal Multiple
36.73
Terminal Equity Value
(+) Terminal Debt
(-) Terminal Cash
(2,877)
Terminal Value 214,718 Terminal Value 198,185
x
Valuation
2020 2021 2022 2023 2024 Terminal
With Terminal P/E Multiple
Cash Flows (Terminal P/E Mult)
Cash Flows (
Perp. G
Terminal P/E Perp. G
Enterprise Value 178,512 166,158
(Debt)
(3,479)
(+) Cash
4,466
Equity Value 179,499 167,145
(/) Shares Out 1,568 1,568
Price / Share
NIKE Forecast
NIKE FORECAST
(In millions, except per share data)
Year Ended May 31,
Historic Average
Growth CAGR
2015
2016
2017
2018
Income Statement
Revenues
30,601
32,376
34,350
36,397
39,117
42,051
45,205
48,595
51,025
52,
55
Cost of sales
(16,534)
(17,405)
(19,038)
(20,441)
(21,643)
(23,103)
(24,836)
(26,698)
(28,033)
(28,8
74
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%
7.5%
Gross Margin
45.1%
46.0%
46.2%
44.6%
43.8%
44.7%
COGS Margin
54
54.0%
53.8%
55.4%
56.2%
55.3%
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%
30.8%
31.6%
32.5%
Interest (expense) / income, net
(28)
(19)
(59)
(54)
(49)
Other income / (expense), net
140
196
(66)
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,670
5,018
5,393
5,661
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.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
Depreciation
606
649
706
747
705
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%
EBITDA Margin
15.1%
15.6%
15.9%
14.3%
14.0%
15.2%
15.3%
15.5%
Other Metrics
Effective Tax Rate
22.2%
18.7%
16.1%
Dividend Payout
36.6%
28.4%
28.0%
27.4%
65.5%
33.7%
Balance Sheet
ASSETS
Current assets:
Cash and equivalents
3,852
3,138
3,808
4,249
3,344
2,293
1,390
2,029
Short-term investments
2,072
2,319
2,371
996
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
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
283
285
Goodwill
131
139
154
Deferred income taxes and other assets
2,587
2,422
2,787
2,509
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
Notes payable
336
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,269
5,010
Income taxes payable
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
Deferred income taxes and other liabilities
1,479
1,770
1,907
3,216
3,347
Total non-current liabilities
2,558
3,763
5,378
6,684
6,811
Shareholders’ equity:
PIC
6,776
7,789
5,713
6,387
7,166
3,916
(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
Check
– 0
Cash Flow Statement
Net Income 4,670 5,018 5,393 5,661 5,830
Change in A/R
(120)
(329)
(354)
(254)
(160)
Change in Inventories
(489)
(458)
(493)
(353)
(222)
Change in Payables
135
206
221
159
Cash From Operations
5,070
5,401
5,832
6,388
6,845
Capital Expenditures
(1,113)
(993)
(1,105)
(1,119)
Free Cash Flow
3,703
3,891
4,165
4,548
4,814
Debt Repayment
Share Repurchases
(
3,250
(
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
Ending Cash Balance
Capex as % of PPE
28.8%
33.0%
31.4%
25.8%
25.1%
Depreciation as % of Capex
63.9%
54.4%
65.4%
72.7%
63.0%
Financial Ratios
Turnover Ratios
EBIT / Assets
20.8%
19.1%
21.2%
23.3%
26.7%
26.2%
ROE
29.6%
34.6%
41.1%
51.7%
56.5%
60.2%
61.0%
52.8%
Efficiency Ratios
A/R Days
Inventory Days
A/P Days
Leverage Ratios
Total Debt
1,260
2,038
3,802
3,810
Debt/ EBITDA
0.26
0.40
0.64
Liquidity Ratios
Current Ratio
2.46
2.80
2.93
2.10
PP&E Schedule
BB
Additions
(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
Share Repurchases
Share Price @ which repurchased
90.0
106.0
115.0
120.0
122.5
Share repurchases ($mm)
Shares BB
1,532
1,501
1,473
1,456
(Shares Repurchased)
(36)
(31)
(17)
(16)
Shares EB
1,440
WASO
Summary Valuation Output
($mm except price/share and multiples and as otherwise indicated)
Memo items
Methodology
EV/
2020 EBITDA
2020 P/E
Premium / (Discount) to Recent Close
Shares out
Net Debt
2020 Net Income
Closing Share Price
Comparable Companies 94.37 147,973 146,986
22.95x
31.69x
1,568.0
(987.0)
6,405.9
4,670.1
DCF (Terminal P/E Approach)
27.87x
38.44x
DCF (Perp. Growth Rate Approach)
25.94x
35.79x
Average 105.15
164,873
163,886
25.58x
35.30x