• 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. Compute the Base Cases NPV and IRR

VALUE DRIVERS Years 0 1 2 3 4 5 Capital Spending OCF: Revenues Expenses Depreciation EBIT Taxes Net Income Depreciation (reference only) OPERATING CASH FLOW Net Working Capital Total Cash Flow NPV IRR