Sullivan Ranch Corporation has purchased a new tractor. The following information is given $ $ 150,000 10,000 Cost: Estimated Prepare the following Double Declinin Balance depreciation schedule by using the Excel DDB FUNCTION() to calculate Depreciati Sullivan Ranch Corporation has purchased a new tractor. The following information is given: – Cost: $150,000 – Estimated Residual: $10,000 – Estimated Life in years: – Estimated Life in hours: – Actual Hours: Year 1: 1200 Year 2: 360 Year 3: 270 Year 4: 350 Year 5: 220 Prepare the following Straight Line depreciation schedule by using the Excel SIN FUNCTION (fx) to calculate the Depreciation Expense for Years 1-4 in the Depreciation Expense column. Enter formulas or absolute cell references for the remaining cells. SULLIVAN RANCH CORPORATION Depreciation Schedule Straight Line Method End of year amounts: – Depreciation Expense – Accumulated Depreciation – Book Value – Total Prepare the following Units-of-Production depreciation schedule by entering formulas. Use absolute cell references when appropriate. SULLIVAN RANCH CORPORATION Depreciation Schedule Units-of-Production Method End of year amounts: – Depreciation Expense – Accumulated Depreciation – Book Value – Total Prepare the following Double Declining Balance depreciation schedule by using the Excel DDB FUNCTION() to calculate Depreciation Expense in the Depreciation Expense column. Enter formulas or absolute cell references for the remaining cells. SULLIVAN RANCH CORPORATION Depreciation Schedule Double Declining Balance Method End of year amounts: – Depreciation Expense – Accumulated Depreciation – Book Value
The Correct Answer and Explanation is:
Given:
- Cost: $150,000
- Residual (Salvage) Value: $10,000
- Estimated Useful Life: 5 years
- Estimated Life in Hours: 2,400 hours (sum of given actual hours)
- Actual Hours:
- Year 1: 1200
- Year 2: 360
- Year 3: 270
- Year 4: 350
- Year 5: 220
🔷 Straight-Line Depreciation (SL)
Excel Formula:
- Annual Depreciation Expense =
(Cost - Residual Value) / Useful Life=($150,000 - $10,000)/5 = $28,000per year
| Year | Depreciation Expense | Accumulated Depreciation | Book Value |
|---|---|---|---|
| 1 | $28,000 | $28,000 | $122,000 |
| 2 | $28,000 | $56,000 | $94,000 |
| 3 | $28,000 | $84,000 | $66,000 |
| 4 | $28,000 | $112,000 | $38,000 |
| 5 | $28,000 | $140,000 | $10,000 |
🔷 Units-of-Production Method
Formula:
- Rate per Hour =
(Cost - Residual) / Estimated Hours= ($150,000 - $10,000) / 2,400 = $58.33/hour
| Year | Hours | Depreciation Expense | Accumulated Depreciation | Book Value |
|---|---|---|---|---|
| 1 | 1200 | $70,000 | $70,000 | $80,000 |
| 2 | 360 | $21,000 | $91,000 | $59,000 |
| 3 | 270 | $15,750 | $106,750 | $43,250 |
| 4 | 350 | $20,416 | $127,166 | $22,834 |
| 5 | 220 | $12,833 | $140,000 | $10,000 |
🔷 Double Declining Balance Method (DDB)
Excel Formula:
Use =DDB(cost, salvage, life, period) in Excel.
| Year | Depreciation Expense | Accumulated Depreciation | Book Value |
|---|---|---|---|
| 1 | =DDB(150000, 10000, 5, 1) → $60,000 | $60,000 | $90,000 |
| 2 | =DDB(150000, 10000, 5, 2) → $36,000 | $96,000 | $54,000 |
| 3 | =DDB(150000, 10000, 5, 3) → $21,600 | $117,600 | $32,400 |
| 4 | =DDB(150000, 10000, 5, 4) → $12,960 | $130,560 | $19,440 |
| 5 | =DDB(150000, 10000, 5, 5) → $9,440 | $140,000 | $10,000 |
🔍 Explanation
Depreciation is the process of allocating the cost of a tangible asset over its useful life. Sullivan Ranch Corporation’s new tractor, costing $150,000 with a residual value of $10,000, is depreciated using three different methods to highlight how depreciation varies depending on usage and accounting strategy.
Straight-Line depreciation spreads the cost evenly over the asset’s life. In this case, the annual depreciation expense is $28,000, calculated by subtracting the salvage value from the cost and dividing by five years. This method is simple and ideal when the asset’s usage is consistent each year.
Units-of-Production depreciation is based on actual usage (hours). This method provides a more accurate match of expense to revenue when the asset’s productivity varies. For example, the highest depreciation occurs in Year 1 when the tractor is used for 1,200 hours. The rate is $58.33/hour. The final year’s depreciation is adjusted to prevent going below the residual value.
Double Declining Balance (DDB) accelerates depreciation, front-loading the expense in early years. This method is used when the asset is expected to be more productive or lose value faster early in its life. Excel’s DDB() function simplifies this process. For example, depreciation in Year 1 is $60,000, which is double the straight-line rate applied to the book value.
In summary, while Straight-Line ensures consistency, Units-of-Production reflects real use, and DDB accelerates expense recognition. Selecting the appropriate method depends on the financial goals and usage pattern of the asset.
