Sullivan Ranch Corporation has purchased a new tractor and has provided you with information about the purchase. The controller has asked you to calculate the depreciation for the new piece of equipment using three different methods: straight$-$line, units$-$of$-$production, and double$-$declining$-$balance.

Cost: $$150,000$

Estimated residual value: $$10,000$

Estimated life in years: $4$

Estimated life in hours: $1,200$

Actual hours used:

Year $1360$

Year $2270$

Year $3350$

Year $4220$

Here are some tips for using Excel:

Cell References: Never type a number from an Excel sheet elsewhere in the sheet. Instead, type an equals sign and then click the cell that includes the number. For example, $=$B$5$ would return the number from B$5.$

Basic Math Functions: Use symbols $(+-*/)$ to add, subtract, multiply, and divide in Excel. Do not do any calculations by hand.

SUM Function: This function sums multiple cells. You can enter individual cell references or ranges. For example, $=$SUM$($C$4,$C$5,$C$6)$ and $=$SUM$($C$4$:C$6)$ will both return the value of C$4+$C$5+$C$6.$

SLN Function: This function calculates depreciation expense using the straight$-$line method. The syntax of the SLN function is $=$SLN$($cost$,$salvage,life$).$ Enter the asset's cost, salvage value, and useful life.

DDB Function: This function calculates depreciation expense using the double$-$declining balance method. The syntax of the DDB function is $=$DDB$($cost$,$salvage,life,period$).$ Enter the asset's cost, salvage value, useful life, and the period number you are calculating depreciation for.

Required:

$1.$ Prepare the following depreciation schedule using the SLN function.

You must use absolute cell references $($$$)$ if a cell reference is repeated multiple times across different cells' formulas.

SULLIVAN RANCH CORPORATION

Depreciation Schedule: Straight$-$Line Method

End$-$of$-$Year Amounts

Year Depreciation Expense Accumulated Depreciation Book Value

$1$ $$35,000$ $$35,000$ $$115,000$

$2$ $$35,000$ $$70,000$ $$80,000$

$3$ $$35,000$ $$105,000$ $$45,000$

$4$ $$35,000$ $$140,000$ $$10,000$

Total $$140,000$

$2.$ Prepare the following depreciation schedule using the units$-$of$-$production method. Use the formula from the Chapter $8$ notes.

You must use absolute cell references $($$$)$ if a cell reference is repeated multiple times across different cells' formulas.

SULLIVAN RANCH CORPORATION

Depreciation Schedule: Units$-$of$-$Production Method

End$-$of$-$Year Amounts

Year Depreciation Expense Accumulated Depreciation Book Value

$1$ $$42,000$ $$42,000$ $$108,000$

$2$ $$31,500$ $$73,500$ $$76,500$

$3$ $$40,833$ $$114,334$ $$35,666$

$4$ $$25,667$ $$140,000$ $$10,000$

Total $$140,000$

$3.$ Prepare the following depreciation schedule using the Excel DDB function.

You must use absolute cell references $($$$)$ if a cell reference is repeated multiple times across different cells' formulas.

SULLIVAN RANCH CORPORATION

Depreciation Schedule: Double$-$Declining$-$Balance Method

End$-$of$-$Year Amounts

Year Depreciation Expense Accumulated Depreciation Book Value

$1$ $$75,000$ $$75,000$ $$75,000$

$2$ $$37,500$ $$112,500$ $$37,500$

$3$ $$18,750$ $$131,250$ $$18,750$

$4$ $$9,375$ $$140,625$ $$9,375$

Total $$140,625$