Need the answers with calculation in excel. highlighted in red?
INCOME STATEMENT Fiscal year Fiscal year end date 2011A 2012A 2013A 2014P 2 015P 2016P 2017P 2018P 9/24/2011 9/29/2012 9/28/2013 9/30/2014 9/30/2015 9/30/2016 9/30/2017 9/30/2018 Revenue Cost of sales (enter as-) Gross Profit Research & development (enter as-) Selling, general & administrative (enter as-) Operating profit (EBIT) Interest income Interest expense (enter as-) Other expense (enter as-> Pretax profit Taxes (enter expense as-> Net income 108,249 (64,431) 43,818 (2,429) (7,599) 33,790 519 0 156,508 170,910 179,242 188,256 200,190 213,811 229,355 (87,846) (106,606) (112,743) (117,848) (125,319) (133,846) (143,576) 68,662 64,304 66,499 70,408 74,871 79,965 85,779 (3,381) (4,475) (5,198) (5,836) (6,206) (6,628) (7,110) (10,040) (10,830) (12,188) (12,801) (13,613) (14,539) (15,596) 55,241 48,999 49,112 51,770 55,052 58,798 63,073 1,088 1,616 0 (136) (566) (324) (324) (324) (324) (324) (324) 55,763 50,155 48,788 51,446 54,728 58,474 62,749 (14,030) (13,118) (12,831) (13,376) (14,229) (15,203) (16,315) 41,733 37,037 35,957 38,070 40,499 43,271 46,434 (104) 34,205 (8,283) 25,922 925 906 870 838 808 781 Basic shares outstanding Impact of dilutive securities Diluted shares outstanding 924 12 937 935 11 945 932 913 877 844 814 787 Basic EPS Diluted EPS 28.05 27.68 44.64 44.15 40.03 39.75 39.67 39.39 43.73 43.42 48.34 47.98 53.55 53.14 59.46 58.98 3760 Growth rates & margins Revenue growth Gross profit as % of sales R&D margin SG&A margin Tax rate 40.5% 2.2% 7.0% 24.2% 44.58% 43.9% 2.2% 6.4% 25.2% 9.20% 37.6% 2.5% 6.3% 26.2% 4.9% 37.1% 2.9% 6.8% 26.3% 5.0% 37.4% 3.1% 6.8% 26.0% 6.3% 37.4% 3.1% 6.8% 26.0% 6.8% 37.4% 3.1% 6.8% 26.0% 7.3% 37.4% 3.1% 6.8% 26.0% EBITDA reconciliation Depreciation & amortization Stock based compensation EBITDA 1,814 1,168 36,772 3,277 1,740 60,258 6,757 2,253 58,009 6,281 2,220 57,613 7,262 2,220 61,252 7,508 2,361 64,920 7,735 2,521 69,054 8,081 2,705 73,858 2011A 2012A 2013A 2014P _2015P 2016P 2017P2018P 9/24/119/29/129/28/139/30/149/30/159/30/169/30/179/30/18 SEGMENTS Fiscal year Fiscal year end date Product iPhone iPad Mac iPod iTunes / Software / Services % growth Accessories % growth Total % growth 45,998 78,632 31,273 97,936.6 104,732 13,168 30,345 31,980 31,536 31,536 21,783 21,433 20,967 18,872 7,4535,615 4,4113 ,2642 ,724 3,373 12,830 16,051 13,261 23,113 37.56 24.5% 20% 20% 4,4745,1455,7066 ,277 7,218 15.0% 10.96 10.0% 15.0% 108,249156,508 170,910 179,242188,256 44.66 9.2% 4.96 5.0% 112,128 31,536 18,174 2,316 27,736 20% 8,301 15.0% 200,190 6.39%| 119,977 128,375 31,536 31,536 17.501 16.854 1,968 1.673 33,283 33,940 206 20% 9,546 10,378 15.0% 15.0% 213,811229,355 6.8% 7.3% 32 Units (in the 10-K are in thousands, we converted to millions) iPhone % growth iPad % growth Mac % growth iPod % growth 125150 168180 73.0% 20.296 11.896 796 5 71 72 72 80.0% 21.8% 0.96 0.0% 18 161615 3.5% (10.0% (2.56 (3.7% (3,796 35 26 (17.5%) (25.0% (25.0% (15.0% 192 206220 76 76 76 72 72 72 0.0% 0.0% 0.0% 151414 (3.7% (3.7% (3.76 14 10 (15.09% (15.0%) (15.0% 17 43 17 12 ASPS iPhone % growth iPad % growth Mac 9% growth iPod % growth - 440 636623 607 583 583 (1.1% (3.5% (4.0% 0.0% 592 531 450 440 (10.3% (15.2% (2.3%) 0.0% 1302 1273131513161230 (1.89% 2.8% 0.19% (6.5%) 175 160 167 165 162 [8.7% 4,796 (1.3% (1.86 583 0.09%5 440 0.0% 1230 0.0% 162 0.09% 583 0.0% 440 0.0% 1230 0.0% 162 0.09% 583 0.0% 440 0.0% 1230 0.0% 162 0.0% BALANCE SHEET Fiscal year Fiscal year end date Cash & equivalents ST & LT market. securities Accounts receivable Inventory Deferred tax assets Other current assets (inc. non-trade receivables) Property, plant & equipment Acquired intangible assets (inc. Goodwill) Other assets Total assets 2012 9/29/12 121,251 10,930 791 2,583 14,220 15,452 5,359 5,478 176,064 2013 9/28/13 146,761 13,102 1,764 3,453 14,421 16,597 5,756 5,146 207,000 2014P 9/30/14 168,151 11,442 2,364 3,453 14,421 20,348 4,706 5,146 230,031 2015P 9/30/15 172,322 12,017 2,471 3,453 14,421 24,849 3,721 5,146 238,400 2016P 9/30/16 178,635 12,779 2,627 3,453 14,421 29,636 2 ,888 5,146 249,585 2017P 9/30/17 186,880 13,649 2,806 3,453 14,421 34,748 2,282 5,146 263,385 2018P 9/30/18 193,917 14,641 3,010 3,453 14,421 40,232 1,848 5,146 276,669 21,175 20,015 22,367 23,916 25,267 25,094 26,411 26,356 Accounts payable Accrued expenses & def rev. (current & non-current) Revolver Long term debt Other non-current liabilities Total liabilities 16,960 20,208 83,451 28,085 28,027 2 23,960 29,208 109,280 28,960 23,208 102,529 29,996 29,934 > 21,460 32,208 113,598 16,664 57,854 32,177 32,110 2 . 15,460 35,208 114,955 26,460 26,208 105,435 16,422 Common stock/ additional paid in capital Treasury stock Retained earnings / accumulated deficit Other comprehensive income / (loss) Total equity 101,289 499 118,210 19,764 0 104,256 (471) 123,549 2 1,984 (23,968) 129,957 (471) 127,502 24,204 (47,936) 157,169 (471) 132,966 26,565 (71,904) 186,116 (471) 140,306 29,086 31,791 (95,872) (119,840) 217,045 250,234 (471) (471) 149,788 161,714 Balance check 0 0 Ratios Net debt Asset turnover (Revenue / Total assets) Net profit margin Return on assets (ROA) Return on book equity (ROE) (121,251) (129,801) (139,191) (145,862) (154,675) (165,420) (178,457) 0.89 0.83 0.78 0.79 0.80 0.81 0.83 26.7% 21.7% 20.1% 20.2% 20.2% 20.2% 20.2% 23.7% 17.9% 15.6% 16.0% 16.2% 16.4% 16.8% 35.3% 30.0% 28.2% 28.6% 28.9% 28.9% 28.7% WORKING CAPITAL Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Accounts receivable Beginning of period Increases / (decreases) End of period 10,930 2,172 13,102 13,102 (1,660) 11,442 11,442 575 12,017 12,017 762 12,779 12,779 870 13,649 13,649 992 14,641 10,930 AR as % of sales Days sales outstanding (DSO) 7.0% 25 7.7% 28 6.4% 23 6.4% 23 6.4% 23 6.4% 23 6.4% 23 Inventory Beginning of period Increases / (decreases) End of period 791 973 1,764 1,764 600 2,363.59 2,364 107 2,470.61 2,471 157 2,627.23 2,627 179 2,805.99 2,806 204 3,009.99 791 Inventory as % of COGS Inventory turnover 0.9% 111 1.7% 2.1% 6048 | 2.1% 48 2.1% 48 2.1% 48 2.1% 48 Accounts payable Beginning of period Increases / (decreases) End of period 21,175 1,192 22,367 22,367 2,900 25,267 25,267 1,144 26,411 26,411 1,674 28,085 28,085 1,911 29,996 29,996 2,181 32,177 21,175 22.4% AP as % of COGS Days payables outstanding (DPO) 21.0% 77 22.4% 82 22.4% 82 22.4% 82 22.4% 82 88 Accrued expenses & def revenues Beginning of period Increases / (decreases) End of period 20,015 3,901 23,916 23,916 1,178 25,094 25,094 1,262 26,356 26,356 1,671 28,027 28,027 1,907 29,934 29,934 2,176 32,110 20,015 Accrued expenses & def revs as % of sales 12.8% 14.0% 14% 14% 14% 14% 14% INTANGIBLE ASSETS (INC. GOODWILL) Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 2,282 O Beginning of period Plus: Purchases of intangible assets Less: Amortization End of period 5,359 911 (2002) 5,756 5,756 0 (1050) 4,706 4,706 0 (985) 3,721 3,721 0 (833) 2,888 2,888 0 (606) 2,282 (434) 1,848 5,359 0 Purchases of intangible assets (enter as +) Amortization (enter as-> 1,107 (1,042) 911 (2,002) (1,050) 0 (985) 0 (833) (606) (434) PROPERTY, PLANT & EQUIPMENT Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 15,452 Beginning of period Plus: Capital expenditures Less: Depreciation End of period 16,597 8,982 (5,231) 20,348 20,348 10,778 (6,277) 24,849 24,849 11,461 (6,675) 29,636 29,636 12,241 (7,129) 34,748 34,748 13,131 (7,647) 40,232 15,452 16,597 Capex (enter as +) Capital expenditures as % of revenue Depreciation (enter as-> Depreciation as a % of capital expenditures 8,295 5.3% (2,235) 26.9% 8,165 4.8% (4,755) 58.2% 8,982 5.0% (5,231) 58.2% 10,778 5.7% (6,277) 58.2% 11,461 5.7% (6,675) 58.2% 12,241 5.7% (7,129) 58.2% 13,131 5.7% (7,647) 58.2% OTHER ASSETS / LIABILITIES & DEFERRED TAXES Fiscal year Fiscal year end date 2011A 9/24/11 2012 9/29/12 2013 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 14,220 14,421 14,421 14,421 14,421 Other current assets (inc. non-trade receivables) Beginning of period Increases / (decreases) End of period 14,421 0 14,421 0 14,220 14,421 14,421 14,421 14,421 14,421 2,583 3,453 3,453 3,453 3,453 3,453 Deferred tax assets (DTAS) Beginning of period Increases / (decreases) End of period 0 2,583 3,453 3,453 3,453 3,453 3,453 3,453 5,478 5,146 5,146 5,146 5,146 5,146 0 Other assets Beginning of period Increases / (decreases) End of period 5,478 5,146 5146 5146 5146 5146 5146 16,664 23,208 Other non current liabilities Beginning of period Increases / (decreases) End of period 20,208 3,000 23208 3,000 26,208 3,000 29208 29,208 3,000 32208 32,208 3,000 35208 16,664 20,208 26208 DEBT Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Long term debt Beginning of period Additional borrowing / (pay down) PIK accrual End of period 16,960 28,960 26,460 23,960 12,000 (2,500) (2,500) (2,500) 000 0 28,960 26,460 23,960 21,460 21,460 (6,000) 0 15,460 0 16,960 - Interest expense on long term debt Weighted average interest rate 444 1.9% 444 1.6% 444 1.8% 431 1.9% 424 2.3% % of interest expense paid in cash % of interest expense accrues as PIK 100% 0% 100% 0% 100% 0% 100% 0% 100% 0% CAPITAL STOCK Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P2015P 9/30/14 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 16,422 Common stock / APIC Beginning of period Plus: new share issuances Plus: Stock based compensation End of period 19,764 0 2,220 21,984 21,984 0 2,220 1 24,204 24,204 0 2,361 26,565 26,565 0 2,521 29,086 29,086 0 2,705 31,791 16,422 19,764 New share issuance Stock based compensation SBC as % of all operating expenses 0 2,220 1.7% 0 2,220 1.6% 0 2,361 1.6% 0 2,521 1.6% 0 2,705 1.6% Treasury stock Beginning of period Less: Stock repurchases End of period 0 (23,968) (47,936) (23,968) (23,968) (23,968) (23,968) (47,936) (71,904) (71,904) (95,872) (23,968) (23,968) (95,872) (119,840) 0 0 Stock repurchases 23,968 23,968 23,968 23,968 23,968 RETAINED EARNINGS & OCI Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Retained earnings Beginning of period Plus: Net income Less: Common dividends End of period 101,289 37,037 104,256 35,957 (10,256) 129,957 129,957 38,070 (10,859) 157,169 157,169 40,499 (11,551) 186,116 186,116 217,045 43,271 46,434 (12,342) (13,244) 217,045 250,234 101,289 104,256 Net income Dividend payout ratio Common dividends 37,037 28.5% 10,564 35,957 28.5% 10,256 38,070 28.5% 10,859 40,499 28.5% 11,551 43,271 28.5% 12,342 46,434 28.5% 13,244 2,488 Other comprehensive income / (loss) Beginning of period Plus: Income / (loss) End of period 499 (471) (471) (471) (471) 0 (471) (471) 0 (471) 0 499 (471) (471) (471) (471) CASH FLOW STATEMENT Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Net income Depreciation and amortization Stock based compensation Accounts receivable Inventory Accounts payable Accrued expenses & def revenues Other current assets (inc. non-trade receivables) Deferred tax assets (DTAS) Other assets Other non current liabilities Non-cash (PIK) interest Cash from operating activities 35,957 6,281 2,220 1,660 (600) 2,900 1,178 38,070 7,262 2,220 (575) (107) 1,144 1,262 40,499 7,508 2,361 (762) (157) 1,674 1,671 43,271 7,735 2,521 (870) (179) 1,911 1,907 46,434 8,081 2,705 (992) (204) 2,181 2,176 0 0 3,000 3,000 3,000 3,000 3,000 0 52,276 52,596 55,794 59,297 63,380 Capital expenditures Purchases of intangible assets Cash from investing activities (8,982) 0 (8,982) (10,778) (11,461) (12,241) (13,131) 0 0 (10,778) (11,461) (12,241) (13,131) Long term debt Common dividends New share issuances Share repurchases Other comprehensive income / (loss) Revolver Cash from financing activities 12,000 (10,256) 0 (23,968) 0 (2,500) (2,500) (2,500) (10,859) (11,551) (12,342) 0 0 0 (23,968) (23,968) (23,968) 0 0 (37,327) (38,019) (38,810) (6,000) (13,244) 0 (23,968) (22,224) (43,212) Net change in cash during period 21,390 4,171 6,313 8,245 7,037 MODEL PLUG: REVOLVER & CASH Fiscal year Fiscal year end date 300 2012 9/29/12 2013 9/28/13 2014P 9/30/14 2 015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 301 O Revolver Beginning of period Increases / (decreases) End of period O 304 305 O 306 Maximum availability Compliance check 312 Revolver needs analysis Beginning of period Less: Minimum cash desired Equals: Excess cash at BOP Plus: Free cash flows generated during period Equals: Cash available (needed) to paydown (draw from) revolver 314 316 317 Interest rate on revolver Interest expense SEO 319 321 Cash Beginning of period +/- additions End of period 324 325 Interest rate on cash Interest income 328 SHARES OUTSTANDING Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 935 888 853 822 794 Basic shares outstanding Beginning of period + new shares issued - shares repurchased End of period 0 925 0 (38) 888 (28) (34) 853 (31) 822 (26) 768 935 925 794 44.15 Consensus EPS % change in EPS, year-over-year Average share price 39.75 -9.9% $604.86 41.77 5.1% $635.54 45.95 10.0% $699.14 50.55 10.0% $769.10 55.61 10.0% $846.07 61.17 10.0% $930.74 INCOME STATEMENT Fiscal year Fiscal year end date 2011A 2012A 2013A 2014P 2 015P 2016P 2017P 2018P 9/24/2011 9/29/2012 9/28/2013 9/30/2014 9/30/2015 9/30/2016 9/30/2017 9/30/2018 Revenue Cost of sales (enter as-) Gross Profit Research & development (enter as-) Selling, general & administrative (enter as-) Operating profit (EBIT) Interest income Interest expense (enter as-) Other expense (enter as-> Pretax profit Taxes (enter expense as-> Net income 108,249 (64,431) 43,818 (2,429) (7,599) 33,790 519 0 156,508 170,910 179,242 188,256 200,190 213,811 229,355 (87,846) (106,606) (112,743) (117,848) (125,319) (133,846) (143,576) 68,662 64,304 66,499 70,408 74,871 79,965 85,779 (3,381) (4,475) (5,198) (5,836) (6,206) (6,628) (7,110) (10,040) (10,830) (12,188) (12,801) (13,613) (14,539) (15,596) 55,241 48,999 49,112 51,770 55,052 58,798 63,073 1,088 1,616 0 (136) (566) (324) (324) (324) (324) (324) (324) 55,763 50,155 48,788 51,446 54,728 58,474 62,749 (14,030) (13,118) (12,831) (13,376) (14,229) (15,203) (16,315) 41,733 37,037 35,957 38,070 40,499 43,271 46,434 (104) 34,205 (8,283) 25,922 925 906 870 838 808 781 Basic shares outstanding Impact of dilutive securities Diluted shares outstanding 924 12 937 935 11 945 932 913 877 844 814 787 Basic EPS Diluted EPS 28.05 27.68 44.64 44.15 40.03 39.75 39.67 39.39 43.73 43.42 48.34 47.98 53.55 53.14 59.46 58.98 3760 Growth rates & margins Revenue growth Gross profit as % of sales R&D margin SG&A margin Tax rate 40.5% 2.2% 7.0% 24.2% 44.58% 43.9% 2.2% 6.4% 25.2% 9.20% 37.6% 2.5% 6.3% 26.2% 4.9% 37.1% 2.9% 6.8% 26.3% 5.0% 37.4% 3.1% 6.8% 26.0% 6.3% 37.4% 3.1% 6.8% 26.0% 6.8% 37.4% 3.1% 6.8% 26.0% 7.3% 37.4% 3.1% 6.8% 26.0% EBITDA reconciliation Depreciation & amortization Stock based compensation EBITDA 1,814 1,168 36,772 3,277 1,740 60,258 6,757 2,253 58,009 6,281 2,220 57,613 7,262 2,220 61,252 7,508 2,361 64,920 7,735 2,521 69,054 8,081 2,705 73,858 2011A 2012A 2013A 2014P _2015P 2016P 2017P2018P 9/24/119/29/129/28/139/30/149/30/159/30/169/30/179/30/18 SEGMENTS Fiscal year Fiscal year end date Product iPhone iPad Mac iPod iTunes / Software / Services % growth Accessories % growth Total % growth 45,998 78,632 31,273 97,936.6 104,732 13,168 30,345 31,980 31,536 31,536 21,783 21,433 20,967 18,872 7,4535,615 4,4113 ,2642 ,724 3,373 12,830 16,051 13,261 23,113 37.56 24.5% 20% 20% 4,4745,1455,7066 ,277 7,218 15.0% 10.96 10.0% 15.0% 108,249156,508 170,910 179,242188,256 44.66 9.2% 4.96 5.0% 112,128 31,536 18,174 2,316 27,736 20% 8,301 15.0% 200,190 6.39%| 119,977 128,375 31,536 31,536 17.501 16.854 1,968 1.673 33,283 33,940 206 20% 9,546 10,378 15.0% 15.0% 213,811229,355 6.8% 7.3% 32 Units (in the 10-K are in thousands, we converted to millions) iPhone % growth iPad % growth Mac % growth iPod % growth 125150 168180 73.0% 20.296 11.896 796 5 71 72 72 80.0% 21.8% 0.96 0.0% 18 161615 3.5% (10.0% (2.56 (3.7% (3,796 35 26 (17.5%) (25.0% (25.0% (15.0% 192 206220 76 76 76 72 72 72 0.0% 0.0% 0.0% 151414 (3.7% (3.7% (3.76 14 10 (15.09% (15.0%) (15.0% 17 43 17 12 ASPS iPhone % growth iPad % growth Mac 9% growth iPod % growth - 440 636623 607 583 583 (1.1% (3.5% (4.0% 0.0% 592 531 450 440 (10.3% (15.2% (2.3%) 0.0% 1302 1273131513161230 (1.89% 2.8% 0.19% (6.5%) 175 160 167 165 162 [8.7% 4,796 (1.3% (1.86 583 0.09%5 440 0.0% 1230 0.0% 162 0.09% 583 0.0% 440 0.0% 1230 0.0% 162 0.09% 583 0.0% 440 0.0% 1230 0.0% 162 0.0% BALANCE SHEET Fiscal year Fiscal year end date Cash & equivalents ST & LT market. securities Accounts receivable Inventory Deferred tax assets Other current assets (inc. non-trade receivables) Property, plant & equipment Acquired intangible assets (inc. Goodwill) Other assets Total assets 2012 9/29/12 121,251 10,930 791 2,583 14,220 15,452 5,359 5,478 176,064 2013 9/28/13 146,761 13,102 1,764 3,453 14,421 16,597 5,756 5,146 207,000 2014P 9/30/14 168,151 11,442 2,364 3,453 14,421 20,348 4,706 5,146 230,031 2015P 9/30/15 172,322 12,017 2,471 3,453 14,421 24,849 3,721 5,146 238,400 2016P 9/30/16 178,635 12,779 2,627 3,453 14,421 29,636 2 ,888 5,146 249,585 2017P 9/30/17 186,880 13,649 2,806 3,453 14,421 34,748 2,282 5,146 263,385 2018P 9/30/18 193,917 14,641 3,010 3,453 14,421 40,232 1,848 5,146 276,669 21,175 20,015 22,367 23,916 25,267 25,094 26,411 26,356 Accounts payable Accrued expenses & def rev. (current & non-current) Revolver Long term debt Other non-current liabilities Total liabilities 16,960 20,208 83,451 28,085 28,027 2 23,960 29,208 109,280 28,960 23,208 102,529 29,996 29,934 > 21,460 32,208 113,598 16,664 57,854 32,177 32,110 2 . 15,460 35,208 114,955 26,460 26,208 105,435 16,422 Common stock/ additional paid in capital Treasury stock Retained earnings / accumulated deficit Other comprehensive income / (loss) Total equity 101,289 499 118,210 19,764 0 104,256 (471) 123,549 2 1,984 (23,968) 129,957 (471) 127,502 24,204 (47,936) 157,169 (471) 132,966 26,565 (71,904) 186,116 (471) 140,306 29,086 31,791 (95,872) (119,840) 217,045 250,234 (471) (471) 149,788 161,714 Balance check 0 0 Ratios Net debt Asset turnover (Revenue / Total assets) Net profit margin Return on assets (ROA) Return on book equity (ROE) (121,251) (129,801) (139,191) (145,862) (154,675) (165,420) (178,457) 0.89 0.83 0.78 0.79 0.80 0.81 0.83 26.7% 21.7% 20.1% 20.2% 20.2% 20.2% 20.2% 23.7% 17.9% 15.6% 16.0% 16.2% 16.4% 16.8% 35.3% 30.0% 28.2% 28.6% 28.9% 28.9% 28.7% WORKING CAPITAL Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Accounts receivable Beginning of period Increases / (decreases) End of period 10,930 2,172 13,102 13,102 (1,660) 11,442 11,442 575 12,017 12,017 762 12,779 12,779 870 13,649 13,649 992 14,641 10,930 AR as % of sales Days sales outstanding (DSO) 7.0% 25 7.7% 28 6.4% 23 6.4% 23 6.4% 23 6.4% 23 6.4% 23 Inventory Beginning of period Increases / (decreases) End of period 791 973 1,764 1,764 600 2,363.59 2,364 107 2,470.61 2,471 157 2,627.23 2,627 179 2,805.99 2,806 204 3,009.99 791 Inventory as % of COGS Inventory turnover 0.9% 111 1.7% 2.1% 6048 | 2.1% 48 2.1% 48 2.1% 48 2.1% 48 Accounts payable Beginning of period Increases / (decreases) End of period 21,175 1,192 22,367 22,367 2,900 25,267 25,267 1,144 26,411 26,411 1,674 28,085 28,085 1,911 29,996 29,996 2,181 32,177 21,175 22.4% AP as % of COGS Days payables outstanding (DPO) 21.0% 77 22.4% 82 22.4% 82 22.4% 82 22.4% 82 88 Accrued expenses & def revenues Beginning of period Increases / (decreases) End of period 20,015 3,901 23,916 23,916 1,178 25,094 25,094 1,262 26,356 26,356 1,671 28,027 28,027 1,907 29,934 29,934 2,176 32,110 20,015 Accrued expenses & def revs as % of sales 12.8% 14.0% 14% 14% 14% 14% 14% INTANGIBLE ASSETS (INC. GOODWILL) Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 2,282 O Beginning of period Plus: Purchases of intangible assets Less: Amortization End of period 5,359 911 (2002) 5,756 5,756 0 (1050) 4,706 4,706 0 (985) 3,721 3,721 0 (833) 2,888 2,888 0 (606) 2,282 (434) 1,848 5,359 0 Purchases of intangible assets (enter as +) Amortization (enter as-> 1,107 (1,042) 911 (2,002) (1,050) 0 (985) 0 (833) (606) (434) PROPERTY, PLANT & EQUIPMENT Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 15,452 Beginning of period Plus: Capital expenditures Less: Depreciation End of period 16,597 8,982 (5,231) 20,348 20,348 10,778 (6,277) 24,849 24,849 11,461 (6,675) 29,636 29,636 12,241 (7,129) 34,748 34,748 13,131 (7,647) 40,232 15,452 16,597 Capex (enter as +) Capital expenditures as % of revenue Depreciation (enter as-> Depreciation as a % of capital expenditures 8,295 5.3% (2,235) 26.9% 8,165 4.8% (4,755) 58.2% 8,982 5.0% (5,231) 58.2% 10,778 5.7% (6,277) 58.2% 11,461 5.7% (6,675) 58.2% 12,241 5.7% (7,129) 58.2% 13,131 5.7% (7,647) 58.2% OTHER ASSETS / LIABILITIES & DEFERRED TAXES Fiscal year Fiscal year end date 2011A 9/24/11 2012 9/29/12 2013 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 14,220 14,421 14,421 14,421 14,421 Other current assets (inc. non-trade receivables) Beginning of period Increases / (decreases) End of period 14,421 0 14,421 0 14,220 14,421 14,421 14,421 14,421 14,421 2,583 3,453 3,453 3,453 3,453 3,453 Deferred tax assets (DTAS) Beginning of period Increases / (decreases) End of period 0 2,583 3,453 3,453 3,453 3,453 3,453 3,453 5,478 5,146 5,146 5,146 5,146 5,146 0 Other assets Beginning of period Increases / (decreases) End of period 5,478 5,146 5146 5146 5146 5146 5146 16,664 23,208 Other non current liabilities Beginning of period Increases / (decreases) End of period 20,208 3,000 23208 3,000 26,208 3,000 29208 29,208 3,000 32208 32,208 3,000 35208 16,664 20,208 26208 DEBT Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Long term debt Beginning of period Additional borrowing / (pay down) PIK accrual End of period 16,960 28,960 26,460 23,960 12,000 (2,500) (2,500) (2,500) 000 0 28,960 26,460 23,960 21,460 21,460 (6,000) 0 15,460 0 16,960 - Interest expense on long term debt Weighted average interest rate 444 1.9% 444 1.6% 444 1.8% 431 1.9% 424 2.3% % of interest expense paid in cash % of interest expense accrues as PIK 100% 0% 100% 0% 100% 0% 100% 0% 100% 0% CAPITAL STOCK Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P2015P 9/30/14 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 16,422 Common stock / APIC Beginning of period Plus: new share issuances Plus: Stock based compensation End of period 19,764 0 2,220 21,984 21,984 0 2,220 1 24,204 24,204 0 2,361 26,565 26,565 0 2,521 29,086 29,086 0 2,705 31,791 16,422 19,764 New share issuance Stock based compensation SBC as % of all operating expenses 0 2,220 1.7% 0 2,220 1.6% 0 2,361 1.6% 0 2,521 1.6% 0 2,705 1.6% Treasury stock Beginning of period Less: Stock repurchases End of period 0 (23,968) (47,936) (23,968) (23,968) (23,968) (23,968) (47,936) (71,904) (71,904) (95,872) (23,968) (23,968) (95,872) (119,840) 0 0 Stock repurchases 23,968 23,968 23,968 23,968 23,968 RETAINED EARNINGS & OCI Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Retained earnings Beginning of period Plus: Net income Less: Common dividends End of period 101,289 37,037 104,256 35,957 (10,256) 129,957 129,957 38,070 (10,859) 157,169 157,169 40,499 (11,551) 186,116 186,116 217,045 43,271 46,434 (12,342) (13,244) 217,045 250,234 101,289 104,256 Net income Dividend payout ratio Common dividends 37,037 28.5% 10,564 35,957 28.5% 10,256 38,070 28.5% 10,859 40,499 28.5% 11,551 43,271 28.5% 12,342 46,434 28.5% 13,244 2,488 Other comprehensive income / (loss) Beginning of period Plus: Income / (loss) End of period 499 (471) (471) (471) (471) 0 (471) (471) 0 (471) 0 499 (471) (471) (471) (471) CASH FLOW STATEMENT Fiscal year Fiscal year end date 2011A 9/24/11 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 Net income Depreciation and amortization Stock based compensation Accounts receivable Inventory Accounts payable Accrued expenses & def revenues Other current assets (inc. non-trade receivables) Deferred tax assets (DTAS) Other assets Other non current liabilities Non-cash (PIK) interest Cash from operating activities 35,957 6,281 2,220 1,660 (600) 2,900 1,178 38,070 7,262 2,220 (575) (107) 1,144 1,262 40,499 7,508 2,361 (762) (157) 1,674 1,671 43,271 7,735 2,521 (870) (179) 1,911 1,907 46,434 8,081 2,705 (992) (204) 2,181 2,176 0 0 3,000 3,000 3,000 3,000 3,000 0 52,276 52,596 55,794 59,297 63,380 Capital expenditures Purchases of intangible assets Cash from investing activities (8,982) 0 (8,982) (10,778) (11,461) (12,241) (13,131) 0 0 (10,778) (11,461) (12,241) (13,131) Long term debt Common dividends New share issuances Share repurchases Other comprehensive income / (loss) Revolver Cash from financing activities 12,000 (10,256) 0 (23,968) 0 (2,500) (2,500) (2,500) (10,859) (11,551) (12,342) 0 0 0 (23,968) (23,968) (23,968) 0 0 (37,327) (38,019) (38,810) (6,000) (13,244) 0 (23,968) (22,224) (43,212) Net change in cash during period 21,390 4,171 6,313 8,245 7,037 MODEL PLUG: REVOLVER & CASH Fiscal year Fiscal year end date 300 2012 9/29/12 2013 9/28/13 2014P 9/30/14 2 015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 301 O Revolver Beginning of period Increases / (decreases) End of period O 304 305 O 306 Maximum availability Compliance check 312 Revolver needs analysis Beginning of period Less: Minimum cash desired Equals: Excess cash at BOP Plus: Free cash flows generated during period Equals: Cash available (needed) to paydown (draw from) revolver 314 316 317 Interest rate on revolver Interest expense SEO 319 321 Cash Beginning of period +/- additions End of period 324 325 Interest rate on cash Interest income 328 SHARES OUTSTANDING Fiscal year Fiscal year end date 2012A 9/29/12 2013A 9/28/13 2014P 9/30/14 2015P 9/30/15 2016P 9/30/16 2017P 9/30/17 2018P 9/30/18 935 888 853 822 794 Basic shares outstanding Beginning of period + new shares issued - shares repurchased End of period 0 925 0 (38) 888 (28) (34) 853 (31) 822 (26) 768 935 925 794 44.15 Consensus EPS % change in EPS, year-over-year Average share price 39.75 -9.9% $604.86 41.77 5.1% $635.54 45.95 10.0% $699.14 50.55 10.0% $769.10 55.61 10.0% $846.07 61.17 10.0% $930.74
