This preview shows page 1. Sign up to view the full content.
Unformatted text preview: e annual depreciation by 4 if posting depreciation quarterly.
Monthly depreciation is provided on the worksheet as annual depreciation divided by 12 as well as
monthly depreciation based on monthly life for comparison.
Like the previous formulas, the “SYD” formula in the worksheet looks like
“=IF(A12="","",SYD($B$2,$B$4,$B$5,A12)) where absolute references acquire the values from the top
of the worksheet and relational references get period references. This allows the formula to be written in
the second row of the matrix and dragged down the worksheet.
The “SYD” formula will not violate salvage value or require an adjustment at the end of the asset life
as “DDB” and “unswitched” “VDB” may. Units-Of-Activity Depreciation
Excel does not have a formula to handle units of activity depreciation. While the concept is rather simple,
the sum of acquisition cost less salvage value divided by the expected units yields a per unit of activity
depreciation value, that value is multiplied by the units of activity in the period to get period depreciation
expense. Excel formulas to handle these issues get rather complicated if written to provide period
depreciation, calculate book value, and calculate accumulated depreciation without violating the concepts
of salvage or residual value. The “Units Of Activity” worksheet within the “Depreciation” data file has a
working model of how this function can be written into Excel. This model makes extensive use of
embedded formulas to ensure that depreciable value is not exceeded either in total or in a period entry.
Examination of the formulas in row 12 will show that the initial formula in this line is different then the
formula on row 13. This difference allows for a single period to fully depreciate the asset – it should have
been a period expense if that were possible, then it checks and provides the appropriate depreciation for
the period. In lines 13 and below, the formulas evaluate accumulated depreciation of prior periods to
determine available depr...
View Full Document
- Fall '10