Using Excel to prepare depreciation schedules The Fraser Riv

Using Excel to prepare depreciation schedules The Fraser River Company has purchased a new piece of factory equipment on January 1, 2018, and wishes to compare three depreciation methods: straight-line, double-declining-balance, and units-of-production. The equipment costs $400,000 and has an estimated useful life of four years, or 8,000 machine hours After four years, the equipment will have a residual value of $20,000. Requirements Use Excel to prepare depreciation schedules for straight-line, double-declining-balance, and units-of- production methods. 2 Prepare a second depreciation schedule for double-declining-balance method, using the Excel function DDB. The DDB function cannot be used in the last year of the asset\'s useful life. At December 31, 2018, Fraser River is trying to determine if it should sell the factory equipment. Fraser River will only sell the factory equipment if the company earns a gain of at least $6,000. For each of the depreciation methods, what is the minimum amount that Fraser River will sell the factory equipment for in order to have a gain of $6,000?

Solution

CALCULATION OF THE DEPCIATION IN ALL METHOD CALCULATION OF THE DEPRECIATION AS PER STRAIGHT LINE METHOD Purchase Cost of Machine $                  4,00,000 Less: Salvage Value $                      20,000 Net Value for Depreciation $                  3,80,000 Usefule life of the Assets 4 years Depreciation per year = Value for Depreciation / 4 years = $                      95,000 CALCULATION OF THE DEPRECIATION AS PER ACTIVITY METHOD UNITS OF PRODUCTION Purchase Cost of Machine $                  4,00,000 Less: Salvage Value $                      20,000 Net Value for Depreciation $                  3,80,000 Expected to produce units $                        8,000 Hrs Depreciation per unit = $                              48 Per Hrs ($ 380,000 / 8,000 Units) Depreciation for Year 1 = (2400 units * $ 47.50) $                  1,14,000 Depreciation for Year 2 = (1800 units * $ 47.50) $                      85,500 Depreciation for Year 1 = (2480 units * $ 47.50) $                  1,17,800 Depreciation for Year 1 = (1320 units * $ 47.50) $                      62,700 CALCULATION OF THE DEPRECIATION AS PER SUM OF DOUBLE DECLINE METHOD Purchase Cost of Machine $                  4,00,000 Useful Life = 4 years Depreciation per year = $                  1,00,000 (Purchase price / Useful life) Rate of Depreciation = 0.25 or 25% (Depreication / Purchase price ) Double decline deprection rate = 25% * 2 = $                                1 Purchase Value of the Equipment $                  4,00,000 Depreciation for the year 1 @ 50% = $                  2,00,000 Closing balance for the year 1 $                  2,00,000 Opening Balance for the year 2 $                  2,00,000 Depreciation for the year 2 @ 50% = $                  1,00,000 Closing balance for the year 2 $                  1,00,000 Opening Balance for the year 3 $                  1,00,000 Depreciation for the year 3 @ 50% = $                      50,000 Closing balance for the year 3 $                      50,000 Opening Balance for the year 4 $                      50,000 Depreciation for the year 4 @ 50% = $                      25,000 Closing balance for the year 4 $                      25,000 SELLING PRICE AT THE END OF YEAR 1 THAT RESULT IN GAIN OF $ 6,000 Straight line   unit of production double decline Book Value at the year end of dec 31 ,2018 $                  3,05,000 $                      2,86,000 $           2,00,000 Add: Profit required from the each method $                        6,000 $                            6,000 $                 6,000 Selling price for Earn profit of $ 6,000 $                  3,11,000 $                      2,92,000 $           2,06,000