Please complete question in spreadsheet clearly labeling answers part A)-D)

Please Complete each part B) for Year 1-6 separately

Complete the following cash flows with the new machine separately for each year 1-6.

Please do in excel for each year 1-6 separately for part b.

Instructor Tip:

1.The tax on sale = sale proceeds less net book value times the tax rate. 2)Operating cash flows = cash revenues - cash operating expenses - taxes. a) In calculating annual taxes for the existing machine, note that it is depreciated over 5 years and is already 2 years old. Therefore there can only be depreciation expense for 3 more years. b) Ignore any messages you get regarding MACRS. Those couldn't be changed. 3)All amounts for year 6 should be zero. This is leftover from when the problem used MACRS depreciation. 4)To check the incremental cash flows, see if they agree with any of the amounts on the timelines in part d. 5) It should be assumed that the initial increase in working capital is recovered at the end of the project and included in the calculation of terminal cash flows. 6)In part d, the cash flows are all off by one period to the left, and the program won't let me fix this. For the correct answer, assume the timelines are shifted one period to the right.

IntegrativeDetermining relevant cash flows Lombard Company is contemplating the purchase of a new high-speed widget grinder to replace the existing grinder. The existing grinder was purchased 2 years ago at an installed cost of $63,600; it was being depreciated straight-line for 5 years. The existing grinder is expected to have a usable life of 5 more years. The new grinder costs $107,900 and requires $4,500 in installation costs; it has a 5-year usable life and would be depreciated on a straight-line basis. Lombard can currently sell the existing grinder for $69,100 without incurring any removal or cleanup costs. To support the increased business resulting from purchase of the new grinder, accounts receivable would increase by $41,000, inventories by $29,900, and accounts payable by $57,300. At the end of 5 years, the existing grinder would have a market value of zero; the new grinder would be sold to net $28,100 after removal and cleanup costs and before taxes. The firm is subject a 40% tax rate. The estimated earnings before depreciation, interest, and taxes over the 5 years for both the new and the existing grinder are shown in the following table . a. Calculate the initial investment associated with the replacement of the existing grinder by the new one. b. Determine the incremental operating cash inflows associated with the proposed grinder replacement. c. Determine the terminal cash flow expected at the end of year 5 from the proposed grinder replacement. d. Depict on a time line the relevant cash flows associated with the proposed grinder replacement decision. Data Table (Click on the icon located on the top-right corner of the data table below in order to copy its contents into a spreadsheet.) Year 1 Earnings before depreciation, interest, and taxes New grinder Existing grinder $43,100 $26,200 43,100 24,200 43,100 22,200 43,100 20,200 43,100 18,200 2 3 4 5 a. Calculate the initial investment associated with replacement of the old machine by the new one. Calculate the initial investment below: (Round to the nearest dollar.) $ Cost of new asset Installation costs Total cost of new asset Proceeds from sale of old asset Tax on sale of old asset Total proceeds, sale of old asset Change in working capital $ $ Initial investment $ Calculation the cash flows with the new machine and the incremental cash flows below: (Round to the nearest dollar Year 1 Profit before depreciation and taxes Depreciation Net profit before taxes $ $ Taxes $ $ Net profit after taxes Operating cash inflows $ Incremental cash flows $ (Round to the nearest dollar.) c. Determine the terminal cash flow expected at the end of year 5 from the proposed grinder replacement. Calculate the terminal cash flow below: (Round to the nearest dollar.) Proceeds from sale of new asset $ Tax on sale of new asset $ Total proceeds from sale of new asset Change in working capital Terminal cash flow $ d. Depict on a time line the relevant cash flows associated with the proposed grinder replacement decision. The time line for the incremental operating cash inflows is shown below: (Select the best choice below.) O A. Year Year 0 2 3 4. 5 6 Cash flow - $69,276 $14,044 $15,244 $16,444 $22,732 $23,932 $0 B. Year Year 0 1 2 3 4. 5 6 Cash flow - $69,276 $14,044 $15,244 $16,444 $22,732 $23,932 OC. Year Year 0 1 2 3 4 5 6 Cash flow - $69,276 $14,044 $15,244 $16,444 $22,732 $54,392