 


Good
Spreadsheet Practice Pays Off
Excel’s Depreciation Functions
By
Tina M. Loraas and Jennifer M. Mueller
MAY 2008  Spreadsheets
support accounting and financial statement preparation on a daily
basis, but few users ever stop to question the reliability of a
Microsoft Excel accounting function. An unspoken assumption is that
the software developer completely understood the calculation that
the function was designed to perform. Given the complexity of some
accounting and finance calculations, this assumption may not always
be correct.
Good
Practice
The authors
suggest three spreadsheet practices to help ensure that Excel
functions are working appropriately from an accounting standpoint:
 Understand
the purpose of the function you are going to use.
 Understand
the inputs or arguments of the function.
 Check
the method of calculation to ensure it is operating as expected.
These practices
can be illustrated in the context of Excel’s builtin depreciation
functions (see Sidebar).
They are as follows: straightline (SLN), sum of the years’
digits (SYD), declining balance (DB), doubledeclining balance
(DDB), and variable declining balance (VDB). The depreciation
context should be familiar to users because spreadsheets are commonly
used in corporate accounting to calculate and maintain assetdepreciation
schedules for use in planning, budgeting, financial reporting,
or whatif analyses related to asset acquisitions and disposals.
In addition, Excel’s depreciation functions are a likely
point of error without the three spreadsheet practices mentioned
above.
The first
good practice is to understand the intended purpose of the function
to be used. Though this step may seem superficial at first glance,
acronyms used by Excel to represent functions do not always clearly
indicate what calculation a function is performing. For example,
Excel offers three different declining balance functions: DB,
DDB, and VDB. The question for a user is, “Which is the
most appropriate?”
Second, it
is important to understand the arguments, or inputs, sought by
a function. For example, the DDB function has five inputs (cost,
salvage, life, period, factor), one of which is optional argument
(factor). While the first three inputs (cost, salvage, and life)
seem straightforward, the period and factor inputs are less clear.
Incorrectly entering any one of the inputs may prohibit Excel
from completing the calculation, resulting in an error message
(e.g., #N/A or #DIV/0) or, worse, the function may yield an apparently
valid result that is actually incorrect.
The third,
and perhaps most important, good spreadsheet practice is to check
the function’s method of calculation against the correct
or intended method of calculation when first constructing a spreadsheet.
Doing so allows a user to avoid assuming that the programmer understood
the accounting method. Explanations found in Excel’s help
function are useful for establishing a clear understanding of
a function, its inputs, and its calculations.
Checking
Calculations
The significance
of checking a function’s calculation method against the
intended calculation can be clearly demonstrated when considering
the declining balance functions within Excel. The DB and DDB functions
are subject to misuse in both the 2003 and 2007 versions of Excel,
as well as in templates that can be downloaded from Microsoft.
Declining
balance. The DB function determines depreciation
amounts on a fixed declining balance schedule by applying a constant
rate to the declining book value of the asset. The rate is a function
of the original asset cost, the salvage value, and the useful
life; specifically, Depreciation Rate = [1 – (Salvage Value/Cost)
1/Useful Life].
Although
this method meets the GAAP requirement of “systematic and
rational,” any asset with a zerodollar salvage value will
be fully depreciated in the first period using the DB function.
This error occurs because the latter part of the depreciation
rate equation becomes zero, yielding a depreciation rate of 100%.
The formula causes initial depreciation calculations to be extreme
any time the salvage value is less than approximately 10% of the
asset’s acquisition cost. Thus DB should be used only when
the salvage value of the asset is a significant portion of the
asset base.
Doubledeclining
balance. Doubledeclining balance, perhaps a more
commonly used accelerated method, also produces declining depreciation
expenses over time by applying a constant rate to the book value
of the asset. The rate can be up to double the straightline depreciation
rate. For example, consider an asset with a 10year life. The
straightline depreciation rate is 10% per year (100% of depreciable
base/10 years). The DDB rate can be up to 20% per year. This 20%
rate should be applied to the book value of the asset until the
asset reaches the salvage value. This is the point at which Excel’s
DDB function is noticably problematic.
If the salvage
value of the asset is low compared to its acquisition cost, then
DDB will not completely depreciate the asset by the end of the
useful life. The amount of “leftover” depreciation
varies depending upon the length of the asset’s life. For
a $10,000 asset with no salvage value, the unrecorded depreciation
at the end of the useful life will range from $370, if its life
is three years, to $1,262, if its life is 30 years. In practice,
to prevent “leftover” depreciation, a switch to the
straightline method is necessary in the year that the straightline
balance method, using the remaining depreciable balance, yields
a higher depreciation expense than the doubledeclining method.
Exhibit
1 illustrates the need to switch methods for the $10,000 asset
mentioned above, assuming a 10year useful life. Note that if
the doubledeclining balance method is used throughout the 10year
life, $1,073.74 of unrecorded depreciation will remain at the
end of Year 10. A switch to the straightline method should occur
in the year that it yields a greater depreciation expense than
the doubledeclining balance method. For this asset, the switch
occurs in Year 7. This adjustment needed to correctly use the
doubledeclining balance method is not inherent in the DDB functionality
of Excel. A lesswellknown function, VDB, provides for the switch
in methods.
Variabledeclining
balance. The VDB function is an improvement over
the DDB function because it allows for additional inputs. An optional
VDB input called “noswitch” allows the user to instruct
Excel on whether to switch to the straightline method as discussed
previously. If a value for “noswitch” is omitted,
Excel will automatically switch to the straightline method when
appropriate. The depreciation schedule for the same $10,000 asset
illustrated in Exhibit 1 is computed using VDB in Exhibit
2.
The
Bigger Picture
In reality,
financial reporting is supported by various spreadsheets containing
a multitude of builtin functions and userdeveloped formulas.
In the wake of the SarbanesOxley Act, many public companies and
their auditors have examined the validity of key spreadsheets
used in the financial reporting process. While tests of spreadsheets
should rightly focus on spreadsheet design, including the accuracy
of userdeveloped formulas and cell references, users may not
be skeptical enough when it comes to Excel’s builtin functions.
Absent a complete understanding of a function, an end user must
accept that the function always produces a correct answer. Such
blind faith could lead to inaccuracies in financial reports. The
authors believe that following three good spreadsheet practices
can eliminate such problems: understanding the purpose of the
function you are going to use; understanding the inputs or arguments
of the function; and checking the method of calculation to ensure
it is operating as expected.
Tina
M. Loraas, PhD, is an assistant professor, and Jennifer
M. Mueller, PhD, is an associate professor, both in the
school of accountancy at Auburn University, Auburn, Ala.

