Sullivan Ranch Corporation has purchased a new tractor and has provided you with the information related to the purchase. The Controller has asked you to calculate the depreciation for the new piece of equipment using three different methods: Straight-Line depreciation, Units-of-Production depreciation, and Double-Declining-Balance depreciation. Here are some tips for using Excel: Cell Reference: Allows you to refer to data from another cell in the worksheet. If you entered "=85" into a blank cell, the formula would output the value from cell 05. Basic Math Functions: Allow you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add). - (minus sign to subtract)." (asterisk sign to multiply), and / (forward slash to divide). For example, if you entered -=B4+B5" in a blank cell, the formula would add the values from those cells and output the result. SUM Function: Allows you to refer to multiple cells and adds all the values. You can add individual cell references or ranges. If you entered "SUM(C4,C5,C6)" into a blank cell, the formula would output the result of adding those three separate cells. Similarly, if you entered "=SUMC4:C6)". the formula would output the same result of adding those cells. SLN Function: Allows you to calculate the depreciation of an asset using the straight-line depreciation method. The syntax of the SLN function is a SLN(cost, salvage,life)" and it outputs the depreciation for one period. The cost argument is the initial cost of the asset. The salvage argument is the salvage value at the end of the life of the asset. The life argument is the number of periods over which the asset is being depreciated, also called the useful life. DDB Function: Allows you to calculate the depreciation of an asset using the double-declining balance method. The syntax of the DDB function is DDB cost salvage, life,period.[factor)" and it outputs the depreciation for one period. The function must include the first four arguments, and it supports a fifth optional argument. The cost argument is the initial cost of the asset. The salvage argument is the salvage value at the end of the life of the asset. The life argument is the number of periods over which the asset is being depreciated, also called the useful life. The period argument is the period for which you want to calculate the depreciation and must use the same units as the life argument. The (factor argument is the rate at which the balance declines: when omitted, the rate is assumed to be 2 (the double-declining balance method). 1. Use the Open Excel in New Tab button to launch this question. 2. When finished in Excel , use the Save and Return to Assignment button in the lower right to return to Connect. X Am Open Excel in new tab H K K 220 B D E F G 1 1 2 Sullivan Ranch Corporation has purchased a new tractor. The following information is given: 3 4 Cost: $150,000 5 Estimated Residual: $10,000 6 Estimated Life in years: 4 7 Estimated Life in hours: 1200 Actual Hours: 9 Year 1 360 10 Year 2 270 11 Year 3 350 12 Year 4 13 14 Required: 15 1. Prepare the following Straight-Line depreciation schedule by using the Excel SLN function to calculate Depreciation Expense 16 and entering formulas for the remaining cells. Use absolute cell references when appropriate. 17 18 (Use cells A4 to B12 from the given information to complete this question.) 19 20 SULLIVAN RANCH CORPORATION 21 Depreciation Schedule-Straight-Line Method 22 End of year amounts Depreciation Accumulated 23 Year Expense Depreciation Book Value 24 1 25 2 26 3 27 28 Total 29 4 10 on the followbackie Broddons cantarise for cobrouollotroncorban B D E F H H I 2 K 30 2. Prepare the following Activity Based depreciation schedule by entering formulas. Use absolute cell references when appropriate. 31 32 (Use cells A4 to B12 from the given information to complete this question.) 33 34 SULLIVAN RANCH CORPORATION 35 Depreciation Schedule-Activity Based Method 36 End of year amounts Depreciation Accumulated 37 Year Expense Depreciation Book Value 38 1 39 2 3 40 4 41 42 Total 43 44 3. Prepare the following Double-Declining-Balance depreciation schedule by using the Excel DDB function to calculate 45 Depreciation Expense and entering formulas for the remaining cells. Use absolute cell references when appropriate. 46 47 (Use cells A4 to B12 from the given information to complete this question.) 48 51 SULLIVAN RANCH CORPORATION Depreciation Schedule-Double-Declining-Balance Method End of year amounts Depreciation Accumulated Expense Depreciation Book Value 1 2 3 Year 52 53 54 55 56 57 58 59 4 Total 60