On January 4, 2023, Michael Scott Co. purchased a truck for deliveries at a cost of $42,000. MS Co. spent $1,800 replacing the tires and $4,500 rebuilding the engine. The vehicle is expected to have a useful life of five years with a residual value of $2,000. The truck is expected to have a useful life of 300,000 miles. Anticipated usage in miles is as follows: Requirements: 2023 2024 38,500 52,900 2025 67,800 2026 84,300 2027 Total 56,500 300,000 1. Calculate total cost of the vehicle. 2. Prepare a depreciation schedule for each depreciation method showing asset cost, depreciation expense, accumulated depreciation, and asset book value. 3. Michael Scott Co. prepares financial statements using the depreciation method that reports the highest net income in the early years of asset use. Using the data from 2023, which method should be used? 4. Create a pivot table using straight-line depreciation data. Use cell references and formulas to fill in all of the green boxes. Use fill handles and absolute references whenever possible to avoid the need to re-type formulas multiple times. Your lab instructor will show you how to use fill handles and absolute references. To fill in the date column, select cells A15 and A16. Click on the fill handle and drag through A19. You should see 12/31/25, 12/31/26, 12/31/27 appear through cell A19. Cost Residual Value Life $ $ 48,300 2,000 5 Annual depreciation $ 9,260 Use the SLN function to calculate annual depreciation Date 1/4/23 12/31/23 Beginning Book Value Depreciation Accumulated Depreciation Ending Book Value $ 300,000 $ 300,000 $ 79,600 12/31/24 $ 79,600 12/31/25 $ 79,600 12/31/26 $ 79,600 12/31/27 $ 79,600 79600 Use cell references and formulas to fill in all of the green boxes. Use fill handles and absolute references whenever possible to avoid the need to re-type formulas multiple times. Your lab instructor will show you how to use fill handles and absolute references. To fill in the date column, select cells A16 and A17. Click on the fill handle and drag through A20. You should see 12/31/25, 12/31/26, and 12/31/27 appear through cell A20. Cost Residual Value Life in Miles Cost per Mile 2023 Miles 2024 Miles 2025 Miles 2026 Miles 2027 Miles Date 1/4/23 12/31/23 12/31/24 Beginning Book Value Depreciation Accumulated Depreciation Ending Book Value