1) Dunedin buys equipment frequently and wants to print a depreciation schedule for each asset's life

Subject:BusinessPrice: Bought3

Share With

1) Dunedin buys equipment frequently and wants to print a depreciation schedule for each asset's life. Review the worksheet called DEPREC that follows these requirements. Since some assets acquired are depreciated by straight-line, others by units of production, and others by double-declining balance, DEPREC shows all three methods. You are to use this worksheet to prepare depreciation schedules for the new machine. 2. Open the file DEPREC from the website for this book at cengagebrain.com. The formulas on this worksheet are somewhat complicated. Be sure to use cell references wherever possible in your formulas instead of numbers. You should find the year numbers in column B helpful for some of the formulas in columns C, D, and E. Your instructor may tell you whether you are to construct your own formulas or use =SLN and =DDB (see Appendix A of Excel Quick for an explanation of these functions). For example, FORMULA1 could be either =(D7- D8)/D9 or =SLN(D7,D8,D9). Assume that all assets acquired will have at least a three-year life. Note that all cells on the worksheet containing zeros have been preprogrammed to perform depreciation calculations. Enter the formulas in the appropriate cells. Does your depreciation total $320,000 under all three methods? If not, correct your error. (Hint: If your double-declining-balance method is oft, check cell E32 where FORMULA12 is located. It should include an =IF statement that will enter a modified calculation of depreciation if Year 4 is the last year of the asset's expected life. See Appendix A of Excel Quick for an explanation of the =IF function. Save the completed file as DEPREC2. Print the worksheet. Also print your formulas using Print the works landscape orientation and fit-to-1 page scaling. Check figure: DDB depreciation for Year 3 (cell E31). $43,750. 3. In the space below, prepare the journal entry to record the depreciation taken in Year 3 under the units of production method. WHAT-IF ANALYSIS 4. To test your formulas, assume the machine purchased had an estimated useful life of three years (20,000, 30,000, and 50,000 hours, respectively). Enter the new information in the Data Section of the worksheet. Does your depreciation total $320,000 under all three methods? There are three common errors made by students completing this worksheet. Let's clear up two of them. One, an asset that has a three-year life should have no depreciation claimed in Year 4. This can be corrected using an =IF statement in Year 4. For example, the correct formula in cell C32 is =IF(B32SD9,0,(07-D8y/D9) or =IF(B32>D9,0,SLN(D7, D8,09)). You may wish to edit what you have already entered rather than retype it. Two, as mentioned in requirement 2, the double-declining balance calculation needs to be modified in the last year of the asset's life. Assuming you have already modified the formula for Year 4 (per instructions in step 2), after the formula for Year 3 also. If you corrected any formulas, test their correctness by trying different estimated useful lives (between 3 and 8) in cell E9. Then reset the Data Section to the original values, save the revised file as DEPREC2, and reprint the worksheet to show the correct formulas. The third common error doesn't need to be corrected in this problem. The general form of the double-declining-balance formula needs to be modified to check the net book value of the asset each year to make sure it does not go below salvage value. =DDB does this automatically, but if you are writing your own formulas, this gets very complicated and is beyond the scope of the problem. 5. A truck was recently purchased for $75,000 with a salvage value of $5,000 and an estimated useful life of eight years or 150,000 miles (24,000 miles per year for the first five years and 10,000 miles per year after that). Enter the new information in the Data Section of the worksheet. Again, make sure the totals for all three methods are in agreement. Print the worksheet. Save this new data as DEPREC5. CHART ANALYSIS 6. With DEPRECS still on the screen, click the Chart sheet tab. This chart shows the accumulated depreciation under all three depreciation methods. Identify below the depreciation method that each represents. Series 1 Series 2 Series 3 When the assignment is complete, close the file without saving it again.