Please do in excel and show all formulas.
Canon Body Currency Copy Paste B U Merge & Center $ % Cor SPERA AMMI Format Fort F3 Xfx 116300000 A E 31-Dec-15 $105,300,000 $161,700,000 (12,600,000) (181,300,000) (41,100,000) 37.84% D NICCAIR CORPORATION, 2011 - 2015 31-Dec-14 31-Dec-13 $128,000,000 $122,100,000 $155,000,000 $148,800,000 268,300,000 491,300,000 (192,500,000) (185,700,000) (34,600,000) (46,900,000) 31.03% 32.62% 31-Dec-12 $35,800,000 $145,100,000 233,000,000 (158,400,000) (50,600,000) 16.945 31-Dec-11 $116,300,000 $141,600,000 213,400,000 (154,000,000) (45,500,000) 34.56% 1 2 3 Net Income 4 Depreciation 5. Changes in 6 Capital exped 7 Net interest 8 Tax rate 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 32 36 27 38 8. (Challenge problem) The last 5 years' results for Niccair Corp. are given below. Value the company's stock based on a model of FCF growth of your own design and the following additional facts. (This is not an easy problem, and it has no explicit answer-valuation is often like that!) Niccair's 2003 year-end debt is $750 million. Niccair's 2003 year-end cash is $50 million. The company has a cost of debt of r = 5%. The company has 44,080,000 shares outstanding; the end-2003 share price is $37. Niccair's share p = 0.437, r,= 3%, and E(1x) = 12%. . 1 2 3 Net income 4 Depreciation 5Changes in net working capital Capital expenditures Z Net interest paid before taxes B D E F NICCAIR CORPORATION, 2011-2015 31-Dec-15 31-Dec-14 31-Dec-13 31-Dec-12 31-Dec-11 $105,300,000 $128,000,000 $122,100,000 $35,800,000 $116,300,000 $161,700,000 $155,000,000 $148,800,000 $145,100,000 $141,600,000 -12,600,000 268,300,000 491,300,000 233,000,000 213,400,000 -181,300,000 -192,500,000 -185.700,000 -158,400,000 -154,000,000 -41,100,000 -34,600,000 -46,900,000 -50,600,000 -45,500,000 37.84% 31.03% 32.62% 16.94% 34.56% 8 Tax rate Canon Body Currency Copy Paste B U Merge & Center $ % Cor SPERA AMMI Format Fort F3 Xfx 116300000 A E 31-Dec-15 $105,300,000 $161,700,000 (12,600,000) (181,300,000) (41,100,000) 37.84% D NICCAIR CORPORATION, 2011 - 2015 31-Dec-14 31-Dec-13 $128,000,000 $122,100,000 $155,000,000 $148,800,000 268,300,000 491,300,000 (192,500,000) (185,700,000) (34,600,000) (46,900,000) 31.03% 32.62% 31-Dec-12 $35,800,000 $145,100,000 233,000,000 (158,400,000) (50,600,000) 16.945 31-Dec-11 $116,300,000 $141,600,000 213,400,000 (154,000,000) (45,500,000) 34.56% 1 2 3 Net Income 4 Depreciation 5. Changes in 6 Capital exped 7 Net interest 8 Tax rate 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 32 36 27 38 8. (Challenge problem) The last 5 years' results for Niccair Corp. are given below. Value the company's stock based on a model of FCF growth of your own design and the following additional facts. (This is not an easy problem, and it has no explicit answer-valuation is often like that!) Niccair's 2003 year-end debt is $750 million. Niccair's 2003 year-end cash is $50 million. The company has a cost of debt of r = 5%. The company has 44,080,000 shares outstanding; the end-2003 share price is $37. Niccair's share p = 0.437, r,= 3%, and E(1x) = 12%. . 1 2 3 Net income 4 Depreciation 5Changes in net working capital Capital expenditures Z Net interest paid before taxes B D E F NICCAIR CORPORATION, 2011-2015 31-Dec-15 31-Dec-14 31-Dec-13 31-Dec-12 31-Dec-11 $105,300,000 $128,000,000 $122,100,000 $35,800,000 $116,300,000 $161,700,000 $155,000,000 $148,800,000 $145,100,000 $141,600,000 -12,600,000 268,300,000 491,300,000 233,000,000 213,400,000 -181,300,000 -192,500,000 -185.700,000 -158,400,000 -154,000,000 -41,100,000 -34,600,000 -46,900,000 -50,600,000 -45,500,000 37.84% 31.03% 32.62% 16.94% 34.56% 8 Tax rate
