Question

Prepare depreciation schedules using Excel - Thanks in advance

G H I J K L M N O P Prepare depreciation schedules using Straight-line, Units-of-production, and double-declining balance depreciation. Make use of Excel capabilities by using as many formulas and functions as possible. Sullivan Ranch Corporation has purchased a new tractor. The following information is given: 1 150,000 10,000 Reference or given data section. 1,200 2 3 Cost: 4 Estimated Residual: 5 Estimated Life in years 6 Estimated Life in hours 7 Actual hours: 8 Year 1 9 Year 2 10 Year 3 11 Year 4 12 360 270 350 220 Prepare a straight-line depreciation schedule using Excel formulas for years 1-4. The schedule should contain for each year: the year number, the depreciation expense, the accumulated depreciation and the book value. Sample header for schedule in answer section Sullivan Ranch Corporation Depreciation Schedule Straight-line method End of year amounts

Answer 1

1. Straight-line depreciation schedule

Depreciation p.a = (Cost - Residual value)/ Estimated Useful life

= ($150,000-$10,000)/ 4 years

= $140,000/4 years

= $35,000

Years	Book Value at the beginning of the year ($)	Depreciation Expenses ($)	Accumulated Depreciation($)	Book value at the end of the year($)
1	150,000.00	35,000.00	35,000.00	115,000.00
2	115,000.00	35,000.00	70,000.00	80,000.00
3	80,000.00	35,000.00	105,000.00	45,000.00
4	45,000.00	35,000.00	140,000.00	10,000.00

2. Depreciation under Units- of- production method :

Depreciation expenses per hour = [(cost of asset - Residual value)] / (Estimated life in Hours)

= [($150,000-$10,000)] / (1,200 hours)

= ($140,000) / 1200 hours

= $116.67

Years	Book Value at the beginning (a)	Number of hours used	Depreciation expenses ($116.67* no.of hours used) (b)	Accumulated Depreciation	Book value at the end of the year ((a) -(b))
1	150,000	360	42,000	42,000	108,000
2	108,000	270	31,500	73,500	76,500
3	76,500	350	40,833	114,333	35,667
4	35,667	220	25,667	140,000	10,000

3. Double - Declining Balance Depreciation Schedule :

Depreciation rate = (1/estimated life)*100

= (1/4years)*100

= 25%

3. Double - declining balance depreciation rate = 2*Depreciation rate = 2 * 25% = 50%

Years	Book value at the beginning	Depreciation Expenses ((a) * 50%)	Accumulated Depreciation Expenses	Book Value at the end of the year
1	150,000	75,000	75,000	75,000
2	75,000	37,500	112,500	37,500
3	37,500	18,750	131,250	18,750
4	18,750	9,375	140,625	9,375