You can use the sum-of-the-years’-digits depreciation starter workbook shown in Figure 15-3 to construct depreciation schedules with the sum-of-the-years’-digits method. In general, you use this starter workbook if you’ve selected or been counseled by your tax ad- viser to use the sum-of-the-years’-digits convention for tax accounting. You can also use this starter workbook if you’re calculating depreciation for financial accounting and you feel that this depreciation method allocates costs in a way that matches economic reality.
Given three parameters—original cost, salvage value, and estimated life—this starter work- book calculates the period depreciation, the accumulated depreciation, and the net book value for each period of the forecasting horizon.
The schedule also calculates the excess accelerated depreciation taken as a result of using the sum-of-years’-digits method, because this amount might be subject to special tax treat- ment either on a current basis or at disposal. To calculate the excess, this starter workbook incorporates a straight-line depreciation schedule.
The sum-of-the-years’-digits depreciation starter workbook has four parts: the Sum-of-the- Years’-Digits Calculation Inputs box, the Sum-of-the-Years’-Digits Depreciation Sched- ule, the Straight-Line Depreciation Schedule, and the Excess Accelerated Depreciation Schedule.
Sum-of-the-Years’-Digits Calculation Inputs
The calculation inputs are Original Cost, Salvage Value, and Estimated Life. These are the only three variables you enter, and, unless you turn off cell protection, the three cells con- taining these values are the only cells within the spreadsheet in which you can enter data.
For convenience and good documentation within the starter workbook, cell B4 contains the original cost and is named Original_Cost, cell B5 contains the salvage value and is named Salvage_Value, and cell B6 contains the estimated life and is named Estimated_Life. The formulas within the actual schedule use these cell names rather than the cell addresses.
Sum-of-the-Years’-Digits Depreciation Schedule
The Sum-of-the-Years’-Digits Depreciation Schedule has four columns: Period, Period Depreciation, Accumulated Depreciation, and Net Book Value.
Period
The period identifier simply numbers the time period over which you’re depreciating the asset. The first period identifier is stored in cell B1 as the integer 1. Periods that follow are stored as the previous period plus 1.
Tip: Column A is empty in the area next to the period column. You might want to use this space to store ending dates for the accounting periods that correspond with the period depreciation.
Period Depreciation
Period depreciation is the depreciation expense for the current period. If you’re using the starter workbook for depreciable assets accounting, the Period Depreciation expense is the debit component of a depreciation journal entry and ultimately shows up in the profit and loss state- ment. If you’re using the starter workbook as part of a financial forecast, you can add the Period Depreciation expense to other expenses in the profit and loss forecast. Additionally, any in- come tax effect of this noncash expense ripples through the cash flow statement.
The Period Depreciation formula for the first period in the forecasting horizon is:
=SYD(Original_Cost,Salvage_Value,Estimated_Life,B11)
The formula for the second period, however, is modified so that the asset is not depreci- ated below its salvage value. Starting with the second period, the formula to calculate the Period Depreciation expense is enclosed in an IF statement that first verifies that the asset hasn’t already been fully depreciated. For the comparison, rounded amounts are used so that trailing digits of insignificance don’t affect the test. The formula for the second period is:
=IF(ROUND(Original_Cost-D11,0)=ROUND(Salvage_Value,0),0, SYD(Original_Cost,Salvage_Value,Estimated_Life,B12))
The Original_Cost–D11 portion is the Net Book Value amount at the end of the previous period. B12 contains the period identifier for the expense that is calculated. In subsequent periods, these parts of the formula change so that the formula always uses the Net Book Value amount from the previous period and the period identifier from the current period.
In other words, this equation states that if the net book value after depreciation equaled the salvage value after the last period, set the depreciation for this period to 0.
Accumulated Depreciation
If you’re using the starter workbook for depreciable assets accounting, the incremental in- crease in the Accumulated Depreciation amount is the credit component of a depreciation journal entry and ultimately shows up on the balance sheet as an adjustment to the asset’s carrying cost. If you’re using the starter workbook as part of a financial forecast, you can deduct the Accumulated Depreciation amount from the original cost of the asset in the balance sheet forecast to show the asset’s net book value. Alternatively, you might simply use the Net Book Value amount calculated by this schedule.
The formula for the Accumulated Depreciation balance in the first period is:
=SUM(C$11:C11)
The formula for the second period is:
=SUM(C$11:C12)
The formula for the third period is:
=SUM(C$11:C13)
and so on.
Net Book Value
The net book value is an asset’s carrying cost and is the amount that you report either in- dividually or with other assets’ net book values on any historical or pro forma balance sheets.
For each period, the Net Book Value amount is the Original Cost amount less any accu- mulated depreciation. The formula for the first period is:
=Original_Cost-D11
The formula for the second period is:
=Original_Cost-D12
The formula for the third period is:
=Original_Cost-D13
and so on.
Straight-Line Depreciation Schedule
The Straight-Line Depreciation Schedule has four columns: Period, Period Depreciation, Accumulated Depreciation, and Net Book Value.
Period
The period identifier simply numbers the time periods over which you’re depreciating the asset. The first period is stored as the integer 1. Periods that follow are stored as the previ- ous period plus 1.
Period Depreciation
Period depreciation is the depreciation expense for the current period. If you’re using the starter workbook for depreciable assets accounting, the Period Depreciation expense is the debit component of a depreciation journal entry and ultimately shows up in the profit and loss state- ment. If you’re using the starter workbook as part of a financial forecast, you can add the Period Depreciation expense to other expenses in the profit and loss forecast. Additionally, any in- come tax effect of this noncash expense ripples through the cash flow statement.
Because the asset is expensed equally in each period in straight-line depreciation, the basic Period Depreciation formula used in the first period is incorporated in the formula for each period of the forecasting horizon:
=SLN(Original_Cost,Salvage_Value,Estimated_Life)
The formula for subsequent periods is modified to prevent an asset from being depreciated below its salvage value. Starting in the second period, the basic formula is enclosed in a MIN statement, which selects the smaller of two amounts: the straight-line depreciation expense or the amount yet to be depreciated. For example, the period expense formula for the sec- ond period is:
=MIN(SLN(Original_Cost,Salvage_Value,Estimated_Life),J11-Salvage_Value)
The J11–Salvage_Value portion calculates the amount yet to be depreciated. In subsequent periods, this part of the formula uses the Net Book Value amount from the previous period.
Accumulated Depreciation
If you’re using the starter workbook for depreciable assets accounting, the incremental in- crease in the Accumulated Depreciation amount is the credit component of a depreciation journal entry and ultimately shows up on the balance sheet as an adjustment to the asset’s carrying cost. If you’re using the starter workbook as part of a financial forecast, you can deduct the accumulated depreciation from the original cost of the asset in the balance sheet forecast to show the asset’s net book value. Alternatively, you might simply use the Net Book Value amount calculated by this schedule.
The formula for the Accumulated Depreciation balance in the first period is:
=SUM(H$11:H11)
The formula for the second period is:
=SUM(H$11:H12)
and so on.
Net Book Value
The net book value is an asset’s carrying cost and is the amount that you report either in- dividually or with other assets’ net book values on any historical or pro forma balance sheets.
For each period, the Net Book Value amount is the Original Cost amount less any accu- mulated depreciation. The formula for the first period is:
=Original_Cost-I11
The formula for the second period is:
=Original_Cost-I12
and so on.
Excess Accelerated Depreciation Schedule
In certain situations, excess accelerated depreciation is accorded special income tax treat- ment. The calculated results in the one-column Excess Accelerated Depreciation Sched- ule are simply the difference between the Net Book Value amount for each period calculated in the Sum-of-the-Years’-Digits Depreciation Schedule and the Net Book Value amount calculated in the Straight-Line Depreciation Schedule. The formula for the first period is:
=J11-E11
The formula for the second period is:
=J12-E12
and so on.
Excess Accelerated Depreciation Schedule
In certain situations, excess accelerated depreciation is accorded special income tax treat- ment. The calculated results in the one-column Excess Accelerated Depreciation Sched- ule are simply the difference between the Net Book Value amount for each period calculated in the Sum-of-the-Years’-Digits Depreciation Schedule and the Net Book Value amount calculated in the Straight-Line Depreciation Schedule. The formula for the first period is:
=J11-E11
The formula for the second period is:
=J12-E12
and so on.
Leave a Reply