ABC Company has spent $2 million in the last year in research and development (R&D) for a next generation energy drink for athletes. Although it costs $2 million in the first year's R&D, it is predicted that achieving a target result in this research may take another 4 to 6 years' time. In the meantime, despite huge controversy, the company is planning to introduce the revolutionary pre-version of this drink, called X-TRM, which might cause long-term health hazards for some users due to unknown reasons.

The General Manager (GM) of ABC Company is asking for a detail analysis on X-TRM project. If the project is initiated, it will require anannual expenditure on R&D of 2% of the above amount spent for R&D in the first year.After renovating one existing section of the factory, the production line for X-TRM can bestarted. The project is expected to run for six years when the target drug will be ready to introduce. Required renovation can be conducted immediately at a cost of $220,000 that includes installation cost of new plant and equipment (P&E). The company has decided to capitalise total renovation costs to new P&E. Local distributor of a German company can immediately supply all required parts and accessories of the new P&E for a total charge of $3,400,000 including import duty of $330,000.In addition, for new P&E, transportation cost is estimated to be $80,000. Total costs for P&E would be depreciated using a tax allowable straight line rate of 15% per year.

However, the company can sell P&E at the termination of the project for $400,000.It is also estimated that the new production line will require an initial increased investment of$57,000 in stock (inventories) and $39,000 in debtors (accounts receivables) that are offset by an increase in creditors (accounts payable) of $36,000.The procurement of HR will be one-off cost at the beginning and estimated to be $56,000. The project requires annual quality assurance inspection that will cost $40,000 annually. It is projected that sale of X-TRM would be 56,000 cartons per year when variable operating cost will be 45% of sales. Selling price per carton will be $50. Annual fixed operating cost, excluding depreciation, will be $450,000. Due to increasing demand, it is estimated that the sales will increase by 25% in the fourth year and that will remain the same in the last two years.For increased sales volume, variable operating cost would be 40% of sales.Existing section of the factory, where the new P&E will be installed, is in use by a sub contractor who pays monthly rent of $6,000. This rent income for ABC will discontinue once the new production line X-TRM will commence its operation.The firm has a 13% weighted average cost of capital (WACC) and is subject to a 30% tax rate.The required discounted payback period is 4 years.

The GM hesitates to take the final decision unless all issues are clearly explained. The GM also asks for a detail analysis of cash flows and explanations of results of capital budgeting methods that are usually used in evaluating projects. Required Using Excel Spreadsheet prepare a full analysis to be presented to the GM of ABC evaluating whether the X-TRM project should be started or not. Your analysis should include the following:

Table of cash flows Use of excel formula where appropriate

A written report outlining your recommendation as to whether ABC should proceed.

Justify your recommendation using quantitative and qualitative issues and your analysis of probable risks relating to the project.