The straight-line depreciation starter workbook has two parts: the Straight-Line Depreciation Calculation Inputs box in the range A2:B6 and the Straight-Line Depreciation Schedule, starting with the title in row 8 (see Figure 15-1).
Straight-Line Depreciation 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 Straight-Line Depreciation Schedule use these cell names rather than the cell addresses.
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 identifier is stored in cell B11 as the integer 1. Periods that follow are stored as the previous period plus 1.
Note: 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 statement. If you’re using the starter workbook as part of a financial forecast, you can include the Period Depreciation expense from other expenses in the profit and loss fore- cast. Additionally, any income 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)
This formula for subsequent periods is modified to prevent an asset from being depreciated below its salvage value and to deal with an estimated life expressed as a non-integer. 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 Depreciation formula for the second period is:
=MIN(SLN(Original_Cost,Salvage_Value,Estimated_Life), E11-Salvage_Value)
The E11–Salvage_Value portion of the formula calculates the amount yet to be depreciated. In subsequent periods, this part of the formula uses the Net Book Value amount from previous periods.
Accumulated Depreciation
If you’re using the starter workbook for depreciable assets accounting, the incremental increase in accumulated depreciation 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 include 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(C$11:C11)
The formula for the second period is:
=SUM(C$11:C12)
and so on.
Net Book Value
The net book value is an asset’s carrying cost and is the amount that you report either individually 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 accumulated depreciation. The formula for the first period is:
=Original_Cost-D11
The formula for the second period is:
=Original_Cost-D12
and so on.
Leave a Reply