You can use the activity depreciation starter workbook shown in Figure 15-5 to construct depreciation schedules with the activity method. The activity method is unique among depreciation methods because it expenses the original cost of an asset based on use rather than on time. In general, you use this starter workbook if you’re calculating depreciation for financial or managerial accounting and you feel that its allocation of costs matches economic reality.
Given the four parameters—original cost, salvage value, estimated useful life (expressed in units of use rather than time), and period units of use—this schedule calculates the depreciation expense, the accumulated depreciation, and the net book value for each period of the forecasting horizon.
The activity depreciation starter workbook has two parts: the Activity Depreciation Calculation Inputs box and the Activity Depreciation Schedule.
Activity Depreciation Calculation Inputs
The calculation inputs and the period units of use are the only variables you enter, and, unless you turn off cell protection, these are the only cells in which you can enter data.
Most often, the financial accounting standards or internal managerial accounting conventions that apply to your modeling assumptions determine the method you use to calculate these variables.
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.
Activity Depreciation Schedule
The Activity Depreciation Schedule has five columns: Period, Period Units of Use, Period Depreciation, Accumulated Depreciation, and Net Book Value.
The period identifier simply numbers the time periods over which you’re depreciating the asset. If you’re using the starter workbook as a building block for a financial projection, use the same number of periods in your depreciation schedule as you use in the other schedules that make up your financial forecasting model. The first-period identifier is stored in cell B11 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 Units of Use
The Period Units of Use isn’t calculated by a formula. You enter either the actual or the forecasted units of use for each period of the forecast as input values. You should check that the total of this column equals the value that you entered for estimated life; it is not absolutely required, as you will see in the following discussion on Period Depreciation.
Period depreciation is the depreciation expense for the current period. If you use 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 use the starter workbook as part of a financial forecast, you’ll probably add the Period Depreciation expense with other expenses in the profit and loss forecast.
The activity depreciation method expenses a portion of an asset’s depreciable cost based on the ratio of that period’s units of use to the estimated life, calibrated in units of use. For example, the formula for the first period is:
The formula for subsequent periods is modified to prevent an asset from being depreciated below its salvage value (as might be the case if you accidentally entered more period units of use than the estimated life in units of use). Starting in the second period, the basic formula is enclosed in the MIN statement, which selects the smaller of two amounts: the activity depreciation expense or the amount yet to be depreciated. For example, the period expense formula for the second period is:
The F11–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.
If you use the starter workbook for depreciable assets accounting, use the incremental increase in accumulated depreciation as the credit component of a depreciation journal entry, and it will ultimately show up on the balance sheet as an adjustment to the asset’s original cost. If you use the starter workbook as part of a financial forecast, you might deduct the accumulated depreciation from the original cost of the asset on the balance sheet forecast to show the asset’s net book value. Alternatively, you can use the Net Book Value amount calculated in this schedule.
The first period’s Accumulated Depreciation balance is the first Period Depreciation expense. The formula is:
The formula for the second period is:
and so on.
Net Book Value
Net book value is an asset’s original cost minus its accumulated depreciation and is the amount that you would 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 figure is the Original Cost amount less any accumulated depreciation. The formula for the first period is:
The formula for the second period is:
and so on.