Atlantic Glass Company makes custom, luxury shower door glass enclosures for new construction homes and bathroom remodeling projects. Recently they have received a significant number of inquiries from home builders and contractors about a new glass door design that includes both glass and iron in the shower enclosure materials.

Atlantic Glasss current production facilities do not have the machinery or space to produce such a specialty, multi material enclosure so the company has turned down these potential orders. The sales department is frustrated by this and tells the CEO that this new product is in high demand and that it could be a significant source of growth for the company. The CEO agrees, and as a next step, launches two initiatives:

1. A customer research study to measure the potential size of the market for this specialty product within Atlantics addressable market geography. For this study, Atlantic will need to hire an outside consulting firm and pay them $500,000. This cost of this study is sunk and will be incurred whether or not the project moves forward.

2. A capital budgeting analysis to evaluate the potential investment merits (if any) of manufacturing and selling this specialty product line for the next 5 years.

The consulting firms findings confirm the sales departments instincts. It is estimated that, at a selling price of $9,995 per unit, the company could sell 700 units in year one of production and that unit volume would grow by 10.0% per year in years 2-5. However, it is also estimated that the new enclosures would cannibalize existing product sales and result in $500,000 of lost revenue per year.

The manufacturing department estimates that, in order to produce the specialty enclosures, the company would need to invest $10.0 million in new fixed assets today. The fixed assets would be depreciated on a straight line basis over 8 years. The salvage value of these fixed assets is estimated to be $4 million at the termination of the project. It is also estimated that the shower enclosures could be produced at a variable cost per unit of $4,000 (excluding depreciation and amortization) in year 1. The company anticipates that the variable costs per unit will increase by $150 per year in years 2-5.

Fixed SG&A expenses (excluding depreciation and amortization) for the project are expected to be $1.5 million per year. It is also expected that the project will require operating capital investments in years 1-5. Estimates for operating capital investment are based on an expectation of maintaining accounts receivable days of 45, inventory days of 60 and accounts payable days of 30 in each year of the initiative. Note: (please use total variable cost instead of cost of goods sold for calculating inventory and accounts payable values). Atlantic expects to recoup 80% of its operating capital investment at the termination of the project.

Atlantic currently has a stock price of $22.50 per share and 18.4 million shares of common stock outstanding. The firm has $200 million in total debt with an interest rate of 6.75%. Current risk free rates are 4.10% and the expected equity market risk premium is 5.00%. Atlantic has a beta of 1.83. The company has a 25.0% tax rate.

Part 1. Using an excel spreadsheet, please build a 5 year incremental cash flow projection for the contemplated shower enclosure project. Please construct your analysis starting with a blank file and DO NOT USE ANY OF THE PRE-FORMATTED TEMPLATES FROM CLASS. Based on the projections and the other provided information, please estimate the following:

What is the projects expected NPV?

What is the projects expected IRR? (2 decimal places)

What is the projects expected profitability index? (2 decimal places)

Part 2. In the same spreadsheet, please create a 5 x 5 sensitivity analysis/table to show how sensitive NPV is to price and variable cost assumptions. Please vary each row and column assumption in increments of $300. (hint; if your table is not functioning be sure to check excel settings to make sure you have enabled iterative calculations and that you are in manual mode)