# depreciation Depreciation and the Spreadsheet Title Depreciation

Document Sample

```					Depreciation and the Spreadsheet
Title: Depreciation                                                                 Cross References
Name of Author: Duncan Williamson                                                   See also Microsoft Word file:
Purpose: To demonstrate various methods of calculating depreciation provisions      www.duncanwil.co.uk/depr.htm
Filename and Directory: depreciation.xls
Software and Version: Microsoft Excel 2000
Version: Final (demonstration)

Contents of the file
The File: this sheet
Definition: definitions of the key depreciation terms
Introduction: calculating the costs of a fixed assets
Methods: five methods of calculating depreciation provisions and introduction to Excel's built in dpereciation functions
Straight (1): the Straight Line Method demonstration
Straight (2): the Straight Line Method for you to do
Red Bal (1): the Reducing Balance Method
Red Bal (2): the Reducing Balance Method
Units (1): the Units of Production Method
Units (2): the Units of Production Method
Sum (1): the Sum of the Year's Digits Method
Sum (2): the Sum of the Year's Digits Method
Depletion (1): the Depletion Method demonstration
Depletion (2): the Depletion Method there is no example for you to do
Others: a demonstration of seven of Excel's built in depreciation functions, including some of the above
Exercise: additional exercises for you to do

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Depreciation: definitions
Spreadsheet devised by Duncan Williamson
Let's start with a few definitions so that we know exactly what depreciation is
Depreciation arises because fixed assets lose value as they get older and
wear out. Depreciation is the allocation of the depreciable amount of an
asset over its working life.                                                     In this module, we
use the term
Depreciable amount is the cost of the asset less the scrap or resale value       Carrying cost: this
that is expected to be received when the asset is finished with.                 is a synonym for
Depreciable assets are assets which are expected to have a limited working       Net Book Value
life that is greater than one year and that are used in the production or        (NBV) and Written
supply of goods or services or for administration purposes.                      Down Value
(WDB)
Working life is the length of time that the organisation expects the asset to
remain useful; or the number of units of production that can be expected to
be made by the asset. The working life is also known as the Useful Life.
Go to Introduction

These red and gold
text boxes are
them and go there!

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Introduction to depreciation calculations
Before we start our discussion of the methods of providing for depreciation, we need to know how to
calculate the cost of buying a fixed asset. The precise costs under this heading will vary from case to
case but in general, the net cost is the cost of all expenditures needed to get the asset in place and
ready for use less any discounts given by the supplier. Let's look at two examples:
Example 1: the costs to include when buying a machine include
basic cost of the machine from the supplier          carriage/transport costs from supplier to buyer
transit insurance                                     installation, testing and commissioning costs
any discounts given by the supplier are taken away from these costs

Example 2: the costs to include when buying some land include
the basic cost of the land                      taxes paid by the buyer
commission to estate agents                             cost of drainage
lawyers' fees                                      land clearance costs
less any discounts
Go to Methods

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Methods of calculating depreciation 1
Now let's list and describe five different ways of calculating depreciation charges

Straight Line Method: This is the simplest method. Here we divide the total cost of an asset by an estimate of its working
life; we also have to take into account any selling or scrap value that we might get back when we sell the asset at the end of
that working life. The amount charged for depreciation will be the same for every year using this method.
Reducing balance method: This method charges a lot of depreciation in the early years and less later. The method works
on the basis that in the early years of the life of an asset its repair and maintenance costs will be small; but they will increase
as the asset gets older. This suggests that depreciation + repairs and maintenance will be the same each year.

Units of production method: With this method, depreciation is calculated by sharing the cost of the asset over its working
life in terms of the number of units it will make.
Sum of the years' digits method: This method calculated depreciation by taking the total working life of the asset and using
this to apply a fraction based on that working life.
Depletion method: This method of depreciation applies to natural resources such as coal, oil, gold: assets that naturally are
not replaced as they are exploited. This method is similar to the production unit method.
Go to Straight (1)
Other methods: The last sheet in this file illustrates seven methods of providing for depreciation that Excel has built in
functions to deal with

SLN = straight line method
DB = the fixed declining balance method
DDB = the double declining balance method
SYD = Sum of the Year's Digits Method
VDB = the double declining balance method but includes the ability to calculate for partial periods
AMORDEGRC and AMORLINC = both give us the prorated linear depreciation amount per period

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Straight Line Method
2 Depreciation schedule
For example, it is 1/1/01 and we have just                 Year ended Balance b/d        Annual Carrying
bought a new asset for £100,000 that we                                              Depreciation    Cost
1/1/2001     -              -       100,000
estimate will have a working life of 5 years,
12/31/2001    100,000          16,000  84,000
after which we think we will be able to sell                12/31/2002      84,000         16,000  68,000
it for £20,000.                                             12/31/2003      68,000         16,000  52,000
1 Calculate the annual depreciation                         12/31/2004      52,000         16,000  36,000
provision for the asset and                                 12/31/2005      36,000         16,000  20,000
2 show the depreciation schedule for this
asset.                                                           Note: the carrying cost at 31 December
2005 is exactly £20,000 which is the
The formula is:   Annual Depreciation Provision =                selling value we expect to get back.
Cost of the asset - Selling Value
Go to Straight (2)
Working life of the
1 Annual depreciation provision asset
£100,000 - 20,000 ÷ 5 = £16,000 per year

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Straight Line Method for you to do
2 Depreciation schedule
It is 1/1/01 and we have bought an asset for    Year ended Balance b/d        Annual       Carrying
£200,000 that we estimate will have a                                     Depreciation         Cost
working life of 4 years; and that we think we      1/1/2001     -             -
will be able to sell for £25,000 after that.     12/31/2001
1 Calculate the annual depreciation provision    12/31/2002
for the asset and                                12/31/2003
2 show the depreciation schedule for this
12/31/2004
asset.

1 Annual depreciation provision =                    CLICK HERE for an exercise: go to Exercise
Put your answer to 1 in the yellow cell above        sheet and look for Straight Line example
For 2, enter the relevant amounts in the
yellow coloured cells in the depreciation                                 Go to Red Bal (1)
schedule.

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Reducing Balance Method                               1 Annual depreciation provision
20,000
For example, it is 1/1/01 and we have             1- 5           = 1 - 5 0.2 = 1 - 0.7247797= 0.2752203
100,000
just bought a new asset for £100,000
that we estimate will have a working              We now use this result to multiply it by the balance b/d each year
life of 5 years, after which we think we
will be able to sell it for £20,000.              2 Depreciation schedule
1 Calculate the annual depreciation               Year ended      Balance b/d        Annual Carrying
provision for the asset and                                                      Depreciation     Cost
2 show the depreciation schedule for                 1/1/2001          -              -        100,000
this asset.                                        12/31/2001         100,000          27,522   72,478
12/31/2002           72,478         19,947   52,531
12/31/2003           52,531         14,457   38,073
The formula is:                                    12/31/2004           38,073         10,478   27,595
n
S         12/31/2005           27,595          7,595   20,000
Annual depreciation provision = 1-
C                                              Go to Red Bal (2)
Where n is the working life of the asset, years
S is the selling value of the asset
C is the initial cost of the asset

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Reducing Balance Method for you
For example, it is 1/1/01 and we have just         2 Depreciation schedule: fill in the orange cells
bought a new asset for £200,000 that we            Year ended     Balance b/d       Annual Carrying
estimate will have a working life of 4 years,                                   Depreciation   Cost
after which we think we will be able to sell it        1/1/2001       -              -
for £25,000.                                         12/31/2001
1 Calculate the annual depreciation provision        12/31/2002
rate for the asset and
12/31/2003
2 show the depreciation schedule for this asset.
12/31/2004

1 Annual depreciation provision rate
25,000                The amount of depreciation will FALL
1-   4           =1-    ___ = 1 - ___ = ____
200,000                year by year with this method

Enter your answer as a decimal in the yellow cell below

CLICK HERE for an exercise: go to Exercise
sheet and look for Reducing Balance example

Go to Units (1)

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Units of Production Method
2 Depreciation schedule
For example, it is 1/1/01 and we have just                Year ended Balance b/d           Annual Carrying
bought a new asset for £100,000 that we                                                Depreciation    Cost
1/1/2001        -              -       100,000
estimate will have a working life of 5
12/31/2001       100,000          16,000  84,000
years, after which we think we will be able                12/31/2002         84,000         16,000  68,000
to sell it for £20,000, having produced                    12/31/2003         68,000         16,000  52,000
800,000 units of output at 160,000 units                   12/31/2004         52,000         16,000  36,000
per year.                                                  12/31/2005         36,000         16,000  20,000
1 Calculate the annual depreciation
provision per unit for the asset and
Note: the carrying cost at 31
2 show the depreciation schedule                                        December 2005 is exactly £20,000
The formula is:   Annual Depreciation Provision =
which is the selling value we
Cost of the asset - Selling Value                 expect to get back.
Total estimated units of output
Go to Units (2)
1 Annual depreciation provision
£100,000 - 20,000 ÷ 800,000 = £0.1 per
unit which we now multiply by the number
of units per year.

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Units of Production Method for you                 2 Depreciation schedule: fill in the orange cells

For example, it is 1/1/01 and we have just        Year ended    Balance b/d       Annual     Carrying
bought a new asset for £200,000 that we                                       Depreciation       Cost
1/1/2001       -              -
estimate will have a working life of 4
12/31/2001
years, after which we think we will be able        12/31/2002
to sell it for £25,000, having produced            12/31/2003
1,000,000 units of output at 250,000 units         12/31/2004
per year.
1 Calculate the annual depreciation
provision per unit for the asset and

1 Annual depreciation provision per unit             CLICK HERE for an exercise: go to Exercise
sheet and look for Units pf Production example
Put your answer in the yellow cell below

The amount of depreciation depends on the                                             Go to Sum (1)
number of units per year: this will change from
year to year in some cases.

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Sum of Year's Digits Method                          1 Annual depreciation fraction

For example, it is 1/1/01 and we have just                é 5 + 1ù    é6ù
SYD = 5ê      ú = 5ê 2 ú = 5 * 3 = 15
bought a new asset for £100,000 that we                   ë 2 û       ë û
estimate will have a working life of 5 years,      We now use 15 as the basis of the fraction which we then multiply by the
after which we think we will be able to sell it    (cost - selling value), not the cost.
for £20,000.                                                                             See the "Comments" in cells J12 and
1 Calculate the annual depreciation provision      2 Depreciation schedule J13 for help if you need it.
for the asset and
Year ended Fraction          Balance b/d         Annual Carrying
2 show the depreciation schedule for this asset.
Depreciation     Cost
1/1/2001                       -              -        100,000
The formula is:                                      12/31/2001      5/15            100,000          26,667   73,333
é n + 1ù
SYD = n ê      ú                   12/31/2002      4/15              73,333         21,333   52,000
ë 2 û                      12/31/2003      3/15              52,000         16,000   36,000
Where n is the working life of the asset, years      12/31/2004      2/15              36,000         10,667   25,333
12/31/2005      1/15              25,333          5,333   20,000
Go to Sum (2)

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Sum of Year's Digits Method to do              2 Depreciation schedule: read the comment

For example, it is 1/1/01 and we have        Year ended Fraction   Balance b/d       Annual     Carrying
just bought a new asset for £200,000                                             Depreciation       Cost
1/1/2001               -              -
that we estimate will have a working
12/31/2001
life of 4 years, after which we think we      12/31/2002
will be able to sell it for £25,000.          12/31/2003
1 Calculate the annual depreciation           12/31/2004
provision for the asset and
2 show the depreciation schedule for
this asset.                                      CLICK HERE for an exercise: go to Exercise sheet and
look for Sum of the Year's Digits example
1 Annual depreciation fraction
é4 + 1ù                      The amount of depreciation will FALL
SYD   = 4ê     ú =
ë 2 û                        year by year with this method
Go to Depletion (1)
Enter your answer to this formula below

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Depletion Method
2 Depreciation schedule
Assume a coal mine has just been acquired               Year ended Balance b/d          Annual     Carrying
that is estimated by geologists to have                                             Depreciation       Cost
1/1/2001        -             -          500,000
reserves of coal of 500,000 tonnes. The
12/31/2001       500,000       100,000     400,000
total acquisition cost was £500,000 and we               12/31/2002       400,000       100,000     300,000
are to assume that the net residual value will           12/31/2003       300,000       100,000     200,000
be zero. Finally, we will assume that                    12/31/2004       200,000       100,000     100,000
100,000 tonnes of coal will be extracted                 12/31/2005       100,000       100,000           0
each year.
1 Calculate the annual depreciation
Note: the carrying cost at 31
provision per unit for the asset and                                  December 2005 is exactly £0
2 show the depreciation schedule                                      since the selling value is zero in
this case.
The formula is:   Annual Depreciation Provision =
Go to Depletion (2)
Cost of the asset - Selling Value
1 Depreciation         Total
per unit estimated reserves
£500,000/500,000 = £1.00 per tonne

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Depletion Method

Since the Depletion method is so
similar to the Units of Production
Method, there is no example of this
method for you to attempt.

Go to Other Methods

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Other Methods
This sheet in this file illustrates seven methods of providing for depreciation that Excel has built in functions to deal wit h. What follows is the Excel's
function abbreviation followed by the name of the method and finally by the information that the function needs in order to w ork.

SLN = straight line method = (cost,salvage,life)
DB = the fixed declining balance method = (cost,salvage,life,period,month)
DDB = the double declining balance method = (cost,salvage,life,period,factor)
SYD = Sum of the Year's Digits Method = (cost,salvage,life,per)
VDB = the variable declining balance method (includes the ability to calculate for partial periods) =
(cost,salvage,life,start_period,end_period,factor,no_switch)
AMORDEGRC and AMORLINC = both give us the prorated linear depreciation amount per period =
(cost,date_purchased,first_period,salvage,period,rate,basis)

Basic details to illustrate all methods                                 Method    Depreciation
cost                                    50000 start_period                        1        SLN            \$750.00
salvage                                   5000 end_period                        10        DB           \$1,900.00
life (months)                               60 no_switch                     TRUE          DDB          \$1,666.67
period (month number)                        1 date_purchased              1/1/2001        SYD          \$1,475.41
month (number of months in first year)      12 first_period              12/31/2001        VDB         \$12,709.76
factor                                       2 rate                         10.00%         AMORDEGRC
\$9,384.00
period                                       1 basis                              3        AMORLINC

Change some or all of these values to test what the methods are trying to achieve.         Check Excel's Help files for more
For example, change the period (month number) from 1 to 12, then from 12 to 24             information if you need it
and watch what happens to the DB and DDB method values ...

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP
Additional exercises for you to do

For each example, consider your reader/user and present your results with them in mind.

Straight Line Method: In a new file, set up a table to calculate acquisition cost, written down values and annual depreciation costs from the following data:
buying cost of machine GEL100,000, shipping costs of machine GEL10,000, useful life of the machine 9 years with zero residual value. The machine is
bought on 1 January 2001.
Go to Red Bal (1)

Reducing Balance Method: In the same file as you set up for the straight line method additional example, set up a table to calculate acquisition cost,
written down values and annual depreciation costs from the following data: buying cost of machine GEL250,000, shipping costs of machine GEL15,000,
useful life of the machine 9 years with residual value of GEL50,000. The machine is bought on 1 January 2001.
Go to Units (1)

Production Units Method: In the same file as you set up for the straight line method additional example, set up a table to calculate acquisition cost,
written down values and annual depreciation costs from the following data: new asset bought for GEL1,200,000 that we estimate will have a working life of
7 years, after which we think we will be able to sell it for GEL250,000, having produced at the rate of 250,000 units per year. The machine is bought on 1
January 2001.
Go to Sum (1)

Sum of the Year's Digits Method: In the same file as you set up for the straight line method additional example, model the legder accounts for the asset
account and the depreciation account for the asset found in the Sum (2) sheet in this file The machine is bought on 1 January 2001. The layout you should
follow is shown below. Show the balances carried down at the end of each year within the accounts for each account.
Go to Depletion (1)
Dr                        Asset Account                        Cr      Dr                              Depreciation Account                             Cr
Date Details              Amount Date Details               Amount     Date      Details               Amount     Date      Details               Amount
.                                 .                                             .                                        .
.                                 .                                             .                                        .
.                                 .                                             .                                        .
.                                 .                                             .                                        .
.                                 .                                             .                                        .

560a4205-68c3-4c7f-9041-92803673a70b.xlsx
Duncan Williamson GESP

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 56 posted: 4/17/2012 language: English pages: 16