can you lay this out in excel but most importantly show me how each cell answer was done. not just the answers but please break it down for me in excel. thank you

74 3 P12-23 Modified - use the template below for problem setup including the ***UPDATES*** section for additional Instructions The Carson Distribution Corporation, a firm with a 28% tax rate and a 14% required rate of return or discount rate, is considering a new project that involves the Introduction of a new product. This project is expected to last 5 years and then because this is somewhat of a fad product, it will be terminated. Given the following information, determine the net cash flows associated with the project and the project's the NPV, the Pl and the IRR. Apply the appropriate decision criteria *Cost of new plant and equipment: $12,000,000 Shipping and installation costs: $100,000 (directly related to the new equipment) Unit Sales: 60,000 in year 1, 90,000 in year 2, 120,000 in year 3, 70,000 in year 4,50,000 in year 5. *Sales price per unit: $295/unit in Years 1-4, $250/unit in Yr 5 Variable cost per unit: $150/unit Annual fixed costs: $2,500,000 Working capital requirements: There will be an initial working capital requirement of $1,250,000 to get the project started. For each year, the total investment in networking capital will equal 10% of the dollar value of the incremental sales for that year. Thus, the investment in working capital will increase during Years 1 through 3, then decrease in Year 4. Finally, all working capital will be liquidated at the termination of the project at the end of Years Depreciation Method: Use the simplified straight-line method over 5 years. It is assumed that the plant and equipment will have no salvage value after five years ***UPDATES Assume the following additional information and/or changes to the information provided in P12-23: 1. As of today, Carson Distribution Corporation has incurred a total of $300,000 in market research costs associated with determining the new product's potential demand. 2. The Annual fixed costs starting in Year 1 at $2,500,000 are expected to increase at a rate of 3% annually 3. The introduction of the new product will positively impact the sales of an existing product by the following: - Additional units sold of existing product 5,000, 10,000, 12,000, 6,000 and 3,000 respectively for years 1-5. Agunit sales price of additional existing product units - 5245 Avgunit variable costs of additional existing product units - $135 4. Carson Distribution Corporation expects to use some of its existing manufacturing space for the new project. If Carson did not use such space, it would be able to lease the space to another company for $425,000 per year. 5. The firm would have to borrow $1,000,000 at 6% interest from its local bank resulting in additional interest payments of $60,000 per year. 6. There will be $500,000 in costs to be incurred before product launch for Sales team training, 7 25 20 31 12 Start from scratch or utilize template from a previous problem. 34 35 D. 74 3 P12-23 Modified - use the template below for problem setup including the ***UPDATES*** section for additional Instructions The Carson Distribution Corporation, a firm with a 28% tax rate and a 14% required rate of return or discount rate, is considering a new project that involves the Introduction of a new product. This project is expected to last 5 years and then because this is somewhat of a fad product, it will be terminated. Given the following information, determine the net cash flows associated with the project and the project's the NPV, the Pl and the IRR. Apply the appropriate decision criteria *Cost of new plant and equipment: $12,000,000 Shipping and installation costs: $100,000 (directly related to the new equipment) Unit Sales: 60,000 in year 1, 90,000 in year 2, 120,000 in year 3, 70,000 in year 4,50,000 in year 5. *Sales price per unit: $295/unit in Years 1-4, $250/unit in Yr 5 Variable cost per unit: $150/unit Annual fixed costs: $2,500,000 Working capital requirements: There will be an initial working capital requirement of $1,250,000 to get the project started. For each year, the total investment in networking capital will equal 10% of the dollar value of the incremental sales for that year. Thus, the investment in working capital will increase during Years 1 through 3, then decrease in Year 4. Finally, all working capital will be liquidated at the termination of the project at the end of Years Depreciation Method: Use the simplified straight-line method over 5 years. It is assumed that the plant and equipment will have no salvage value after five years ***UPDATES Assume the following additional information and/or changes to the information provided in P12-23: 1. As of today, Carson Distribution Corporation has incurred a total of $300,000 in market research costs associated with determining the new product's potential demand. 2. The Annual fixed costs starting in Year 1 at $2,500,000 are expected to increase at a rate of 3% annually 3. The introduction of the new product will positively impact the sales of an existing product by the following: - Additional units sold of existing product 5,000, 10,000, 12,000, 6,000 and 3,000 respectively for years 1-5. Agunit sales price of additional existing product units - 5245 Avgunit variable costs of additional existing product units - $135 4. Carson Distribution Corporation expects to use some of its existing manufacturing space for the new project. If Carson did not use such space, it would be able to lease the space to another company for $425,000 per year. 5. The firm would have to borrow $1,000,000 at 6% interest from its local bank resulting in additional interest payments of $60,000 per year. 6. There will be $500,000 in costs to be incurred before product launch for Sales team training, 7 25 20 31 12 Start from scratch or utilize template from a previous problem. 34 35 D