MBA's Guide to Microsoft Excel

Chapter 15

Understanding the Declining Balance Depreciation Starter Workbook

In general, you use the declining balance depreciation starter workbook (see Figure 15-2) if you’ve selected or been counseled by your tax adviser to use a declining balance conven- tion, such as Accelerated Cost Recovery System (ACRS) or modified Accelerated Cost Recovery System (MACRS), for tax accounting. You can also use this starter workbook if you’re calculating depreciation for financial accounting and you feel that the declining balance method of depreciation allocates costs in a way that matches economic reality.

Figure 15-2The declining balance depreciation starter workbook.

Given four parameters—original cost, salvage value, estimated life, and the decline percent- age—this starter workbook calculates the depreciation expense, the accumulated deprecia- tion, and the net book value for each period of the forecasting horizon.

The declining balance starter workbook has two parts: the Declining Balance Depreciation Calculation Inputs box and the Declining Balance Depreciation Schedule.

Declining Balance Depreciation Calculation Inputs

The calculation inputs are Original Cost, Salvage Value, Estimated Life, and Decline Per- cent. These are the only four variables you enter, and, unless you turn off cell protection, the four cells containing 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, cell B6 contains the estimated life and is named Estimated_Life, and cell B7 contains the declining balance percentage and is named Decline_Percent. The formu- las within the schedules use these cell names rather than the cell addresses.

Declining Balance Depreciation Schedule

The Declining Balance Depreciation Schedule has four columns: Period, Period Deprecia- tion, 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 B12 as the integer 1. Periods that follow are stored as the previous period plus 1.

Period Depreciation

If you’re using declining balance depreciation over the asset’s entire estimated life, period depreciation is the depreciation expense for the current period. If you’re using the starter workbook for depreciable assets bookkeeping, 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 add the Period Depreciation expense to other expenses in the profit and loss forecast. Addition- ally, any income 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:

=VDB(Original_Cost,Salvage_Value,Estimated_Life,B122,B12,Decline_Percent)

The formula simply supplies the needed input values for calculating declining balance de- preciation to the VDB function. (If you have questions about how the VDB works, refer to Chapter 5.)

Accumulated Depreciation

If you’re using the starter workbook for depreciable assets accounting, the incremental in- crease in accumulated depreciation is the credit component of a depreciation journal entry and ultimately shows up on the balance sheet as an adjustment of the asset’s carrying cost. If you’re using the starter workbook as part of a financial forecast, you can deduct the ac- cumulated 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$12:C12)

The formula for the second period is:

=SUM(C$12:C13)

The formula for the third period is:

=SUM(C$12:C14)

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 net book value formula for the first period is:

=Original_Cost–D12

The formula for the second period is:

=Original_Cost-D13

The formula for the third period is:

=Original_Cost-D14

and so on.

Chapter Contents: