Docstoc

The File

Document Sample
The File Powered By Docstoc
					Depreciation and the Spreadsheet
  Title: Depreciation                                                                 Cross References
  Name of Author: Duncan Williamson                                                   See also Microsoft Word file:
  Date of Creation/Latest Revision: December 2000/22 July 2001                        Depreciation and the Spreadsheet .doc
  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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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
                                              Hyperlinks: click on
                                              them and go there!




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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.




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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)




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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.




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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.




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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)




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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 ...




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.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
                                 .                                 .                                             .                                        .
                                 .                                 .                                             .                                        .
                                 .                                 .                                             .                                        .
                                 .                                 .                                             .                                        .
                                 .                                 .                                             .                                        .




31ff349b-6a9e-4ff8-96df-def9c5d0a75c.xlsx
Duncan Williamson GESP

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:8
posted:11/22/2011
language:English
pages:16