Vo_Zutter_Spreadsheet_Chapter_11_Start

.xlsx

School

Portland State University *

*We aren’t endorsed by this school

Course

303

Subject

Accounting

Date

Jun 7, 2024

Type

xlsx

Pages

Uploaded by EarlQuailMaster1108

Spreadsheet Exercise: Chapter 11 (Table 4.2 contains the applicable MACRS depreciation percentages.) To Do Create a spreadsheet similar to Tables 11.1, 11.5, 11.7, and 11.9 to answer the a. Create a spreadsheet to calculate the initial cash flow. b. c. d. Create a spreadsheet to calculate the terminal cash flow associated with t e. Repeat all of the calculations above assuming that the new machine quali Damon Corporation, a sports equipment manufacturer, has a machine currently years ago for $120,000. The firm depreciates the machine under MACRS using removal and cleanup costs are taken into consideration, the expected net selling $70,000. Damon can buy a new machine for a net price of $160,000 (including proposed machine will be depreciated under MACRS using a five-year recover machine its working capital needs will change: Accounts receivable will incre $19,000, and accounts payable will increase $16,000. Earnings before depreciation, interest, and taxes (EBDIT) for the present mach the successive five years. For the proposed machine, the expected EBDIT for e $110,000, $120,000, $120,000, and $120,000, respectively. The corporate tax r Damon expects to be able to liquidate the proposed machine at the end of its fi paying removal and cleanup costs). The present machine is expected to net $8, period. Damon expects to recover its net working capital investment upon term Do not use cell references to cells in rows 19 to 36. For all required answer in row 40. Create a spreadsheet to prepare a depreciation schedule for both the prop machines are depreciated under MACRS using a five-year recovery perio only three years of depreciation remaining. Create a spreadsheet to calculate the periodic cash flows for Damon corp present machine.

Solution Original purchase price 3 years ago $ 120,000 Net selling price of the existing machine $ 70,000 Cost of new machine (including installation costs) $ 160,000 Installation costs $ 15,000 Salvage value of new machine (after 5 years) $ 24,000 Salvage value of existing machine (after 5 years) $ 8,000 Changes to working capital: Increase in accounts receivable $ 15,000 Increase in inventory $ 19,000 Increase in acounts payable $ 16,000 EBDIT per year for the present machine next 5 years $ 95,000 EBDIT for the proposed machine for next five years: Year 1 $ 105,000 Year 2 $ 110,000 Year 3 $ 120,000 Year 4 $ 120,000 Year 5 $ 120,000 Tax 21% Depreciation MACRS 5-year recovery Year Recovery 1 20% 2 32% 3 19% 4 12% 5 12% 6 5% a. Create a spreadsheet to calculate the initial cash flow. Tax on sale of old machine Purchase price of the old machine Accumulated depreciation − Depreciation for year 1

− Depreciation for year 2 − Depreciation for year 3 Total accumulated depreciation Book value of old machine at end of year 3 Selling price of old machine Recaptured depreciation Tax on recaptured depreciation Change in net working capital Increase in accounts receivable $ 15,000 Increase in inventory $ 19,000 Change in current assets Increase in accounts payable $ 16,000 Change in current liabilities Change in working capital Installed cost of new machine Cost of machine $ 145,000 Installation cost $ 15,000 Total installed cost − proposed (depreciable value) After-tax proceeds from sale of old machine Proceeds from sale of old machine $ 70,000 Tax on sale of old machine $ - Total after-tax proceeds Change in net working capital Initial Cash Flow b. Depreciation Schedule With Proposed Machine Year Cost Rate Depreciation 1 $ 160,000 20% 2 $ 160,000 32% 3 $ 160,000 19% 4 $ 160,000 12% Create a spreadsheet to prepare a depreciation schedule for both the prop machines are depreciated under MACRS using a five-year recovery perio only three years of depreciation remaining.

5 $ 160,000 12% 6 $ 160,000 5% Total Depreciation Schedule With Present Machine Year Cost Rate Depreciation 1 $ 120,000 12% 2 $ 120,000 12% 3 $ 120,000 5% 4 5 6 c. Year 1 Year 2 With proposed machine Earnings before depr., int. and taxes $ 105,000 $ 110,000 − Depreciation $ - $ - Earnings before interest and taxes − Taxes 21% Net operating profits after taxes + Depreciation $ - $ - Operating cash flows With present machine Earnings before depr., int. and taxes $ 95,000 $ 95,000 − Depreciation $ - $ - Earnings before interest and taxes − Taxes 21% Net operating profits after taxes + Depreciation $ - $ - Periodic cash flows d. Create a spreadsheet to calculate the terminal cash flow associated with t Create a spreadsheet to calculate the periodic cash flows for Damon Corp present machine.

After-tax proceeds from sale of proposed machine Proceeds from sale of proposed machine Book value as of end of year 5 Net gain Tax on gain 21% Total after-tax proceeds - proposed After-tax proceeds from sale of present machine Proceeds from sale of present machine Book value as of end of year 5 Net gain Tax on gain 21% Total after-tax proceeds - present Change in net working capital Terminal Cash Flow Requirements 1 2 3 4 5 6 7 8 9 10 11 12 In cell G52 , by using cell references to the given data, calculate the depre In cell G53 , by using cell references to the given data, calculate the depre In cell G54 , by using cell references to the given data, calculate the depre In cell H55 , by using cell references to the given data, calculate the total machine. In cell H57 , by using cell references to the given data, calculate the book year 3. In cell H59 , by using cell references to the given data, calculate the recap In cell H60 , by using cell references to the given data, calculate the tax o In cell H65 , by using cell references to the given data, calculate the chan In cell H68 , by using cell references to the given data, calculate the chan In cell G71 , by using cell references to the data in cells G21 and G72 , ca In cell H73 , by using cell references to the data in cells G71 and G72 , ca machine. In cell H77 , by using cell references to the given data, calculate the total machine. In cell H79 , by using cell references to the given data, calculate the initia In cell range F85:F90 , by using cell references to the given data, calculat machine.

13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 In cell F91 , by using cell references to the given data, calculate the total In cell range F95:F100 , by using cell references to the given data, calcul machine. Enter =0 in those years without depreciation. In cell F101 , by using cell references to the given data, calculate the total In cell range F106:K106 , the earnings before depreciation, interest and ta years 1:6 are shown. In cell range F107: K107 , the depreciation expense with the proposed m In cell range F108:K108 , by using cell references to the given data, calcu for years 1:6 of the new machine. In cell range F109:K109 , by using cell references to the given data, calcu machine. In cell range F110:K110 , by using cell references to the given data, calcu for years 1:6 of the new machine. In cell range F111:K111 , the depreciation expense with the proposed ma In cell range F112:K112 , by using cell references to the given data, calcu of the new machine. In cell range F115:K115 , the earnings before depreciation, interest and ta 1:6 are shown. In cell range F116: K116 , the depreciation expense with the current mac In cell range F117:K117 , by using cell references to the given data, calcu for years 1:6 of the old machine. In cell range F118:K118 , by using cell references to the given data, calcu machine. In cell range F119:K119 , by using cell references to the given data, calcu for years 1:6 of the old machine. In cell range F120:K120 , the depreciation expense with the current mach In cell range F121:K121 , by using cell references to the given data, calcu of the old machine. In cell H128 , by using cell references to the given data, calculate the net In cell H129 , by using cell references to the given data, calculate the tax In cell I130 , by using cell references to the given data, calculate the total In cell H135 , by using cell references to the given data, calculate the net In cell H136 , by using cell references to the given data, calculate the tax In cell I137 , by using cell references to the given data, calculate the total In cell I139 , by using cell references to the given data, calculate the term Now move on to spreadsheet Chapter 11.e . Repeat all of the calculation qualifies for 100% bonus depreciation in spreadsheet Chapter 11.e. Reme among the previous steps and spreadsheet Chapter 11.e must be complete

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version