Arndt Corporation is a globally diversified manufacturer with operations in fluid power systems, electrical power quality, distribution and control, and automotive engine air management and fuel economy. It is considering an expansion into the hydrogen fuel cell business. It has been researching its move into the fuel cell industry over the past few years. To date it has spent $130 million on research and development expenses on fuel cell technology and an additional $25 million in marketing and feasibility studies and hopes to recover these costs with the successful move into the industry. It is evaluating the project proposal over a nine-year time horizon.

You have been asked to collect the data needed to assess the project and have summarized the data as follows:

• You estimate that it will cost Arndt $7.5 billion to establish its presence in this business. Of this amount, $4 billion will be spent right now (t = 0) acquiring the capital assets needed for the business with a second investment of $3.5 billion made one year from now (t = 1). Beginning in year 2, an additional expenditure of $300 million will be needed to maintain the project with that amount growing at a rate of 4% per year thereafter. The project will be fully operational at the beginning of the second year (t = 2). The entire initial investment of $7.5 billion is depreciable starting in the second year and will be depreciated using the MACRS schedule for a 5-year asset. You may assume that the additional expenditures that begin in year 2 will be fully expensed and are not depreciable.
• The total market for fuel cell technology products is currently estimated to be $20 billion but is expected to grow by 30% per year for the next 4 years, with growth leveling off to 10% per year by the 9^th year and beyond (i.e., the growth rate will be 30% per year through year 4, 26% in year 5, 22% in year 6, 18% in year 7, 14% per year in year 8, and 10% per year in year 9 and beyond). Arndt is hoping to attain a 5% market share in the first year in which it enters the market (t = 2), a 6% share in year 3, a 7% share in year 4, an 8% share in year 5, a 9% share in year 6, and a 10% share in year 7 and beyond.
• Arndt expects that its total cost of sales will be 60% of its total revenues in the first year of operations (t = 2). Production efficiencies will reduce this amount by 1 percentage point per year until the cost of sales levels off at 55% of total revenues in year 7 and beyond (i.e., the cost of sales as a percentage of sales will be 59% in year 3, 58% in year 4, 57% in year 5, 56% in year 6, and 55% in year 7 and beyond).
• Arndt will allocate 10% of its existing general and administrative (G&A) costs to the new division. Total G&A costs for the entire firm are currently $2 billion and expected to grow by 3% per year over the long term. In addition, Arndt expects a further increase of $300 million in G&A costs in year 2 when operations begin, and that these additional G&A costs will grow at the same rate as the firms growth in total fuel cell revenues after that.
• Arndt currently spends $600 million firm-wide on research and development expenditures and expects these expenditures to grow at 3% per year indefinitely. However, if the fuel cell division is added to the company, the expected growth rate in Arndts total R&D expenses is expected to be 12% per year beginning in year 1.
• While the new business will need supply chain support, it is anticipated that Arndt can initially use excess capacity in its existing supply chain network. Its existing businesses are currently using 50% of the firms supply chain capacity, with anticipated growth of 10% per year going forward (e.g., it will use 55.0% of the total capacity next year, 60.5% the year after, and so on). Adding the fuel cell business would initially use up an additional 20% of the total capacity in year 2, the first year of operations, and the increased use of supply chain capacity will track revenue growth in the fuel cell business beyond that point. When Arndt runs out of supply chain capacity, it will have to pay for an expansion of its supply chain network in the year prior to it exceeding its total capacity. The current estimated cost of this expansion is $500 million, but this cost is growing at 4 percent per year. For simplicity, you may disregard any depreciation charges associated with its supply chain investment.
• The fuel cell division will create working capital needs, as estimated below:
  • The sale of fuel cells on credit to wholesalers and various industrial firms will create accounts receivable amounting to 20% of total revenues each year.
  • The purchase of inventory (of both raw material and finished goods) will be approximately 12% of the total cost of sales (not including depreciation, G&A, or R&D expenses).
  • The credit (i.e., accounts payable) offered by suppliers of inventory will be 8% of the total cost of sales (not including depreciation, G&A, or R&D expenses).

Any new investment in net working capital will be made at the beginning of each year in which the revenues are generated. Thus, the total amount of working capital investment needed for the second year will be made at the beginning of the second year (i.e., end of the first year), the incremental amount needed for the third year will be made at the beginning of the third year (i.e., end of the second year), and so on.

• The company faces a 25% tax rate and uses a weighted average cost of capital of 10.5% when analyzing similar projects.

1. Estimate the incremental cash flows from the proposed project. Although the project does not have a fixed termination date, for purposes of project valuation, cash flows generated after year 9 are expected to remain constant at the Year 9 amount.Note you will need to estimate total revenues and total cost of sales for year 10 to determine the amount of net working capital to be invested at the end of year 9 to be able to determine the net cash flow for Year 9. The terminal or horizon value (i.e., present value of cash flows beyond year 9) can be estimated assuming all remaining cash flows after year 9 can be valued as a simple perpetuity based on the net cash flow expected in year 9. Using those cash flows, calculate the net present value and internal rate of return of the proposed project.

Please show work in Excel