Account and Finance 3 statement model valuation

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

Assigment

x
You work as an analyst at Retail group at the Best Investment Bank. One of the companies you help cover is 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-statement model and fill out the corresponding schedules provided
x Part 1 - Historical Performance
Item Model Line Reference
1 Revenue Growth. 9 Calculate 2016-2019 revenue growth CAGR. Calculate 2016-2018 revenue growth CAGR as well. Based on this information, has the revenue growth been slowing or increasing?
2 Gross Margin 14 Calculate gross margin % for the business for each historic year provided. Compute the average
3 O&M Margin 17,18 Calculate O&M margin and O&M growth rate. Compute the historic average
4 EBIT and EBITDA 21,23,24 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 goes up, will DPS increase, decrease, or remain the same?
7 Returns 130-on Calculate EBIT / Assets and ROE for 2017,2018, and 2019. Use beginning balance of the balance sheet account to calculate the ratios.
8 Returns 130-on 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 105-on 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 Days (of Sales)
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 and Interest 142-on Calculate an implied interest rate for 2019 based on average debt balance
12 Debt and Interest 142-on Calculate Debt / EBITDA and EBITDA / Interest for 2016-2019. What can you tell about Apple's leverage and ability to cover interest?
13 Capex and 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 to decline compared to 2019, but you believe that Apple has a good product pipeline and you expect revenues to grow meaninfully going forward.
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 is expected to be the same in the projected years as in 2019.
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 averages for all working capital accounts mentioned in question 9.
Marketable securities,other assets and other liabilities are flatlined.
17 Cash Flow Statement 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 $282.8 / share
Based on the recent stock price and 2019 EPS, calculate the LTM P/E ratio and assume that it remains constant across the forecast period for the purposes of calculation of the share repurchase price
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 EPS 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, and 2020 for you to perfectly match the equity research average on EPS?
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 2016-19
($ 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
x Income Statement
Revenues 215,639 229,234 265,595 260,174
% Growth 6.3% 15.9% -2.0% -5.0% 15.0% 10.0% 10.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.8% 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 4,625 4,625 4,625 4,625 4,625
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% 15.94% 15.94% 15.94% 15.94% 15.94%
Net Income 45,687 48,351 59,531 55,256
Profit Margin 21.2% 21.1% 22.4% 21.2%
Weighted Average Shares Outstanding (Thousands) 5,471 5,217 4,955 4,618
Shares Beginning Balance 5,126
Shares Ending Balance 4,755 4,443
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 157,054 157,054 157,054 157,054 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 45,330 45,330 45,330 45,330 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 $ 202,384 $ 202,384 $ 202,384 $ 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 88,223 88,223 88,223 88,223 88,223
Debt 87,032 115,680 114,483 108,047
Total Liabilities $ 193,437 $ 241,272 $ 258,578 $ 248,028 $ 88,223 $ 88,223 $ 88,223 $ 88,223 $ 88,223
Shareholders Equity 128,249 134,047 107,147 90,488
Total Liabilities and Shareholders Equity $ 321,686 $ 375,319 $ 365,725 $ 338,516 $ 88,223 $ 88,223 $ 88,223 $ 88,223 $ 88,223
Check - 0 - 0 - 0 - 0
Cash Flow Statement
Cash From Operations
Net Income
Depreciation and Amortization 10,505 10,157 10,903 12,547
Changes in Working Capital
Changes in A/R
Changes in Inventories
Changes in Non-Vendor Receivables
Changes in A/P
Chandes in Deferred Revenues
Cash From Operations
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 Flows
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)
Inventory 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 Re-Purchased @ 282.8
Nadia Zaets: Nadia Zaets: Stock Price as of 4/17/20
Share Repurchase Amount ($mm) 60,000 60,000 50,000 50,000 50,000
Repurchase # of Shares (mm)
# of Shares Outstanding (mm)
Beginning Balance
Issued
Shares Repurchased
Ending Balance
x PP&E and Capex Schedule
Sales
Capex
Depreciation
Capex / Sales %
Depreciation / Capex %
Net PP&E
Beginning Balance
Capex
(Depreciation)
Ending Balance

Sheet2

Apple Financial Model Q1 Growth CAGR
2016-18 2016-19
($ 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% 38.4% 38.4% 38.4% 38.4% 38.4% 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% 10.0% 10.0% 10.0% 10.0% 10.0% 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% 26.8% 26.7% 24.6% 23.1% 23.7% 23.7% 23.7% 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 23.0% 22.3% 22.6% 19.7% 18.4% 19.1% 19.1% 19.1% 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) (3,468) (3,468) (3,468) (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.7% 19.8% 19.8% 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 5,126
Shares Ending Balance 4,755 4,443 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% 22.6% 25.1% 24.9% 24.9% 24.9% 24.9% 24.9% 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 2020 2021 2022 2023 2024 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
Marketable Securities 217,101 248,606 211,187 157,054 157,054 157,054 157,054 157,054 157,054
Accounts Receivable 15,754 17,874 23,186 22,926 20,315 23,362 25,698 28,268 31,095 Q19
Inventories 2,132 4,855 3,956 4,106 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
Other Assets (current and non-current) 25,660 32,113 34,370 45,330 45,330 45,330 45,330 45,330 45,330
Net PP&E 27,010 33,783 41,304 37,378 38,462 39,709 41,080 42,589 44,248 Year 2020 2021 2022
Total Assets $ 321,686 $ 375,319 $ 365,725 $ 338,516 $ 318,522 $ 309,111 $ 311,990 $ 320,199 $ 334,272 Average 12.316 14.736 16.595
Apple EPS 10.92 13.64 15.76
Liabilities
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
Other Liabilities (Current and Non-Current) 58,101 70,966 77,867 88,223 88,223 88,223 88,223 88,223 88,223
Debt 87,032 115,680 114,483 108,047 108,047 108,047 108,047 108,047 108,047 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
Cash From Operations
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 Working Capital
Changes in A/R (2,120) (5,312) 260 2,611 (3,047) (2,336) (2,570) (2,827)
Changes in Inventories (2,723) 899 (150) 43 (609) (467) (514) (565)
Changes in Non-Vendor Receivables (4,254) (8,010) 2,931 2,563 (3,047) (2,336) (2,570) (2,827)
Changes in A/P 6,948 11,646 (9,652) 483 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)
Cash From Investing Activities
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) (60,000) (50,000) (50,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
Debt Repayment
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 32,983 15,621 11,989 13,037 19,230
Working Capital Schedule 2016A 2017A 2018A 2019P 2020 2021 2022 2023 2024
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) 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
WC Liability Ratios
A/P Days (of COGS) 112 104 114 125 104 112 112 112 112 112
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
Turnover Ratios
EBIT/Assets 19.9% 19.4% 18.0% 16.6% 21.0% 23.8% 25.9% 27.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
Beginning Balance
Issuance
(Repayment)
Ending Balance
Average Balance 101,356 115,082 111,265 108,047 108,047 108,047 108,047 108,047
Interest Rate 2.29% 2.82% 3.21% 3.21% 3.21% 3.21% 3.21% 3.21%
Interest Expense (1,456) (2,323) (3,240) (3,576) (3,468) (3,468) (3,468) (3,468) (3,468)
Total Interest Expense
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 23.63x 23.63x 23.63x 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
Share Repurchase Amount ($mm) 60,000 60,000 50,000 50,000 50,000
Repurchase # of Shares (mm) 212.16 232.55 155.10 134.24 117.43
# of Shares Outstanding (mm)
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% 5.1% 5.1% 5.1% 5.1% 5.1%
Depreciation / Capex % 91.4% 82.50% 81.58% 81.90% 119.55% 91.4% 91.4% 91.4% 91.4% 91.4%
Net PP&E
Beginning Balance 27,010 29,304 31,714 37,378 38,462 39,709 41,080 42,589
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 38,462 39,709 41,080 42,589 44,248

Sheet1

Unlevered DCF Valuation

2 Terminal Value Selection
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 Comments
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 ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Unlevered Taxes at ETR
NOPAT ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Net Operating Profit After Tax
(+) D&A ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Non-Cash Item
Changes in Working Capital ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
(-) Capex ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 111,644 x 15x OR 1
Unlevered Free Cash Flow ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 76,659 x (1+3%)/ (8.04%- 3%) 2
Enterprise Value ERROR:#REF!
(Debt) ERROR:#REF!
Cash ERROR:#REF!
Equity Value ERROR:#REF!
Shares Out ERROR:#REF!
Price/ Share ERROR:#REF!
Trading price 262.00 (Google Finance)
Conclusion? -> BUY

Levered DCF Valuation

EV/EBITDA 15.00 (Assumption)
Terminal Net Debt ERROR:#REF!
Re 8.60% (Per prior calc)
g 3.0% (Assumption)
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 ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Cash Flow to Equity ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 111,644 x 15 - 75,781
Note: Includes CF for Dividends and Share repurchases.
Equity Value ERROR:#REF!
Shares Out ERROR:#REF!
Price/ Share ERROR:#REF!
Trading price 262.00 (Google Finance)
Conclusion? -> BUY
Today ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! Terminal
Cash Flows to Equity ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
(Net Ouflow) ERROR:#REF!
Net Cash Flow ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
NPV at 8.596% ERROR:#REF!
IRR ERROR:#VALUE!

WACC

WACC Comment Company Name Ticker Symbol Tax Rate Beta Total Debt Market Cap. Debt/Equity Unlevered Beta D/ (D+E)
Cost of Debt 2.500% Provided Apple Inc. NASDAQ: AAPL ERROR:#REF! 1.24 ERROR:#REF! 1,184,000 ERROR:#REF! ERROR:#REF! ERROR:#REF!
Tax Rate ERROR:#REF! Projected HP Inc. NYSE:HPQ 25.44% 1.410 5,047 30,111 16.76% 1.253
D/(D+E) ERROR:#REF! 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 ERROR:#REF! Intel Corporation NasdaqGS:INTC 25.67% 0.810 28,487 209,076 13.63% 0.736
Cost of Equity 8.596% Use CAPM NetApp, Inc. NasdaqGS:NTAP 37.18% 1.50 1,793 15,352 11.68% 1.397
E/(D+E) ERROR:#REF! 1- D/(D+E) Samsung Electronics Co., Ltd. KOSE:A005930 24.22% 0.840 11,241 247,705 4.54% 0.812
Equity Portion ERROR:#REF! 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 ERROR:#REF!
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% 1.776% Target Company Levered Beta ERROR:#REF!
MRP 5.50% 5.50%
Re 8.596% ERROR:#REF!

Sample Output Summary

($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
Unlevered DCF (Terminal EV/ EBITDA Approach) 284.82 ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 8.71% ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! 262.00
DCF (Perp. Growth Rate Approach) ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Levered DCF (Terminal EV/EBITDA Approach) ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
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 15,000 20,000 25,000 30,000
5.0 Depreciation
Yrs
Capex
1 10,000 1,000 2,000 2,000 2,000 2,000 1,000
2 15,000 1,500 3,000 3,000 3,000 3,000 1,500
3 20,000 2,000 4,000 4,000 4,000 4,000 2,000
4 25,000 2,500 5,000 5,000 5,000 5,000 2,500
5 30,000 3,000 6,000 6,000 6,000 6,000 3,000
Total Depreciation 1,000 3,500 7,000 11,500 17,000 19,000 16,500 13,000 8,500 3,000