Show work in excel please!

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. Boeings cost of capital is 15% based on a dividend plus growth model, but the projects 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 projects 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 projects 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?

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 Beta	1	0.9	0.9	1.2
Risk free rate	1%
Market return	14%
Table 2	A	A	A
	0 Lev	20% Lev	50% Lev
Beta
Cost of Equity
WACC
NPV
IRR
MIRR
	B	B	B
	0 Lev	20% Lev	50% Lev
Beta
Cost of Equity
WACC
NPV
IRR
MIRR
	C	C	C
	0 Lev	20% Lev	50% Lev
Beta
Cost of Equity
WACC
NPV
IRR
MIRR
	D	D	D
	0 Lev	20% Lev	50% Lev
Beta
Cost of Equity
WACC
NPV
IRR
MIRR