The word document has all the instructions and they need to be answered in excel. I have attached the excel worksheet as well. Please answer in excel and show all the calculations as well as answer the 10 questions.

Thank you!(:

FIN 370 - AOL ASSIGNMENT CASE ANALYSIS: The Case of the Junior Analyst Mark was recently hired by Boeing as a junior budget analyst. He is working for the Venture Capital Division and has been given six capital budgeting projects to evaluate. He must give his analysis and recommendation to the capital budgeting committee on Tuesday December 8. Mark has a B.S. in accounting from CWU (2010) and passed the CPA exam (2011). He has been in public accounting for 2 years. During that time he earned an MBA from Seattle U. He would like to be the CFO of a company someday--maybe Boeing-- and this is an opportunity to get onto that career track and to prove his ability. As Mark looks over the financial data collected, he is trying to make sense of it all. He already has the most difficult part of the analysis complete--the estimation of cash flows. Through some internet research and application of finance theory (learned in Dr. Young's FIN 370 at CWU), he has also determined the unlevered betas for each project. Here is the information that Mark has accumulated so far: The Capital Budgeting Projects He must choose one of the four capital budgeting projects listed in Table 1: Table 1 shows the expected after-tax operating cash flows for each project. The tax rate used in his calculations is 30%. All projects are expected to have a 4 year life and to be depreciated straight-line to a zero salvage value. The projects differ in size (the cost of the initial investment) and their cash flow patterns are different. They also differ in risk as indicated by their unlevered betas. The capital budget is $20 million and the projects are mutually exclusive. Capital Structures In addition to recommending one of the four capital budgeting projects, Mark has also been asked to evaluate alternative capital structures for financing the chosen project. He has decided to evaluate 3 capital structures: no debt, 20% debt, and 50% debt. If debt is used, the cost of the debt will depend upon the amount of leverage in the capital structure. At 20% leverage, the before tax cost of debt will be 7%. At 50% leverage, the cost of debt rises to 16% for all projects Cost of Capital Mark knows that in order to evaluate the projects he will have to determine the cost of capital for each of them. Boeing's cost of capital is 15% based on a dividend plus growth model, but the project's risk levels are different from the company and from each other. Also the projects have no long term growth. Mark thinks the dividend/growth model is inappropriate for these projects and plans to calculate the cost of equity using the CAPM model. The unlevered betas for each project are given in Table 1. The project's betas are based on the betas of companies that are in the same industries (\"pure plays\"). The betas have been adjusted (using the Hamada equation), so that they represent the unlevered betas of these pure play companies. If the projects are financed with leverage, the betas will increase and so too will the cost of capital. Mark plans to use the Hamada equation to estimate the levered betas. The unlevered project betas, the risk free rate and the expected market return are given in Table 1. He will use the WACC as both the discount rate and the reinvestment rate in his capital budgeting analysis. Mark knows that Boeing executives have favored IRR in the past for making their capital budgeting decisions. His professor at Seattle U. said NPV was better than IRR. His textbook says that MIRR is also better than IRR. He is the new kid on the block and must be prepared to defend his recommendations. First, however, he must finish the analysis and write his report. To begin, he has formulated the following questions that need to be answered: 1. What is the beta for each project at 20% and 50% leverage? 2. What is the cost of equity for each project at 0, 20%, and 50% leverage? Do you think the CAPM model is an appropriate way to calculate the cost of equity for these projects? Why or why not. 3. What is the WACC for each project at 0, 20%, and 50% leverage? 4. Calculate all relevant capital budgeting measures for each project (complete Table 2)? 5. Which of the projects are unacceptable and why? Include on a single graph the NPV profile for each project (acceptable & unacceptable). 6. Rank the projects that are acceptable, according to your criterion of choice. Tell me your criterion! 7. Which project do you recommend and why? 8. For the capital budgeting project you selected, which capital structure do you recommend and why? 9. For the project and capital structure that you have selected, calculate the project's net income after-tax for the first year of operation. 10. For the capital budgeting project you selected, which capital structure do you recommend and why? Instructions Your answers should be word processed. The questions should be included before each answer. Questions 2, 5, 7, 8, and 10 are discussion questions. Your calculations should be done on Excel. Format NPV to whole dollars, Beta to three decimals, and percent to two decimals. Include your Excel spreadsheet on a single page at the end of the case. Place your numerical solutions in the word document. A cover page should include at least: Case name, Course name and number, course section number, your name, scenario number, date. No folders please. Place a single staple in the top left corner. Bonus points will be awarded for better than normal answers and appearance. FIN 370 NAME ____________________________ SCENARIO 11 TABLE 1 Operating Cash Flows After Tax t A B C D 0 -20,000,000 -20,000,000 -14,000,000 -18,000,000 1 8,000,000 11,000,000 6,200,000 7,000,000 2 8,000,000 10,000,000 6,200,000 7,000,000 3 8,000,000 8,000,000 6,200,000 6,000,000 4 8,000,000 2,000,000 6,200,000 6,000,000 Other Information Unlevereed B Risk free rate Market return Table 2 A 0 Lev 1 1% 14% 0.9 0.9 A 20% Lev A 50% Lev B 20% Lev B 50% Lev C 20% Lev C 50% Lev D 20% Lev D 50% Lev Beta Cost of Equity WACC NPV IRR MIRR B 0 Lev Beta Cost of Equity WACC NPV IRR MIRR C 0 Lev Beta Cost of Equity WACC NPV IRR MIRR D 0 Lev Beta Cost of Equity WACC NPV IRR MIRR 1.2