I need this assignment to be done in excel. Please let me know if you have any questions, i will also add a tip!

AVIATOR CORPORATION 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. 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: 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. 2 3 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! 4 Required: 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. For the payback period, merely indicate the year in which the cash flow equals or exceeds the initial investment. You do not have to compute midyear points. 3. Based on your calculations rank the projects and clearly recommend which project(s) should be accepted and which project(s) should be rejected. Prepare a report for Mary explaining the advantages and disadvantages of each method and supporting arguments for your recommendations. 4. Use the following headings in your report: Executive Summary Background Information and assumptions Alternatives Analysis of alternatives Conclusion/Recommendations References Be sure to cite material appropriately. If you are using our course textbook you can cite it as (p274, textbook). Any material taken from the case assignment does not need to be cited. 5 Examples: Aviator Corporatio n Capital Budget Analysis Summary Project A Project B Tax Rate: Project C 20% Project D Total Reported Earnings Payback IRR NPV Profitability Index WACC Cost (After Tax) XXX XXX XXX Debt Preferred Stock Common Equity WACC Debt Preferred Stock Common Stock Cost (After Tax) XXX XXX Weights XXX XXX XXX XXX Aviator Corporation Capital Budget Analysis Project A Cost of Capital Weighted Cost XXX XXX XXX XXX 0.00 % Tax Rate: 20% 6 Initial Expenditure s Year 1 Year 2 Year 3 Year 4 Year 5 Total Net cost of new plane Additional revenue Additional operating costs Amortization Net increase in income Less: Tax Increase in aftertax income Add back amortization Net change in cash flow Payback Period NPV IRR Profitability Index 7 Aviator Rubric Name: _______________________ Requirement Your grade Part A: WACC calculation /10 Discussion of limitations of WACC calculated in this manner Part B: Calculations Payback period IRR NPV PI Increase in after tax income Part B: Report Executive Summary Background Information and assumptions Alternatives Analysis of alternatives /10 /5 /5 /7 /5 /3 Errors in formulas or inputs Many Some Few None Considers relevant theory Many Some Few None Gaps or contradictions in logic Many Some Few None Errors in formulas or inputs Many Some Few None Many Some Few None Many Some Few None Many Some Few None Many Some Few None /5 /5 /5 /20 Conclusion/Recommendations Overall: Use of Excel /5 Report writing skills /10 Final Grade Comments /5 Considers relevant theory Many Some Few None Gaps or contradictions in logic Many Some Few None Excel used to \"Advanced Spreadsheets\" standards Spelling and grammar errors Many Some Few None Report uses headings, easy to follow Many Some Few None /100 8