Please answer in excel and show the calculation. Thank you so much.

Introduction Siobhan McGurkin, chief operating officer for the Aviator Corporation, wished she could remember more of her training in financial theory that she had been exposed to in college. Aviator Corporation was a computer services firm that specialized in airborne support. Siobhan had just completed summarizing the financial aspects of four capital investment projects that were open to Aviator during the coming year, and she was faced with the task of recommending which should be selected. Her boss, Mary Marshall, was a "street-smart" chief executive, with no background in financial theory, and CEO of Aviator. As she headed toward her boss's office, she was most concerned with the knowledge that her boss would immediately favour the project that promised the highest gain in reported net income. Siobhan knew that selecting projects purely on that basis would be incorrect; but she wasn't sure of her ability to convince Mary, who tended to assume financiers thought up fancy methods just to show how smart they were. Part A (20 marks) Aviator Corporation is trying to calculate its cost of capital for use in the above capital budgeting decision. Ms. McGurkin, has given you the following information and has asked you to compute the weighted average cost of capital. The company currently has an outstanding bond with a 9.5 percent coupon rate and another bond with a 7.8 percent rate. The firm has been informed by its investment dealer that bonds of equal risk and credit ratings are now selling to yield 8.0 percent with a flotation cost of 3%. The common stock has a price of $108.44 and an expected dividend (D1) of $3.15 per share. The historical growth pattern (g) for dividends is as follows: $2.00 2.24 2.51 2.81 The preferred stock is selling at $90 per share and pays a dividend of $8.50 per share. The corporate tax rate is 20 percent. The flotation cost is 2 percent of the selling price for preferred stock. The optimum capital structure for the firm is 40 percent debt, 30 percent preferred stock, and 30 percent common equity in the form of retained earnings. 1 Required: 1. In Excel, compute the cost of capital for the individual components in the capital structure, and then calculate the weighted average cost of capital. (10 marks) 2. Prepare a brief summary to Ms. McGurkin explaining the limitations of using a weighted average cost of capital calculated in this manner. (10 marks) 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 plane. $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 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 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! Introduction Siobhan McGurkin, chief operating officer for the Aviator Corporation, wished she could remember more of her training in financial theory that she had been exposed to in college. Aviator Corporation was a computer services firm that specialized in airborne support. Siobhan had just completed summarizing the financial aspects of four capital investment projects that were open to Aviator during the coming year, and she was faced with the task of recommending which should be selected. Her boss, Mary Marshall, was a "street-smart" chief executive, with no background in financial theory, and CEO of Aviator. As she headed toward her boss's office, she was most concerned with the knowledge that her boss would immediately favour the project that promised the highest gain in reported net income. Siobhan knew that selecting projects purely on that basis would be incorrect; but she wasn't sure of her ability to convince Mary, who tended to assume financiers thought up fancy methods just to show how smart they were. Part A (20 marks) Aviator Corporation is trying to calculate its cost of capital for use in the above capital budgeting decision. Ms. McGurkin, has given you the following information and has asked you to compute the weighted average cost of capital. The company currently has an outstanding bond with a 9.5 percent coupon rate and another bond with a 7.8 percent rate. The firm has been informed by its investment dealer that bonds of equal risk and credit ratings are now selling to yield 8.0 percent with a flotation cost of 3%. The common stock has a price of $108.44 and an expected dividend (D1) of $3.15 per share. The historical growth pattern (g) for dividends is as follows: $2.00 2.24 2.51 2.81 The preferred stock is selling at $90 per share and pays a dividend of $8.50 per share. The corporate tax rate is 20 percent. The flotation cost is 2 percent of the selling price for preferred stock. The optimum capital structure for the firm is 40 percent debt, 30 percent preferred stock, and 30 percent common equity in the form of retained earnings. 1 Required: 1. In Excel, compute the cost of capital for the individual components in the capital structure, and then calculate the weighted average cost of capital. (10 marks) 2. Prepare a brief summary to Ms. McGurkin explaining the limitations of using a weighted average cost of capital calculated in this manner. (10 marks) 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 plane. $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 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 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