Shown me if my calculation is wrong and put the right one in place on the excel spreadsheet.

Please enter the following information below into theICAR formatExcel Cash Flow Estimation Worksheet.

1. The equipment has a delivery cost of $115,000. An additional $3,000 is required to install

and test the new system.

2. The new pumping system is classified by the IRS as 7-year property with the same 7-year

estimated service life. For assets classified by the IRS as 7-year property, the Modified

Accelerated Cost Recovery System (MACRS) permits the company to depreciate the

asset over 8 years at the following rates: Year 1 = 14%; Year 2 = 25%; Year 3 = 17%;

Year 4 = 13%; Year 5 = 9%; Year 6 = 9%; Year 7 = 9%; Year 8 = 4%. At the end of its

4 estimated service life of 7 years, the salvage value is expected to be $8,000, with removal

costs of $1,200.

3. The existing pumping system was purchased at $48,000 five years ago and has been

depreciated on a straight-line basis over its economic life of 6 years. If the existing system

is removed from the well and created for pickup, it can be sold for $4,200 before tax. It will

cost $1,000 to remove the system and create it.

4. At the time of replacement (t=0), the firm will need to increase its net working capital

requirements by $6,500 to support inventories.

5. The new pumping system offers lower maintenance costs and frees personnel who would

otherwise have to monitor the system. In addition, it reduces product wastage because of

a higher cooling efficiency. In total, it is estimated that the yearly savings will amount to

$32,000 if the new pumping system is used.

6. FPC's assets are financed by debt and common equity and has a target debt ratio of 30

percent. Its debt carries an interest rate of 6 percent. The firm has paid $2.00 of dividend

per share this year (D0) and expects a constant dividend growth rate of 5 percent per year

in the coming years. The firm's current stock price, P0, is $28.00. The firm uses its overall

weighted average cost of capital in evaluating average risk projects, and the replacement

project is perceived to be of average risk.

7. The firm's federal-plus-state tax rate is 25 percent, and this rate is projected to remain

fairly constant into the future.

SRS S S W NO ;e W N 2 = e e S W o & B c D E F G H | J K FALCONVILLE PUMP COMPANY - CASH FLOW ESTIMATION WORKSHEET Input Data (could be more or less than those listed here) M Cost of NEW equipment 118000 Annual dep. of old equipment 8000 Salvage value new equipment 8000 Old equipment's depreciable life left 1 Cost of old equipment 48000 Old equipment's depreciated years 5 Depreciation of old equipment till date 40000 Annal cost savings 32000 Salvage value of old equipment 4200 Removal cost of old equipment 1000 Tax rate 0.25 Removal cost of new equipment 1200 WACC 0.10575 Net working capital requirement 6500 =0 t=1 t=2 =3 t=4 =5 =6 =7 INVESTMENT OUTLAY 1|Cost of new equipment (118,000.00) 2|Market value of old equipment 4.200.00 3|Tax savings on sale of old equipment 950.00 4|Increase in net working capital (6,500.00) 5|Removal Cost of old equipment (1,000.00) 6|Total Net ivestment (124,000.00) 21 I |OPERATING CASH FLOWS OVER THE PROJECT'S LIFE 22| 7|After tax decrease in cost 32.000.00 32.000.00 32.000.00 32.000.00 32.000.00 32.000.00 32.000.00 23| 8|Depreciation on new machine| (16,520.00)| (29,500.00) (20,060.00)| (15,350.00) (10,620.00)| (10,620.00) (10,620.00) 24| 9|Depreciation on old machine 15.4580.00 2.500.00 11.940.00 16.660.00 21.3580.00 21.380.00 21.3580.00 25| 10|Change in depreciation 16,520.00 29.500.00 20,060.00 15,340.00 10,620.00 10,620.00 10,620.00 26| 11|Tax saving from depreciation 3.870.00 625.00 2.985.00 4.165.00 5.345.00 5.345.00 5.345.00 27 | 12|Net operating cash flow 26,130.00 31,375.00 29.015.00 27,835.00 26,655.00 26,655.00 26,655.00 28 29 III |TERMINAL YEAR CASH FLOWS 30 13 |Estimated salvage value of new machine 5.000.00 31| 14|Tax effect on sale of new machine (820.00) 32 | 15|Return of net working capital 6.500.00 33| 16|Removal cost of new equipment (1,200.00) 34 | 17|Total termination cash flows 5,950.00 35 36 IV |NET CASH FLOWS 37| 18|Net cash flow time line (124,500.00) 26,130.00 31,375.00 29.015.00 27,835.00 26,655.00 26,655.00 26,635.00 \f