Use Microsoft Excel spreadsheet to calculate the depreciation charge for different depreciation methods and to compute the book value of different assets using different depreciation methods. Also, determine the best alternative using the present worth of after-tax cash flows (ATCF) and perform a sensitivity analysis.
Solve example 17.3 using a different depreciation method: DB (declining balance), DDB (Double declining balance), SLN (straight line), and SYD (Sum of year digits) (each method on a different page). Also, compute the MACRS method. Decide the best alternative (depreciation method) by comparing the after cash flows using the present worth method.
Questions:
1. Which depreciation method gives the higher after-tax cash flow?
2. If revenues decrease each year by a constant of $20,000 (e.g., in year 1 $200,000, in year 2 $180,000, etc.), will your decision of the best depreciation method change? Why or why not? Construct another set of five (5) Microsoft Excel sheets to answer this question.
3. If revenues increase each year by a constant of $20,000 (e.g., in year 1 $200,000, in year 2 $220,000, etc.), will your decision of the best depreciation method change? Why or why not? Construct another set of five (5) Microsoft Excel sheets to answer this question.
TABLE 17-3 Comparison of Taxes and Present Worth of Taxes for Different Depreciation Methods
Classical Double Declining Classical Straight Line Balance MACRS
(1) (2) (3) (4) (5 = 0.35(4) (6) (7) 8=0.357 (6) (10) (11) = 0.35(10)
Year t CFBT,S D.'S TI,S Taxes, $ D,'S TI,S Taxes, $ D.'S TI,S Taxes, $
1 +20,000 10,000 10,000 3,500 20,000 0 0 10,000 10,000 3,500
2 +20,000 10,000 10,000 3,500 12,000 8,000 2,800 16,000 4,000 1,400
3 +20,000 10,000 10,000 3,500 7,200 12,800 4,480 9,600 10,400 3,640
4 +20,000 10,000 10,000 3,500 4,320 15,680 5,488 5,760 14,240 4,984
5 +20,000 10,000 10,000 3,500 2,592 17,408 6,093 5,760 14,240 4,984
6 +20,000 0 20,000 7,000 0 20,000 7,000 2,880 17,120 5,992
Totals 50,000 24,500 46,112 25,861* 50,000 24,500 PWax 18,386 18,549 18,162
*Larger than other values since there is an implied salvage value of $3888 not recovered.