EXCEL PLEASE!!!

.

10. Your company is looking at setting up a manufacturing plant overseas to manufacture driverless flying cars. The company bought some land in that country three years ago for $2.7 million. The land was just appraised for $3.8 million after tax. The proposed project will last three years. It is expected that you can sell the land at the end of 3 years for $4 million. The manufacturing plant will cost $34 million to build. The following market data on your company are current. Debt: 187,000 bonds with a coupon rate of 6.3 percent outstanding, 25 years to maturity, selling for 105 percent of par; the bonds make annual payments. Common Stock: 8,000,000 shares outstanding selling for $96 per share; the beta is 1.58. The next dividend payments will be $12. Thereafter. dividends are expected to grow at 5% forever. Preferred Stock: 400,000 shares of 8 percent preferred stock outstanding, selling for $103 per share. Market: 10 percent expected market risk premium: 3.1 percent risk free rate. Your underwriter tells you the floatation costs for common stock, preferred stock, and debt are 6%, 5%, and 3% respectively. They recommend you raise all needed funds for the project by issuing new debt because it has the lowest cost and will be even lower once you consider your corporate tax rate of 30%. The project requires $5.7 million in initial net working capital that you will need to also raise through floatation. a) Your manager wants you to use both the CAPM and DDM methods to calculate Re and then take the average of the two numbers and use that as the Re in the WACC calculation. Calculate the appropriate discount rate to use when evaluating this project if we assume the project risk is similar to the overall company risk. b) Calculate the average cost of floatation. c) Calculate the project's initial Time 0 cash flow (CFFA) taking into account all side effects. Assume floatation only affects your capital investments, not your net working capital. d) The manufacturing plant uses a 3 years MACRS schedule for depreciation. At the end of the project that is at the end of 3 years) the plant and equipment can be scrapped for $1 million. What is the after tax salvage value of this plant and equipment? e) The company will incur $9.9 million in annual fixed costs. The plan is to manufacture 120 driverless flying cars per year and sell them for $1.300,000 each; the variable costs are $950,000 per unit. What is the annual operating cash flow (OCF) per year for the project? f) What is the IRR and NPV of this project? 10. Your company is looking at setting up a manufacturing plant overseas to manufacture driverless flying cars. The company bought some land in that country three years ago for $2.7 million. The land was just appraised for $3.8 million after tax. The proposed project will last three years. It is expected that you can sell the land at the end of 3 years for $4 million. The manufacturing plant will cost $34 million to build. The following market data on your company are current. Debt: 187,000 bonds with a coupon rate of 6.3 percent outstanding, 25 years to maturity, selling for 105 percent of par; the bonds make annual payments. Common Stock: 8,000,000 shares outstanding selling for $96 per share; the beta is 1.58. The next dividend payments will be $12. Thereafter. dividends are expected to grow at 5% forever. Preferred Stock: 400,000 shares of 8 percent preferred stock outstanding, selling for $103 per share. Market: 10 percent expected market risk premium: 3.1 percent risk free rate. Your underwriter tells you the floatation costs for common stock, preferred stock, and debt are 6%, 5%, and 3% respectively. They recommend you raise all needed funds for the project by issuing new debt because it has the lowest cost and will be even lower once you consider your corporate tax rate of 30%. The project requires $5.7 million in initial net working capital that you will need to also raise through floatation. a) Your manager wants you to use both the CAPM and DDM methods to calculate Re and then take the average of the two numbers and use that as the Re in the WACC calculation. Calculate the appropriate discount rate to use when evaluating this project if we assume the project risk is similar to the overall company risk. b) Calculate the average cost of floatation. c) Calculate the project's initial Time 0 cash flow (CFFA) taking into account all side effects. Assume floatation only affects your capital investments, not your net working capital. d) The manufacturing plant uses a 3 years MACRS schedule for depreciation. At the end of the project that is at the end of 3 years) the plant and equipment can be scrapped for $1 million. What is the after tax salvage value of this plant and equipment? e) The company will incur $9.9 million in annual fixed costs. The plan is to manufacture 120 driverless flying cars per year and sell them for $1.300,000 each; the variable costs are $950,000 per unit. What is the annual operating cash flow (OCF) per year for the project? f) What is the IRR and NPV of this project