Depreciation Analysis: Straight-Line Method versus Double Declining Balance Method

Completing this activity will help you learn to:

1.	analyze long-term asset depreciation by the straight-line method and the double declining balance method.
2.	create Excel calculations using cell references.
3.	create Excel calculations using cell names.
4.	create a limit for a cell value.
5.	create Excel bar charts.

Case scenario: You are working at an industrial supplier, Great Gadgets. You are planning to purchase a truck for work purposes. The truck costs $60,000 to purchase. You plan to use it for five years. After five years, you can sell it for $6,000. You want to find the depreciation expense per year. You also want to find the book value at the end of each year for the next five years. You want to perform your analysis of two different depreciation methods: the straight-line method and the double declining balance method. Compare your results from the two methods. Create a reusable Excel spreadsheet to perform this analysis.

Required:

Build a spreadsheet that will analyze the depreciation expense by the straight-line method and the double declining balance (DDB) method. Specifically, you need to calculate the straight-line rate, the DDB rate, the book value at the beginning of the year, the book value at the end of the year, and also the depreciation expense each year. Compare the results of the two methods.

HINT: STEP-BY-STEP WALKTHROUGH

Straight-line rate and DDB rate: calculate the rates. You should name cells so that these values can be used in calculations more conveniently.

Straight-line rate	fill in the blank
DDB rate

Straight-line method

For year 1, find the accumulated depreciation at the beginning of the year, the book value at the beginning of the year, the book value at the end of the year, and the depreciation expense for the year. Use cell references and formulas; do not hard-code numbers.

For year 2, find the accumulated depreciation at the beginning of the year, the book value at the beginning of the year, the book value at the end of the year, and the depreciation expense for the year. You should set up the formulas so that they can be copied and pasted for the subsequent years.

For years 35, copy year 2's cells and paste them in the rows for years 35.

If your answer is zero, enter "0".

	Accumulated
	Depreciation at	Book Value at	Book Value at	Depreciation
Year	Beginning of Year	Beginning of Year	End of Year	for Year
1	$fill in the blanks
2
3
4
5

DDB method

For year 1, find the accumulated depreciation at the beginning of the year, the book value at the beginning of the year, the book value at the end of the year, and the depreciation expense for the year. Use cell references and formulas; do not hard-code numbers.

For year 2, find the accumulated depreciation at the beginning of the year, the book value at the beginning of the year, the book value at the end of the year, and the depreciation for the year. Use cell references and formulas; do not hard-code numbers. You should set up the formulas so that they can be copied and pasted for the subsequent years. Make sure there is a lower limit for the year-end book value so that it does not become lower than the residual value.

For years 35, copy year 2's cells and paste them in the rows for years 35.

If your answer is zero, enter "0".

	Accumulated
	Depreciation at	Book Value at	Book Value at	Depreciation
Year	Beginning of Year	Beginning of Year	End of Year	for Year
1	$fill in the blanks
2
3
4
5

Create two bar charts, one for depreciation expense per year and the other for book value at year end. Choose the correct graphs.

A.		B.
C.		D.

The correct graph for depreciation expense per year is?

graph A

graph B

graph C

graph D

A.		B.
C.		D.

The correct graph for book value at year end is?

graph A

graph B

graph C

graph D

.

Compare the straight-line and the DDB methods in each chart. Fill in the blank/circle one.

The (DDB or straight-line) method has a constant depreciation expense over the years. On the other hand, the (DDB or straight-line) method has a (Larger or Smaller) depreciation expense in the earlier years, and the expense (Decreases or Increases) over the year. The book value goes down more quickly for the (DDB or straight-line) method, but at the end of year 5, the two methods have the same book value, which is the residual value. Explain why a company would chose to use each method.

Depreciation Analysis Cost Residual Value $60,000 $6,000 Useful Life 5 years Straight-Line Rate Straight-Line Depreciation Per Year Double-Declining Balance Rate Straight-Line Method Double-Declining-Balance Method 1 2 3 4 5 1 2 3 4 5 Depreciation Analysis Cost Residual Value $60,000 $6,000 Useful Life 5 years Straight-Line Rate Straight-Line Depreciation Per Year Double-Declining Balance Rate Straight-Line Method Double-Declining-Balance Method 1 2 3 4 5 1 2 3 4 5