Results 1 to 9 of 9

Excel Fixed Asset Register

This is a discussion on Excel Fixed Asset Register within the Excel Questions forums, part of the Question Forums category; I am looking for a Excel Fixed asset template that I can use for Fixed asset additions, depreciation and controlling ...

  1. #1
    Board Regular
    Join Date
    Jun 2006
    Posts
    3,163

    Default Excel Fixed Asset Register

    I am looking for a Excel Fixed asset template that I can use for Fixed asset additions, depreciation and controlling disposals (sale of assets)

    It would be appreciated if anyone can advise where I can download a termplate

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,168

    Default Re: Excel Fixed Asset Register

    I've never seen a good one.
    You can get some canned software pretty cheap (probably) - I'd go that route if possible. Otherwise building it out yourself isn't very hard but is time consuming and usually requires a few iterations as you add or fix it. A good asset ledger gets very large as you need to be sure you capture a lot of key information - date acquired, date in service, salvage, method, convention, purchase information (PO, Vendor, Invoice). Then you have asset classes (maybe) or departments, serial numbers ... and possibly tax methods as well, and bonus depreciation taken, plus all your reports for depreciation and retirement. etc. etc. etc.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Jun 2006
    Posts
    3,163

    Default Re: Excel Fixed Asset Register

    Hi Xenou

    Thanks for the reply. One can build ones own Fixed asset Register, but it is time consuming , but the end product would be great if designed correctly. One could also purchase an off the shelf product, but it it is not as flexible as creating one's own asset register. It is something I need to think about before making a decision

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    1,681

    Default Re: Excel Fixed Asset Register

    Quote Originally Posted by howard View Post
    I am looking for a Excel Fixed asset template that I can use for Fixed asset additions, depreciation and controlling disposals (sale of assets)

    It would be appreciated if anyone can advise where I can download a termplate

    couple of suggestions maybe?

    Fixed asset record with depreciation - Templates - Office.com

    Fixed asset record - Templates - Office.com

    Free Asset Tracking Template for Excel by Vertex42

  5. #5
    Board Regular
    Join Date
    Jun 2006
    Posts
    3,163

    Default Re: Excel Fixed Asset Register

    Thanks for the reply & the links.

    I found the link below to be very useful & I can be adapt this to suit my needs

    Fixed asset record with depreciation - Templates - Office.com

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,168

    Default Re: Excel Fixed Asset Register

    How many assets do you need to keep track of (about)? Do you need to keep separate schedules of book and tax depreciation or are they both the same for you? I've built a couple of these over the years but usually for keeping track of additions and tying out to the assets to the general ledger. Though I have included depreciation too, usually to get an estimate. I take a dim view of templates because generally I never see any that would actually work the way I needed it to. But you do have to start somewhere.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Jun 2006
    Posts
    3,163

    Default Re: Excel Fixed Asset Register

    Hi Xenou

    Thanks for the reply

    I have looked at a number of templates on Google and none of them are suitable


    I would need a Fixed Asset register that would give me the following reports

    1) Book value report showing asset #, Cost Price, Prior Year Depr, Current Month Dep, YTD Dep, Accumulated Dep., Net Book Value-this report must be by department
    The depreciation is on a straight line basis and the % depends on the asset category for eg P & M = 20%, F & F = 16.67%-see sample
    2) Purchases Report -showing assets purchased-see attached sample
    3) Disposals report-see attached sample

    Page 1

     ABCDEFGHIJK
    1BR1        Page :1
    2Book Value Report as at End of September 2012        Date :1/09/2011
    3         Time :10:43
    4Asset No.DescriptionPurchasedTypeQuantityCost PricePrior Years Depr  Month-To-Date Depr Year-To-Date DeprAccumulated DeprNet Book Value
    5* Group:CORPORATE SIGNAGE         
    6** Cost Centre: Br1          
    7*** Department:NEW  VEHICLES          
    8*** TOTALS FORNEW VEHICLES  1 2928.47 536.89 48.81 585.69 1122.58 1805.89
    9** TOTALS FORBr1  1 2928.47 536.89 48.81 585.69 1122.58 1805.89
    10** Cost Centre:Br1         
    11*** Department:NEW  VEHICLES          
    12*** TOTALS FORNEW VEHICLES  4 2119.67 1379.21 13.99 189.18 1568.39 551.27
    13*** Department:SERVICE          
    14*** TOTALS FORSERVICE  5 161.87 159.17 0.00 2.70 161.87 0.00
    15*** Department:USED VEHICLES          
    16*** TOTALS FORUSED VEHICLES  1 1080.00 1062.00 0.00 18.00 1080.00 0.00
    17** TOTALS FOR   10 3361.53 2600.38 13.99 209.88 2810.26 551.27
    18* TOTALS FORCORPORATE SIGNAGE  11 6290.00 3137.27 62.81 795.57 3932.84 2357.16
    19* Group:FURNITURE & FITTINGS         
    20** Cost Centre:          
    21*** Department:NEW  VEHICLES          
    22*** TOTALS FORNEW VEHICLES  6 1426.49 204.21 19.83 237.80 442.00 984.49
    23*** Department:SERVICE          
    24*** TOTALS FORSERVICE  6 1288.40 193.14 17.91 214.78 407.92 880.48

    Excel tables to the web - Excel Jeanie Html 4



    Page 2

     ABCDEFGHIJ
    1        Page :2
    2Purchases Report (Book Values) For  1/10/2011 To 30/09/2012       Date :30/09/2010
    3        Time :13:58
    4Asset No.DescriptionLocationPurchasedStart Depr.TypeQuantityCost PriceYTD DeprNet Book Value
    5* TOTALS FORFURNITURE & FITTINGS   SL 16.6713 42,068.32 0.00 0.00
    6* Group:OFFICE EQUIPMENT        
    7** Department:NEW VEHICLES        
    8*** Cost Centre:   BR1         
    9*** TOTALS FORLAND ROVER   SL 20%1 1.00 0.00 0.00
    10** TOTALS FORNEW VEHICLES   SL 20%1 1.00 0.00 0.00
    11* TOTALS FOROFFICE EQUIPMENT   SL 20%1 1.00 0.00 0.00
    12* Group:PLANT & MACHINERY        
    13** Department:SERVICE        
    14*** Cost Centre:   Br1         
    15*** TOTALS FORBR1   SL 20%8 139,315.46 0.00 0.00
    16** TOTALS FORSERVICE   SL 20%8 139,315.46 0.00 0.00
    17* TOTALS FORPLANT & MACHINERY   SL 20%8 139,315.46 0.00 0.00
    18COMPANY TOTALS     24 236,349.78 0.00 0.00
    19          

    Excel tables to the web - Excel Jeanie Html 4




    Page 1

     ABCDEFGHIJKLMN
    1            Page :1
    2Disposals Report (Book Values) For October 2011 To May 2012           Date :1/05/2012
    3            Time :10:40
    4Asset No. DescriptionTypePeriodPurchased SoldQuantity Cost PriceAccumulated Depr.Net Book ValueProceedsProfit
    5* Group: CORPORATE SIGNAGE           
    6** Department:   NEW VEHICLES             
    7*** Cost Centre: BR1             
    81013 01CORPORATE SIGNAGESCRAPPED4 1/02/2007 1/01/20121 63,027.50 63,027.47 0.03 0.00 -0.03
    91073 01CORPORATE SIGNAGESCRAPPED4 1/06/2007 1/01/20121 80,500.00 75,133.31 5,366.69 0.00 -5,366.69
    10*** TOTALS FOR FIAT EASTERN CAPE  1/06/2007  2 143,527.50 138,160.78 5,366.72 0.00 -5,366.72
    11** TOTALS FORNEW VEHICLES    1/06/2007  2 143,527.50 138,160.78 5,366.72 0.00 -5,366.72
    12** Department:   USED             
    13*** Cost Centre: BR1             
    141089 01SIGNAGE - CHROMADEKSCRAPPED4 1/05/2008 1/01/20121 7,459.00 5,594.22 1,864.78 0.00 -1,864.78
    151097 01NOTICE BOARDSSCRAPPED4 1/07/2008 1/01/20121 2,955.00 2,117.75 837.25 0.00 -837.25
    161102 01SIGNAGE BOARDSSCRAPPED4 1/06/2009 1/01/20121 3,688.00 1,966.91 1,721.09 0.00 -1,721.09
    17*** TOTALS FOR FIAT EASTERN CAPE  1/06/2009  3 14,102.00 9,678.88 4,423.12 0.00 -4,423.12
    18** TOTALS FORUSED    1/06/2009  3 14,102.00 9,678.88 4,423.12 0.00 -4,423.12
    19* TOTALS FOR CORPORATE SIGNAGE    1/06/2009  5 157,629.50 147,839.66 9,789.84 0.00 -9,789.84

    Excel tables to the web - Excel Jeanie Html 4

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,168

    Default Re: Excel Fixed Asset Register

    It looks alright for a start. I'd want to keep the raw data in a cleaner data table without the summaries in between. You can pull the summaries on a separate tab using SumIf or similar formulas to foot the items by class. calculating the depreciation is the sticky part. Either you create a procedure to update the prior year accumulated totals, or you fill out the yearly depreciation using a column for each year which you can use to sum up the depreciation up to the current year. Or if you are brave and trust your data you can re-calculate the depreciation on demand based on cost, method, and time. Either way, be sure to tie out to the ledger and keep backups at the end of each year. I would also include: Date Retired and Asset ID, and if applicable a model number and serial number for the asset (which could also be in the description rather than a separate field). It might be very helpful to have the Vendor, Invoice Number, Invoice Date, and PO Number. If you have dates then you can combine the report for additions with the other assets (since you just filter your data on the current year and that's your additions). Ditto for retirements.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    Board Regular
    Join Date
    Jun 2006
    Posts
    3,163

    Default Re: Excel Fixed Asset Register

    Hi Xenou

    Thanks for the valuable input. I will take this into account when I set up my Asset Register

    Regards


    Howard

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com