You work for a consulting group hired to analyze whether a firm should invest in its new product.The project is intended to start in $2025($year $1)$ and last until $2039($year $15).$ The firm has estimated that each unit can be sold for $$75,$ costing them $$30$ per unit to manufacture. If the project is approved, the firm will need to hire employees for $$700,000$ annually during the project's life. The firm will also need to rent the space for the new facility for $$5,000,000$ per year.

The equipment necessary for production can be purchased for $$40,000,000.$ Considering the decay and loss of equipment efficiency over the years, the firm will need to buy a second and smaller piece of equipment in $2034($year $10)$ for $$20,000,000.$ At the end of the project, the first piece of equipment will be sold as scraps for $$500,000.$ The second piece of equipment will still be functional, so it can be sold for $$6,000,000.$ Following tax code regulations, each piece must be depreciated according to a schedule highlighted in the depreciation table below. Note that the equipment purchased in $2034($year $10)$ will begin to be depreciated in $2035($year $11).$

The firm will need to borrow money at a rate of $3\%$ per year to finance the amount necessary forthe initial investment.

To estimate the number of units the firm will sell, you have access to historical data on a similarproduct. In this data, the median household income and number of households in the target market can properly explain the number of unit sales. Based on your experience with this type ofproject, you have determined that you need to adjust the forecast obtained from historical data. Specifically, you will assume that unit sales remain constant starting from year $2035($year $11).$ This means that you will stop your forecast at year $2034($year $10)$ and use the same forecast you obtained in year $2034$ for years $2035$ to $2039($years $11$ to $15).$

Before production can start, the new facility must be constructed, and personnel must be trained.This implies that production in $2025($year $1)$ will be $0.$ Subsequently, the requirement for networking capital will also start in $2026($year $2)$ and will occur with the timing of cash flows for the year. The firm's net working capital requirement is equal to $20\%$ of sales.

The firm currently sells a similar product, which will be affected if the project is approved. The current product sells $300,000$ units a year and its production is expected to end in $2030($year $6).$The product is currently sold for $$60,$ costing the firm $$30$ per unit to manufacture. If the newproject is approved, sales for the current product starting in $2026($year $2)$ will drop to $150,000$ peryear, and the price will have to be lowered to $$50.$Your consulting services fee is $$5,000,000,$ which will be paid in equal $$1,000,000$ installmentsover the next $5$ years.

Assume a corporate tax rate of $21\%$ and a required return of $14\%$

A$)$ Estimate the project's cash flows.

B$)$ Make a recommendation to the firm based on an analysis of this project. Explain your answer

Use Excel for everything.