Part B (80 marks) As she prepared to enter Mary's office, Siobhan 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. In return, the plane would bring over $600,000 in additional revenue during the next five years with only about $56,000 in operating costs. (See Table 1 for details.) B. A proposal to diversify into copy machines. The franchise was to cost $700,000, which would be amortized over a 40-year period. The new business was expected to generate over $1.4 million in sales over the next five years, and over $800,000 in after tax earnings. (See Table 2 for details.) C. 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 over $1.5 million in additional revenue, and it would give the company access to a wider market as well. (See Table 3 for details.) D. A proposal to begin operating a fleet of trucks. Ten could be bought for only $51,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.) In her mind, Siobhan quickly went over the evaluation methods she had used in the past payback period, internal rate of return, and net present value. Siobhan knew that Mary would add a fourth, size of reported earnings, but she hoped she could talk Mary out of using it this time. Siobhan herself favoured the net present value method, but she had always had a tough time getting Mary to understand it. Table 1 Financial analysis of project A: Add a twin-jet to the company's fleet Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of new plano.... $300,000 Additional revenue. $43,000 $76,800 $112,300 $225,000 $168,750 Additional operating costs. 11,250 11,250 11,250 11,250 11,250 Amortization ..... 45,000 66,000 63,000 63,000 63,000 Net increase in income (13,250) (450) 38,050 150,750 94,500 Table 2 Financial analysis of project B: Diversify into copy machines Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of new franchise. $700,000 Additional revenue. $87.500 $175,000 $262,500 $393,750 $525.000 Additional operating costs. 26,250 26,250 26,250 26,250 26,250 Amortization 17,500 17,500 17,500 17,500 17,500 Net increase in income.. 43,750 131,250 218,750 350,000 481,250 Table 3 Financial analysis of project C: Add a helicopter to the company's fleet Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of helicopter .... $800,000 Additional revenue $100,000 $200,000 $300,000 $450,000 $600,000 Additional operating costs. 40,000 40,000 40,000 40,000 Amortization 120,000 176,000 168.000 168.000 168,000 Net increase in income (60,000) (16,000) 92.000 242,000 392,000 40,000 Table 4 Financial analysis of project D: Add fleet of trucks Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of new trucks... $510,000 Additional revenue $382,500 $325,125 $ 89,250 $ 76,500 $ 51,000 Additional operating costs. 19.125 19,125 25,500 31,875 38,250 Amortization 76.500 112,200 107,100 107,100 107.100 Net increase in income 286.875 193.800 (43,350) (62,475) 194,350) As she closed her briefcase and walked toward Mary's door, Siobhan reminded herself to have patience; Mary might not trust financial analysis, but she would listen to sensible arguments. Siobhan only hoped her financial analysis sounded sensible! Required (Working in groups of 1 to 4 people): 1. Refer to Tables 1 through 4. All calculations must be completed in Excel. In an appendix, present a summary sheet for the decision, and a worksheet for each project 2. Compute the payback period internal rate of return (IRR), net present value (NPV), profitability index (PI) and total increase in after tax income of all four alternatives. Round your cost of capital calculation from Part A to 2 decimal places for use in your calculations Part B (80 marks) As she prepared to enter Mary's office, Siobhan 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. In return, the plane would bring over $600,000 in additional revenue during the next five years with only about $56,000 in operating costs. (See Table 1 for details.) B. A proposal to diversify into copy machines. The franchise was to cost $700,000, which would be amortized over a 40-year period. The new business was expected to generate over $1.4 million in sales over the next five years, and over $800,000 in after tax earnings. (See Table 2 for details.) C. 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 over $1.5 million in additional revenue, and it would give the company access to a wider market as well. (See Table 3 for details.) D. A proposal to begin operating a fleet of trucks. Ten could be bought for only $51,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.) In her mind, Siobhan quickly went over the evaluation methods she had used in the past payback period, internal rate of return, and net present value. Siobhan knew that Mary would add a fourth, size of reported earnings, but she hoped she could talk Mary out of using it this time. Siobhan herself favoured the net present value method, but she had always had a tough time getting Mary to understand it. Table 1 Financial analysis of project A: Add a twin-jet to the company's fleet Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of new plano.... $300,000 Additional revenue. $43,000 $76,800 $112,300 $225,000 $168,750 Additional operating costs. 11,250 11,250 11,250 11,250 11,250 Amortization ..... 45,000 66,000 63,000 63,000 63,000 Net increase in income (13,250) (450) 38,050 150,750 94,500 Table 2 Financial analysis of project B: Diversify into copy machines Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of new franchise. $700,000 Additional revenue. $87.500 $175,000 $262,500 $393,750 $525.000 Additional operating costs. 26,250 26,250 26,250 26,250 26,250 Amortization 17,500 17,500 17,500 17,500 17,500 Net increase in income.. 43,750 131,250 218,750 350,000 481,250 Table 3 Financial analysis of project C: Add a helicopter to the company's fleet Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of helicopter .... $800,000 Additional revenue $100,000 $200,000 $300,000 $450,000 $600,000 Additional operating costs. 40,000 40,000 40,000 40,000 Amortization 120,000 176,000 168.000 168.000 168,000 Net increase in income (60,000) (16,000) 92.000 242,000 392,000 40,000 Table 4 Financial analysis of project D: Add fleet of trucks Initial Expenditures Year 1 Year 2 Year 3 Year 4 Year 5 Net cost of new trucks... $510,000 Additional revenue $382,500 $325,125 $ 89,250 $ 76,500 $ 51,000 Additional operating costs. 19.125 19,125 25,500 31,875 38,250 Amortization 76.500 112,200 107,100 107,100 107.100 Net increase in income 286.875 193.800 (43,350) (62,475) 194,350) As she closed her briefcase and walked toward Mary's door, Siobhan reminded herself to have patience; Mary might not trust financial analysis, but she would listen to sensible arguments. Siobhan only hoped her financial analysis sounded sensible! Required (Working in groups of 1 to 4 people): 1. Refer to Tables 1 through 4. All calculations must be completed in Excel. In an appendix, present a summary sheet for the decision, and a worksheet for each project 2. Compute the payback period internal rate of return (IRR), net present value (NPV), profitability index (PI) and total increase in after tax income of all four alternatives. Round your cost of capital calculation from Part A to 2 decimal places for use in your calculations