You are working in the accounting department of a manufacturing firm that has purchased a new high-tech piece of machinery. The purchase price of the machinery (not including taxes) was $175,000, sales tax was $15,750, and installation and setup costs totalled $2,250. The CFO of the company would like to understand how various methods of depreciating the asset would affect the company's income in the current and future years. You have been tasked with answering this question. Additional Information: The company's fiscal year = calendar year. The machine was purchased on January 1, 2021. Due to its modular design, the machine can be easily repaired and have its components upgraded/replaced as necessary. Thus, management expects its useful life to be 27 years. Management expects to sell the machine after 27 years for $17,500. The depreciation methods that are being considered are straight line, double declining balance (*with a switch to straight-line when straight-line would result in higher depreciation*), sum-of-the-years-digits, and units of production. The machine is expected to produce 15,000 units of output in each of the first 18 years it is in service, and 7,500 units of output in each of the last 9 years of service. Required: 1 On each of the respective tabs, prepare the machinery depreciation schedule for the respective depreciation method. At a minimum, each schedule should have columns for year, depreciation expense, accumulated depreciation, and net book value. These columns would show each of these amounts for each respective year (depreciation expense for the year, and A/D and net book value at the end of a given year). 2 On the "Summary and Graph" tab, prepare a summary schedule that has a "Year" column, and a column for each depreciation method showing the depreciation in the given year under the respective method of depreciation. 3 On the "Summary and Graph" tab, prepare a line graph that visually presents the depreciation amounts under each method. Include years on the x-axis, $/depreciation amounts on the y-axis, and a line for each method. 4 You will need to hard-code information related to the asset on each tab so that you can reference this information in your tables. Do this on each tab to the side or above the table, not in the table itsself. Do not hard-code amounts in any tables - use formulas and cell referencing. Full credit will not be awarded if amounts are hard-coded in tables. Asset Information Placed in service: Cast: Salvage Value/Residual Value: Useful Life: 1-Jan-21 193,000 27 years a. Straight-line depreciation Cost: Salvage Value: Depreciable Basis: Useful life: Annual Depreciation Expense: Year Depreciation Expense Accumulated Depreciation Net Book Value
