Stockbridge Sprockets, Inc. (hereinafter referred to as the company), has had a surge in orders that they believe will continue into the foreseeable future, and if so will likely necessitate building or buying a new factory to keep up with product orders. They have decided to do an analysis on potentially building a factory on a tract of land they currently own near Mason. They have hired you to complete this analysis and make a recommendation.

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 $4,000,000 in infrastructure improvements should they decide to build the factory on that site. (Hint: do NOT factor this in when calculating annual Depreciation).
• 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 $40,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 8-year useful life.
• A competitor, Williamston Widgets, Inc., has told the company that they will buy the new factory and all of its equipment for $10,000,000 at the end of the project (the end of Year 5). The company plans to accept the offer.
• Products produced by the factory will add an estimated $46,000,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 $800,000, 20% 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%.

Using the above data, complete the DCF Model in Excel posted on Connect. Compute the Base Cases NPV and IRR.

(Please show Excel formulas)