Please complete in Excel
Rules: 1). Remember always (a) data input tables and (b) Excel functions whenever you can do so. 2.) Turn in the results AND a formula listing. 3.) ALL work must be your own. Any cheating fails the course. I reserve the right to have you explain portions of your spreadsheet verbally to me. 4.) You will be graded on content and form. Make sure your work is easy to understand and follow and all numbers are properly formatted. A firm plans to produce and sell a new product for 5 years. First year sales of this product are expected to be 200,000 units and sales are expected to grow at 3% for years 2-3 and then at 4% for years 4-5. Selling price per unit of the product is $30 and variable operating costs are $8 per unit. The product will have fixed operating costs of $350,000 a year for the first 3 years and then $500,000 per year for years 4 and 5. The firm will also need to purchase some new equipment for $8 million in order to produce this product. This equipment has a five-year life and salvage value of $2 million The firm uses straight-line depreciation, and its tax-rate is 35%. The new equipment that is purchased will be financed at a rate of 8% per year. Test marketing costs of $400,000 for the new product were incurred last year. The new product is also expected to reduce sales of one of the firm's existing products by $320,000 per year. The cost of capital (the required return) on this project is 10 percent. Required: (a) Set up a data table and then calculate the expected cash flows for the project. (b) Find the NPV, PI, IRR of this project. (e) Conduct a sensitivity analysis varying the NPV over discount rates starting from 3% and going up to 30%, increasing the rate by 3% each time. Graph the NPV against the discount rates. (d) What starting level (first year) unit sales will be needed to obtain an NPV of 82 million Rules: 1). Remember always (a) data input tables and (b) Excel functions whenever you can do so. 2.) Turn in the results AND a formula listing. 3.) ALL work must be your own. Any cheating fails the course. I reserve the right to have you explain portions of your spreadsheet verbally to me. 4.) You will be graded on content and form. Make sure your work is easy to understand and follow and all numbers are properly formatted. A firm plans to produce and sell a new product for 5 years. First year sales of this product are expected to be 200,000 units and sales are expected to grow at 3% for years 2-3 and then at 4% for years 4-5. Selling price per unit of the product is $30 and variable operating costs are $8 per unit. The product will have fixed operating costs of $350,000 a year for the first 3 years and then $500,000 per year for years 4 and 5. The firm will also need to purchase some new equipment for $8 million in order to produce this product. This equipment has a five-year life and salvage value of $2 million The firm uses straight-line depreciation, and its tax-rate is 35%. The new equipment that is purchased will be financed at a rate of 8% per year. Test marketing costs of $400,000 for the new product were incurred last year. The new product is also expected to reduce sales of one of the firm's existing products by $320,000 per year. The cost of capital (the required return) on this project is 10 percent. Required: (a) Set up a data table and then calculate the expected cash flows for the project. (b) Find the NPV, PI, IRR of this project. (e) Conduct a sensitivity analysis varying the NPV over discount rates starting from 3% and going up to 30%, increasing the rate by 3% each time. Graph the NPV against the discount rates. (d) What starting level (first year) unit sales will be needed to obtain an NPV of 82 million
