Label and show all work ; 2. For Excel work, use Excel functions whenever possible 3. Use data input tables. 4. Clear and complete explanations Stock Pricing: A firm expects to pay a $3 dividend next year. This dividend is expected to grow at 4.2 percent a year for two, then at zero percent for the next four years and then 0.25 percent forever. Find the price of this firms stock if the required return on the stock is 8 percent a year. Capital Budgeting Analysis: A firm plans to produce and sell a new product for five years. First year sales of this product are expected to be 200,000 units and sales are expected to grow at 3.5% per year for years 2 and 3 and then at 4% for years 4 and 5. The selling price per unit of the product is $50 and variable operating costs are 48 percent of sales. The product will have fixed operating costs of $850,000 a year for the first 3 years and then $900,000 per year for the last two years. 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 $5.2 million. The firm expects to be able to sell some old equipment for $1.2 million today (with no depreciation or tax effects on this sale). Depreciation is straight-line and the firms tax-rate is 25%. The new equipment that is purchased will be financed by borrowing the $8 million at a rate of 7.9% per year. Test marketing costs of $400,000 for the new product were incurred over the past two months. The new product is also expected to reduce sales of one of the firms existing products by $520,000 per year. The required return on the investment is 9 percent. Any annual working capital can be ignored. 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 for the base case above. (c) 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 exactly $2.12 million? (e) Conduct a scenario analysis for a scenario where the selling price is $80 per unit, variable operating costs are $7 per unit and starting sales are 250,000 units. There are no other changes