The company is considering a proposal to lead the industry by manufacturing and providing a new and state-of-the-art equipment to provide services to customers. Bonnie considers that a new electric equipment, which is a product from this proposed investment project, would replace most of the existing equipment. A research break- through drawn from Bonnies years of experience gives Bonnie a two-year lead on its competitors. The project proposal is summarised in the following table:

1. Capital expenditure: $10 million for new machine and $1,600,000 for a warehouse extension. The extension has been charged to the project at 50% of the full cost since only about half of the space is needed.

2. Research and development: $1,000,000 spent in 2021.

3. Working capital: initial investment in inventories.

4. Revenue: These figures assume sales of 2,000 equipment in 2023 and 3,000 per year from 2024 to 2026 at a price of $10,000 per unit.

5. Operating costs: These include all direct and indirect costs. Indirect costs (heat, light, power, fringe benefit, etc.) are assumed to be 100% of direct labour costs. Operating costs are forecasted to be $4,000 per unit.

6. Overhead: Allocated marketing and administrative costs, assumed to equal 10% of revenue.

7. Depreciation: Straight-line for 4 years.

8. Interest: Charged on capital expenditure and working capital at its current

   borrowing rate of 15%.

9. Income: Revenue less the sum of research and development, operating costs, overhead, depreciation, and interest.

10. Tax: 30% of income. Loss can be carried forward and deducted from taxable income in the future.

11. Net cash flow: Assumed equal to income less tax. 12. Net present value: Net cash flows are discounted at the companys cost of capital of 20%

Question

Prepare a cash-flow list that you believe is correct. Calculate the NPV, payback period, internal rate of return (IRR) and the profitability index for the project using your revised cash-flow calculation. Assume the acceptable payback period is 3 years, should the company undertake the proposed investment, based on each of the four different evaluation methods?

Note: Can you show the Excel Work

Thanks

\begin{tabular}{|l|c|c|c|} \hline & 2022 & 2023 & 20242026 \\ \hline 1. Capital expenditure & 11,600 & & \\ \hline 2. Research and development & 1000 & & \\ \hline 3. Working capital & 400 & & \\ \hline 4. Revenue & & 20,000 & 30,000 \\ \hline 5. Operating costs & & 8,000 & 12,000 \\ \hline 6. Overhead & & 2,000 & 3,000 \\ \hline 7. Depreciation & & 2,900 & 2,900 \\ \hline 8. Interest & & 1,800 & 1,800 \\ \hline 9. Income & 1,000 & 5,300 & 10,300 \\ \hline 10. Tax & 0 & 1,290 & 3,090 \\ \hline 11. Net cash flows & 13,000 & 4,010 & 7,210 \\ \hline 12. Net present value =2,998 & & & \\ \hline \end{tabular}