Finance Final Project

Asdfghjkl22*
ValuationTemplate-11.xlsx
Home>Business & Finance homework help>Finance Final Project

IS

NIKE Inc (NYS: NKE)
Exchange rate used is that of the Year End reported date
(in thousands except per share values)
1 2 3 4 5 6 7 8 9 10 11
As Reported Annual Income Statement 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
Revenues 14,954,900 16,325,900 18,627,000 19,176,100 19,014,000 20,862,000 24,128,000 25,313,000 27,799,000 30,601,000
Cost of sales 8,367,900 9,165,400 10,239,600 10,571,700 10,213,600 11,354,000 13,657,000 14,279,000 15,353,000 16,534,000
Gross profit 6,587,000 7,160,500 8,387,400 8,604,400 8,800,400 9,508,000 10,471,000 11,034,000 12,446,000 14,067,000
Demand creation expense - - - - - 2,448,000 2,711,000 2,745,000 3,031,000 3,213,000
Operating overhead expense - - - - - 4,245,000 4,720,000 5,035,000 5,735,000 6,679,000
Total selling & administrative expense 4,477,800 5,028,700 5,953,700 6,149,600 6,326,400 6,693,000 7,431,000 7,780,000 8,766,000 9,892,000
Restructuring charges - - - 195,000 - - - - - -
Goodwill impairment - - - 199,300 - - - - - -
Intangible & other asset impairment - - - 202,000 - - - - - -
Interest income (expense), net 36,800 67,200 77,100 9,500 (6,300) (4,000) (3,000) 3,000 (33,000) (28,000)
Other expense (income), net 4,400 (900) 7,900 (88,500) (49,200) (33,000) 54,000 (15,000) 103,000 (58,000)
Income (loss) before income taxes - United States 838,600 805,100 713,000 845,700 698,600 1,084,000 792,000 1,240,000 3,066,000 1,967,000
Income (loss) before income taxes - foreign 1,303,000 1,394,800 1,789,900 1,110,800 1,818,300 1,760,000 2,191,000 2,032,000 478,000 2,238,000
Income (loss) before income taxes 2,141,600 2,199,900 2,502,900 1,956,500 2,516,900 2,844,000 2,983,000 3,272,000 3,544,000 4,205,000
Current provision (benefit) for income taxes - federal 359,000 352,600 469,900 410,100 200,200 289,000 274,000 434,000 371,000 596,000
Current provision (benefit) for income taxes - state 60,600 59,600 58,400 46,100 50,000 57,000 51,000 69,000 93,000 80,000
Current provision (benefit) for income taxes - foreign 356,000 261,900 391,800 307,700 348,500 441,000 495,000 398,000 398,000 369,000
Total current provision (benefit) for income taxes 775,600 674,100 920,100 763,900 598,700 787,000 820,000 901,000 862,000 1,045,000
Deferred provision (benefit) for income taxes - federal (4,200) 38,700 (273,000) (251,400) 17,700 (61,000) (54,000) 1,000 8,000 (66,000)
Deferred provision (benefit) for income taxes - state (6,800) (4,800) (5,000) (7,900) (1,100) - 4,000 (4,000) (3,000) (11,000)
Deferred provision (benefit) for income tax - foreign (15,000) 400 (22,600) (34,800) (5,100) (15,000) (10,000) (90,000) (16,000) (36,000)
Total deferred provision (benefit) for income taxes (26,000) 34,300 (300,600) (294,100) 11,500 (76,000) (60,000) (93,000) (11,000) (113,000)
Income tax expense (benefit) 749,600 708,400 619,500 469,800 610,200 711,000 760,000 808,000 851,000 932,000
Net income from continuing operations ` - - - - - - 2,464,000 2,693,000 3,273,000
Income (loss) from discontinued operations - - - - - - - 21,000 - -
Net income (loss) 1,392,000 1,491,500 1,883,400 1,486,700 1,906,700 2,133,000 2,223,000 2,485,000 2,693,000 3,273,000
Weighted average shares outstanding - basic 1,036,000 1,007,600 991,200 969,800 971,000 951,000 920,000 897,300 883,400 861,700
Weighted average shares outstanding - diluted 1,055,200 1,019,800 1,008,200 981,400 987,800 971,400 939,600 916,400 905,800 884,400
Year end shares outstanding 1,024,000 1,003,400 982,200 971,000 968,000 936,000 916,000 894,000 870,000 857,000
Income (loss) per common share - continuing operations - basic 1 - - - - - - 3 3 4
Income (loss) per share - discontinued operations - basic - - - - - - - 0 - -
Net income (loss) per common share - basic 1 1 2 2 2 2 2 3 3 4
Income (loss) per common share - continuing operations - diluted 1 - - - - - - 3 3 4
Income (loss) per share - discontinued operations - diluted - - - - - - - 0 - -
Net income (loss) per common share - diluted 1 1 2 2 2 2 2 3 3 4
Dividends declared per common share 0 0 0 0 1 1 1 1 1 1
Total number of employees 28,000 30,200 32,500 34,300 34,400 38,000 44,000 48,000 56,500 62,600
Class A common stockholders 18 23 21 16 19 19 18 19 21 20
Class B common stockholders 19,348 19,621 16,542 20,672 20,452 16,400 19,740 30,586 32,758 23,347
Number of stockholders 19,366 19,644 - - - - - - - -
Foreign currency translation adjustments - - - - - - - - - (20,000)

BS

NIKE Inc (NYS: NKE)
Indicates accounts that comprise Capital
Exchange rate used is that of the Year End reported date
(in thousands except per share values)
1 2 3 4 5 6 7 8 9 10 11
As Reported Annual Balance Sheet 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
Cash & equivalents 954,200 1,856,700 2,133,900 2,291,100 3,079,100 1,955,000 2,317,000 3,337,000 2,220,000 3,852,000
Short-term investments 1,348,800 990,300 642,200 1,164,000 2,066,800 2,583,000 1,440,000 2,628,000 2,922,000 2,072,000
Accounts receivable, gross 2,463,500 2,566,200 2,873,700 2,994,700 2,766,500 3,262,000 3,375,000 3,221,000 3,512,000 3,436,000
Less: allowance for uncollectible accounts receivable 67,600 71,500 78,400 110,800 116,700 124,000 95,000 104,000 78,000 78,000
Accounts receivable, net 2,395,900 2,494,700 2,795,300 2,883,900 2,649,800 3,138,000 3,280,000 3,117,000 3,434,000 3,358,000
Finished goods - 2,121,900 2,438,400 2,357,000 2,040,800 2,715,000 3,350,000 3,434,000 3,947,000 4,337,000
Inventories 2,076,700 2,121,900 2,438,400 2,357,000 2,040,800 2,715,000 3,350,000 3,434,000 3,947,000 4,337,000
Deferred income taxes 203,300 219,700 227,200 272,400 248,800 312,000 274,000 308,000 355,000 389,000
Prepaid expenses & other current assets 380,100 393,200 602,300 765,600 873,900 594,000 870,000 802,000 818,000 1,968,000
Total current assets 7,359,000 8,076,500 8,839,300 9,734,000 10,959,200 11,297,000 11,531,000 13,626,000 13,696,000 15,976,000
Land 195,900 193,800 209,400 221,600 222,800 237,000 252,000 268,000 270,000 273,000
Buildings 842,600 840,900 934,600 974,000 951,900 1,124,000 1,158,000 1,174,000 1,261,000 1,250,000
Machinery & equipment 1,661,700 1,817,200 2,005,000 2,094,300 2,217,500 2,487,000 - - - -
Machinery, equipment & internal-use software - - - - - - 2,755,000 2,985,000 3,376,000 3,329,000
Leasehold improvements 626,700 672,800 757,300 802,000 820,600 931,000 968,000 945,000 1,066,000 1,150,000
Construction in process 81,400 94,400 196,700 163,800 177,000 127,000 111,000 128,000 247,000 350,000
Total property, plant & equipment, gross 3,408,300 3,619,100 4,103,000 4,255,700 4,389,800 4,906,000 5,244,000 5,500,000 6,220,000 6,352,000
Less: accumulated depreciation 1,750,600 1,940,800 2,211,900 2,298,000 2,457,900 2,791,000 2,965,000 3,048,000 3,386,000 3,341,000
Property, plant & equipment, net 1,657,700 1,678,300 1,891,100 1,957,700 1,931,900 2,115,000 2,279,000 2,452,000 2,834,000 3,011,000
Identifiable intangible assets, net 405,500 409,900 743,100 467,400 467,000 487,000 535,000 382,000 282,000 281,000
Goodwill 130,800 130,800 448,800 193,500 187,600 205,000 201,000 131,000 131,000 131,000
Deferred income taxes & other assets 316,600 392,800 520,400 897,000 873,600 894,000 919,000 993,000 1,651,000 2,201,000
Total assets 9,869,600 10,688,300 12,442,700 13,249,600 14,419,300 14,998,000 15,465,000 17,584,000 18,594,000 21,600,000
Current portion of long-term debt 255,300 30,500 6,300 32,000 7,400 200,000 49,000 57,000 7,000 107,000
Notes payable 43,400 100,800 177,700 342,900 138,600 187,000 108,000 121,000 167,000 74,000
Accounts payable 952,200 1,040,300 1,287,600 1,031,900 1,254,500 1,469,000 1,588,000 1,646,000 1,930,000 2,131,000
Accrued compensation & benefits, excluding taxes 427,200 451,600 538,000 491,900 598,800 628,000 711,000 713,000 782,000 997,000
Accrual collateral received from counterparties to hedging instruments - - - - - - - - - 968,000
Accrued endorsement compensation 124,700 139,900 203,500 237,100 266,900 284,000 294,000 264,000 328,000 388,000
Accrued dividends payable 79,500 92,900 112,900 121,400 130,700 145,000 165,000 188,000 209,000 240,000
Accrued import & logistics costs - 81,400 78,800 59,400 80,000 98,000 133,000 111,000 127,000 207,000
Accrud taxes other than income taxes 115,100 133,400 147,600 161,900 157,900 214,000 179,000 192,000 204,000 174,000
Accrued fair value of derivatives 111,200 90,500 173,300 68,900 163,600 186,000 55,000 34,000 85,000 162,000
Accrued advertising & marketing 75,400 70,600 121,400 97,600 124,900 139,000 132,000 77,000 133,000 117,000
Accrued restructuring charges - - - 149,600 8,200 - - - - -
Converse arbitration 51,900 - - - - - - - - -
Other accrued liabilities 301,900 243,100 386,400 396,100 373,400 291,000 384,000 407,000 623,000 698,000
Accrued liabilities 1,286,900 1,303,400 1,761,900 1,783,900 1,904,400 1,985,000 2,053,000 1,986,000 2,491,000 3,951,000
Income taxes payable 85,500 109,000 88,000 86,300 59,300 117,000 67,000 98,000 432,000 71,000
Liabilities of discontinued operations - - - - - - - 18,000 - -
Total current liabilities 2,623,300 2,584,000 3,321,500 3,277,000 3,364,200 3,958,000 3,865,000 3,926,000 5,027,000 6,334,000
Corporate bond payables 485,800 238,800 221,500 229,800 205,500 206,000 205,000 1,159,000 1,106,000 1,099,000
Promissory notes - - - - - - - 59,000 58,000 58,000
Japanese Yen notes 180,100 201,600 225,900 239,400 247,700 270,000 72,000 49,000 42,000 29,000
Other long-term debt 100 - - - - - - - - -
Total long-term debt 666,000 440,400 447,400 469,200 453,200 476,000 277,000 1,267,000 1,206,000 1,186,000
Less: current maturities 255,300 30,500 6,300 32,000 7,400 200,000 49,000 57,000 7,000 107,000
Long-term debt 410,700 409,900 441,100 437,200 445,800 276,000 228,000 1,210,000 1,199,000 1,079,000
Deferred income taxes & other liabilities 550,100 668,700 854,500 842,000 855,300 921,000 991,000 1,292,000 1,544,000 1,480,000
Redeemable preferred stock 300 300 300 300 300 - - - - -
Class A convertible common stock 100 100 100 100 100 - - - - -
Class B common stock 2,700 2,700 2,700 2,700 2,700 3,000 3,000 3,000 3,000 3,000
Capital in excess of stated value 1,451,400 1,960,000 2,497,800 2,871,400 3,440,600 3,944,000 4,641,000 5,184,000 5,865,000 6,773,000
Unearned stock compensation 4,100 - - - - - - - - -
Foreign currency translation adjustment - - - - - - - - 9,000 (11,000)
Cumulative translation adjustment & other comprehensive income (loss) 161,900 234,300 356,400 64,600 (94,600) 168,000 (127,000) (14,000) - -
Cash flow hedges (40,200) (56,900) (105,000) 240,400 202,100 (123,000) 181,000 193,000 32,000 1,220,000
Net investment hedges - - - 62,500 107,300 50,000 95,000 95,000 95,000 95,000
Other accumulated other comprehensive income - - - - - - - - (51,000) (58,000)
Accumulated other comprehensive income (loss) 121,700 177,400 251,400 367,500 214,800 95,000 149,000 274,000 85,000 1,246,000
Retained earnings 4,713,400 4,885,200 5,073,300 5,451,400 6,095,500 5,801,000 5,588,000 5,695,000 4,871,000 4,685,000
Total shareholders' equity 6,285,200 7,025,400 7,825,300 8,693,100 9,753,700 9,843,000 10,381,000 11,156,000 10,824,000 12,707,000

SCFs

NIKE Inc (NYS: NKE)
Exchange rate used is that of the Year End reported date
(in thousands except per share values)
As Reported Annual Cash Flow 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
Net income (loss) 1,392,000 1,491,500 1,883,400 1,486,700 1,906,700 2,133,000 2,223,000 2,485,000 2,693,000 3,273,000
Depreciation 282,000 269,700 303,600 335,000 323,700 335,000 373,000 438,000 518,000 606,000
Deferred income taxes (26,000) 34,100 (300,600) (294,100) 8,300 (76,000) (60,000) 21,000 (11,000) (113,000)
Stock-based compensation - 147,700 141,000 170,600 159,000 105,000 130,000 174,000 177,000 191,000
Impairment of goodwill, intangibles & other assets - - - 401,300 - - - - - -
Amortization & other - - - - - - - - - 43,000
Net foreign currency adjustments - - - - - - - - - 424,000
Amortization & other income charges (credits) not affecting cash 8,900 500 17,900 48,300 71,800 23,000 32,000 75,000 114,000 -
Net loss (gain) on divestitures - - (60,600) - - - - (124,000) - -
Income tax benefit from exercise of stock options 54,200 - - - - - - - - -
Accounts receivable (85,100) (39,600) (118,300) (238,000) 181,700 (273,000) (323,000) 142,000 (298,000) (216,000)
Inventories (200,300) (49,500) (249,800) 32,200 284,600 (551,000) (805,000) (197,000) (505,000) (621,000)
Prepaid expenses & other current assets (37,200) (60,800) (11,200) 14,100 (69,600) (35,000) (141,000) (28,000) (210,000) (144,000)
Accounts payable, accrued liabilities & income taxes payable 279,400 85,100 330,900 (220,000) 298,000 151,000 470,000 41,000 525,000 1,237,000
Net cash flows from operating activities 1,667,900 1,878,700 1,936,300 1,736,100 3,164,200 1,812,000 1,899,000 3,027,000 3,003,000 4,680,000
Purchases of short-term investments (2,619,700) (2,133,800) (1,865,600) (2,908,700) (3,724,400) (7,616,000) (2,705,000) (3,702,000) (5,386,000) (4,936,000)
Maturities of short-term investments 1,709,800 2,516,200 2,246,000 2,390,000 2,787,600 4,313,000 2,585,000 1,501,000 3,932,000 3,655,000
Sales of short-term investments - - - - - 2,766,000 1,244,000 998,000 1,126,000 2,216,000
Investments in reverse repurchase agreements - - - - - - - - - (150,000)
Additions to property, plant & equipment (333,700) (313,500) (449,200) (455,700) (335,100) (432,000) (597,000) (636,000) (880,000) (963,000)
Disposals of property, plant & equipment 1,600 28,300 1,900 32,000 10,100 1,000 2,000 14,000 3,000 3,000
Proceeds from divestitures - - 246,000 - - - - 786,000 - -
Increase in other assets, net of other liablities (30,300) (4,300) (21,800) (47,000) (11,200) (30,000) (37,000) (28,000) (2,000) -
Settlement of net investment hedges - - - 191,300 5,500 (23,000) 22,000 - - -
Acquisition of subsidiary, net assets acquired - - (571,100) - - - - - - -
Increase (decrease) in other liabilities (4,300) - - - - - - - - -
Net cash flows from investing activities (1,276,600) 92,900 (413,800) (798,100) (1,267,500) (1,021,000) 514,000 (1,067,000) (1,207,000) (175,000)
Net proceeds from long-term debt issuance - 41,800 - - - - - 986,000 - -
Long-term debt payments, including current portion (6,000) (255,700) (35,200) (6,800) (32,200) (8,000) (203,000) (49,000) (60,000) (7,000)
Increase (decrease) in notes payable (18,200) 52,600 63,700 177,100 (205,400) 41,000 (65,000) 15,000 75,000 (63,000)
Payments on capital lease obligations - - - - - - - - (17,000) (19,000)
Proceeds from exercise of stock options & other stock issuances 225,300 322,900 343,300 186,600 364,500 345,000 468,000 313,000 383,000 514,000
Excess tax benefits from share-based payment arrangements - 55,800 63,000 25,100 58,500 64,000 115,000 72,000 132,000 218,000
Issuance (repurchase) of common stock (761,100) (985,200) (1,248,000) (649,200) (741,200) (1,859,000) (1,814,000) (1,674,000) (2,628,000) (2,534,000)
Dividends - common & preferred (290,900) (343,700) (412,900) (466,700) (505,400) (555,000) (619,000) (703,000) (799,000) (899,000)
Net cash flows from financing activities (850,900) (1,111,500) (1,226,100) (733,900) (1,061,200) (1,972,000) (2,118,000) (1,040,000) (2,914,000) (2,790,000)
Effect of exchange rate changes on cash & equivalents 25,700 42,400 (19,200) (46,900) (47,500) 57,000 67,000 100,000 1,000 (83,000)
Net increase (decrease) in cash & equivalents (433,900) 902,500 277,200 157,200 788,000 (1,124,000) 362,000 1,020,000 (1,117,000) 1,632,000
Cash & equivalents, beginning of year 1,388,100 954,200 1,856,700 2,133,900 2,291,100 3,079,000 1,955,000 2,317,000 3,337,000 2,220,000
Cash & equivalents, end of year 954,200 1,856,700 2,133,900 2,291,100 3,079,100 1,955,000 2,317,000 3,337,000 2,220,000 3,852,000
Cash paid during the year for interest, net of capitalized interest 54,200 60,000 44,100 46,700 48,400 32,000 29,000 20,000 53,000 53,000
Cash paid during the year for income taxes 752,600 601,100 717,500 765,200 537,200 736,000 638,000 702,000 856,000 1,262,000

Past Performance Analysis

2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
YoY revenue growth --> 9.2% 14.1% 2.9% -0.8% 9.7% 15.7% 4.9% 9.8% 10.1%
To calculate GEOMEAN 1 109.2% 114.1% 102.9% 99.2% 109.7% 115.7% 104.9% 109.8% 110.1%
Revenue growth A.M. (07'-15') 8.39%
Revenue growth G.M. (07'-15') 8.28%
Sales to Capital

Beta & CAPM-ER

***Please check this file has three other sheets for Work and the sheet with initial Assumptions (Five Sheets total)***
****You can assume the Risk-Free is 2% and MRP is 5% or you can calculate both parameters if you wish to do so. (MRP = Expected Return of the Market - RiskFree Rate) (Rf you can use 10Yr-TreasuryBond)
****You can get the price data below from Yahoo Finance:
Yahoo Finance -> Historical Data Covariance of Stock and Market 13.0345572225 *You can use Excel Formula "COVARIANCE.P"
Frequency -> Monthly Variance of Market 16.6863021239 *You can use Excel Formula "VAR.P"
Time Period: December 31st, 2013 to December 31st, 2016 Stock Beta 0.7811531354 Beta = CovarianceStock,Market / VarianceMarket
Download Data CAPM E[R] 5.91% E[R] = Rf + Beta*(Market Risk Premium)
Must choose ***Adj Close
(P1-P0)/P0 or P1/P0 - 1
Stock Price Market "Price" Stock Return Market Return Adj Close Open High Low Close Volume
1/1/17 28.520 30.610 0.0733 0.0733 28.520 28.950 30.610 28.690 30.340 2,252,488,000
12/1/16 27.220 29.505 0.0839 0.0839 27.220 27.593 29.505 27.063 28.955 2,435,086,800
11/1/16 25.842 28.442 0.1006 0.1006 25.842 28.365 28.442 26.020 27.630 2,886,220,000
10/1/16 26.548 29.673 0.1177 0.1177 26.548 28.178 29.673 28.070 28.385 2,747,657,200
9/1/16 26.433 29.045 0.0988 0.0988 26.433 26.535 29.045 25.633 28.263 3,872,062,400
8/1/16 24.675 27.558 0.1168 0.1168 24.675 26.103 27.558 26.000 26.525 2,520,514,000
7/1/16 24.235 26.138 0.0785 0.0785 24.235 23.872 26.138 23.593 26.053 2,743,118,400
6/1/16 22.233 25.473 0.1457 0.1457 22.233 24.755 25.473 22.875 23.900 3,117,990,800
5/1/16 23.083 25.183 0.0910 0.0910 23.083 23.493 25.183 22.368 24.965 3,602,686,000
4/1/16 21.668 28.098 0.2967 0.2967 21.668 27.195 28.098 23.128 23.435 3,489,534,800
3/1/16 25.193 27.605 0.0957 0.0957 25.193 24.413 27.605 24.355 27.247 2,984,198,400
2/1/16 22.230 24.723 0.1121 0.1121 22.230 24.118 24.723 23.147 24.173 3,243,450,400
1/1/16 22.379 26.463 0.1825 0.1825 22.379 25.653 26.463 23.098 24.335 5,087,392,000
12/1/15 24.200 29.965 0.2382 0.2382 24.200 29.688 29.965 26.205 26.315 3,687,660,800
11/1/15 27.082 30.955 0.1430 0.1430 27.082 30.200 30.955 27.750 29.575 3,002,562,400
10/1/15 27.357 30.305 0.1078 0.1078 27.357 27.268 30.305 26.827 29.875 4,450,048,400
9/1/15 25.251 29.223 0.1573 0.1573 25.251 27.538 29.223 26.840 27.575 4,826,189,200
8/1/15 25.697 30.643 0.1924 0.1924 25.697 30.375 30.643 23.000 28.190 6,427,276,400
7/1/15 27.644 33.243 0.2025 0.2025 27.644 31.725 33.243 29.805 30.325 4,233,122,400
6/1/15 28.585 32.848 0.1491 0.1491 28.585 32.570 32.848 31.120 31.358 3,514,426,800
5/1/15 29.567 33.243 0.1243 0.1243 29.567 31.525 33.243 30.840 32.570 3,816,608,400
4/1/15 28.402 33.635 0.1842 0.1842 28.402 31.205 33.635 30.775 31.288 3,984,542,000
3/1/15 28.239 32.570 0.1534 0.1534 28.239 32.313 32.570 30.407 31.108 4,554,568,400
2/1/15 29.039 33.400 0.1502 0.1502 29.039 29.513 33.400 29.020 32.115 4,546,140,800
1/1/15 26.485 30.000 0.1327 0.1327 26.485 27.848 30.000 26.157 29.290 5,221,053,600
12/1/14 24.952 29.813 0.1948 0.1948 24.952 29.702 29.813 26.565 27.595 4,294,378,400
11/1/14 26.769 29.938 0.1184 0.1184 26.769 27.055 29.938 26.930 29.733 3,281,632,800
10/1/14 24.308 27.010 0.1111 0.1111 24.308 25.147 27.010 23.795 27.000 5,441,120,800
9/1/14 22.677 25.935 0.1437 0.1437 22.677 25.765 25.935 24.035 25.188 6,105,680,000
8/1/14 22.956 25.725 0.1206 0.1206 22.956 23.725 25.725 23.320 25.625 3,748,308,000
7/1/14 21.411 24.860 0.1611 0.1611 21.411 23.380 24.860 23.143 23.900 4,140,344,000
6/1/14 20.813 23.763 0.1417 0.1417 20.813 22.641 23.763 22.232 23.233 4,827,739,200
5/1/14 20.140 23.006 0.1423 0.1423 20.140 21.143 23.006 20.726 22.607 5,735,668,400
4/1/14 18.775 21.408 0.1403 0.1403 18.775 19.206 21.408 18.262 21.075 6,435,060,800
3/1/14 17.078 19.607 0.1481 0.1481 17.078 18.694 19.607 18.672 19.169 5,001,698,800
2/1/14 16.644 19.685 0.1827 0.1827 16.644 17.950 19.685 17.832 18.794 5,880,366,800
1/1/14 15.833 20.007 0.2636 0.2636 15.833 19.846 20.007 17.627 17.879 8,765,954,400

DDM Val Method

***Please check this file has three other sheets for Work and the sheet with initial Assumptions (Five Sheets total)***
**Remember the discount rate used in DDM is the expected return of investors
**Hint to get supernomal growth rate? What is the average of previous years growth rate of dividends?
**According to your qualitative research. How long do you think this business cycle will last?
This time, D1 will be D0 (Dividend of 2016) * (1 = g.supernormal)
Dividends
TICKER 2012 2013 2014 2015 2016 D1 … Date Dividends
AAPL 5.3000 11.8000 7.2800 2.0300 2.2300 -19% … 8/9/12 2.65
11/7/12 2.65
2/7/13 2.65
5/9/13 3.05
8/8/13 3.05
11/6/13 3.05
2/6/14 3.05
5/8/14 3.29
8/7/14 0.47
11/6/14 0.47
2/5/15 0.47
5/7/15 0.52
8/6/15 0.52
11/5/15 0.52
2/4/16 0.52
5/5/16 0.57
8/4/16 0.57
11/3/16 0.57

PE Val Method

***Please check this file has three other sheets for Work and the sheet with initial Assumptions (Five Sheets total)***
You can get this EPS1 from your DCF (NOPAT - Interest Expense for 2017)
***Average P/E ratio must also consider industry-peers ratios. It is also ok for this project if you assume EPS2016 *(1+SuperNormalGrowthRate)
EPS PE Ratios of Industry-Peers (Average of at least 3 firms) EPS1 Average PE Ratio of Industry PE Value
TICKER 2012 2013 2014 2015 2016 TICKER 2012 2013 2014 2015 2016 2017
AAPL 1.58 1.45 1.61 2.31 2.08 AAPL 11.92 10.19 13.1 12.09 10.25
IBM 14.37 14.94 11.90 13.42 12.38 IBM 9.28 8.93 8.57 9.43 7.41
MSFT 2.00 2.58 2.63 1.48 2.56 MSFT 7.62 12.17 11.94 16.52 34.44
HPQ -6.41 2.62 2.62 2.48 1.43 HPQ 3.33 0 3.83 5.09 3.88

Assumptions

WACC Estimation ***If Highlighted in Yellow: Needs your Input Manually
Equity Ratio 0.3987
Debt Ratio 0.2345
Effective Tax Rate 40.0% *You can also look it up online and change for more accuracy
Average Cost of Debt (Average PreTax) 2.00% *You can also look it up online and change for more accuracy
WACC 2.64%
Cost of Equity Estimation
Risk Free Rate 2.00%
Market Risk Premium 5.00%
Beta (Coming from sheet "Beta & CAPM-ER") 0.78
Cost of Equity Estimation (CAPM E[R]) 6%
Proformas Estimation
COGS to Sales 61.98% *What is 2016 COGS-to-Sales?
Projected Depeciation Rate 5.0% *What is 2016 depreciation rate (depreciation expense / fixed assets for simplicity)?
*Expense to Sales (except depreciation) 20.00% *What is 2016 Expense-to-Sales (exclude depreciation)?
Growth of Fixed Assets 16.10% *What has been the growth rate of fixed assets (use total)? (i.e. compare Fixed Assets of 2015 and 2016)
Growth of Capital Expenditures 6.90% *What has been the growth rate of Capital Expenditures (use total)? (i.e. compare Fixed Assets of 2015 and 2016)
Number of Shares of the firm 16,865 *As of today
Super Normal Growth Rate 7% *Assume a super normal growth rate and provide your arguments in the qualitative portion of the project
Long-Term Growth rate 10.50% *Assume a long-term growth rate and provide your arguments in the qualitative portion of the project

Assumptions

DCF Valuation

Free Cash Flows and Valuation Summary
***This sheet is intended to provide you with an idea of valuation using DCF. Several of the parameters are assumed and/or fixed-through-time for simplicity
***Complete 2017 with data from Financial Statements of your Firm. 2018 will be filled automatically after you have input parameters in "Assumptions" sheet.
***Terminal Value (in 2021) should be calculated automatically after you have chosen the relevant parameters as well.
Fixed Assets (You will need this field to calculate depreciation expense) 4,000.00 4,200.00 4,410.00 4,630.50 4,862.03
T0 (2017) T1 (2018) T2 (2019) T3 (2020) T4 (2021)
Revenues 229,234.00 245,968.08 263,923.75 283,190.19 303,863.07
- Cost of Goods Sold 141,048.00 152,451.02 163,579.94 175,521.28 188,334.33
= Gross Margin 88,186.00 93,517.06 100,343.81 107,668.91 115,528.74
- Depreciation & Amortization 11,581.00 210.00 220.50 231.53 243.10
- All Other Expenses except Interest (for simplicity ) 15,261.00 49,193.62 52,784.75 56,638.04 60,772.61
= EBIT: 61,344.00 44,113.45 47,338.56 50,799.35 54,513.02
- Taxes (24,537.60) (17,645.38) (18,935.42) (20,319.74) (21,805.21)
NOPAT 36,806 26,468 28,403 30,480 32,708
+ Depreciation & Amortization 11,581 210 221 232 243
- Increases to Working Capital (you can assume 0 for this project) - 0 - 0
- CAPEX 15,261 16,314 17,440 18,643 19,929
Free Cash Flows $ 63,648 $ 42,992 $ 46,063 $ 49,354 $ 52,880
Terminal Value $ (743,043.51)
Total $ 63,648 $ 42,992 $ 46,063 $ 49,354 $ (690,163)
Year 0 1 2 3 4
Present Value of Free Cash Flows $ 41,887.90 $ 43,727.58 $ 45,648.25 $ (621,945.67)
References:
PV of FCFs and Terminal Value $ (490,681.94) https://www.sec.gov/Archives/edgar/data/320193/000032019317000070/a10-k20179302017.htm
Add Back Initial Cash $ 1,000.00
Firm's Value $ (489,681.94)
Subtract year 0 Debt $ (2,000.00)
Equity Value $ (491,681.94)
Number of Outstanding Shares $ 2,000.00
Value per Share $ (245.84)

2 other valuation models

DDM Do is dividend paid on 2015 FY. Estimate D1 to D5 using growth in NE for each year. Use terminal period. Discount using Re
P/E
EVA Present it not as a model but as a measure that indicates that company's value increases overtime. Calculate EVA % (EVA/Capital)

To dos

Chris will provide me with Beta and Risk Free Rate
Fore 2016 to 2020, the annual increase in EBIT margin is due to reductions in COS and Demand creation expense (DCE). 75% COS and 25% DCE
1 create EVA Schedule
3 present I/S, BS and SCFs included in latest 10K in appendix