Fixed Asset System

[Pat Hartman]

I do not store running balances. They are too easy to "break" and a violation of third normal form. I prefer creating transactions for each asset for each period. Then to find the depreciation to date, you would sum the individual transactions up to the specified point in time. To calculate a current value, you would sum the appropriate transactions and then subtract that amount from the purchase price.

You need a unique index on AssetID and DepreciationDate to prevent the append query from running more than once. I would force the date field to either the first or last day of any given month. You don't want someone re-running the depreciation process and creating multiple records for the same period.

You probably need to add AssetClass to the Asset table and a lookup table for it that includes depreciationMethod and UsefulLife. I think those two values are constant for a given class of assets such as autos or office equipment or commercial rental property, etc.

Anyway, a review of the tax law is required in order to properly structure the tables.

 

[Robert Dunstan]

Hi there Pat,

Yeah I agree with what you say regarding storing current values. My only concern was that if Dave was using the 'Reducing Balance' method for calculating depreciation then this relies on the current book value, hence why I sugessted storing the current value at then end of a financial year.

On re-visiting his tables you are right. I believe it would be better to create a seperate table for the category of asset and link it on AssetID to the main table. The category table would contain the DepreciationMethod, UsefulLife and DepreciationRate. It is normal accounting practice to assign methods of depreciation to a category of asset as many categories may use the same method. This approach is in line with accounting standards and provides consistency