NYSF_Practice_Template.xlsx

《NYSF_Practice_Template.xlsx》由会员分享，可在线阅读，更多相关《NYSF_Practice_Template.xlsx（22页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Income Statement 1 of 22 ActualsEstimates 2009A2010A2011A2012E2013E2014E2015E2016E2017E Price$20.0$20.0$20.0 Y/Y price growth (%)0%0%0%0%0%0% Volume500.0550.0605.0 Y/Y volume growth (%)10%10%10%10%10%10% Total revenue Y/Y

2、 revenue growth (%) Cost of goods sold2,500.02,750.03,025.0 COGS as a % of revenue Gross profit Gross profit margin (%) Research and development900.01,000.01,050.0 R&D as a % of revenue Selling, general and administrative1,250.01,400.01,550.0 SG&A as a % of revenue Other0.00.00.0 Total operating exp

3、enses Equity in earnings of unconsolidated affiliates0.00.00.0 EBITDA EBITDA margin (%) Depreciation and amortization600.0650.0725.0 EBIT EBIT margin (%) Interest expense70.071.071.0 Interest income(15.0)(22.7)(57.1) Net interest expense The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197

4、be3a06854f.xlsx, Income Statement 2 of 22 ActualsEstimates 2009A2010A2011A2012E2013E2014E2015E2016E2017E Income before tax (IBT) IBT margin (%) Income tax expense1,650.01,800.02,000.0 All-in effective tax rate (%) Net income from continuing ops The Analyst Exchange On - Line Training 09394b6cd7dd672

5、4a96197be3a06854f.xlsx, Income Statement 3 of 22 ActualsEstimates 2009A2010A2011A2012E2013E2014E2015E2016E2017E Discontinued operations0.00.00.0 Extraordinary items0.00.00.0 Effect of accounting changes0.00.00.0 Other items0.00.00.0 Total non-recurring events Net income Income attributable to minori

6、ty interests0.00.00.0 Percent minority interests Dividends paid on common stock Dividends paid on common stock (per share)$0.08$0.08$0.08 Net income after dividends and minority interests Basic Diluted Basic1,000,0001,000,0001,000,000 Diluted1,500,0001,500,0001,500,000 The Analyst Exchange On - Line

7、 Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Income Statement 4 of 22 2018E 0% 10% Estimates The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Income Statement 5 of 22 2018E The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Income Stateme

8、nt 6 of 22 2018E The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Cash Flow Statement 7 of 22 ActualsEstimates 2009A2010A2011A2012E2013E2014E2015E2016E2017E Net income Depreciation and amortization600.0650.0725.0 Amortization of identifiable intangible assets0.00.00.0 D

9、eferred income taxes316.3131.048.0 Changes in operating operating working capital Changes in accounts receivable(125.0)(150.0)(150.0) Changes in inventory(32.0)(5.0)(19.0) Changes in prepaid expenses(9.0)(9.0)0.0 Changes in other current assets0.00.00.0 Changes in accounts payable1.01.02.0 Changes i

10、n accrued salaries and employee benefits5.03.07.0 Changes in other current liabilities0.00.00.0 Net changes in working capital Total cash provided by (used for) operating activities CAPEX Purchase of property, leasehold improvements & equipment(1,000.0)(500.0)(750.0) Proceeds from sale of property,

11、leasehold improvements & equipment0.00.00.0 Business acquisitions net of cash acquired & net of intangibles/other0.00.00.0 Proceeds from sale of businesses spin-offs0.00.00.0 Investments (proceeds) in marketable & non-marketable securities0.00.00.0 Other0.00.00.0 Total cash provided by (used for) in

12、vesting activities Short-term borrowings (repayments)0.00.00.0 Long-term borrowings (repayments)500.00.00.0 Proceeds from issuance of preferred stock, net of issuance costs0.00.00.0 Proceeds from issuance of common stock0.00.00.0 Repurchase of common stock treasury stock0.00.00.0 Common dividends pa

13、id(80.0)(80.0)(80.0) Minority shareholders interest0.00.00.0 Other0.00.00.0 Total cash provided by (used for) financing activities The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Cash Flow Statement 8 of 22 Cash flow before debt paydown The Analyst Exchange On - Line T

14、raining 09394b6cd7dd6724a96197be3a06854f.xlsx, Cash Flow Statement 9 of 22 2018E Estimates The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Cash Flow Statement 10 of 22 The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Balance Sheet 11 of 22

15、 ActualsEstimates 2009A2010A2011A2012E2013E2014E2015E2016E2017E Current assets: Cash and cash equivalents400.03,792.87,311.8 Accounts receivable, net of allowance for doubtful accounts1,200.01,350.01,500.0 Inventory, less allowances for loss269.0274.0293.0 Prepaid expenses120.0129.0129.0 Other curre

16、nt assets0.00.00.0 Total current assets Property, plant and equipment, net5,400.05,250.05,275.0 Goodwill0.00.00.0 Acquired identifiable amortizable intangible assets, net of accum. amort.0.00.00.0 Pension assets53.253.253.2 Deferred tax asset0.00.00.0 Other long-term assets21.021.021.0 Total assets

17、Current liabilities: Accounts payable140.0141.0143.0 Accrued salaries and employee benefits165.0168.0175.0 Short-term debt700.0700.0700.0 Other current liabilities0.00.00.0 Total current liabilities Long-term debt1,000.01,000.01,000.0 Deferred tax liability316.3447.3495.3 Pension and benefit obligat

18、ions72.072.072.0 Other long-term liabilities14.014.014.0 Total liabilities Preferred stock500.0500.0500.0 Common stock par value + additional paid-in-capital2,500.02,500.02,500.0 Less stock repurchased treasury stock(100.0)(100.0)(100.0) Accumulated other comprehensive income (loss)0.00.00.0 Retaine

19、d earnings2,155.95,427.79,083.7 Total stockholder equity The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Balance Sheet 12 of 22 Total liabilities & stockholders equity Balance? (Y/N)YYYYYYYYY The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx

20、, Balance Sheet 13 of 22 2018E Estimates The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Balance Sheet 14 of 22 Y The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Depreciation 15 of 22 Depreciation (in US$ thousands)ActualsEstimates Period

21、 Ending December 312009A2010A2011A2012E2013E2014E2015E2016E2017E Property, plant & equipment on Jan 1 20125,275.0 Capital expenditures as of Jan 1 of each year1,000.0500.0750.01,000.0500.0750.0 Straight line depreciation Years101010101010 Existing PP&E 2012 CAPEX 2013 CAPEX 2014 CAPEX 2015 CAPEX 201

22、6 CAPEX 2017 CAPEX 2018 CAPEX Total book depreciation Accelerated depreciation (%) Existing PP&E25%15%10%10%10%5% 2012 CAPEX25%15%10%10%10%5% 2013 CAPEX25%15%10%10%10% 2014 CAPEX25%15%10%10% 2015 CAPEX25%15%10% 2016 CAPEX25%15% 2017 CAPEX25% 2018 CAPEX Accelerated depreciation Existing PP&E 2012 CAP

23、EX 2013 CAPEX 2014 CAPEX 2015 CAPEX 2016 CAPEX 2017 CAPEX 2018 CAPEX Total tax depreciation Difference in depreciation Tax rate (%) The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Depreciation 16 of 22 Deferred taxes The Analyst Exchange On - Line Training 09394b6cd7dd

24、6724a96197be3a06854f.xlsx, Depreciation 17 of 22 2018E 1,000.0 10 5% 5% 5% 10% 10% 10% 15% 25% Estimates The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Depreciation 18 of 22 The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Operating Worki

25、ng Capital 19 of 22 Operating Working Capital Schedule (OWC) (in US$ thousands)ActualsEstimates On December 312009A2010A2011A2012E2013E2014E2015E2016E2017E Current assets Accounts receivable1,200.01,350.01,500.0 Days receivable45.045.045.045.045.045.0 Inventory269.0274.0293.0 Inventory turnover days

26、35.035.035.035.035.035.0 Prepaid expenses120.0129.0129.0 Days prepaid30.030.030.030.030.030.0 Other current assets0.00.00.0 Total current assets Current liabilities Accounts payable140.0141.0143.0 Days payable20.020.020.020.020.020.0 Accrued salaries and employee benefits165.0168.0175.0 Days payable

27、40.040.040.040.040.040.0 Other current liabilities0.00.00.0 Total current liabilities Total operating working capital Change in total operating working capital The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Operating Working Capital 20 of 22 2018E 45.0 35.0 30.0 20.0

28、40.0 Estimates The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Debt Schedule 21 of 22 Debt Schedule (in US$ thousands)ActualsEstimates Period Ending December 312009A2010A2011A2012E2013E2014E2015E2016E2017E Cash available to pay down debt Cash at beginning of year Cash

29、flow before debt paydown Minimum cash cushion(10.0)(10.0)(10.0)(10.0)(10.0)(10.0) Total cash available to pay down debt Short term debt / revolver Short term debt (beginning of year) Mandatory issuances / (retirements) 0.00.00.00.00.00.0 Non-mandatory issuances / (retirements) Short term debt (end o

30、f year)700.0 Short term interest expense Short term interest rate3%3%3%3%3%3% Long term debt Long term debt (beginning of year) Mandatory issuances / (retirements) 0.00.00.00.00.00.0 Non-mandatory issuances / (retirements) Long term debt (end of year)1,000.0 Long term interest expense Long term interest rate5%5%5%5%5%5% Total issuances / (retirements) Total interest expense Cash at the end of the year7,311.8 Interest income The Analyst Exchange On - Line Training 09394b6cd7dd6724a96197be3a06854f.xlsx, Debt Schedule 22 of 22 2018E (10.0) 0.0 3% 0.0 5% Estimates