i need help preparing depreciation please explain the whole process step by step .! thank you for your help .

excel applications for accounting principles 4 E

compute depreciation using straight-line, units of production , and double -declining-balance method.! instructions for the rest of the homework are in pag 62 its excel application for accounting principles.

STUDENT NAME P9 DEPRECIATION (DEPREC) LEARNING OBJECTIVES - Compute depreciation using the straight-ine, units of production, and double-declining balance methods. - Leam to use the =SLN,=ODB, and =IF functions. - I interpret patterns of accumulated depreciation over an asset's life. - Create a chart plotting annual depreciation under all three depreciation methods. PAOBLEM DATA Dunedin Driling Compary recently acquired a new machine at a cost of $350,000. The machine has an estimated useful ise of four years or 100,000 hours, and a salvage value of $30,000. This machine will be used 30,000 hours during Year 1, 20,000 hours in Year 2, 40,000 hours in Year 3 , and 10,000 hours in Year 4. REQUIREMENT 1. Dunedin buys equipment frequently and wants to print a depreciation schedule for each assor's life. Review the workshoot colled 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 wobsite 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 =(D7D8) VO or =SLN(D7,D8,D9), Assume that all assets acquired will have at least a three-year Me. 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 off, check cell E32 where FORMULA12 is located. It should include an alF statement that will enter a modified calculation of depreciation it Year 4 is the last year of the asset's expected life. See Appendix A of Excel Quick for an explanation of the =1F function. Say the completed file as DEPREC2. Print the worksheet. Also print your formulas usi 62 landicape-orientation and fit-to-1 page scaling. Check figure: DOB depreclation for Year 3 ( (an Est), 543,750 3. In the space below, prepare the journat entry to tecord the depreciation taken in Year 3 ynder the units of production mothod. WHATAF 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 Yoar 4 . For example, the correct formula in cell C32 is =IF(B32,D9,0,(D7-DB),D9) or =IF(B32>D9,0,SLN(D7,D8,D9)). 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), alter the formula for Year 3 also. If you corrected any formulas, test their correctness by trying ditlerent 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 intormation 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 scroen, 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. TICKLERS (optional) Worksheet. The problem thus far has assumed that assets are depreciated a full year in the year acquired. Normally, depreciation begins in the month acquired. For example, an asset acquired at the beginning of April is depreciated for only nine months in the year of acquisition. Modity the DEPREC2 worksheet to include the month of acquisition as an additional item of input. To demonstrate proper handiling of this factor on the depreciation schedule, modify the formulas for the first two yoars. Some of the formulas may not actually need to be revised. Do not modity the formulas for Years 3 through 8 and ignore the numbers shown in those years. Some will be incorrect as will be some of the totals. Preview the printout to make sure that the worksheet will print neatly on one page, and then print the worksheet. Save the completed file BS DEPRECT. Hint: Insert the month in row 6 of the Data Section specifying the month by a number (e.g., April is the fourth month of the year). Redo the formulas for Years 1 and 2. For the units of production method, assume no change in the estimated hours for both years. Chart. Using the DEPREC5 file, prepare a line chart or XY chart that plots annual depreciation expense under all three depreciation methods. No Chart Data Table is needed; use the range B29 to E36 on the worksheet as a basis for preparing the chart if you prepare an XY chart. Use C29 to E36 if you prepare a line chart. Enter your name somewhere on the chart. Save the file again as DEPREC5. Print the chart. 65 STUDENT NAME P9 DEPRECIATION (DEPREC) LEAANING OBUECTIVES - Compute depreciation using the straightine, units of production, and double decining. balance methods. - Leam to use the = SLN, =DDB, and alF functions. - Identry patterns of depreciation over an assets Ite. - Imarpret parterns of accumulated depreciasion over an assets Ife. - Aher the wockehpet to allow for the cornputation of partial year depreciation. - Create a chart plotting ancual depreciason under all three depreciation methods. PROBLEM DATA Dunedin Dialing Compary eecently acquited a new machine at a cost of $350,000. The machine has an estimatiod usetul ite of four yoars or 100,000 hours, and a salvage value of $30,000. This machine wid be used 30,000 hours during Year 1, 20,000 hours in Year 2, 40,000 hours in Year 3. and 10.000 hours in Year 4. AEOUIREMENT 1. Punedin buys equipenent tequently and warts to print a depreciation schedule for each assec's Hfe. Feview the worksheet called DEPREC that follows these requirements, Since some assets acquited are dapreciated by straight-line, others by units of peoduction, and others by double-decining balance, DEPREC shows all three methods. You are to use this workstieet to prepaste deprecission schedules tor the new machine. 2. Open the Sle DEPREC from the webtite for this book at cengagebrain com, The formulas on this worksheet are somenhat complicated. Be sure to use coll tederences wherever possiblo in your formulas instead of numbers, You should find the yoat numbers in cotumn B heygtul for some of the formulas in colums C. D, and E Your instructor may tell you whether you are to construct your own forenulas or use aSLN and aDDB (see Appendo A of Evcel Quick for an explarasion of these functons). For example, FOFMULA1 could be either =[D7DayD9 or aSLN(D7,00, D9). Assume that all assots acquired will have at lenst a three-year Me. Note that al cells on the wcrksheot containing zetos have been preprogramened to periorm depreciation calculations. Enter the formulas in the appropriate cells. Does your depreciation total $320,000 under all three methods? If noe correct yout error. (Hint: if your double-declining-balance method is off, check oel E32 where FORMULA12 is located. It should include an =lF statemant that Will enter a modified calculabion of deprecation it Yeat 4 is the last year of the asset's. expected life. See Appendx A of Excol Cuick for an explanation of the atF function. Save the completed file as DEPREC2. Print the worksheet. Also print your formulas using