microsoft Project

profileVIK1299
applereport2.xlsx
Home>Business & Finance homework help>Accounting homework help>microsoft Project

Assumptions

LTM Growth= 7%

IS

Assumptions
iphone Super Cycle Growth Rate 20% 18.34%
Normal Cycle Growth Rate 1.50% 1.09%
As Reported Annual Income Statement
Report Date 09/30/2017 09/29/2018 09/28/2019 Growth 9/302020 9/30/21 9/30/22 9/29/23 9/27/24 9/27/25
Currency USD USD USD
Audit Status Not Qualified Not Qualified Not Qualified
Consolidated Yes Yes Yes
Scale millions millions millions millions millions millions millions millions
Iphone 139,337 164,888 142,381 0.0109 144517 173420 148885 151118 153385
imac 25,569 25,198 25,740 0.0033 25826 25912 25999 26085 26173
Ipad 18,802 18,380 21,280 0.0639 22639 24085 25623 27259 29000
wearable 12,826 17,381 24,482 0.3816 33824 46731 64563 89199 123236
Products 196534 225847 213883 0.0432 226806 270148 265068 293661 331792
Services 32,700 39,748 46291 0.1898 55077 65531 77969 92767 110374
Net sales 229234 265595 260174 0.0654 281883 335678 343037 386428 442167 0.1191272295 473118.492091816
Cost of sales - products - 144996
Cost of sales - services - 16786
Cost of sales 141048 163756 161782 175281 208732 213308 240290 274949
Gross margin 88186 101839 98392 37.82% 106602 126946 129729 146138 167218 178923
Research & development expense 11581 14236 16217
Selling, general & administrative expense 15261 16705 18245
Total operating expenses 26842 30941 34462
Operating income (loss) 61344 70898 63930 24.57% 69264 82483 84291 94953 108649 116255
Interest & dividend income 5201 5686 4961
Interest expense 2323 3240 3576
Other income (expense), net -133 -441 422
Other income/(expense), net 2745 2005 1807
Income (loss) before provision for income taxes 64089 72903 65737
Current federal income tax expense (benefit) 7842 41425 6384
Deferred federal income tax expense (benefit) 5980 -33819 -2939
Total federal income tax expense (benefit) 13822 7606 3445
Current state income tax expense (benefit) 259 551 475
Deferred state income tax expense (benefit) 2 48 -67
Total state income tax expense (benefit) 261 599 408
Current foreign income tax expense (benefit) 1671 3986 3962
Deferred foreign income tax expense (benefit) -16 1181 2666
Total foreign income tax expense (benefit) 1655 5167 6628
Provision for (benefit from) income taxes 15738 13372 10481
Net income (loss) 48351 59531 55256 21.24% 59866 71292 72855 82070 93908 100481
Weighted average shares outstanding - basic 5,217,242 4,955,377 4,617,834 17102.536 17102.536 17102.536 17102.536 17102.536 17102.536 17102.536
Weighted average shares outstanding - diluted 5,251,692 5,000,109 4,648,913 17274.536 17274.536 17274.536 17274.536 17274.536 17274.536 17274.536
Year end shares outstanding
Net earnings (loss) per share - basic 9.270 12.010 11.97 3.500 4.168 4.260 4.799 5.491 0.000 5.875
Net earnings (loss) per share - diluted 9.210 11.910 11.89 3.466 4.127 4.217 4.751 5.436 0.000 5.817
Cash dividends declared per share 0.6 0.68 0.75
Number of full time employees 123000 132000 137000
Number of common stockholders 25333 23710 23233
Foreign currency translation adjustments 224000 -525000 -408000
Select an operating scenario: Base case
Revenue growth 1.5% 20.0% 1.5% 1.5% 1.5%
Gross profit as % of sales 37.1% 37.4% 37.4% 37.4% 37.4%
R&D margin 2.9% 3.1% 3.1% 3.1% 3.1%
SG&A margin 6.8% 6.8% 6.8% 6.8% 6.8%
Revenue growth
Best case 10.0% 30.0% 10.0% 10.0% 10.0%
Base case 1.5% 20.0% 1.5% 1.5% 1.5%
Weak case 0.0 0.0 0.0 0.0 0.0
Gross profit as % of sales
Best case 37.5% 37.5% 37.5% 37.5% 37.5%
Base case 37.1% 37.4% 37.4% 37.4% 37.4%
Weak case 36.5% 36.5% 36.5% 36.5% 36.5%
R&D margin
Best case 2.5% 2.5% 2.5% 2.5% 2.5%
Base case 2.9% 3.1% 3.1% 3.1% 3.1%
Weak case 3.3% 3.3% 3.3% 3.3% 3.3%
SG&A margin
Best case 6.4% 6.4% 6.4% 6.4% 6.4%
Base case 6.8% 6.8% 6.8% 6.8% 6.8%
Weak case 7.0% 7.0% 7.0% 7.0% 7.0%

BS

Powered by Clearbit
Apple Inc (NMS: AAPL)
Exchange rate used is that of the Year End reported date
As Reported Annual Balance Sheet in Thousands
Report Date 09/30/2017 09/29/2018 09/28/2019 2020 2021 2022 2023 2024
Currency USD USD USD
Audit Status Not Qualified Not Qualified Not Qualified
Consolidated Yes Yes Yes
Scale Thousands Thousands Thousands
Cash & cash equivalents 20289000 25913000 48844000
Marketable securities 53892000 40388000 51713000
Accounts receivable, gross 17932000 - -
Allowances 58000 - -
Accounts receivable, net 17874000 23186000 22926000
Inventories 4855000 3956000 4106000
Deferred tax assets - - -
Vendor non-trade receivables 17799000 25809000 22878000
Other current assets 13936000 12087000 12352000
Total current assets 128645000 131339000 162819000
Marketable securities 194714000 170799000 105341000
Land & buildings 13587000 16216000 17085000
Machinery, equipment, & internal-use software 54210000 65982000 69797000
Leasehold improvements 7279000 8205000 9075000
Gross property, plant & equipment 75076000 90403000 95957000
Accumulated depreciation & amortization 41293000 49099000 58579000
Property, plant & equipment, net 33783000 41304000 37378000
Goodwill 5717000 - -
Acquired intangible assets, net 2298000 - -
Other non-current assets 10162000 22283000 32978000
Total non-current assets - 234386000 175697000
Total assets 375319000 365725000 338516000
Accounts payable 49049000 55888000 46236000
Accrued expenses 25744000 - -
Deferred revenue 7548000 7543000 5522000
Other current liabilities - 32687000 37720000
Commercial paper 11977000 11964000 5980000
Term debt - -
Current portion of long-term debt 6496000 8784000 10260000
Total current liabilities 100814000 116866000 105718000
Deferred revenue 2836000 2797000 0
Floating & fixed-rate notes 104021000 104193000 101679000
Total debt 104021000 104193000 101679000
Unamortized discount -225000 -218000 -224000
Hedge accounting fair value adjustments -93000 -1456000 612000
Less: current portion of long-term debt 6496000 8784000 10260000
Term debt 97207000 93735000 91807000
Long-term taxes payable - 33589000 29545000
Deferred tax liabilities 31504000 426000 -
Other non-current liabilities 8911000 11165000 20958000
Other non-current liabilities 40415000 45180000 50503000
Total non-current liabilities - 141712000 142310000
Total liabilities 241272000 258578000 248028000
Common stock 35867000 40201000 45174000
Retained earnings 98330000 70400000 45898000
Cumulative foreign currency translation -354000 -1055000 -1463000
Unrecognized gains (losses) on derivative instruments -124000 810000 172000
Unrealized gains (losses) on marketable securities 328000 -3209000 707000
Accumulated other comprehensive income (loss) -150000 -3454000 -584000
Total shareholders' equity 134047000 107147000 90488000
In Millions 2018 2019 2020 2021 2022 2023 2024
Accounts receivable
Beginning of period 22,926 24,806 28,868 27,443 30,914
Increases / (decreases) 1,880 4,063 (1,425) 3,471 4,459
End of period 23,186 22,926 24,806 28,868 27,443 30,914 35,373
AR as % of sales 8.7% 8.8% 8.8% 8.6% 8.0% 8.0% 8.0%
Days sales outstanding (DSO) 32 days 32 days 32 days 31 days 29 days 29 days 29 days
Inventory
Beginning of period 4,855 4,733 5,218 5,333 6,007
Increases / (decreases) (122) 486 114 675 866
End of period 3,956 4,855 4,733 5,218 5,333 6,007 6,874
Inventory as % of COGS 2.4% 3.0% 2.7% 2.5% 2.5% 2.5% 2.5%
Inventory turnover 41.4x 33.3x 37.0x 40.0x 40.0x 40.0x 40.0x
Accounts payable
Beginning of period 46,236 50,094 64,707 66,126 74,490
Increases / (decreases) 3,858 14,613 1,419 8,364 10,744
End of period 55,888 46,236 50,094 64,707 66,126 74,490 85,234
AP as % of COGS 34.1% 28.6% 28.6% 31.0% 31.0% 31.0% 31.0%
Days payables outstanding (DPO) 125 days 104 days 104 days 113 days 113 days 113 days 113 days
Accrued expenses & def revenues
Beginning of period 43,242 46,511 55,387 56,601 63,761
Increases / (decreases) 3,269 8,876 1,214 7,160 9,197
End of period 43,027 43,242 46,511 55,387 56,601 63,761 72,958
Accrued expenses & def revs as % of sales 16.2% 16.6% 16.5% 16.5% 16.5% 16.5% 16.5%
PROPERTY, PLANT & EQUIPMENT
Beginning of period 37,378 38,787 40,466 42,181 44,113
Plus: Capital expenditures 14,094 16,784 17,152 19,321 22,108
Less: Depreciation (12,685) (15,106) (15,437) (17,389) (19,898)
End of period 37,378 38,787 40,466 42,181 44,113 46,324
Capex (enter as +) 13,313 10,495 14,094 16,784 17,152 19,321 22,108
Capital expenditures as % of revenue 5.0% 4.0% 5.0% 5.0% 5.0% 5.0% 5.0%
Depreciation & Amortization(enter as -) 10,903 12,547 12,685 15,106 15,437 17,389 19,898
Depreciation as a % of capital expenditures 81.9% 119.6% 90.0% 90.0% 90.0% 90.0% 90.0%

Beta Calculation

Date apple spy r rm
11/1/15 27.330675 189.159454
12/1/15 24.422157 184.790527 -10.64% -2.31%
1/1/16 22.584578 176.635452 -7.52% -4.41%
2/1/16 22.433773 176.489532 -0.67% -0.08%
3/1/16 25.424799 187.394791 13.33% 6.18%
4/1/16 21.867334 189.103653 -13.99% 0.91%
5/1/16 23.294987 192.320618 6.53% 1.70%
6/1/16 22.43701 191.990677 -3.68% -0.17%
7/1/16 24.457745 200.027649 9.01% 4.19%
8/1/16 24.901323 200.267212 1.81% 0.12%
9/1/16 26.676199 199.272232 7.13% -0.50%
10/1/16 26.791819 196.806595 0.43% -1.24%
11/1/16 26.079195 204.056641 -2.66% 3.68%
12/1/16 27.470144 206.973282 5.33% 1.43%
1/1/17 28.78175 211.918762 4.77% 2.39%
2/1/17 32.491241 220.245392 12.89% 3.93%
3/1/17 34.220959 219.56546 5.32% -0.31%
4/1/17 34.218575 222.709595 -0.01% 1.43%
5/1/17 36.388645 225.852692 6.34% 1.41%
6/1/17 34.448315 226.189438 -5.33% 0.15%
7/1/17 35.574905 231.964294 3.27% 2.55%
8/1/17 39.227352 232.641113 10.27% 0.29%
9/1/17 37.008904 236.156693 -5.66% 1.51%
10/1/17 40.591652 242.921097 9.68% 2.86%
11/1/17 41.266426 250.346207 1.66% 3.06%
12/1/17 40.783367 252.093826 -1.17% 0.70%
1/1/18 40.349579 267.662811 -1.06% 6.18%
2/1/18 42.925804 257.93045 6.38% -3.64%
3/1/18 40.598782 249.859772 -5.42% -3.13%
4/1/18 39.988998 252.156967 -1.50% 0.92%
5/1/18 45.218109 258.286682 13.08% 2.43%
6/1/18 44.964947 258.61084 -0.56% 0.13%
7/1/18 46.223221 269.395691 2.80% 4.17%
8/1/18 55.293457 277.994781 19.62% 3.19%
9/1/18 55.026672 278.38736 -0.48% 0.14%
10/1/18 53.349594 260.322754 -3.05% -6.49%
11/1/18 43.530891 265.15155 -18.40% 1.85%
12/1/18 38.585068 240.401489 -11.36% -9.33%
1/1/19 40.713184 261.16568 5.52% 8.64%
2/1/19 42.354534 269.6315 4.03% 3.24%
3/1/19 46.663288 273.308167 10.17% 1.36%
4/1/19 49.296772 285.726501 5.64% 4.54%
5/1/19 43.007851 267.505402 -12.76% -6.38%
6/1/19 48.808441 284.735291 13.49% 6.44%
7/1/19 52.53714 290.446136 7.64% 2.01%
8/1/19 51.47673 285.583038 -2.02% -1.67%
9/1/19 55.442406 289.801636 7.70% 1.48%
10/1/19 61.579021 297.575439 11.07% 2.68%
11/1/19 66.156113 308.347168 7.43% 3.62%
12/1/19 72.9095 315.753937 10.21% 2.40%
1/1/20 76.847343 317.178192 5.40% 0.45%
2/1/20 67.871758 292.068542 -11.68% -7.92%
3/1/20 63.28677 254.103378 -6.76% -13.00%
4/1/20 73.119873 288.05426 15.54% 13.36%
5/1/20 79.127747 301.778687 8.22% 4.76%
6/1/20 91.035858 305.784912 15.05% 1.33%
7/1/20 106.068756 325.21814 16.51% 6.36%
8/1/20 128.807755 347.917297 21.44% 6.98%
9/1/20 115.809998 333.55481 -10.09% -4.13%
10/1/20 113.019997 333.839996 -2.41% 0.09%
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.6399602394
R Square 0.4095491081
Adjusted R Square 0.3991903205
Standard Error 0.0680366209
Observations 59
ANOVA
df SS MS F Significance F
Regression 1 0.1830132483 0.1830132483 39.5363940972 0.0000000484
Residual 57 0.2638519619 0.0046289818
Total 58 0.4468652102
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 0.014319303 0.0091250406 1.5692317077 0.1221291452 -0.0039532756 0.0325918817 -0.0039532756 0.0325918817
X Variable 1 1.3007713748 0.2068723475 6.2877972373 0.0000000484 0.8865166446 1.7150261051 0.8865166446 1.7150261051

FCF

Dscounted Cash Flow Model
General assumptions
Share price as of last close $117.00
Latest closing share price date 10/12/20
Latest basic share count
Weighted average cost of capital 8.44%
As Reported Annual Cash Flow
Report Date 09/30/2017 09/29/2018 09/28/2019 9/30/20 9/30/21 9/30/22 9/29/23 9/27/24
Currency
Audit Status
Consolidated
Scale
EBITDA $ 81,949 $ 97,588 $ 99,728 $ 112,342 $ 128,547
EBIT $ 69,264 $ 82,483 $ 84,291 $ 94,953 $ 108,649
tax rate 18.30% 15.90% 15.90% 15.90% 15.90% 15.90% 15.90%
EBIAT (NOPAT) $ 58,251 $ 69,368 $ 70,889 $ 79,856 $ 91,374
Depreciation and amortization 12,685 15,106 15,437 17,389 19,898
Stock based compensation 6068 6068 6068 6068 6068 6068
Accounts receivable (1,880) (4,063) 1,425 (3,471) (4,459)
Inventory 122.4156340547 -485.7221415528 -114.3963166807 -674.5364137432 -866.4906188905
Accounts payable 3,858 14,613 1,419 8,364 10,744
Accrued expenses & def revenues 3,269 8,876 1,214 7,160 9,197
Other current assets (inc. non-trade receivables) 0 0 0 0 0
Deferred tax assets (DTAs) 0 0 0 0 0
Other assets 0 0 0 0 0
Other non current liabilities 0 0 0 0 0
Unlevered CFO 82,373 109,483 96,337 114,691 131,955
Less: Capital expenditures (14,094) (16,784) (17,152) (19,321) (22,108)
Less: Purchases of intangible assets 0 0 0 0 0
Unlevered FCF 68,279 92,699 79,185 95,369 109,847
Unlevered FCF growth rate 35.76% -14.58% 20.44% 15.18%
Basic number of shares outstanding 17102.536 17102.536 17102.536 17102.536 17102.536
Diluted numer of shares outstanding 17274.536 17274.536 17274.536 17274.536 17274.536
FCF PER SHARE 3.9525850274 5.3662093986 4.5839343686 5.5208089486 6.3588985807
Timing factor 0.03 0.97 1.97 2.96 3.96
Present value of Unlevered FCF 68,464 85,712 67,516 75,000 79,694
Perpetuity approach Exit EBITDA multiple approach
Unlevered FCF in last forecast period (t) 109,847 Terminal year EBITDA 128,547
FCFt+1 113,142 Terminal value EBITDA multiple 16.00x
Long term growth rate (g) 3.00% Terminal value 2,056,749
Terminal value 2,078,168
Administrator: Administrator: PV as of 2018		 Present value of terminal value 1,492,178
Present value of terminal value 1,507,718 Present value of stage 1 cash flows 376,385
Present value of stage 1 cash flows 376,385 Enterprise value 1,868,564
Enterprise value 1,884,103
Fair value per share
Cash & equivalents ST & LT market. securities 193,617 Perpetuity EBITDA
Long term debt 101,679 Enterprise value 1,884,103 1,868,564
Net debt (91,938) Less: Net debt 91,938 91,938
Equity Value 1,976,041 1,960,502
Share Price $ 114.39 $ 113.49
Equity value per share
Long term growth rate (g):
$114.39 2.0% 2.5% 3.0% 3.5% 4.0%
10.5% $ 78.13 $ 81.65 $ 85.65 $ 90.23 $ 95.52
WACC: 9.5% $ 87.61 $ 92.31 $ 97.74 $ 104.09 $ 111.59
8.5% $ 100.05 $ 106.57 $ 114.28 $ 123.55 $ 134.90
7.5% $ 117.07 $ 126.59 $ 138.26 $ 152.89 $ 171.75
6.5% $ 141.75 $ 156.78 $ 176.17 $ 202.14 $ 238.71
Equity value per share
Exit EBITDA Multiple
$113.49 14.0x 15.0x 16.0x 17.0x 18.0x
10.5% $ 96.63 $ 101.65 $ 106.67 $ 111.69 $ 116.71
WACC: 9.5% $ 99.59 $ 104.79 $ 110.00 $ 115.20 $ 120.41
8.5% $ 102.68 $ 108.07 $ 113.47 $ 118.87 $ 124.27
7.5% $ 105.91 $ 111.51 $ 117.10 $ 122.70 $ 128.30
6.5% $ 109.29 $ 115.10 $ 120.91 $ 126.72 $ 132.53

WACC

Cost of equity
Beta= 1.3
Rf= 1.58%
Market Risk Premium = 5.50%
Equity =
Re = 8.7300%
Cost of Debt
Interest expense = 3200
Long term debt 101679
Effective cost of debt = 3.15%
Stock Price = 124
MV of Equity 2120714.464
Weght on equity = 0.9542479756
Weight on Debt = 0.0457520244
Federal Tax rate = 21%
WACC = 8.44%
total noncurrent debt = 142310
Shareholder's equity = 90488
Debt to equity ratio = 1.572694722
Asset Beta = 0.5797285436
Cost of Capital for the firm = 4.77%

Multiples

Assumptions
Super Cycle Growth Rate 20%
Normal Cycle Growth Rate 1.50%
The following table is downlaod from Morningstar.com
2009 2011 2012 2013 2014 2015 2016 2017 2018 2019 Current 5-year average index multiple used in Valuation
 Price/Earnings 18.01 14.63 12.07 14.11 17.11 11.42 13.94 18.37 13.24 24.7 34.27 17.75 25.88 24.7
 Price/Cash Flow 13.25 10.11 8.88 9.74 11.32 7.5 9.68 13.97 10.19 19.67 25.05 13.39 14.61 25.05
 Price/Book 5.43 4.93 3.92 4.05 5.77 4.89 4.75 6.42 6.96 14.23 26.74 8.71 3.46
Price/Forward Earnings 14.29 10.34 8.9 12.5 14.08 10.56 12.9 14.9 12.08 22.17 29.33 16.32 — 29.33
Enterprise Value/EBITDA 11.92 9.87 7.76 8.36 10.57 7.18 8.58 11.79 9.12 15.82 23.55 11.65 — 23.55
Stock price as of 10/5 116.5
Based on Forward P/E 121.0443411976 hold
Current P/E 101.9364209881 hold
PCF Multiple = 134.4235454357 buy
The following sensitivity analysis comes from Data -> what if analysis -> Data table.
Equity value per share
supercycle growth rate (g):
$121.04 12% 14% 16% 18% 20% 22% 24% 26% 28%
31.0x $ 123.53 $ 124.63 $ 125.73 $ 126.83 $ 127.94 $ 129.04 $ 130.14 $ 131.24 $ 132.34
Forward P/E Multiple 30.0x $ 119.55 $ 120.61 $ 121.68 $ 122.74 $ 123.81 $ 124.88 $ 125.94 $ 127.01 $ 128.07
29.0x $ 115.56 $ 116.59 $ 117.62 $ 118.65 $ 119.68 $ 120.71 $ 121.74 $ 122.77 $ 123.80
28.0x $ 111.58 $ 112.57 $ 113.57 $ 114.56 $ 115.56 $ 116.55 $ 117.55 $ 118.54 $ 119.54
27.0x $ 107.59 $ 108.55 $ 109.51 $ 110.47 $ 111.43 $ 112.39 $ 113.35 $ 114.31 $ 115.27
26.0x $ 103.61 $ 104.53 $ 105.45 $ 106.38 $ 107.30 $ 108.23 $ 109.15 $ 110.07 $ 111.00
25.0x $ 99.62 $ 100.51 $ 101.40 $ 102.29 $ 103.17 $ 104.06 $ 104.95 $ 105.84 $ 106.73
24.0x $ 95.64 $ 96.49 $ 97.34 $ 98.19 $ 99.05 $ 99.90 $ 100.75 $ 101.61 $ 102.46
23.0x $ 91.65 $ 92.47 $ 93.29 $ 94.10 $ 94.92 $ 95.74 $ 96.56 $ 97.37 $ 98.19
22.0x $ 87.67 $ 88.45 $ 89.23 $ 90.01 $ 90.79 $ 91.58 $ 92.36 $ 93.14 $ 93.92
21.0x $ 83.68 $ 84.43 $ 85.17 $ 85.92 $ 86.67 $ 87.41 $ 88.16 $ 88.91 $ 89.65
20.0x $ 79.70 $ 80.41 $ 81.12 $ 81.83 $ 82.54 $ 83.25 $ 83.96 $ 84.67 $ 85.38
19.0x $ 75.71 $ 76.39 $ 77.06 $ 77.74 $ 78.41 $ 79.09 $ 79.76 $ 80.44 $ 81.11
18.0x $ 71.73 $ 72.37 $ 73.01 $ 73.65 $ 74.29 $ 74.93 $ 75.56 $ 76.20 $ 76.84
17.0x $ 67.74 $ 68.35 $ 68.95 $ 69.55 $ 70.16 $ 70.76 $ 71.37 $ 71.97 $ 72.58
16.0x $ 63.76 $ 64.33 $ 64.89 $ 65.46 $ 66.03 $ 66.60 $ 67.17 $ 67.74 $ 68.31
15.0x $ 59.77 $ 60.31 $ 60.84 $ 61.37 $ 61.90 $ 62.44 $ 62.97 $ 63.50 $ 64.04
14.0x $ 55.79 $ 56.29 $ 56.78 $ 57.28 $ 57.78 $ 58.28 $ 58.77 $ 59.27 $ 59.77
13.0x $ 51.80 $ 52.26 $ 52.73 $ 53.19 $ 53.65 $ 54.11 $ 54.57 $ 55.04 $ 55.50
12.0x $ 47.82 $ 48.24 $ 48.67 $ 49.10 $ 49.52 $ 49.95 $ 50.38 $ 50.80 $ 51.23
11.0x $ 43.83 $ 44.22 $ 44.62 $ 45.01 $ 45.40 $ 45.79 $ 46.18 $ 46.57 $ 46.96