Overview You work for Troy & Lewis Tax Services LLP, who specialize in preparing individual and corporate tax returns. You have been assigned to work with Emylee Jenson, a tax manager, to help prepare the tax return for a client. Emylee has asked if you would use technology to calculate the tax depreciation schedule and total depreciation for the year for the assets of the client. The client provided you a file that lists information about 13 assets. The information is contained in the file Analytics_mindset_case_studies_TaxTechnologist_Part1.xlsx on the tab labeled "Tax Depreciation". This tab contains the following fields: ID: a unique identifier for each asset. Description: a description of the asset. Date Purchased: the date the asset was purchased. Date Placed in Service: the date the asset was placed in service. Date Retired: the date the asset was retired from service. Property Type: the property type declared by the client. Recovery Period: the recovery period declared by the client. Convention: the client's determination of the depreciation convention. Method: the client's determination of the depreciation method. BOY Basis: the dollar amount of the beginning of year tax basis for the asset. Additions: the dollar amount of any assets that were added during the current fiscal year. Retirements: the dollar amount of any assets that were retired during the year. EOY Basis: the end of year tax basis calculated as BOY Basis + Additions - Retirements. BOY Accumulated Depreciation: the beginning of year accumulated depreciation, which is the sum of the amount of depreciation taken each year the asset has been in service. Given the information provided, Emylee would like you to calculate the correct depreciation amount for each asset, the accumulated depreciation for each asset at the end of the year, and the total depreciation amount of all assets for the year. Assume the current date is January 1, 2022, for performing this case. To perform this task, you will need to use appropriate IRS tax rates. Emylee had an intern digitize the appropriate IRS MACRS tables. These tables are contained in separate tabs of the provided Excel data file. Make sure you refer to these tables for your calculations, even if the IRS MACRS tables have changed in the tax code based on the current date rather than the case date. Required Using Excel as your tool: 1. Add a column to the provided file titled "FinalDepreciation." Use formulas to calculate the amount of depreciation expense for each asset that should be taken for 2021. DO NOT input numbers. Pay attention to whether the asset has been "Retired." Remember from Chapter 5 that we have to make a partial year adjustment if the asset is disposed of before it is fully depreciated. 2. Add a second column to the provided file titled "Accumulated Depreciation." Use formulas to calculate the total accumulated depreciation for each asset as of December 31, 2021 (calculated as "BOY Accumulated Depreciation" plus FinalDepreciation). 3. Calculate the total 2021 depreciation expense of all assets (not total accumulated depreciation). A B C D E F G H J K L M N Date Placed in Recovery ID Description Date Purchased Date Retired Property Type Convention Method BOY Basis Additions Retirements EOY Basis BOY Accumulated Dep Service Period 1001 Helicopter 1/15/18 1/15/18 Personal 5 HY 200DB 285,000 285,000 202,920 1002 Building 1/15/18 1/15/18 7/31/21 Real Nonresidential 39 MM SL 950,000 1003 Bus 2/15/18 2/15/18 8/9/21 Personal 5 HY 200DB 57,000 950,000 57,000 72,096 40,584 Office Furniture 1004 2/15/18 2/15/18 Personal 7 HY 200DB 47,500 47,500 26,728 & Equipment Computer 1005 2/15/18 2/15/18 Personal 5 HY 200DB 19,000 19,000 13,528 Equipment Warehouse 1006 2/15/18 2/15/18 Personal 7 HY 200DB 142,500 142,500 80,185 Equipment 1007 Freezer 2/15/18 2/15/18 12/31/21 Personal 5 HY 200DB 28,500 28,500 20,292 1008 Forklift 2/15/18 2/15/18 1/17/21 Personal 5 HY 200DB 16,150 16,150 11,499 1009 Copier 3/1/18 3/1/18 6/1/21 Personal 5 HY 200DB 7,500 7,500 5,340 1010 Delivery Van 3/4/21 3/4/21 Personal 5 HY 200DB 25,000 25,000 Office Furniture 1011 8/15/21 8/15/21 Personal 7 HY 200DB - 332,500 - 332,500 and Equipment 1012 Computer 3/15/21 3/15/21 Personal 5 HY 200DB 5,500 5,500 1013 Building 9/1/21 9/1/21 Real Nonresidential 39 MM SL 256,000 256,000