A company is considering two mutually exclusive methods, Alternative 1 and Alternative 2, of producing a new product. The relevant data concerning the alternatives appear below. At the end of the useful life of whatever equipment is chosen for Alternatives 1 and 2 the product will be discontinued, therefore all net working capital borrowed from the firm will be recouped (returned) to the parent firm. The company's tax rate and the discount rate are provided. Annual depreciation should be calculated using the Straighline method where the Initial Investment is divided by the expected life of the equipment.
I need help with this problem in excel.
Initial Investment- 60,000 120,000
Net Working Capital 5,000 10,000
Annual Receipts- 50,000 60,000
Annual Disbursements- 20,000 12,000
Annual Depreciation- 15,000 20,000
Project life- 4 6
Expected life- 4 6
Salvage value- 0 0
Tax Rate- 35% Discount Rate 9%
Alternative Alternative $60,000 $120,000 $5,000 $10,000 $50,000 $60,000 $20,000 $12.000 Other assumptions Tax rate 35% Discount rate 9% ate Initial investment Net Working Capital Investment Annual receipts Annual disbursements Annual depreciation Project life (years) Expected life of Equipment Salvage value 6 NPV Values Alt. 1 Alt. 2 Disct. Rates 0% 5% 10% 15% 20% 25% Use Differenced CFs to find: Crossover Rate Use either Alternative with Crossover rate: Crossover NPV $0 $0 0% 5% 10% 15% 20% 25% Alt. 1 - Alt. 2 Analysis Alternative 1 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 1 Internal Net Present Benefit Cost Rate of Value Ratio Return Equiv. Annuity Benefit After-tax cash flow Alternative 2 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 2 Internal Net Present Benefit-Cost Rate of Equiv. Annuity Vl Dati Datu Dont Mutually Exclusive Choice Alternative Alternative $120,000 $20,000 $50,000 $10,000 $200,000 $35,000 $80,000 $12,000 *For Modified Accelerated Cost Recovery System (depreciation) amounts, Use the table for the expected life of the equipment. Then mulitply the percentage for each year of your project timeline times the initial investment. MACRS 7 Yr Year 1 2 3 4 5 6 7 8 Depreciatio 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% Initial investment Net Working Capital Investment Annual receipts Annual disbursements Project life (years) Expected life of Equipment (years) Ending Book value Mkt. Value Equip. Project Term. MACRS 10 Yr 10 Year Depreciatio 3 14.40% 4 5 6 7 8 9 10 11 11.52% 9.22% 7.37% 6.55% 6.55% 6.56% 6.55% 3.28% 10.00% 18.00% Frest of the depreciation $30,000 $45,000 *To find the after-tax cash flow, take the amount you can receive at project termination for the asset, then net the tax consequences from either a capital gain (pay taxes) or a capital loss (receive tax credit for your firm). After-tax Salvage Cash Flow = Market Value of Asset - Tax rate * (Market Value - Book Value) Analysis Alternative 3 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 3 Internal Net Present Benefit Cost Rate of Value Ratio Return Equiv. Annuity Benefit After-tax cash flow Alternative 4 Initial investment Net Working Capital Investment Revenue Operating expenses Alternative 4 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 4 Internal Net Present Benefit-Cost Rate of Value Ratio Return Equiv. Annuity Benefit After-tax cash flow Alternative Alternative $60,000 $120,000 $5,000 $10,000 $50,000 $60,000 $20,000 $12.000 Other assumptions Tax rate 35% Discount rate 9% ate Initial investment Net Working Capital Investment Annual receipts Annual disbursements Annual depreciation Project life (years) Expected life of Equipment Salvage value 6 NPV Values Alt. 1 Alt. 2 Disct. Rates 0% 5% 10% 15% 20% 25% Use Differenced CFs to find: Crossover Rate Use either Alternative with Crossover rate: Crossover NPV $0 $0 0% 5% 10% 15% 20% 25% Alt. 1 - Alt. 2 Analysis Alternative 1 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 1 Internal Net Present Benefit Cost Rate of Value Ratio Return Equiv. Annuity Benefit After-tax cash flow Alternative 2 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 2 Internal Net Present Benefit-Cost Rate of Equiv. Annuity Vl Dati Datu Dont Mutually Exclusive Choice Alternative Alternative $120,000 $20,000 $50,000 $10,000 $200,000 $35,000 $80,000 $12,000 *For Modified Accelerated Cost Recovery System (depreciation) amounts, Use the table for the expected life of the equipment. Then mulitply the percentage for each year of your project timeline times the initial investment. MACRS 7 Yr Year 1 2 3 4 5 6 7 8 Depreciatio 14.29% 24.49% 17.49% 12.49% 8.93% 8.92% 8.93% 4.46% Initial investment Net Working Capital Investment Annual receipts Annual disbursements Project life (years) Expected life of Equipment (years) Ending Book value Mkt. Value Equip. Project Term. MACRS 10 Yr 10 Year Depreciatio 3 14.40% 4 5 6 7 8 9 10 11 11.52% 9.22% 7.37% 6.55% 6.55% 6.56% 6.55% 3.28% 10.00% 18.00% Frest of the depreciation $30,000 $45,000 *To find the after-tax cash flow, take the amount you can receive at project termination for the asset, then net the tax consequences from either a capital gain (pay taxes) or a capital loss (receive tax credit for your firm). After-tax Salvage Cash Flow = Market Value of Asset - Tax rate * (Market Value - Book Value) Analysis Alternative 3 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 3 Internal Net Present Benefit Cost Rate of Value Ratio Return Equiv. Annuity Benefit After-tax cash flow Alternative 4 Initial investment Net Working Capital Investment Revenue Operating expenses Alternative 4 Initial investment Net Working Capital Investment Revenue Operating expenses Depreciation expense EBIT Tax EBIT after tax After Tax Salvage Cash Flow Net Working Capital Investment Alternative 4 Internal Net Present Benefit-Cost Rate of Value Ratio Return Equiv. Annuity Benefit After-tax cash flow
