please estimate FCF located in yellow on the excel sheet.

FS to FCF Template Based on historical financial data, find FCF for the company of your choice. 1. In week 1 you were asked to choose a publicly traded company and submit its name for instructor's approval. Assuming 2. Download annual financial data for the last 5 years from Edgar Website (forms 10-K). Attention: if your company became public less than 5 years ago, you will also have to download data from its IPO filing prospectus (form 424B4). Template below is approximate, you may modify it to suit your company 3. Using approach similar to the one described in this module's Excel file you will have to calculate the FCF for the company of your choice Solution Legend Value given in problem Formula/Calculation/Analysis required Qualitative analysis or Short answer required If you have difficulty estimating company's tax rate you may assume statutory tax rate of 35% ($ in millions) Assets Cash & Short Term Investments Short-term Investments Receivables Inventories Prepaid Expenses Total Current Assets Net Property, Plant & Equipment Total Assets Liabilities Accounts payable Current maturities of long-term debt Income taxes payable Total current liabilities Long-term debt Total Liabilities Total stockholders' equity (deficit) Total Liabilities & Shareholder's Equity Income Statement Revenue Total revenues Expenses Cost of Goods Sold General, administrative and other Depreciation and amortization Total expenses Operating income Interest expense Income before income taxes Income tax benefit (expense) Net income Dividends Paid Non-distributed Income Financial Data 2016 2015 Year 1 Year 2 2014 Year 3 2013 Year 4 2012 Year 5 $8,705 $0 $5,624 $44,469.00 $1,441 $60,239 $110,171 $170,410 $9,135 $0 $6,778 $45,141 $2,224 $61,054 $114,280 $175,334 $7,281 $0 $6,677 $44,858 $1,909 $60,725 $115,364 $176,089 $7,781 $0 $6,768 $43,803 $1,551 $59,903 $113,929 $173,832 $6,550 $0 $5,937 $40,714 $1,774 $54,975 $109,603 $164,578 $38,487 $2,745 $521 $41,753 $38,214 $79,967 $80,546 $160,513 $38,410 $4,791 $1,021 $44,222 $40,889 $85,111 $81,394 $166,505 $37,415 $5,985 $2,362 $45,762 $41,771 $87,533 $76,255 $163,788 $38,080 $6,231 $2,262 $46,573 $38,394 $84,967 $76,343 $161,310 $36,608 $5,332 $2,346 $44,286 $44,070 $88,356 $71,315 $159,671 Year 1 Year 2 Year 3 Year 4 reference (2015&2016): https://www.sec.gov/cgi-bin/viewer?action=view&cik=104169&accession_number=0000104169-16-000079&xbrl_type=v# Year 5 $482,130 $485,651 $476,294 $468,651 $446,509 $360,984 $97,041 $9,454 $467,479 $24,105 $2,467 $21,638 $8,111 $4,073 ($6,294) $365,086 $93,418 $9,173 $467,677 $27,147 $2,348 $24,799 $8,169 $7,457 ($6,185) $358,069 $91,353 $8,870 $458,292 $26,872 $2,216 $24,656 $8,641 $7,145 ($6,139) $352,488 $88,629 $8,478 $449,595 $27,801 $ 2,262 $27,801 $7,999 $8,795 ($5,361) $335,127 $85,025 $8,106 $428,258 $26,491 $ 2,346 $26,491 $6,742 $9,163 ($5,048) Find Changes in NWC Year 1 Current Assets, other than Cash and Marketable Securities Current Liabilities, excluding Debt Net Working Capital (CA-CL) Year 2 $51,534 $38,487 $13,047 Change in NWC (NWC Investment) Year 3 Year 4 Year 5 $51,919 $38,410 $13,509 $53,444 $37,415 $16,029 $52,122 $38,080 $14,042 $48,425 $36,608 $11,817 $462 $2,520 ($1,987) ($2,225) Find CapEx Net Property, Plant & Equipment Depreciation and amortization Capex You can read more using the following link Year 1 $110,171 $9,454 Year 2 Year 3 Year 4 Year 5 $114,280 $115,364 $113,929 $109,603 $9,173 $8,870 $8,478 $8,106 $4,109 $1,084 ($1,435) ($4,326) Howard Keen, Clearing Up Confusion over calculation of FCF. Estimate FCF Tax Rate 35% Year Year 1 EBIT EBIT(1-T) = NOPAT Plus: Depreciation Expense Less: CAPEX Less: Working Capital Investment Firm Free Cash Flow Year 2 $15,668 $9,454 $4,109 $462 $20,551 Year 3 $17,646 $9,173 $1,084 $2,520 $23,215 Year 4 $17,467 $8,870 ($1,435) ($1,987) $29,759 Year 5 $18,071 $8,478 ($4,326) ($2,225) $33,100 Page 1