How to do in Excel with formulas:

NPV and Project Reevaluation with Taxes, Straight-Line Depreciation

In 2015, the Bayside Chemical Company prepared the following analysis of an investment proposal

for a new manufacturing facility:

Predicted

Cash

Inflows

(outflows)

(A)

Year(s)

of Cash

Flows

(B)

12%

Present

Value

Factor

(C)

Present

Value of

Cash Flows

(A) 3 (C)

Initial investment

Fixed assets . . . . . . . . . . . . . . . . . . . . . . . . . . $(810,000) 0 1.00000 $ (810,000)

Working capital . . . . . . . . . . . . . . . . . . . . . . . . (100,000) 0 1.00000 (100,000)

Operations

Annual taxable income

without depreciation . . . . . . . . . . . . . . . . 310,000 1-5 3.60478 1,117,482

Taxes on income ($310,000 3 0.40) . . . . . . . . (124,000) 1-5 3.60478 (446,993)

Depreciation tax shield . . . . . . . . . . . . . . . . . . 64,800* 1-5 3.60478 233,590

Disinvestment

Site restoration . . . . . . . . . . . . . . . . . . . . . . . . 80,000 5 0.56743 (45,394)

Tax shield of restoration ($80,000 3 0.40) . . . 32,000 5 0.56743 18,158

Working capital . . . . . . . . . . . . . . . . . . . . . . . . 100,000 5 0.56743 56,743

Net present value of all cash flows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 23,586

*Computation of depreciation tax shield:

Annual straight-line depreciation ($810,000 4 5) .............. $162,000

Tax rate .............................................. 3 0.40

Depreciation tax shield .................................. $ 64,800

Because the proposal had a positive net present value when discounted at Bayside's cost of capi-

tal of 12 percent, the project was approved; all investments were made at the end of 2016. Shortly

after production began in January 2017, a government agency notified Bayside of required additional

expenditures totaling $200,000 to bring the plant into compliance with new federal emission regu-

lations. Bayside has the option either to comply with the regulations by December 31, 2017, or to

sell the entire operation (fixed assets and working capital) for $250,000 on December 31, 2017. The

improvements will be depreciated over the remaining four-year life of the plant using straight-line

depreciation. The cost of site restoration will not be affected by the improvements. If Bayside elects

to sell the plant, any book loss can be treated as an offset against taxable income on other operations.

This tax reduction is an additional cash benefit of selling.

Required

a. Should Bayside sell the plant or comply with the new federal regulations? To simplify calculations,

assume that any additional improvements are paid for on December 31, 2017.