VIEWS: 6 PAGES: 16 POSTED ON: 11/9/2011 Public Domain
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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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! 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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. 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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) 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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. 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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. 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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) 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 ... 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.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 . . . . . . . . . . . . . . . . . . . . 40a10c0e-ab62-4f34-b12b-0f7e691b0feb.xlsx Duncan Williamson GESP