ANSWER ALL
The Shome Corporation is considering a new project that involves the introduction of a new product. The firm in in the 34 percent marginal tax bracket and has a 15 percent required rate of return or discount rate for new investments. This project is expected to last five years, and then, because this is somewhat of a fad project, it will be terminated. Given the following information, determine the net cash flows associated with the project and the project's NPV, profitability index, and internal rate of return. Apply the appropriate decision criteria. Cost of new plant and equipment: $6,900,000 Shipping and installation costs: $ 100,000 Sales price per unit: $250/unit in Years 1-4, $200/unit in Year 5 Variable cost per unit: $130/unit Annual fixed costs: $300,000 Working capital requirements: There will be an initial working capital requirement of $100,000 just to get production started. For each year, the total investment in net working capital will be equal to 10 percent of the dollar value of 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 is liquidated at the termination of the project at the end of Year 5. The depreciation method: Use the simplified straight-line method over five years. It is assumed that the plant and equipment will have no salvage value after five years. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Given data: Initial capital expenditure ($6,900,000.00) Shipping and installation costs ($100,000.00 Life of the initial expenditure 5 Marginal tax rate 34.00% Discount rate 15.00% Net working capital (% of Sales) 10.00% Net working capital investment (0) ($100,000.00 Fixed costs per year ($300,000.00) Sales price (1-4) $250.00 Sales price (5) $200.00 Variable cost of product ($130.00) Year 0 Units sold Revenues Variable costs Fixed costs Gross profit Depreciation Net opearting income Income taxes Net income Cash flow Year 1 80,000 $20,000,000.00 ($10,400,000.00) ($300,000.00) $9,300,000.00 ($1,400,000.00) $7,900,000.00 ($2,686,000.00) $5,214,000.00 $6,614,000.00 Year 2 100,000 $25,000,000.00 ($13,000,000.00) ($300,000.00) $11,700,000.00 ($1,400,000.00 $10,300,000.00 ($3,502,000.00 $6,798,000.00 $8,198,000.00 Year 3 120,000 $30,000,000.00 ($15,600,000.00) ($300,000.00) $14,100,000.00 ($1,400,000.00 $12,700,000.00 ($4,318,000.00) $8,382,000.00 $9,782,000.00 Year 4 70,000 $17,500,000.00 ($9,100,000.00) ($300,000.00) $8,100,000.00 ($1,400,000.00) $6,700,000.00 ($2,278,000.00) $4,422,000.00 $5,822,000.00 Year 5 70,000 $14,000,000.00 ($9,100,000.00) ($300,000.00 $4,600,000.00 ($1,400,000.00) $3,200,000.00 ($1,088,000.00 $2,112,000.00 $3,512,000.00 Additional net working capital Capital expenditure Free cash flow NPV Profitability Index IRR The Shome Corporation is considering a new project that involves the introduction of a new product. The firm in in the 34 percent marginal tax bracket and has a 15 percent required rate of return or discount rate for new investments. This project is expected to last five years, and then, because this is somewhat of a fad project, it will be terminated. Given the following information, determine the net cash flows associated with the project and the project's NPV, profitability index, and internal rate of return. Apply the appropriate decision criteria. Cost of new plant and equipment: $6,900,000 Shipping and installation costs: $ 100,000 Sales price per unit: $250/unit in Years 1-4, $200/unit in Year 5 Variable cost per unit: $130/unit Annual fixed costs: $300,000 Working capital requirements: There will be an initial working capital requirement of $100,000 just to get production started. For each year, the total investment in net working capital will be equal to 10 percent of the dollar value of 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 is liquidated at the termination of the project at the end of Year 5. The depreciation method: Use the simplified straight-line method over five years. It is assumed that the plant and equipment will have no salvage value after five years. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Given data: Initial capital expenditure ($6,900,000.00) Shipping and installation costs ($100,000.00 Life of the initial expenditure 5 Marginal tax rate 34.00% Discount rate 15.00% Net working capital (% of Sales) 10.00% Net working capital investment (0) ($100,000.00 Fixed costs per year ($300,000.00) Sales price (1-4) $250.00 Sales price (5) $200.00 Variable cost of product ($130.00) Year 0 Units sold Revenues Variable costs Fixed costs Gross profit Depreciation Net opearting income Income taxes Net income Cash flow Year 1 80,000 $20,000,000.00 ($10,400,000.00) ($300,000.00) $9,300,000.00 ($1,400,000.00) $7,900,000.00 ($2,686,000.00) $5,214,000.00 $6,614,000.00 Year 2 100,000 $25,000,000.00 ($13,000,000.00) ($300,000.00) $11,700,000.00 ($1,400,000.00 $10,300,000.00 ($3,502,000.00 $6,798,000.00 $8,198,000.00 Year 3 120,000 $30,000,000.00 ($15,600,000.00) ($300,000.00) $14,100,000.00 ($1,400,000.00 $12,700,000.00 ($4,318,000.00) $8,382,000.00 $9,782,000.00 Year 4 70,000 $17,500,000.00 ($9,100,000.00) ($300,000.00) $8,100,000.00 ($1,400,000.00) $6,700,000.00 ($2,278,000.00) $4,422,000.00 $5,822,000.00 Year 5 70,000 $14,000,000.00 ($9,100,000.00) ($300,000.00 $4,600,000.00 ($1,400,000.00) $3,200,000.00 ($1,088,000.00 $2,112,000.00 $3,512,000.00 Additional net working capital Capital expenditure Free cash flow NPV Profitability Index IRR
