PROBLEM DATA Dunedin Drilling Company recently acquired a new machine at a cost of $350,000. The machine has an estimated useful life 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 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- D8y/D9 or =SLN(D7,D8,09). 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 off, 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 DEPREC Depreciation Data Section Cost of asset Estimated salvage value Estimated useful life in years Estimated life in units (miles, etc.) $350,000 $30,000 4 100,000 1 10 3 Year N 5 3 *Estimated Units 30,000 20,000 40,000 10,000 0 0 0 0 7 2 3 4 5 6 7 8 3 10 3 Answer Section in m B 9 . 1 Depreciation Expense Double Straight Units of Declining Year Line Production Balance 1 FORMULA1 FORMULA5 FORMULAS 2 FORMULA2 FORMULA6 FORMULA 10 3 FORMULA3 FORMULA7 FORMULA11 4 FORMULA FORMULAS FORMULA12 5 0 0 6 0 0 7 0 0 0 0 0 Total $0 $0 $0 2 3 O O 4 5 00 7 8