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 $55,000 to purchase. You plan to use it for five years. After five years, you can sell it for $5,500. 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:

Download spreadsheet DepreciationCaseData-233654.xlsx

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

1. 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 1 of 2 % DDB rate fill in the blank 2 of 2 %

2. Straight-line method

a. 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.

b. 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.

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

If your answer is zero, enter "0".

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 $55,000 to purchase. You plan to use it for five years. After five years, you can sell it for $5,500. 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: X Download spreadsheet DepreciationCaseData-233654.xIsx Build a spreadsheet that will analyze the depreciation expense by the straight-line method and the double declining balance (DDB) method. Specificall 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 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 1. 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 X\% DDB rate X % 2. Straight-line method a. 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. b. 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. c. For years 3-5, copy year 2's cells and paste them in the rows for years 3-5. c. For years 35, copy year 2 's cells and paste them in the rows for years 35. If your answer is zero, enter " 0 ". 3. DDB method a. 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. b. 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. c. For years 3-5, copy year 2 's cells and paste them in the rows for years 35. If your answer is zero, enter " 0 ". 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 $55,000 to purchase. You plan to use it for five years. After five years, you can sell it for $5,500. 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: X Download spreadsheet DepreciationCaseData-233654.xIsx Build a spreadsheet that will analyze the depreciation expense by the straight-line method and the double declining balance (DDB) method. Specificall 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 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 1. 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 X\% DDB rate X % 2. Straight-line method a. 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. b. 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. c. For years 3-5, copy year 2's cells and paste them in the rows for years 3-5. c. For years 35, copy year 2 's cells and paste them in the rows for years 35. If your answer is zero, enter " 0 ". 3. DDB method a. 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. b. 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. c. For years 3-5, copy year 2 's cells and paste them in the rows for years 35. If your answer is zero, enter " 0