Please use the provided template to solve the question and show the formula in working cell.

2-6 COMPREHENSIVE PROJECT FCF The TCM Petroleum Corporation is an integrated oil company headquartered in Fort Worth, Texas. Historical income statements for 2014 and 2015 are found below (dollar figures are in the millions): Sales Cost of goods sold Gross profit Selling, general, and administrative expense Decemer 2015 $ 13,368.00 (10,591.00) 2,777.00 (698.00) December 2014 $12,211.00 (9,755.00) 2,456.00 (704.00) Operating income before depreciation Depreciation, depletion, and amortization Operating profit Interest expense Nonoperating income or expense Special items Pretax income Taxes Net income Decemer 2015 2,079.00 (871.00) 1,208.00 (295.00) 151.00 December 2014 1,752.00 (794.00) 958.00 (265.00) 139.00 20.00 852.00 1,064.00 (425.60) $ 638,40 (340.80) $ 511.20 In 2014, TCM made capital expenditures of $875 million, followed by $1,322 million in 2015. TCM also invested an additional $102 million in net working capital in 2014, fol- lowed by a decrease in its investment in net working capital of $430 million in 2015. a. Calculate TCM's FCFs for 2014 and 2015. TCM's tax rate is 40%. b. Estimate TCM's FCFs for 2016 to 2020 using the following assumptions: Operating in- come continues to grow at 10% per year over the next five years, CAPEX is expected to be $1,000 million per year, new investments in net working capital are expected to be $100 million per year, and depreciation expense equals the prior year's total plus 10% of the prior year's CAPEX. Note that because TCM is a going concern, we need not be concerned about the liquidation value of the firm's assets at the end of 2020. Solution Legend = Value given in problem Formula/Calculation Analysis required Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output Given TCM Petroleum Dec-14 Dec-15 Sales $ 12,211.00 $ 13,368.00 Cost of Goods Sold (9.755.00 (10,591.00) Gross Profit 2,456.00 2,777.00 Selling, General, & Administrative Expense (704.00) (698.00) Operating Income Before Deprec. 1,752.00 2,079.00 Depreciation, Depletion, & Amortization (794.00) (871.00) Operating Profit 958.00 1,208.00 Interest Expense (265.00) (295.00) Non-Operating Income/Expense 139.00 151.00 Special Items 20.00 Pretax Income 852.00 1,064.00 Total Income Taxes (340.80) (425.60) Net Income $ 511.20 638.40 Purchase of PP&E (CAPEX) Increase in Net Working Capital 875 102 1.322 (430) CAPEX estimated as follows: CAPEX = NET PP&E ending + Depreciation - NET PP&E Increase in Net Working Capital estimated as follows: NWC 2015 - NWC 2014 TCM's average tax rate 40% 40% Solution a. FCF Calculations for 2014-2015 Year 2014 2015 EBIT EBIT(1-T) = NOPAT Plus: Depreciation Expense Less: CAPEX Less: Working Capital Investment Firm Free Cash Flow b. Estimated FCF for 2016-2020 Year 2015 2016 2017 2018 2019 2020 EBIT (Growing at 10% per year) EBIT(1-40) = NOPAT Plus: Depreciation Expense Less: CAPEX Less: Working Capital Investment Firm Free Cash Flow 1,003 (1,000) (100) 1,103 (1,000) (100) 1,203 (1,000) (100) 1,303 (1,000) (100) 1,403 (1,000) (100)