Excel Case #2

This project will require you to devise an Excel spreadsheet . Have all your work in a single Excel tab. Format your file to print legibly and attractively on no more than two sides of one sheet of paper. Be sure to use Print Preview. Your finished product should be one that you would be proud to submit to your supervisor at your workplace. Other than the original source data, you may not input any additional numbers, except 1, into Excelall values must be formula-driven. You may not enter any numbers, except 1, into any formulas. If the spreadsheet is done correctly, any amount given could be changed & everything will be recomputed and show the new answers. (For instance, a new sales amount or Sales growth could be input & the spreadsheet will compute the new cash flows, NPV, ect.) Round each cell to the nearest dollar.

Info:

Your employer is considering a capital project that involves installing a new manufacturing facility(to manufacture a new product) at a cost of $30,800,000. The facility will be built on land that was purchased in 2018 for $1,250,000. If the facility is not built on this land, the land will remain unused. The new manufacturing facility, if built, will be depreciated on a straight-line basis over five years, to a salvage value of $2,000,000. If the facility is built, the production there will cause an immediate increase in Inventory of $1,300,000. It will also cause immediate increases in Accounts Receivable of $5,900,000, Accounts Payable of $850,000, and Long-Term Debt of $15.2million.

If built and produced, the new product is expected to generate annual sales of $20,375,000 by the end of the first year. Sales are expected to increase 8% per year. COGS expense is expected to be of $9,780,000 during the first year. Thereafter, COGS is expected to remain at a constant percentage of Sales. Because operating efficiency is expected to improve each year, SG&A expense is expected to remain at $3,750,000 for each of the five years of the project. At the end of the project's five-year life, production will cease, and the manufacturing facility will be sold for an estimated $4,500,000. At that time, Inventory, Accounts Receivable and Accounts Payable will return to their pre-project levels.

If the project is implemented, it will likely increase sales of the company's existing complimentary products. The net impact of those sales is expected to be a $2,225,000 annual increase in pre-tax profits.

Your employer's tax rate is 21%. The firm has 5 million shares of common stock outstanding. The firm requires a 11% rate of return on capital projects of this risk.

Prepare discounted cash flow analysis to determine whether your employer should implement this capital project. Your analysis should reveal answers to each of the following questions. Clearly label all cells. Highlight the cells that answer the following questions:

1.What is the initial investment amount (Year 0)?(what do we look at for this?)

2.What are the total cash flows each year (Years 1-5)

3.What is the NPV?

4.What is the IRR?