I have a spreadsheet all built for this I can't seem to upload it and share it.

Suppose you have been hired as a financial consultant to Defense Electronics, Inc. (DEI), a large publicly traded firm that is the market share leader in radar detection systems (RDSs). The company is looking at setting up a manufacturing plant overseas to produce a new line of RDSs. This will be a five-year project. In order to facilitate this project, DEI purchased some land where they will build their new manufacturing plant, which cost $4.4 million on an after-tax basis. In five years, the after-tax value of the land will be $4.8 million. The plant and equipment will cost $37 million to build.

DEI's tax rate is 32 percent. The project requires $1,300,000 in initial net working capital investment to get operational. The manufacturing plant has an eight-year tax life, and DEI uses straight-line depreciation. At the end of the project (that is, the end of Year 5), the plant and equipment can be scrapped for $5.1 million.

The company will incur $6,700,000 in annual fixed costs. The plan is to manufacture 15,300 RDSs per year and sell them at $11,450 per machine; the variable production costs are $9,500 per RDS.

The following market data on DEI's securities are current:

Debt: 210,000 6.4% coupon bonds outstanding, 25 years to maturity, selling for 110 percent of par; the bonds have a $1,000 par value each and make semiannual payments.

Common stock: 8,300,000 shares outstanding, selling for $68 per share; the beta is 1.3.

Preferred stock: 450,000 shares of preferred stock outstanding, selling for $79 per share with a dividend of $4.50.

Market: 6 percent expected market risk premium; 3.5 percent risk-free rate

DEI uses HSOB as its lead underwriter. HSOB charges DEI 10% flotation costs on new common stock issues, 6% on new preferred stock issues, and 4% on new debt issues. Assume DEI raises all equity for new projects externally

Calculate the NPV and the IRR of the proposed project.

Some notes that will help you with this case:

1- When calculating the free cash flow for time 0, remember to include flotation costs as described above. The total cost will be the cost of the land, the cost of the building, the cost of the increase in working capital, and the flotation costs. Even though we already own the land and it might appear to be a sunk cost, remember that if we weren't building the plant on the land we could use it for something else, meaning that there is an opportunity cost associated with it. Because of this, the cost of the land should be included. Be sure to put this cost under the tax line as we are dealing with after tax values.

The cost of the building and working capital will require new financing and therefore financing costs. Thus the building and working capital must be adjusted for the flotation costs as discussed in section 14-7 of the 11th edition and 14-6 of the 10th edition of the text. It is not necessary to adjust the land costs since we already own the land.

2- The appropriate amount to depreciate is just the cost of the building (37,000,000). Financing costs and the cost of the land should not be included.

3- In year 5, remember to include the salvage value of the land. Because the value that you are given is an after tax amount, be sure to put it under the tax line. Also remember that although the working capital will be recovered at the end of the project, the financing costs from working capital will not.