Leave percentages with 1 decimal point. For the Proformas, you can leave as whole dollars. For the TMV and NPV problems, round to the nearest penny. Cells collored yellow are to be solved and cells colored green are to be filled in with information provided in the question You must show the excel formulas, not just the typed in value.
Real pretax salvage Both products: Inflation rate Life (years) Tax rate Real discount rate Output area: Cleaning Solution Product only: Year 1 Year 2 Year 3 Sales Production costs Depreciation EBT Tax Net income OCF NPV Mop Product: Aftertax salvage value Market value Taxes Total Year 1 Year 2 Year 3 Atlantis Waters is expanding into the consumer goods market. It is considering two alternative products. The first is a new cleaning solution for cleaning floors. The second is a new mop, self wringing, for cleaning floors. Both products would be introduced at a price of $8.25 per package in real terms. The cleaning solution is projected to sell 3.2 million packages a year, whereas the self-wringing mop would sell 6.3 million packages a year. Cash costs of production in the first year are expected to be $2.40 per package in real terms for cleaning colution product. Production costs are expected to be $5.11 in real terms for the self-wringing mop. All prices and costs are expected to rise at the general inflation rate of 3 percent. Either product requires further investment. The cleaning solution could be produced using equipment costing $13 million. That equipment would last three years and have no resale value. The machinery required to produce the self-wringing mop would cost $19 million and last three years. The firm expects that equipment to have a $1 million resale value (in real terms) at the end of Year 3 . The firm uses straight-line depreciation and has a tax rate of 21%. The appropriate real discount rate is 5 percent. Calculate the NPV of both investments and make a recommendation based on the NPV. Real pretax salvage Both products: Inflation rate Life (years) Tax rate Real discount rate Output area: Cleaning Solution Product only: Year 1 Year 2 Year 3 Sales Production costs Depreciation EBT Tax Net income OCF NPV Mop Product: Aftertax salvage value Market value Taxes Total Year 1 Year 2 Year 3 Atlantis Waters is expanding into the consumer goods market. It is considering two alternative products. The first is a new cleaning solution for cleaning floors. The second is a new mop, self wringing, for cleaning floors. Both products would be introduced at a price of $8.25 per package in real terms. The cleaning solution is projected to sell 3.2 million packages a year, whereas the self-wringing mop would sell 6.3 million packages a year. Cash costs of production in the first year are expected to be $2.40 per package in real terms for cleaning colution product. Production costs are expected to be $5.11 in real terms for the self-wringing mop. All prices and costs are expected to rise at the general inflation rate of 3 percent. Either product requires further investment. The cleaning solution could be produced using equipment costing $13 million. That equipment would last three years and have no resale value. The machinery required to produce the self-wringing mop would cost $19 million and last three years. The firm expects that equipment to have a $1 million resale value (in real terms) at the end of Year 3 . The firm uses straight-line depreciation and has a tax rate of 21%. The appropriate real discount rate is 5 percent. Calculate the NPV of both investments and make a recommendation based on the NPV
