PLEASE SHOW EXCEL FORMULAS

As she headed toward her boss's office, Cassandra Birch, chief operating officer for the CompAir Corporation, a computer services firm that specialized in airborne support-wished she could remember more of her training in financial theory that she had been exposed to at St. Clair College. Cassandra had just completed summarizing the financial aspects of four capital investment projects that were open to CompAir during the coming year, and she was faced with the task of recommending which should be selected. What concerned her was the knowledge that her boss, Cindy Marsh, a "street smart" chief executive, with no background in financial theory, would immediately favour the project that promised the highest gain in reported net income. Cassandra knew that selecting projects purely on that basis would be incorrect; but she wasn't sure of her ability to convince Cindy, who tended to assume financiers thought up fancy methods just to show how smart they were. In anticipation of an upcoming meeting with Cindy, Cassandra pulled her summary sheets from her briefcase and quickly reviewed the details of the four projects, all of which she considered to be equally risky. A. A proposal to add a jet to the company's fleet. The plane was only six years old and was considered a good buy at $300,000. (See Table 1 for details.) TABLE 1 Financial analysis of Project A: Add a twin-jet to the company's fleet. Year1 Year 2 Year 3 Year 4 Year 5 Year 1 Cost of New Plane 300,000 Additional Revenue Additional Operating Costs Amortization Net Increase in Income Less Tax @ 33% Increase in After Tax Income Add back Ammortization Net After-Tax Cash Flow (300,000) 63,000 66,800 (11,250) (11,249) (60,000) (60,000) (8,250) (4,449) 112,500 (11,248) (60,000) 41,252 13,613 27,639 60,000 87,639 215,200 (11,247) (60,000) 143,953 47,504 96,449 60,000 156,449 172,750 (11,246) (60,000) 101,504 33,496 68,008 60,000 128,008 (8,250) 60,000 51,750 (4,449) 60,000 55,551 A proposal to buy a helicopter. The machine was expensive and, counting additional training and licensing requirements, would cost $40,000 a year to operate. However, the versatility that the helicopter was expected to provide would generate nearly $1.7 million in additional revenue, and it would give the company access to a wider market as well. (See Table 3 for details.) TABLE 3 Financial analysis of Project C: Add a helicopter to the company's fleet. Year 1 Year 2 Year 3 Year 4 Year 5 Year 1 Cost of New Helicopter 800,000 Additional Revenue Additional Operating Costs Amortization Net Increase in Income Less Tax @ 33% Increase in After Tax Income Add back Ammortization Net After-Tax Cash Flow (800,000) 120,445 (40,000) (160,000) (79,555) 194,326 (40,000) (160,000) (5,674) 315,114 461,291 411,250 (40,000) (40,000) (40,000) (160,000) (160,000) (160,000) 115,114 261,291 211,250 37,988 86,226 69, 713 77,126 175,065 141,538 160,000 160,000 160,000 237, 126 335,065 301,538 (79,555) 160,000 80,445 (5,674) 160,000 154,326 D. A proposal to begin operating a fleet of trucks. Ten could be bought for only $50,000 each, and the additional business would bring in almost $700,000 in new sales in the first two years alone. (See Table 4 for details.) TABLE 4 Financial analysis of Project D: Add fleet of trucks Year 1 Year 2 Year 3 Year 4 Year 5 Year 1 Cost of New Trucks 500,000 Additional Revenue Additional Operating Costs Amortization Net Increase in Income Less Tax @ 33% Increase in After Tax Income Add back Ammortization Net After-Tax Cash Flow (500,000) 110,331 (19,125) (100,000) (8,794) 61,234 40,411 (19,125) (19,125) (100,000) (100,000) (57,891) (78,714) 339,078 (19,125) (100,000) 219,953 72,584 147,369 100,000 247,369 305,620 (19,125) (100,000) 186,495 61,543 124,952 100,000 224,952 (8,794) 100,000 91,206 (57,891) 100,000 42,109 (78,714) 100,000 21,286 In her mind, Cassandra quickly went over the evaluation methods she had used in the past: payback, internal rate of return, net present value, and profitability index. Cassandra knew that Cindy would add a fifth, size of reported earnings, but she hoped she could talk Cindy out of using it this time. Cassandra herself favoured the Net Present Value (NPV) method, but she had always had a tough time getting Cindy to understand it. One additional constraint that Cassandra had to deal with was Cindy's insistence that no outside financing be used this year. Cindy was worried that the company was growing too fast and had piled up enough debt for the time being. She was also against a stock issue for fear of diluting earnings and her control over the firm. As a result of Cindy's prohibition of outside financing, the size of the capital budget this year was limited to $800,000, which meant that not all of the four projects under consideration could be chosen. Cassandra was not too happy about that either, but she had decided to accept it for now and concentrate on selecting the best project(s). Cassandra reminded herself to have patience: Cindy might not trust financial analysis, but she would listen to sensible arguments. Cassandra only hoped her financial analysis sounded sensible! Required: You are Cassandra's top financial analyst and she has asked you to analyze the four projects using Excel. Part A For each project, calculate 1) the total increase in after-tax income, 2) the payback period, 3) internal rate of return (IRR), 4) the net present value (NPV), and 5) the Profitability Index (PI). . Ignore the impact of Capital Cost Allowance (CCA) Use 10 percent for the cost of capital in your calculations (i.e. discount rate) Excel formulas must be used to calculate NPV, IRR and PI. You must use the Excel template provided Assume 10.0% Cost of Capital Project A Project B Project C Project D oo Project Cost Yr1 after tax cashflow Yr2 after tax cashflow Yr3 after tax cashflow Yr4 after tax cashflow Yr5 after tax cashflow Total after tax cashflows ooo 0 Total 5yr After tax earnings (input) Payback (input) Internal Rate of Return (IRR) Net Present Value (NPV) Profitability Index (PI) 0.00 0.00% 0.00 0.00% 0.00 0.00% 0.00 0.00% 0 0.00 0 0.001 0.00 0.00 As she headed toward her boss's office, Cassandra Birch, chief operating officer for the CompAir Corporation, a computer services firm that specialized in airborne support-wished she could remember more of her training in financial theory that she had been exposed to at St. Clair College. Cassandra had just completed summarizing the financial aspects of four capital investment projects that were open to CompAir during the coming year, and she was faced with the task of recommending which should be selected. What concerned her was the knowledge that her boss, Cindy Marsh, a "street smart" chief executive, with no background in financial theory, would immediately favour the project that promised the highest gain in reported net income. Cassandra knew that selecting projects purely on that basis would be incorrect; but she wasn't sure of her ability to convince Cindy, who tended to assume financiers thought up fancy methods just to show how smart they were. In anticipation of an upcoming meeting with Cindy, Cassandra pulled her summary sheets from her briefcase and quickly reviewed the details of the four projects, all of which she considered to be equally risky. A. A proposal to add a jet to the company's fleet. The plane was only six years old and was considered a good buy at $300,000. (See Table 1 for details.) TABLE 1 Financial analysis of Project A: Add a twin-jet to the company's fleet. Year1 Year 2 Year 3 Year 4 Year 5 Year 1 Cost of New Plane 300,000 Additional Revenue Additional Operating Costs Amortization Net Increase in Income Less Tax @ 33% Increase in After Tax Income Add back Ammortization Net After-Tax Cash Flow (300,000) 63,000 66,800 (11,250) (11,249) (60,000) (60,000) (8,250) (4,449) 112,500 (11,248) (60,000) 41,252 13,613 27,639 60,000 87,639 215,200 (11,247) (60,000) 143,953 47,504 96,449 60,000 156,449 172,750 (11,246) (60,000) 101,504 33,496 68,008 60,000 128,008 (8,250) 60,000 51,750 (4,449) 60,000 55,551 A proposal to buy a helicopter. The machine was expensive and, counting additional training and licensing requirements, would cost $40,000 a year to operate. However, the versatility that the helicopter was expected to provide would generate nearly $1.7 million in additional revenue, and it would give the company access to a wider market as well. (See Table 3 for details.) TABLE 3 Financial analysis of Project C: Add a helicopter to the company's fleet. Year 1 Year 2 Year 3 Year 4 Year 5 Year 1 Cost of New Helicopter 800,000 Additional Revenue Additional Operating Costs Amortization Net Increase in Income Less Tax @ 33% Increase in After Tax Income Add back Ammortization Net After-Tax Cash Flow (800,000) 120,445 (40,000) (160,000) (79,555) 194,326 (40,000) (160,000) (5,674) 315,114 461,291 411,250 (40,000) (40,000) (40,000) (160,000) (160,000) (160,000) 115,114 261,291 211,250 37,988 86,226 69, 713 77,126 175,065 141,538 160,000 160,000 160,000 237, 126 335,065 301,538 (79,555) 160,000 80,445 (5,674) 160,000 154,326 D. A proposal to begin operating a fleet of trucks. Ten could be bought for only $50,000 each, and the additional business would bring in almost $700,000 in new sales in the first two years alone. (See Table 4 for details.) TABLE 4 Financial analysis of Project D: Add fleet of trucks Year 1 Year 2 Year 3 Year 4 Year 5 Year 1 Cost of New Trucks 500,000 Additional Revenue Additional Operating Costs Amortization Net Increase in Income Less Tax @ 33% Increase in After Tax Income Add back Ammortization Net After-Tax Cash Flow (500,000) 110,331 (19,125) (100,000) (8,794) 61,234 40,411 (19,125) (19,125) (100,000) (100,000) (57,891) (78,714) 339,078 (19,125) (100,000) 219,953 72,584 147,369 100,000 247,369 305,620 (19,125) (100,000) 186,495 61,543 124,952 100,000 224,952 (8,794) 100,000 91,206 (57,891) 100,000 42,109 (78,714) 100,000 21,286 In her mind, Cassandra quickly went over the evaluation methods she had used in the past: payback, internal rate of return, net present value, and profitability index. Cassandra knew that Cindy would add a fifth, size of reported earnings, but she hoped she could talk Cindy out of using it this time. Cassandra herself favoured the Net Present Value (NPV) method, but she had always had a tough time getting Cindy to understand it. One additional constraint that Cassandra had to deal with was Cindy's insistence that no outside financing be used this year. Cindy was worried that the company was growing too fast and had piled up enough debt for the time being. She was also against a stock issue for fear of diluting earnings and her control over the firm. As a result of Cindy's prohibition of outside financing, the size of the capital budget this year was limited to $800,000, which meant that not all of the four projects under consideration could be chosen. Cassandra was not too happy about that either, but she had decided to accept it for now and concentrate on selecting the best project(s). Cassandra reminded herself to have patience: Cindy might not trust financial analysis, but she would listen to sensible arguments. Cassandra only hoped her financial analysis sounded sensible! Required: You are Cassandra's top financial analyst and she has asked you to analyze the four projects using Excel. Part A For each project, calculate 1) the total increase in after-tax income, 2) the payback period, 3) internal rate of return (IRR), 4) the net present value (NPV), and 5) the Profitability Index (PI). . Ignore the impact of Capital Cost Allowance (CCA) Use 10 percent for the cost of capital in your calculations (i.e. discount rate) Excel formulas must be used to calculate NPV, IRR and PI. You must use the Excel template provided Assume 10.0% Cost of Capital Project A Project B Project C Project D oo Project Cost Yr1 after tax cashflow Yr2 after tax cashflow Yr3 after tax cashflow Yr4 after tax cashflow Yr5 after tax cashflow Total after tax cashflows ooo 0 Total 5yr After tax earnings (input) Payback (input) Internal Rate of Return (IRR) Net Present Value (NPV) Profitability Index (PI) 0.00 0.00% 0.00 0.00% 0.00 0.00% 0.00 0.00% 0 0.00 0 0.001 0.00 0.00