To perform the necessary analysis, you have compiled the following data:

The project has a 5 year timeline.

The company purchased the land in Mason that the factory would be built on for -$10,000,000. The purchase was made in 2007.

The factory site will require -$1,000,000 in infrastructure improvements should they decide to build the factory on that site.

The company has performed Research and Development on the products that the factory would build over the past year in the amount of -$500,000.

The cost of building and equipping the factory is estimated at -$28,000,000.

The Marketing Department of the company has spent -$250,000 over the past year to try to increase demand in the companys products.

Both the factory and its equipment would be depreciated straight-line to $0 over their estimated 7-year useful life.

A residential property developer has offered the company +$10,000,000 for the site should they not decide to build the factory on it.

If the company decides to build the factory, they will sell a factory that they own that has been closed for several years located Portland, MI. Mid-Michigan Manufacturing has made an offer to purchase the closed facility for +$1,265,823. This transaction would take place immediately at the beginning of the project (Year 0).

A competitor, Williamston Widgets, Inc., has told the company that they will buy the new factory and all of its equipment for $5,000,000 at the end of the project (the end of Year 5).

Products produced by the factory will add an estimated $45,500,000 to the companys revenue in Year 1.

Sales growth in Years 2 & 3 is expected to be 4.5% per year.

As the market begins to become saturated, sales are expected to decline in Years 4 & 5 by 5% per year.

Total Costs (Expenses) are estimated to be 76% of sales.

Additional Net Working Capital will be required in Year 0 of $500,000, 30% of which will be recovered in the projects terminal year.

The companys tax rate is 21%.

The required rate of return on the project is 10.0%.

Part 1 Base Case:

Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Cases NPV and IRR. Then copy the Base Case worksheet and post to the tabs marked Part 2, Part 3, and Part 4. A consulting firm as suggested a few alternate scenarios based on their analysis and has computed their estimate of the likelihood of each scenario occurring. They have also estimated that

B C D E F G 3 4 5 6 7 8 9 10 EBIT 11 12 13 14 15 16 17 18 19 20 21 22 Depreciation 23 24 Taxes 25 26 Depreciation (reference only) 27 28 29 Net Working Capital 30 31 32 Total Cash Flow 33 Rate 34 NPV 35 IRR 36 37 38 Net Income OPERATING CASH FLOW 0.00 UNUMI B C D E F G 3 4 5 6 7 8 9 10 EBIT 11 12 13 14 15 16 17 18 19 20 21 22 Depreciation 23 24 Taxes 25 26 Depreciation (reference only) 27 28 29 Net Working Capital 30 31 32 Total Cash Flow 33 Rate 34 NPV 35 IRR 36 37 38 Net Income OPERATING CASH FLOW 0.00 UNUMI