E G H Spreadsheet Exercise The paragraphs below include information that will be incorporated over a series of assignments during the last 5-6 weeks Damon Corporation, a sports equipment manufacturer, has a machine currently in use that was originally purchased 3 years ago for $125,000. The firm depreciates the machine under MACRS using a 5-year recovery period. Once removal and cleanup costs are taken into consideration, the expected net selling price for the present machine will be $70,000. Damon can buy a new machine for a net price of $170,000 (including installation costs of $15,000). The proposed machine will be depreciated under MACRS using a 5-year recovery period. If the firm acquires the new machine its working capital needs will change: Accounts receivable will increase $15,000, inventory will increase $19,000, and accounts payable will increase $16,000 Earnings before depreciation, interest, and taxes (EBDIT) for the present machine are expected to be $95,000 for each of the successive 5 years.For the proposed machine, the expected EBDIT for each of the next 5 years are $105,000, $110,000, $120,000, $120,000, and $120,000, respectively. The corporate tax rate (T) for the firm is 40%. (Table 4.2 on page 120 contains the applicable MACRs depreciation percentages.) Damon expects to be able to liquidate the proposed machine at the end of its 5-year usable life for $24,000 (after paying removal and cleanup costs). The present machine is expected to net $8,000 upon liquidation at the end of same period. Damon expects to recover its net working capital investment upon termination of the project. The firm is subject to a tax rate of 30% 1 2 3 Calculation of the Initial Investment $ 3 155,000 15.000 $ 170,000 70,000 28 000 2 000 $ 18,000 Installed cost of proposed machine Cost of proposed machine plus: Installation costs Total installed cost-proposed 3 (depreciable value) 3 0 After-tax proceeds from sale of present machine Proceeds from sale of present machine 12 less: Tax on sale of present machine 13 Total after-tax proceeds - present 14 15 Change in net working Capital 16 17 Initial investment Cash Outflow 18 19 Tax on sale of old machine 20 Cost of old machine 125,000 21 MACRS 22 20% from depr sched 25,000 23 year 2 32% 40,000 24 year 3 19% 23,750 25 Book Value 36,250 26 27 Sale price of old machine S 70,000 29 Gain on sale $ 33,750 30 Tax rate 40% 31 Tax Expense 28000 128,000 Change in Working Capital Increase in receivables increase in inventory increase in payables Net working capital year 1 15,000 19,000 16.000 18.000 32 Problem Details Initial Investment Terminal Cach WALD 3 $ 24,000 10132 18.868 4:05 S 40% 19,947 = proceeds - tax CAREFUL H 4 proceeds from sale of proposed machine 5 Proceeds from sale of proposed machine 6 Book value as of end of year 5 7 Net gain 8 Tax on gain 9 Total after-tax proceeds - proposed 10 11 proceeds from sale of present machine 12 Proceeds from sale of present machine 13 Book value as of end of year 5 14 Net gain 15 Tax on gain 16 Total after-tax proceeds - present 17 18 net working capital 19 20 Cash Flow 21 22 42.000 40% = proceeds - tax CAREFUL HE just reverse initial cost change in NW 2 3 4 5 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 TV 7 Initial Investment O 9 Incremental Operating Cash Flows 11 Terminal Cash Flow 12 13 Total Cash Flows $ $ 14 20 21 Enter a CELL REFERENCE to the previous sheets in this workbook 22 Enter the NUMBER from your previous assignment 23 24 HINT: make sure all the signage is appropriate - negative if an outflow and positive if an inflow 25 Also make sure you understand what each formula is doing! 26 27 Net Present Value (NPV) of the cash flows Calculate this number using either a financial calculator or with an Excel formula 28 29 Should Damon Corp invest in the new machine? Enter YES or NO 30 31 E G H Spreadsheet Exercise The paragraphs below include information that will be incorporated over a series of assignments during the last 5-6 weeks Damon Corporation, a sports equipment manufacturer, has a machine currently in use that was originally purchased 3 years ago for $125,000. The firm depreciates the machine under MACRS using a 5-year recovery period. Once removal and cleanup costs are taken into consideration, the expected net selling price for the present machine will be $70,000. Damon can buy a new machine for a net price of $170,000 (including installation costs of $15,000). The proposed machine will be depreciated under MACRS using a 5-year recovery period. If the firm acquires the new machine its working capital needs will change: Accounts receivable will increase $15,000, inventory will increase $19,000, and accounts payable will increase $16,000 Earnings before depreciation, interest, and taxes (EBDIT) for the present machine are expected to be $95,000 for each of the successive 5 years.For the proposed machine, the expected EBDIT for each of the next 5 years are $105,000, $110,000, $120,000, $120,000, and $120,000, respectively. The corporate tax rate (T) for the firm is 40%. (Table 4.2 on page 120 contains the applicable MACRs depreciation percentages.) Damon expects to be able to liquidate the proposed machine at the end of its 5-year usable life for $24,000 (after paying removal and cleanup costs). The present machine is expected to net $8,000 upon liquidation at the end of same period. Damon expects to recover its net working capital investment upon termination of the project. The firm is subject to a tax rate of 30% 1 2 3 Calculation of the Initial Investment $ 3 155,000 15.000 $ 170,000 70,000 28 000 2 000 $ 18,000 Installed cost of proposed machine Cost of proposed machine plus: Installation costs Total installed cost-proposed 3 (depreciable value) 3 0 After-tax proceeds from sale of present machine Proceeds from sale of present machine 12 less: Tax on sale of present machine 13 Total after-tax proceeds - present 14 15 Change in net working Capital 16 17 Initial investment Cash Outflow 18 19 Tax on sale of old machine 20 Cost of old machine 125,000 21 MACRS 22 20% from depr sched 25,000 23 year 2 32% 40,000 24 year 3 19% 23,750 25 Book Value 36,250 26 27 Sale price of old machine S 70,000 29 Gain on sale $ 33,750 30 Tax rate 40% 31 Tax Expense 28000 128,000 Change in Working Capital Increase in receivables increase in inventory increase in payables Net working capital year 1 15,000 19,000 16.000 18.000 32 Problem Details Initial Investment Terminal Cach WALD 3 $ 24,000 10132 18.868 4:05 S 40% 19,947 = proceeds - tax CAREFUL H 4 proceeds from sale of proposed machine 5 Proceeds from sale of proposed machine 6 Book value as of end of year 5 7 Net gain 8 Tax on gain 9 Total after-tax proceeds - proposed 10 11 proceeds from sale of present machine 12 Proceeds from sale of present machine 13 Book value as of end of year 5 14 Net gain 15 Tax on gain 16 Total after-tax proceeds - present 17 18 net working capital 19 20 Cash Flow 21 22 42.000 40% = proceeds - tax CAREFUL HE just reverse initial cost change in NW 2 3 4 5 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 TV 7 Initial Investment O 9 Incremental Operating Cash Flows 11 Terminal Cash Flow 12 13 Total Cash Flows $ $ 14 20 21 Enter a CELL REFERENCE to the previous sheets in this workbook 22 Enter the NUMBER from your previous assignment 23 24 HINT: make sure all the signage is appropriate - negative if an outflow and positive if an inflow 25 Also make sure you understand what each formula is doing! 26 27 Net Present Value (NPV) of the cash flows Calculate this number using either a financial calculator or with an Excel formula 28 29 Should Damon Corp invest in the new machine? Enter YES or NO 30 31