Fixed Asset System

[sh3498]

Our company is needing a new Fixed Asset system to track assets and run the deprecation for the company. I noticed there is a sample database for Asset Tracking, but I am having to tweek it so much to get the depreciation part of it working. I was just wondering if anyone used Access for their Fixed Assets and Depreciation? If you wouldn't mind sharing your knowledge, I would love to see the layout and design of the database.

Stacy Harmon
Accounting-Finance

 

[Oldsoftboss]

Something along these lines....
http://users.impulse.net.au/predator/assetsontrack.htm
Dave

 

[sh3498]

This looks like a great software package, but it still does not calculate depreciation; this is more of an Asset Tracking system where I need the depreciaiton and Net Book Value fileds. I've thought about building one in Excel and using Pivot Tables to sort and report the information.....what do you think?

 

[Pat Hartman]

Access is much more suitable for your purpose. I looked at the link also. The system looks pretty comprehensive except that since it was built by an Australian company it doesn't deal with depreciation. I would talk to the authors. If the db is Access (looks like it might be) and you get the code, I think you could modify it to handle depreciation without too much trouble. You would need to add fields to the asset record to store useful life and PlacedInService and RemoveFromService dates. It's been a long time and I don't remember all the rules but you may need to also store the depreciation method. You could then add a table to store depreciation taken and run your calculations every January to append rows to the depreciation taken table. Then it's a simple matter of summing the depreciation taken records to know what to report and when to stop. I suggest storing the calculated results because the IRS changes the rules from time to time and you don't want to run into problems with having to maintain old and new calculations.

 

[Oldsoftboss]

The link is indeed an Access Db I've been playing around with in my spare time. I don't know much about depreciation, not being an accountant, or involved in that field, but would be keen to learn and include it. If you can talk me through it.
After reading your post I created a table and linked it to the asset. I copied it from the Asset Management Db created by the Access wizard.

The Sample Db has been there a fair while and is very Amateurish (not that I'm much better now) but learning all the time.
Dave

 

[Oldsoftboss]

And yes Pat, I am in the lucky country, even more so these days
Dave

 

[Pat Hartman]

Hi Dave, I didn't make the connection. I am familiar with this type of application having worked with one for a defense contractor some years ago. The system I used in the past was a mainframe system and was quite complicated. Largely because it needed to satisfy the DOD (Department of Defense). I think your app has a real market in the small to mid-size manufacturing company. If you want to market it in the US, it will need to handle depreciation. You should be able to find the current IRS (Internal Revenue Service) rules by searching their web site for the appropriate publication.

Let me know if you want a collabarator.

 

[Oldsoftboss]

Thanks Pat. Your vote of confidence means a lot. Sounds like your job has led you to many and varied places. Being a lowly fridge tech, all the access I have picked up is exactly that, self taught, except for the wonderful help on this forum.
I will look into the depreciation aspect ASAP.
May I ask, as I have aleady spilt the Db, what extra fields I may need in each table ? Once the fields and data types are in the back end I can work on the front end at my leasure.

So far
Main table, apart from the obvious,

AssetID - Auto No
PurchaseDate - Date/Time
PurchaseCost - Currency
Depreciation - Yes/No 'to tell whether to track depreciation or not
DepreciationMethod - Text 'not sure if that is correct
DepreciableLife - Number
SalvageValue - Currency

Depreciation table

DepreciationID - Auto No
AssetID - Number 'linkfield
DepreciationDate - Date/Time
DepreciationAmount - Currency

Your interest and help means a lot.

Thanks again
Dave

 

[Robert Dunstan]

Hi Dave,

Hope you don't mind me jumping in here but being an accountant I thought I would explain the rules of depreciation.

Basically depreciation is a way of spreading the cost of an asset over its useful life. The 2 most common methods of depreciation you can use are the straight line method or the reducing balance method.

Straight Line Method
This method is probably the most common one. What this method does is charge equal annual amounts of depreciation over the course of the asset's life until it reaches zero value or its scrap value (the technical term here in th UK for scrap value is residual value)

The formula is: (Cost of asset - residual value) / Useful Life

For example if you purchase an asset for £10,000 and you estimate its residual value and useful life to be £500 and 5 years, then the annual depreciation charge would be:

(£10,000-£500) / 5 = £1,900 p.a.

Reducing Balance Method
This method calculates depreciation as a % of the asset's net book value (NBV). The NBV is the cost of the asset less depreciation to date. Using this method means that highest amount of depreciation is charged in the first year of the asset's life. For example, again if you purchase an asset for £10,000 and has a residual value of £500 and the depreciation rate is 20% then the depreciation would be:

Year 1 (10,000-500) x 20% = 1,900
NBV = £7,600

Year 2 = 7,600 x 20% = 1,520
NBV = £6,080

Year 3 = 6,080 x 20% = 1,216
NBV = £4,864

Year 4 = 4,864 x 20% = 972.80
NBV = £3,891.20

Year 5 = 3891.20 x 20% = 778.24
NBV = 3,112.96
etc.

As you can see the depreciation charge is reduced each year and will carry on depreciating until you either write it off or the residual value is reached.

Hopefully I've explained it enough to give you an understanding of how the calculations work.

HTH
Rob

p.s. Apologies to Stacy who started this thread for going slightly off topic

 

[Oldsoftboss]

Thank Robert, it's great to take a different tack every now and then to broaden our knowledge. I'm sure Stacey will be reading with interest.
To put this in a database sense would I be right in saying that by putting in the data as you explained, maybe a pivot table would display the data in a viewable manner.

Having NEVER used pivot tables I am only guessing this is the way to go.
Or do you need to update the data annually so a current value is present ?

I am looking forward to this challenge.
Dave

 

[Robert Dunstan]

Hi Dave

Having never used Access for essentially creating a fixed asset register (i.e. hold the details of all the assets, depreciation etc.), my guess is to follow on from Pat's suggestion and run an append query to your depreciation table at the end of each financial year.

Looking at your tables I believe you have all the relevant fields you need plus you'll need a current value field in your main table. You are correct, I think, with setting your 'DepreciationMethod' to a text data type. This would allow you to create a couple of append queries to run the 2 different depreciation methods if you make use of both them. With reference to that, my interpetation of the accounting standards say that you can use different depreciation methods for different categories of asset providing it is consistent for each category.

For example if you have a fleet of vehicles each with their own asset number, you may decide that the reducing balance method is best as vehicles tend to lose more value in year 1 than in later years. What you can't do is have 1 vehicle using the reducing balance method and another using the straight line method. This would be inconsistent.

With regards to displaying data, I can't comment on the pivot tables as I've never used them, but from an accounting point of view, the most useful information to see is the cost asset of the asset, a cumulative depreciation figure and a net book value figure. This means that you would need to store the net book value at the end of year so that if your using the reducing balance method, the depreciation will be calculated correctly.

Well that's my 2 pennies worth Hopefully Pat may have a some more thoughts / suggestions.

HTH
Rob

 

[sh3498]

You guys are great! This is my first time to post to this forum and I am getting a lot of good information, although ya'll are getting above my head.

The one thing I cannot figure out how to do in the Access database is to actually "run" depreciation on the assets monthly. We have about 50,000 assets and I want to be able to push a button and have the Accumulated Depreciation and Net Book Value fields update each month after depreciating. I'm sure there is a query to do this, but I am not seeing it. I am fairly new at Acces and have had no formal training, so I'm just baby stepping through it. Thanks for all your help, you guys have been great!

 

[Rich]

For example if you have a fleet of vehicles each with their own asset number, you may decide that the reducing balance method is best as vehicles tend to lose more value in year 1 than in later years.

Is that acceptable to the IR?

 

[Robert Dunstan]

Rich,

Are you talking about depreciation for corporation tax purposes or tax on company cars for private use?

As far as I'm aware the IR is not really concerned about depreciation methods as when it comes to calculating taxable profits the depreciation is added back and then you use whats called Capital Allowances and these are deducted of your profit for tax purposes

 

[Rich]

Is Capital allowances based on a fixed percentage rate, per year?

 

[Robert Dunstan]

In short yes...but to be honest I don't get too involved with dealing with the company tax return. I leave that to the auditors

The whole idea behind capital allowances is essentially its the IR telling you what you can deduct off taxable profits as depreciation. The mechanism for calculating the allowance is based upon a percentage of the audited actual depreciation, the % rates being set by the IR. So the newer the asset, the more allowance you can claim. Well som ething like that

 

[Rich]

Thank you

 

[Robert Dunstan]

And the moral of the story is.....I HATE TAX!

 

[Rich]

And the moral of the story is.....I HATE TAX!

I don't mind paying the tax, it's what they spend it on that I object to

 

[Robert Dunstan]

I totally agree Rich, I just hate having to account for it...it's such a blooming minefield