Question

Sullivan ranch corporation has purchased a new tractor and has provided the information related to the purchase. the controller has asked you to calculate the depreciation for the new piece of equipment using different methods. straight-line depreciation, units if production depreciation, and double-declining balance depreciation. use the information included in the excel simulation and EXCEL FUNCTION FORMULAS described below to complete the task.

 Prepare the following Units-of-Production depreciation schedule by entering formulas. Use absolute cell references when appropriate.

 Prepare the following Double-Declining-Balance depreciation schedule by using the Excel DDB FUNCTION (fx) to calculate Depreciation Expense for Years 1-4 in the Depreciation Expense column. Enter formulas or absolute cell references for the remaining cells.

Answer 1

Correct Answer:

Requirement 1: Straight Line Depreciation

	A	B	C=A-B	sum of each year's depreciation expense
Year	Book Value	Depreciation expense	Ending Book Value	Accumulated Depreciation
1	$     150,000.00	$            35,000.00	$       115,000.00	$       35,000.00
2	$     115,000.00	$            35,000.00	$         80,000.00	$       70,000.00
3	$       80,000.00	$            35,000.00	$         45,000.00	$    105,000.00
4	$       45,000.00	$            35,000.00	$         10,000.00	$    140,000.00

Working:

(Straight Line Method)		tractor A
A	Cost	$          150,000.00
B	Residual Value	$            10,000.00
C=A - B	Depreciable base	$          140,000.00
D	Life [in years]	4
E=C/D	Annual SLM depreciation	$            35,000.00

Requirement 2:

	A	B	C =B * Depreciation expense per hour	D=A-C	sum of each year's depreciation expense
Year	Book Value	Usage	Depreciation expense	Ending Book Value	Accumulated Depreciation
1	$        150,000.00	360	$         42,000.00	$    108,000.00	$        42,000.00
2	$        108,000.00	270	$         31,500.00	$       76,500.00	$        73,500.00
3	$          76,500.00	350	$         40,833.33	$       35,666.67	$      114,333.33
4	$          35,666.67	220	$         25,666.67	$       10,000.00	$      140,000.00

Working:

Bus 3 (Unit of Activity)
A	Cost	$          150,000.00
B	Residual Value	$            10,000.00
C=A - B	Depreciable base	$          140,000.00
D	Usage IN Hours	1,200
E = C/D	Depreciation per Hour	$                  116.67

Requirement 3:

Year	Beginning Book Value	Depreciation rate	Depreciation expense	Ending Book Value	Accumulated Depreciation
1	$        150,000.00	50.00%	$         75,000.00	$       75,000.00	$        75,000.00
2	$          75,000.00	50.00%	$         37,500.00	$       37,500.00	$      112,500.00
3	$          37,500.00	50.00%	$         18,750.00	$       18,750.00	$      131,250.00
4	$          18,750.00	Beginning book value - Residual value	$            8,750.00	$       10,000.00	$      140,000.00

Working:

Bus 2 (Double declining method)		Tractor A
A	Cost	$          150,000.00
B	Residual Value	$            10,000.00
C=A - B	Depreciable base	$          140,000.00
D	Life [in years]	4
E=C/D	Annual SLM depreciation	$            35,000.00
F=E/C	SLM Rate	25.000%
G=F x 2	DDB Rate	50.000%

End of answer.

Please give a thumbs-up, it will be highly appreciated.

Thanks.