Instructions: The general objectives of this activity are to develop proficiency in the 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, to determine the best alternative using the present worth of after tax cash flows (ATCF) and to perform a sensitivity analysis. This activity has a grade of 10 points. General Requirements: Read from the textbook the example 17.3. The assignment is based on this example. Also read from the textbook Example 16. 3. This example is a good model to follow to complete the assignment. At the end of this third assignment there is also an explanation of how to construct it in Excel. Have available the Microsoft Excel software program. . Answer and hand in the assignment questions in a Microsoft word sheet and at least fifteen (15) sheets of the Microsoft Excel software. For example: one sheet of Microsoft Excel using the straight line depreciation method, another with the declining balance, the third with double declining balance, the sum of the digits depreciation method and the last one using the MARCS method. When the whole assignment is complete you will have worked 15 Excel sheets. Steps: 1. Start the Microsoft Excel software program. 2. Use the 17.3 data to complete the assignment. 3. The first costs is: (B2) = $550,000 Salvage value is: (B3) = $150,000 Recovery period is: (B4) = 5 Depreciation method is: (C2) = (different in each page) Tax rate is: (C3) = 0.35 4. Enter the data as in the examples 16-3 and 17 -3 in the Microsoft Excel spreadsheet. Remember the data is from example 17. 3. Notes: To format columns with numbers: right click the mouse, select format cell, and number. You also may select currency. The empty spaces must be filled with Os. 5. To compute the depreciation charge select from the upper menu (?X), then select financial, and then DB (declining balance), DDB (Double declining balance), SLN (straight line), SYD (Sum of year digits). Use the MARCS table to compute the depreciation charge. At the end of the assignment are the examples of how to enter the depreciation method. 6. Solve example 17.3 using a different depreciation method (each method in a different page). Also compute the MACRS method. 7. Decide the best alternative (depreciation method) by comparing the after cash flows using the present worth method. Note: In each Microsoft Excel sheet you will produce a sheet with a different depreciation schedule as in example 16.3, and in the same Excel sheet a before and after tax cash flow schedule as in the example 17.3. Total five (5) Microsoft Excel sheets. Questions: 1. Which depreciation method gives the higher after tax cash flow? 2. If revenues decrease each year by a constant of $20,000, for example 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, for example 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. For example 16.3 First cost Salvage Recovery Period Year, t 3 4 5 6 7 8 9 10 Year, t 80,000 10000 10 Declining Balance D(t) $ 15,040.00 $ 64,960.00 $ 12,212.48 $ 52,747.52 $9,916.53 $ 42,830.99 $ 8,052.23 $ 34,778.76 $ 6,538.41 $ 28,240.35 $ 5,309.19 $ 22,931.17 $4,311.06 $ 18,620.11 $ 3,500.58 $ 15,119.53 $ 2,842.47 $ 12,277.06 $ 2,308.09 $ 9,968.97 Sum of Digits SOD book D(t) value DB book value Double declining D (t) $ 16,000.00 $ 12,800.00 $ 10,240.00 $ 8,192.00 $ 6,553.60 $ 5,242.88 $ 4,194.30 $ 3,355.44 $ 2,684.35 $ 737.42 MACRS D(t) DDB book value $ 64,000.00 $ 51,200.00 $ 40,960.00 $ 32,768.00 $ 26,214.40 $ 20,971.52 $ 16,777.22 $ 13,421.77 $ 10,737.42 $ 10,000.00 MACRS book value Year, t 1 2 3 4 5 16 7 8 9 10 SOD book value D(t) $ 12,727.27 $ 67,272.73 $ 11,454.55 $ 55,818.18 $10,181.82 $ 45,636.36 $ 8,909.09 $ 36,727.27 $ 7,636.36 $ 29,090.91 $ 6,363.64 $ 22,727.27 $ 5,090.91 $ 17,636.36 $ 3,818.18 $ 13,818.18 $ 2,545.45 $ 11,272.73 $1,272.73 $ 10,000.00 D(t) 8000 14400 11520 9216 7376 5896 5240 5240 5248 5240 2624 MACRS book value 72000 57600 46080 36864 29488 23592 18352 13112 7864 2624 10 Year, t 1 12 3 4 5 6 7 8 19 10 Straight Line D(t) $ 7,000 $ 7,000 $ 7,000 $ 7,000 $ 7,000 $ 7,000 $ 7,000 $ 7,000 $ 7,000 $ 7,000 SL book value $ 73,000.00 $ 66,000.00 $ 59,000.00 $ 52,000.00 $ 45,000.00 $ 38,000.00 $ 31,000.00 $ 24,000.00 $ 17,000.00 $ 10,000.00 Examples D(1) =DB(C$2,CS3,C$4, A8) D(1)-DDB(C$2,C$3,C$4,48) D(1) SYD(C$2,C$3,C$4,A22) D(1) = SLN(C$2,C$3,C$4) MACRS (1) = 80,000*0.10 EXAMPLE 16.3 Freeport-McMoran Copper and Gold has purchased a new ore grading unit for $80,000. The unit has an anticipated life of 10 years and a salvage value of $10,000. Use the DB and DDB methods to compare the schedule of depreciation and book values for each year. Solve by hand and by spreadsheet. Solution by Hand An implied DB depreciation rate is determined by Equation [16.12]. d=1- 10,000 1/10 80,000 = 0.1877 Note that 0.1877