here's the template: pleases use the template, and show work

XYZ Co. is operating at full capacity. It can only meet the increased demand for its product by investing in new plant and equipment. It is considering a proposal for an investment of $4.0 million in fixed assets, which would allow the firm to increase its annual production by 200,000 units. The estimated sale price of the each unit is $25, and the variable cost of each additional unit is $15. Insurance, maintenance, license fees and other fixed costs associated with the new assets would amount to $100,000 per year. The assets' useful life is estimated at four (4) years, and their residual value at the end of four years is antipated to be at least $400,000. In addition to the investment in fixed assets, the increase in sales would require additional working capital in the form of inventory and accounts receivable, partially funded by higher accounts payable. This investment in working capital is estimated at $500,000. For tax purposes, the new assets fall into the three-year MACRS asset class. The company's tax rate is 30%. The company's cost of funds is 12%. Required: Using the templates empoyed in class, compute the net present value of this proposed investment. (See the illustration presented in class about the proposed factory in Ohio and the Solutions to Chapter 10 Problem nos. 13 & 19 if you are in doubt about the templates to use.) Modified Accelerated Cost Recover System (MACRS) Depreciation Rates Applicable to Asset Classes Recovery Year Fifteen- Three-Year Five-Year Seven-Year Ten-Year Year Asset Class Asset Class Asset Class Asset Class Asset Class 1 2 3 0.3333 0.4445 0.1481 0.0741 0.2000 0.3200 0.1920 0.1152 0.1152 0.0576 4 0.1429 0.2449 0.1749 0.1249 0.0893 0.0892 0.0893 0.0446 5 6 0.1000 0.1800 0.1440 0.1152 0.0922 0.0737 0.0655 0.0655 0.0656 0.0655 0.0328 7 8 0.0500 0.0950 0.0855 0.0770 0.0693 0.0623 0.0590 0.0590 0.0591 0.0590 0.0591 0.0590 0.0591 0.0590 0.0591 0.0295 9 10 11 12 13 14 15 16 Problem 19 Step 3: After tax residual value Facts 470.000 4 80,000 190,000 Estimated residual end of year 4 Tax basis end of year 4 Tax gain/loss) Tax rate Tax benefit $80.000 -81,216 -$1,216 35% $426 Capital investment Estimated life in years Estimated residual value Annual reduction of operating expense Working capital investment: Year 1 Years 2 to 4 Tax depreciation method Tax rate Cost of capital Estimated residual end of year 4 Tax benefit After tax residual value $80.000 $426 $80.426 20,000 2.500 5 year MACRS 35% 9% Step 4: Net present value calculation Year 0 1 2 3 4 Step 1: Tax depreciation schedule Depreciation Asset Cost Depn Rate Deduction Tax Basis Cap. Inv. -470.000 Wkg capit -20,000 -2.500 -2,500 -2.500 OCF 156,400 176.140 155.084 -490,000 153,900 173.640 152,584 PVIF 1.0000 0.9174 0.8417 0.7722 PVS -490.000 141,193 146, 149 117,823 Year Total 80,426 27.500 142,450 250,376 0.7084 177,373 NPV 92,537 0 1 2 2 3 4 5 6 470.000 470.000 470.000 470.000 470.000 470.000 0.2000 0.3200 0.1920 0.1152 0.1152 0.0576 94.000 150.400 90, 240 54, 144 54.144 27.072 470.000 376.000 225,600 135, 360 81.216 27,072 0 IRR 16.736% Step 2: Pro Forma Income Statement and Operating Cash Flow Computation Year 1 2 3 4 Savings Depreciation EBIT Income tax NIAT Add depn OCF 190,000 -94.000 96,000 33,600 62,400 94,000 156, 400 190.000 -150,400 39,600 13.860 25.740 150.400 176, 140 190,000 -90.240 99.760 34,916 64,844 90.240 155,084 190.000 -54,144 135.856 47.550 88.306 54,144 142, 450 EBIT+ Dp-Tx 156,400 176,140 155,084 142,450 XYZ Co. is operating at full capacity. It can only meet the increased demand for its product by investing in new plant and equipment. It is considering a proposal for an investment of $4.0 million in fixed assets, which would allow the firm to increase its annual production by 200,000 units. The estimated sale price of the each unit is $25, and the variable cost of each additional unit is $15. Insurance, maintenance, license fees and other fixed costs associated with the new assets would amount to $100,000 per year. The assets' useful life is estimated at four (4) years, and their residual value at the end of four years is antipated to be at least $400,000. In addition to the investment in fixed assets, the increase in sales would require additional working capital in the form of inventory and accounts receivable, partially funded by higher accounts payable. This investment in working capital is estimated at $500,000. For tax purposes, the new assets fall into the three-year MACRS asset class. The company's tax rate is 30%. The company's cost of funds is 12%. Required: Using the templates empoyed in class, compute the net present value of this proposed investment. (See the illustration presented in class about the proposed factory in Ohio and the Solutions to Chapter 10 Problem nos. 13 & 19 if you are in doubt about the templates to use.) Modified Accelerated Cost Recover System (MACRS) Depreciation Rates Applicable to Asset Classes Recovery Year Fifteen- Three-Year Five-Year Seven-Year Ten-Year Year Asset Class Asset Class Asset Class Asset Class Asset Class 1 2 3 0.3333 0.4445 0.1481 0.0741 0.2000 0.3200 0.1920 0.1152 0.1152 0.0576 4 0.1429 0.2449 0.1749 0.1249 0.0893 0.0892 0.0893 0.0446 5 6 0.1000 0.1800 0.1440 0.1152 0.0922 0.0737 0.0655 0.0655 0.0656 0.0655 0.0328 7 8 0.0500 0.0950 0.0855 0.0770 0.0693 0.0623 0.0590 0.0590 0.0591 0.0590 0.0591 0.0590 0.0591 0.0590 0.0591 0.0295 9 10 11 12 13 14 15 16 Problem 19 Step 3: After tax residual value Facts 470.000 4 80,000 190,000 Estimated residual end of year 4 Tax basis end of year 4 Tax gain/loss) Tax rate Tax benefit $80.000 -81,216 -$1,216 35% $426 Capital investment Estimated life in years Estimated residual value Annual reduction of operating expense Working capital investment: Year 1 Years 2 to 4 Tax depreciation method Tax rate Cost of capital Estimated residual end of year 4 Tax benefit After tax residual value $80.000 $426 $80.426 20,000 2.500 5 year MACRS 35% 9% Step 4: Net present value calculation Year 0 1 2 3 4 Step 1: Tax depreciation schedule Depreciation Asset Cost Depn Rate Deduction Tax Basis Cap. Inv. -470.000 Wkg capit -20,000 -2.500 -2,500 -2.500 OCF 156,400 176.140 155.084 -490,000 153,900 173.640 152,584 PVIF 1.0000 0.9174 0.8417 0.7722 PVS -490.000 141,193 146, 149 117,823 Year Total 80,426 27.500 142,450 250,376 0.7084 177,373 NPV 92,537 0 1 2 2 3 4 5 6 470.000 470.000 470.000 470.000 470.000 470.000 0.2000 0.3200 0.1920 0.1152 0.1152 0.0576 94.000 150.400 90, 240 54, 144 54.144 27.072 470.000 376.000 225,600 135, 360 81.216 27,072 0 IRR 16.736% Step 2: Pro Forma Income Statement and Operating Cash Flow Computation Year 1 2 3 4 Savings Depreciation EBIT Income tax NIAT Add depn OCF 190,000 -94.000 96,000 33,600 62,400 94,000 156, 400 190.000 -150,400 39,600 13.860 25.740 150.400 176, 140 190,000 -90.240 99.760 34,916 64,844 90.240 155,084 190.000 -54,144 135.856 47.550 88.306 54,144 142, 450 EBIT+ Dp-Tx 156,400 176,140 155,084 142,450