Please show steps on how to solve in Excel

a. Upload your completed assignment to Elms (Canvas). For this exercise, you can download the case excel and a handout with case exhibits that you will need to complete this assignment. Boeing must decide whether to build the 7E7 aircraft. Your task is to calculate an appropriate WACC with which to evaluate the project cash flows. The issue is that Boeing is in both the commercial and the military aircraft business, where each division has a different risk level; so, it is inappropriate to use Boeing's overall company cost of capital. You need to create a divisional cost of capital for a commercial aircraft project. You don't have any comparable commercial aircraft companies, but you do have military aircraft comparable companies. You can calculate the beta for a military aircraft project and use that beta to calculate a commercial aircraft project beta. Calculate a military/defense asset beta. Use Lockheed Martin (LM) and Northrup Grummon (NG) as military comps. (Raytheon has too much commercial business to be a reliable comparable.) Choose a beta from the first exhibit no right wrong beta. I'm hoping students will pick a variety of betas so we can see the differences in WACC. Use the Hamada equation (see video about how to unlever and re-lever betas) to unlever LM's and NG's beta; then average them to have a military asset beta. Then find Boeing's asset beta. Boeing's overall company beta is a weighted average of a military beta and a commercial beta (percent of revenue for Boeing's divisions are given.) Solve for Boeing's commercial asset beta. (Look at the video and PowerPoint Boeing Equations for hints) b. Relever Boeing's commercial asset beta according to Boeing's chosen capital structure. Plug this into the capital asset pricing model. Note that you are given a risk free rate and market risk premium. c. Using the second exhibit in the handout, decide what is an appropriate cost of debt - are you going to use all the debt? Just the long term debt? d. Calculate WACC. (You will need to turn a D/E ratio (given) into a debt ratio and an equity ratio. Assume a tax rate of 35%. Remember that the debt ratio = [D/CD + E)] = {(D/E)/[(D/E)+(E/E)]} = {(D/E)[(D/E) + 1]} so you can convert a debt ratio into a D/E ratio and vice versa. Also, note that the Equity Ratio = 1 minus the debt ratio. Boeing 7E7 Market Information Boeing 46% Lockheed Martin 93% Northrop Grumman 91% 1.05 0.60 0.70 0.80 1.05 1.45 0.36 0.38 0.34 0.34 0.31 0.27 % of sales derived from defense and space operations BETAS Value Line Calculated against the S&P 500 (End date, June 2013) 5 years 21 months 60 days Calculated against the NYSE Composite (end date June 2013) 5 years 21 months 60 days Tax rate Market value Debt/Equity ratios Market Risk Premium Risk Free Rate 1.00 1.17 1.62 0.35 0.535 0.49 0.44 0.37 0.35 0.41 0.44 0.36 0.30 0.35 0.64 6% 6% 6% 4.56% a. Upload your completed assignment to Elms (Canvas). For this exercise, you can download the case excel and a handout with case exhibits that you will need to complete this assignment. Boeing must decide whether to build the 7E7 aircraft. Your task is to calculate an appropriate WACC with which to evaluate the project cash flows. The issue is that Boeing is in both the commercial and the military aircraft business, where each division has a different risk level; so, it is inappropriate to use Boeing's overall company cost of capital. You need to create a divisional cost of capital for a commercial aircraft project. You don't have any comparable commercial aircraft companies, but you do have military aircraft comparable companies. You can calculate the beta for a military aircraft project and use that beta to calculate a commercial aircraft project beta. Calculate a military/defense asset beta. Use Lockheed Martin (LM) and Northrup Grummon (NG) as military comps. (Raytheon has too much commercial business to be a reliable comparable.) Choose a beta from the first exhibit no right wrong beta. I'm hoping students will pick a variety of betas so we can see the differences in WACC. Use the Hamada equation (see video about how to unlever and re-lever betas) to unlever LM's and NG's beta; then average them to have a military asset beta. Then find Boeing's asset beta. Boeing's overall company beta is a weighted average of a military beta and a commercial beta (percent of revenue for Boeing's divisions are given.) Solve for Boeing's commercial asset beta. (Look at the video and PowerPoint Boeing Equations for hints) b. Relever Boeing's commercial asset beta according to Boeing's chosen capital structure. Plug this into the capital asset pricing model. Note that you are given a risk free rate and market risk premium. c. Using the second exhibit in the handout, decide what is an appropriate cost of debt - are you going to use all the debt? Just the long term debt? d. Calculate WACC. (You will need to turn a D/E ratio (given) into a debt ratio and an equity ratio. Assume a tax rate of 35%. Remember that the debt ratio = [D/CD + E)] = {(D/E)/[(D/E)+(E/E)]} = {(D/E)[(D/E) + 1]} so you can convert a debt ratio into a D/E ratio and vice versa. Also, note that the Equity Ratio = 1 minus the debt ratio. Boeing 7E7 Market Information Boeing 46% Lockheed Martin 93% Northrop Grumman 91% 1.05 0.60 0.70 0.80 1.05 1.45 0.36 0.38 0.34 0.34 0.31 0.27 % of sales derived from defense and space operations BETAS Value Line Calculated against the S&P 500 (End date, June 2013) 5 years 21 months 60 days Calculated against the NYSE Composite (end date June 2013) 5 years 21 months 60 days Tax rate Market value Debt/Equity ratios Market Risk Premium Risk Free Rate 1.00 1.17 1.62 0.35 0.535 0.49 0.44 0.37 0.35 0.41 0.44 0.36 0.30 0.35 0.64 6% 6% 6% 4.56%