Click here to Skip to main content
16,016,623 members
Articles / Programming Languages / SQL

Database for financial accounting application IV: General Purpose Documents & Fixed assets

Rate me:
Please Sign up or sign in to vote.
5.00/5 (10 votes)
18 Sep 2019CPOL80 min read 20K   1K   29   8
Designing a simple yet functional database for financial accounting application.

Introduction

In the previous articles, we discussed and implemented core accounting infrastructure design: general ledger, chart of accounts, (source) documents, financial statements structure, various profiles etc. Having core accounting infrastructure in place, we can continue to individual accounting operations.

If you wish to implement individual accounting operations for particular jurisdiction, a recommended way to go these days is to read state accounting authority’s methodologies/recommendations one by one and try to abstract individual operations to a generic one. (Of course, before doing that, it is highly recommended to read books: “Accounting for dummies”, “Accounting for beginners” and “Advanced accounting” :) )

Never try to implement an individual operation neither at database nor at business logic core level. You will end up in a fragile web of entities/tables/objects that is next to impossible to support. As you will see starting from this article, financial accounting is very friendly to generic operations and allows for implementing dozens of operations using a generic – state delta – method. Financial accounting is also very friendly to composite operations. Often you can compose a complex entity (document) out of a set of “trivial” (generic) operations. Therefore, modelling of financial accounting essentially means finding a common denominator for a bunch of simple (atomic) accounting operations. Once you’ve done that, you can continue to complex accounting operations and check whether they could be effectively represented as a harmonious collection of generic ones. You shouldn’t expect to do that in one run. Multiple iterations are required till all ends meet. This very article has been rewritten multiple times while I do grokk financial accounting.

A particular order of individual accounting operations implementation is not important except for the invoices that may operate pretty much on any other accounting entities (e.g. fixed assets, inventory etc.). I decided to follow the structure of balance and the first entity in a balance is assets.

However, before we continue to the assets we need to discuss a few accounting operations of general purpose that will be “used” by many other accounting operations including assets.

Transfer of Balance

Rarely an accountant starts a process of accounting from the very beginning of a company. It’s not that it cannot happen. It can, but rarely. In typical use case, an accountant begins his work in already established and functioning company. If the company used different accounting software previously or a company decides to discard some old accounting software and move for example to our software, there is no way to transfer all of the historical accounting data. Simply there is no common standard for financial data to rely on. The only way to “initialize” the accounting database in such common cases is to transfer balances of the ledger accounts only. The transfer of balance means that you plainly add ledger entries with the initial values at the moment of transfer (likely grouped by persons and cost centres).

Within the accounting infrastructure we developed in the previous articles, it means a specific type of (source) document – balance transfer statement – and a child (ledger) transaction, which is manually entered by an accountant (well, maybe copy/pasted or imported from some excel document). This way the accounting application gets to know the correct balances at the beginning.

Obviously, there could be only one such document within the accounting database. If it exists, it also sets the minimum date of all the other documents, as the balance transfer can only be the very first document within the database (chronologically).

This would be pretty much all the implementation, but the ledger is not the only accounting entity to be “initialized”. The application as well needs to know the state of fixed assets, inventory, cash, equity etc. Therefore, for most of the accounting entities there should be some implementation of the balance transfer, e.g. a specific type of (asset) operation. All of those entity specific implementations (in database terms – rows in some tables) will be children of the balance transfer statement document. As we incrementally develop our schema, it will grow pretty huge. Therefore, the transfer of balance document implementation in the business layer shall contain multiple “interfaces” to handle different aspects of transfer, e.g. one “interface” to allow an accountant for ledger balance transfer, one “interface” to allow an accountant to transfer fixed asset state data etc. I.e. there will be multiple business entities representing the same document.

Closing Entry

A closing entry is a journal entry that is made at the end of the accounting period (calendar year as a rule) to transfer balances from temporary accounts to a permanent account. Companies use closing entries to reset the balances of temporary accounts − accounts that show balances over a single accounting period − to zero. By doing so, the company moves these balances into permanent accounts on the balance sheet. These permanent accounts show a company’s long-standing financials.

Temporary accounts are accounts in the general ledger that are used to accumulate transactions over a single accounting period. The balances of these accounts are eventually used to construct the income statement at the end of the fiscal year. In simple words – those are the revenue and expenses accounts.  

Permanent accounts are accounts that show the long-standing financial position of a company. Balance sheet accounts are permanent accounts. These accounts carry forward their balances throughout multiple accounting periods. In simple words – those are the assets, equity and liabilities accounts.

The business logic behind the closing entry is simple:

  • When we fetch a balance of a revenue account (sum over all of the entries at a particular date) we expect to get a revenues for the current period (which is informative), not for all of the periods (which is not informative);
  • The financial result (profit/loss) at the end of the period is turned into equity (retained earnings) that could be used for different purposes on its own, e.g., to increase a stock capital, to pay dividends etc. You cannot do that on a collection of accounts.

Closing entry algorithm is as follows: (a) move balances of the revenue and expenses accounts to the summary account; (b) move retained earnings balance for the current period to the retained earnings balance for the previous periods; (c) move balance of the summary account to the retained earnings balance for the current period.

The algorithm can be broken down to the following ledger entries:

  • Get a balance for each of the revenue and expenses account within the ledger for the closing day;
  • Add a ledger entry for each revenue and expenses account equal to its balance but with opposite “sign”, i.e. debit instead of the credit and vice versa; It effectively makes the balances null/zero;
  • Add a “balancing” ledger entry to the summary account (a special equity account that is dedicated exclusively for the closing purpose); a value of the entry as well as its type (debit/credit) is calculated so that the whole transaction’s total debit amount is equal to the total credit amount;
  • Get a balance of current retained earnings account (a special equity account that stores the retained earnings for the current period);
  • Add a ledger entry to the current retained earnings account that has the same value as the current balance but with an opposite “sign”, i.e. debit instead of the credit and vice versa; It effectively makes the balance of the current retained earnings account null/zero;
  • Add a ledger entry to the previous retained earnings account (a special equity account that stores the retained earnings for the previous periods) that has the same value and “sign” as the current balance of the current retained earnings account; It effectively moves the balance of the current retained earnings account to the previous retained earnings account;
  • Add a ledger entry to the summary account that has the same value as the “balancing” ledger entry mentioned previously but different “sign”, i.e. debit instead of the credit and vice versa; It effectively makes the summary account balance null/zero;
  • Add a ledger entry to the current retained earnings account that has the same value and “sign” as the “balancing” ledger entry mentioned previously; it effectively moves the balance of the summary account to the current retained earnings account.

As the closing transaction moves amounts, the closing entry shall act as a blocking operation. I.e. no financial data of the documents before the closing could be changed. Otherwise, the resulting balance at the closure day will become invalid. Yet you may allow changing previous documents non-financial data, e.g. descriptions, analytics, as those will not invalidate the balance. As it’s up to a company whether to allow such changes or not, it could be implemented as a company’s policy field, i.e., add a field closing_policy to the company’s non-regionalizable non-versioned profile that we’ve developed in the previous article. I would use the following application defined enumeration for it:

  • No changes allowed
  • Description changes allowed
  • Description and analytics changes allowed

The closing entry document does not require any extra data structures in excess of the ones that we have already implemented. It’s just a (source) document of specific type with a child transaction.

From the objective programing point of view, closing entry is a read-only object that is created by a command with the following parameters: closing date, summary account, current retained earnings account and previous retained earnings account. Those parameter accounts are great candidates to add to the list of default company’s accounts, we developed in the previous article.

Accounting Note & Miscellaneous

No matter how much functionality you implement in an accounting application there will be more or less extraordinary accounting/source documents that are not covered by any dedicated functionality. In order to handle such documents every accounting application has to implement a generic document, which encompass only basic document data and could encompass ledger transactions that express a financial impact of the document. The type of generic document is commonly referred to as “miscellaneous”.

There are also some situations that have some financial impact on a company yet have no backing (source) document genuinely. E.g., corrections required due to the accounting policy changes, some error corrections etc. In such cases, the company should issue a descriptive document by itself. Such a document In Lithuanian legal system is referred to as “accounting note”. I haven’t found analogous document/concept in English sources. Hence, I believe it’s a jurisdiction dependent requirement. However, the application database under development is meant for Lithuania and this specific type of (source) document shall be implemented. Even though the data structure of the accounting note document is identical to miscellaneous document type, a distinct type should still be implemented as it has a special meaning in the domain. From an accountant’s point of view the difference is that accounting note could be (and shall be) printed as a document (either on paper or electronically), while miscellaneous document cannot (as its meaning is basically “unknown document”).

In our database terms, both of those documents have identical structure: a (source) document of specific type with (or without) a child transaction (or multiple transactions). Therefore, nothing to implement at the database level.

Source Document vs. Attachable Operation

Both miscellaneous document and accounting note might have an impact on other accounting entities as well. E.g., if an accountant makes some asset related ledger correction, it shall also be reflected in the appropriate asset entity. Which means that (almost) all of the accounting entities shall implement a generic operation as well.

From the business layer point of view, it means that these documents have indefinite structure (might include an arbitrary collection of children). A straightforward way to deal with that is to implement an attachable operation concept – to allow for a weak reference. The base data of the document will be dealt by a base entity while every attached operation will be dealt by a specialized entity, which requires a strict responsibility delimitation. A base entity cannot update a specialized entity’s data fields and vice versa. As a financial transaction clearly will be owned by the base entity, any attached child entity cannot modify it. Otherwise, it can break the business logic of the base entity. The same is true in reverse order. Therefore, if a particular entity (operation) requires full control over the ledger transaction a dedicated (source) document type shall be implemented.

As you will see later in this article, the attachable operation concept applies to the most types of (source) documents, not only to miscellaneous document and accounting note. E.g., you can acquire an asset by a bank payment.

Assets Overview

In financial accounting, an asset is any resource owned by the business. Anything tangible or intangible that can be owned or controlled to produce value and that is held by a company to produce positive economic value is an asset. Simply stated, assets represent value of ownership that can be converted into cash (although cash itself is also considered an asset). (See wiki) Assets are commonly classified in the following way:

  • Fixed assets (aka long-term assets) – assets that cannot easily be converted into cash and are not meant to be sold to a company's consumers/end-users directly. These are items of value that the organization has bought and will use for an extended period of time; fixed assets normally include items such as land and buildings, vehicles, furniture, office equipment, computers, software, plant and machinery etc. In simple words, assets, which are held for a long period, are known as fixed assets.
  • Current assets (aka short-term assets) – assets that can reasonably be expected to be sold, consumed, or exhausted through the normal operations of a business within the current fiscal year or operating cycle (whichever period is longer). Typical current assets include cash, cash equivalents, short-term investments (marketable securities), notes receivable, stock inventory, supplies, and the portion of prepaid liabilities (sometimes referred to as prepaid expenses) which will be paid within a year. In simple words, assets, which are held for a short period, are known as current assets.

The essential criteria of the grouping is the intended usage of a particular asset, e.g., if a building is meant to be used in company’s activities (as a factory, office etc.), it’s a fixed-asset; if the same building is meant for sale, it’s a current asset. However, there is some peculiarity about this fundamental grouping. If a company decides to sell some fixed asset, the asset shall be transferred to the current assets group, the transfer, however, shall be reversible. I.e., if the company changes its mind about the sale, the asset shall transferred back to the fixed assets and fully restore its previous state. If an asset is meant for sale but not in the current accounting period (financial year, “observable future”) the asset is classified as a fixed asset (referred to as an investment asset).

Accounting standards distinguish three more asset groups by their nature and specific use cases:

  • Intangible assets: an identifiable non-monetary asset without physical substance, e.g., brand, copyright, patent, license, software, trade secret, goodwill etc.;
  • Financial assets: a liquid tangible (rarely) or intangible asset that gets its value from a contractual right or ownership claim, e.g., cash, shares, bonds, mutual funds, bank deposits etc. Unlike land, property, commodities, or other tangible physical assets, financial assets do not necessarily have inherent physical worth or even a physical form. Rather, their value reflects factors of supply and demand in the marketplace in which they trade, as well as the degree of risk they carry;
  • Investment assets: tangible items obtained for producing additional income or held for speculation in anticipation of a future increase in value. Examples of investment assets include real estate, land, artwork etc.

Each of the groups have some specific accounting rules set by some accounting standards. However, the groups have little differences compared to “common” fixed-assets. At least Lithuanian accounting standards for those groups are just a rephrasing of fixed assets standard with a few derogations. The exceptions are long-term bank deposits, loans and notes receivable that are fundamentally different and will be implemented in the future articles. Therefore, for simplicity I am not going to split fixed assets into four distinct entities and will implement specific features for those asset groups in the fixed assets context. As the grouping of assets is set by accounting standards and is used for validation, it’s a good thing to include this classification within common asset data as an application defined enumeration. It also reveals what types of assets are included within application’s fixed asset concept:

  • Plant assets (long term tangible assets)
  • Investment assets
  • Financial assets (except for the long-term loans, notes receivable and bank deposits)
  • Intangible assets

Asset transactions act on (no surprize) asset accounts that have the following characteristics:

  • They have debit balance, i.e. asset value increase results in debit ledger entry and consequently in debit balance increase and vice versa, which allows to simplify state values to simple numeric values – positive for increase; negative – for decrease;
  • They are permanent accounts, i.e. their balance is not affected by closing entry and always reflects either a (current) value of an asset or a particular aspect (segment) of a value. Which allows for defining a set of asset (type) accounts as an asset’s state.

Considering the asset account characteristics, we can define an asset operation as a mutation (delta) of the assets state. Therefore, in order to describe pretty much any type of asset you shall:

  • Identify all of the operations that affect a particular type of assets (by reading the respective accounting standards and recommendations);
  • Identify all of the asset state elements – asset state variables – that are mutated by the respective operations; For the most part that will be the balances of permanent accounts, but other types of state variables also exist.
  • Identify all of the operations’ data (fields) – support variables – that are required to support each of the operation type, i.e. the data fields that are not state deltas, but are required to describe (and construct) a particular asset operation.

What you get is a table that has a column for each state variable, which allows querying for the asset’s state for any period. The table also has columns for the support variables. Each asset’s operation is a row in such a state table. Each row in such a table is a child of a (source) document.

Add a table for static asset’s descriptors that are not affected by operations, e.g. name, description, inventory number etc. Add a table for grouping. That’s all you are done with base functionality for any type of assets. Of course, it doesn’t mean that a particular type of assets wouldn’t require some extra/helper entities. But those are asset type specific, i.e. no common pattern.

Fixed-Assets Operations

Acquisition & Acquisition Costs

To be classified as a fixed-asset, an asset must: (1) have a value equal or greater than a fixed-asset capitalization value defined by the company’s accounting/financial policy (you wouldn’t want to declare an USB memory stick as a fixed-asset even if you use it for couple of years); (2) have a useful service life of more than one year; and (3) be used in business operations rather than held for resale (investment asset is an exception – a resale is expected to happen in indefinite future, not any time soon).

A fixed-asset capitalization value is a good candidate for a company’s non-regionalizable versioned profile field (it’s a numeric value that could change and should not be applied retroactively). Accounting standards also allow setting different capitalization values for different asset groups. Therefore, we will also need a field in the asset’s group table. The field shall be nullable to distinguish between actual capitalization value override and base capitalization value.

When a company acquires an asset, accountants record the asset at the cost of acquisition (historical cost). When an asset is purchased for cash, its acquisition cost is simply the agreed on cash price.  This cost is objective, verifiable, and the best measure of an asset’s fair market value at the time of purchase. Even if the market value of the asset changes over time, accountants continue to report the acquisition cost in the asset account in subsequent periods. However, the acquisition cost of an asset is not only the price payed for the asset itself but also the amount of cost incurred to place the asset in operating condition. I.e., in real life situations acquisition is not that simple.

To illustrate, assume that a company purchased new equipment to replace equipment that it has used for five years. The company paid a net purchase price of $150,000, brokerage fees of $5,000, legal fees of $2,000, and freight and insurance in transit of $3,000. In addition, the company paid $1,500 to remove old equipment and $2,000 to install new equipment. The company would compute the cost of new equipment as follows:

Net purchase price  

$150,000

Brokerage fees  

$5,000

Legal fees  

$2,000

Freight and insurance in transit 

 $3,000

Installation costs 

$2,000

Total Equipment cost 

$162,000

Obviously, acquisition of the equipment itself, installation and other costs related events occur at different times and are formalized by different source documents:

  • Acquisition (purchase) itself could be based on a sales contract;
  • Legal fees could be based either on an invoice or a bank payment (issued as required by law);
  • Installation costs could be based on an invoice (of a contractor) or a wage sheet (wage of the employees that installed the equipment) etc.

Moreover, in some cases acquisition costs related events could extend over really long periods. E.g., in case of long-term bonds acquisition value is increased every year by the value of accrued interests (which are formalized as an accounting note document).

Which brings us to two fixed assets operations: asset acquisition (itself) and asset acquisition costs (increase).

Both of the operations act on the same asset state variable:

  • Acquisition costs account – a balance of an acquisition costs account in the ledger. As we are examining assets, we deal with a debit balance, i.e. acquisition costs are debit entry in the ledger.

The equipment acquisition operations above could be represented like that:

Description

Operation Type

Δ

Acquisition costs account

Net purchase price

Acquisition

$150,000

Brokerage fees

Acquisition Costs

$5,000

Legal fees

Acquisition Costs

$2,000

Freight and insurance in transit

Acquisition Costs

$3,000

Installation costs

Acquisition Costs

$2,000

Balance: 

$162,000

Obviously, the acquisition and acquisition costs operations are applicable to all of the asset types. There can be no asset without an acquisition value.

As it was already mentioned, there could be various source documents behind every asset operation. Moreover, a single source document can affect multiple assets, e.g. buying a building and land within the same contract. Which brings us to the following requirements:

  • Relation between (source) documents and fixed-assets acquisition and acquisition costs operations shall be 0…n;
  • The (source) document type does say little to nothing about the asset operation and vice versa; the full asset acquisition transaction in the ledger is defined exclusively by the parent source document whatever it is. Acquisition and acquisition costs operations only care about the entries for the acquisition costs account and only about the amount attributable to a particular asset, as a single source document can be used for multiple assets acquisition within the same ledger account. I.e. fixed-assets acquisition and acquisition costs operations are attachable.
  •  Fixed-assets acquisition and acquisition costs operations act on semantically the same variable, i.e. the Concrete Table Inheritance model is off the table, we can only use either Single Table Inheritance model or Class Table Inheritance model.

Asset acquisition value for tax and deferred tax

From the (corporate) tax point of view, fixed assets are sort of “bags of expenses”. When a company acquires some fixed asset it doesn’t incur expenses, i.e. you cannot deduce the value of the asset from taxable income. On the other hand, an asset acquisition has a potential to incur either expenses or revenue when it is sold or discarded. An asset that is used by a company wears out and looses value, e.g. a new computer and a used computer obviously have different values. Tax law acknowledge these facts and sets out rules that define:

  • What is considered an asset’s acquisition value for tax purposes (not all costs could be added to an asset’s value);
  • How asset’s value could be allocated to expenses to account for the wearing out (depreciation) (again, there are more or less strict limits how to calculate the allocation for a year).

Financial accounting standards also set out rules on how to evaluate asset’s value and wearing out (depreciation). The rules set out by accounting standards are a way more detailed yet a way more flexible. Because the purpose of accounting standards is to provide precise information about a company’s financial state at any moment given. As tax rules and accounting rules have fundamentally different purposes, no surprise, they are different. Every so often, a value of the same asset will be different for accounting purposes and for tax purposes. (Actually, the same is true for income statement and corporate tax declaration) However, tax is a liability and as such shall also be treated as an accounting object, i.e. accounting cannot plainly disregard taxes.

In case of fixed assets, the tax impact is the following:

  • It is presumed that the book value of an asset (a value calculated for accounting purposes) equals to its market price, i.e. you can sell an asset for a book value and that will be a fair price.
  • If an asset’s tax value is less than its book value and you sell the asset, a company will gain profit for tax purpose. In accounting terms, it means that the company has a long-term tax liability that is equal to the value difference multiplied by tax rate. The concept is referred to as a deferred tax liability.
  • If an asset’s tax value is greater than its book value and you sell the asset, a company will suffer loses for tax purpose. In this case, it will decrease future tax liability. In accounting terms, it means that the company has a long-term tax liability decrease (sort of tax “advance payment”) that is equal to the value difference multiplied by tax rate. As it represents a long-term economic value for a company, it is accounted for in an assets account. The concept is referred to as a deferred tax asset.

That tax related side effects should be handled in tandem with fixed asset operations. Which means that we need to track tax value of an asset, i.e.:

  • Every operation that (potentially) affects tax value shall update it (by delta);
  • If there is a change in difference between tax value and book value due to the operation, the operation shall update balances of deferred tax liability account and/or deferred tax asset account.

Most of the fixed asset operations (potentially) can change the difference between tax value and book value. Therefore, it’s a common functionality across all of the operations. Moreover, deferred tax allocation does not affect the “parent“ operation behaviour (with a single exception – revaluation). I.e. the business logic is as follows:

  • Allow “parent” operation to do whatever it does;
  • Calculate the difference between tax value and book value before the operation and after the operation; if the difference changed, update deferred tax accounts to reflect the new difference.

Standard ledger entries to create or increase deferred tax liability are (switch debit & credit for decrease):

Dr Deferred tax expenses/revenue account

$1,500

 
 

Cr Deferred tax liability account

 

$1,500

       

Standard ledger entries to create or increase deferred tax asset are (switch debit & credit for decrease):

Dr Deferred tax asset account

$1,500

 
 

Cr Deferred tax expenses/revenue account

 

$1,500

       

If a deferred tax total balance is debit, it means that the Deferred tax liability account has zero balance while the Deferred tax asset account balance has a debit balance equal to the total balance. If a deferred tax total balance is credit, it means that the Deferred tax liability account has balance equal to the total balance while the Deferred tax asset account balance has a zero balance. Only one of the accounts can have non-zero balance at a time.

To sum it all up, we need four more asset state variables to reflect deferred tax:

  • Deferred tax liability account – a (credit) balance of a deferred tax liability account in the ledger.
  • Deferred tax asset account – a (debit) balance of a deferred tax asset account in the ledger.
  • Tax value – a tax value of the asset as required by law.
  • Tax value depreciation – accrued depreciation as required by law.

The tax rate variable that an operation requires in order to calculate deferred tax is somehow tricky. Obviously, you cannot use different tax rates for different assets. Obviously, the tax rate can change. A tempting conclusion is that the tax rate belongs to the non-regionalized versioned profile of the company (see the previous article for details). However, if an accountant changes the version (tax rate value) that has been used by an asset operation, the asset operation will not (and shall not) automatically update itself (a tax rate is used to calculate a value that in turn results into a ledger entry). We have a contradiction. On one hand, we shall not allow an accountant to specify the tax rate manually. On the other hand, the tax rate profile change shall not (cannot) cascade into existing operations. The solution is to have the tax rate in both company’s profile and operation (as a support variable), but in the application business layer only allow to update it to the current value set in company’s profile. It can still cause discrepancies between the respective tax rate in company’s profile and a tax rate in a particular operation. Yet such discrepancies can (and should) be easily identified though auditing procedure (some manual method or cron job).

The Deferred tax expenses/revenue account is irrelevant for asset acquisition and acquisition costs account, as they are not in control of the parent (source) document ledger transaction. However, other operations (e.g. depreciation) are in control of the parent (source) document ledger transactions. Which means that they will require the deferred tax expenses/revenue account reference in order to prepare a transaction. Therefore, we need to add the reference as a support variable – deferred tax expenses account. (naming due to size limitations and special symbols) This account in most cases will be a dedicated one for this specific purpose. Therefore, it’s a great candidate to add it to the list of default company’s accounts, we developed in the previous article.

Deferred tax functionality is common across all of the operations, i.e. its state variables as well as support variables are (potentially) used by multiple operations of different types. Therefore, it belongs to the abstract/common table (if you choose to use Class Table Inheritance model later on).

The potential tax impact for each operation will be discussed later on when describing each of the operation.  In the meantime, we have already described asset acquisition and acquisition costs operations. Therefore, let’s see the potential deferred tax impact for them.

Usually asset acquisition and acquisition costs operations does not have any specific tax impact like in the example used for illustration. However, there is one specific use case where they do have a tax impact. When you recognize some R&D investments (expenses) as an intangible asset:

Dr R&D assets account

$10,000

 
 

Cr Some expenses account

 

$7,000

 

Cr Some other expenses account

 

$3,000

For the tax purposes these expenses are, well, just ordinary expenses, i.e. they decrease taxable income. When you recognize them as an asset, you move their value (some portion of balance in the respective expenses accounts) into the asset acquisition costs account, i.e. income is reduced in the income statement but not in the corporate tax declaration. The other side of this “transformation” is that tax law wouldn’t recognize such an asset at all because it is expenses, same thing cannot be an asset and expenses at the same time, i.e., its tax value is zero. This creates a difference between tax value and book value, which is equal to the acquisition value. In this case, the tax value is less than the book value, which means that we need to register a deferred tax liability. Assuming 15% tax rate and using standard deferred tax ledger entries, the R&D expenses “transformation” operations above could be represented like that:

Description

Operation Type

Δ

Acquisition costs account

Tax Value

Deferred tax liability account (Cr)

Deferred tax asset account

R&D expenses capitalization

Acquisition

$10,000

$0

-$1,500

$0

Balance:  

$162,000

$0

-$1,500

$0

Deferred tax liability account is, well, a liability account, which has a credit balance. As we decided to use positive values for a debit balance, the credit balance should be represented as negative value for consistency.

From a functional point of view, both acquisition and acquisition costs operations shall be able to set tax value irrespective of acquisition value. However, as it is actually used very rarely, there should be means to expressly indicate intent to override equality (e.g. visually – a checkbox).

I will not include deferred tax liability account and deferred tax asset account variables in the further asset state tables, as it is not (generally) applicable for our main example with (physical) equipment. As you will soon see, the asset state table will get crowded soon – all of the variables will not fit a screen.

Depreciation & Amortization

The term “depreciation” is used for tangible assets while the term “amortization” is used for intangible assets. Otherwise, the concepts are same. Onward I will use term depreciation, just keep in mind that that the same also applies for amortization.

Depreciation is the amount of asset acquisition costs allocated to each accounting period benefiting from the asset’s use. Depreciation is a process of allocation, not valuation. Eventually, most assets wear out or become so inadequate or outmoded that they are sold or discarded; therefore, companies must record depreciation on every asset in service unless an asset does not wear out by nature (e.g. land, artworks, shares etc.). They record depreciation even when the market value of an asset temporarily rises above its original cost because eventually the asset is no longer useful to its current owner.

Depreciation is applicable to:

  • All of the plant assets except for land;
  • Some of the investment assets (e.g., it is applicable for rented premises, but not applicable for artworks);
  • Some of the intangible assets (e.g., it is applicable for software, fixed-term licence bought, but not applicable for trademarks).

Depreciation is not applicable to financial assets.

Depreciation is only allocated for the assets in service. If a particular asset is (temporally) not in service, depreciation cannot be allocated for the out of service period (e.g. while a building is under reconstruction). Which brings us to two fixed assets operations: asset depreciation and asset operational switch. The latter is trivial: affects no specific state variables as its mere existence toggles the operational state of the asset by itself. On the other hand, in Lithuanian legal system rendering an asset operational or non-operational shall be formalized by two specific (source) documents: fixed asset operational statement and fixed asset non-operational statement. I haven’t found analogous documents or concepts in English sources. I assume those are jurisdiction specific. However, the application is meant for Lithuanian jurisdiction. Therefore, I will split the operational switch type into two types – turn operational and turn non-operational – each with a respective (source) document type.

There are four common methods how the depreciation allocation for a period is calculated (in theory, any math function could be used, yet common accounting standards):

  • Straight Line;
  • Reducing Balance;
  • Sum of the Year' Digits;
  • Units of Activity.

All of the methods use asset‘s salvage value  (aka scrap value, liquidation value) – the amount of money the company expects to recover, less disposal costs, on the date an asset is scrapped/disposed, sold, or traded in. (could be zero) The acquisition costs of an asset are reduced by salvage value before calculating depreciation. The idea is not to depreciate an asset below salvage value.

The first three methods use asset service life in years or months to allocate depreciation. E.g. if the equipment acquisition costs are $162,000, salvage value is $6,000 and service life is 60 months (5 years), then a depreciation allocation value per month using straight line method is:

($162,000 - $6,000) / 60 months = $2,600

The fourth method is different. It measures asset service life in custom units, e.g., mileage for a car, some production units for production equipment etc. Which brings us to a requirement to have two asset service life values – one measured in months; the other – in custom measure units.

Another point to consider is whether to store the periods that the depreciation has been allocated (calculated) for. As mentioned above the depreciation is only allocated for the assets in service. There might be multiple service periods within a year, therefore, multiple depreciation periods per operation. On the other hand, you can ignore out of service periods and only store the total period. After all the allocation (calculation) periods are only used within the relevant depreciation method. Yet, if you allow an accountant to specify a period (a range of dates) to allocate (calculate) the depreciation for, the accountant shall also be responsible for continuity of such periods, i.e. that no gaps are left. Which is not good, as every mistake, that might be made, will be made. Therefore, a safe approach is only to specify the end of the desired period (“allocated at”). We already have (source) document date field. The domain question is whether there could be a requirement to allocate (calculate) depreciation for some other date than the allocation document date. In Lithuanian accounting practice there couldn’t. Therefore, I wouldn’t store “allocated at” as a field. However, this design choice is jurisdiction specific.

The design decision not to store allocation (calculation) periods does not refute the fact, that the calculations involved are relatively complex. An accountant would still like to know not only the final numeric value but also the math behind it. To fulfil this requirement an allocation method (probably encapsulated as an object) can be (and should be) made self-describing, i.e. to reflect the calculations in natural language step by step. The result is a textual description of the calculations, which is exactly what an accountant would like to see. Though typically the description will be short (several hundred characters), in some circumstances it could become as large as multiple thousands characters. (e.g., impairment, major repair, service life changes etc.)

Unlike assets acquisition and acquisition costs (increase) operations, the depreciation operation owns/controls all the ledger transaction and has a dedicated source document – depreciation report. A ledger transaction for a depreciation document looks like that:

Dr Fixed-assets depreciation costs account

$2,600

 
 

Cr Accumulated depreciation account (-)

 

$2,600

       

Fixed-assets depreciation costs account could be pretty much any costs (expenses) account, e.g. it could be some production costs account if the depreciated equipment is used in production; it could be some administrative expenses account if the depreciated asset is a company‘s office premises/building. As the depreciation operation owns the (whole) transaction, the reference to the costs account should be stored as a support field.

Accumulated depreciation account is a special kind of ledger account referred as a contra account and denoted with (-) sign. It has a credit balance even though it is used for assets. The idea of the account could be easiest explained by visualizing asset related ledger accounts hierarchy:

   

Dr

Cr

123 Equipment

$159,400

 
 

1230 Acquisition costs

$162,000

 
 

1231 Accumulated depreciation (-)

 

$2,600

Within the hierarchy, the Equipment account is an aggregate account, i.e. not used by a transaction, only sum over the child accounts. It‘s balance is also referred to as a book value of the assets. The Acquisition costs account is the one we used for acquisition. And the Accumulated depreciation account is used to store depreciation amounts. The idea behind the ledger account structure is pretty obvious – to allow analytics.

To sum it all up, we need four more asset state variables to reflect depreciation:

  • Accumulated depreciation account – a (credit) balance of an accumulated depreciation account in the ledger.
  • Service life months – the intended term of use/service in months.
  • Service life custom – the intended units of activity per asset lifetime.
  • Salvage value – the amount of money the company expects to recover, less disposal costs, on the date an asset is scrapped/disposed, sold, or traded in. (could be zero)

Moreover, to reflect depreciation as a source document we need three extra fields that are depreciation operation specific:

  • Depreciation method – a depreciation method used to allocate depreciation. Could be defined either as ENUM or application defined enumeration (TINYINT in database terms). The methods haven’t changed for the last couple of hundred years and there are no indications that it might change in the next one hundred. Therefore, it is quite safe to define them as ENUM at database level. However, for the consistency, I prefer application defined enumeration approach.
  • Depreciation description – a natural language description how the depreciation value was calculated.
  • Depreciation costs account – a (reference to) ledger account to store the depreciations costs for that particular operation.

Assuming that we are not going to use units of activity depreciation method, the previous example of the equipment acquisition can now be represented in the following way:

Description

Operation Type

Δ

Acquisition costs account

Accumulated depreciation account (-)

Service life months

Service life custom

Salvage value

Net purchase price

Acquisition

$150,000

$0

60

0

$6,000

Brokerage fees

Acquisition Costs

$5,000

$0

0

0

$0

Legal fees

Acquisition Costs

$2,000

$0

0

0

$0

Freight and insurance in transit

Acquisition Costs

$3,000

$0

0

0

$0

Installation costs

Acquisition Costs

$2,000

$0

0

0

$0

Starting to use the equipment

Operational Switch

$0

$0

0

0

$0

Allocating depreciation

Depreciation

$0

-$2,600

-1

0

$0

Balance:   

$162,000

-$2,600

59

0

$6,000

Accumulated depreciation account has a credit balance, because it’s a contra account as described previously. As we decided to use positive values for a debit balance, the credit balance should be represented as negative value for consistency.

Now comes the taxes. There are three tax related aspects for a depreciation operation:

  • Different depreciation for accounting and for tax. First of all the depreciation method and its parameters (service life, salvage value) used for financial accounting could be different from the depreciation method and parameters used for tax. It happens rarely, yet, it happens. E.g., a company could decide to use reducing balance method while law only allows for straight-line method. As a result, depreciation costs in accounting will be different from the depreciation costs calculated for tax purpose, which in turn creates a gap between tax value and book value.
  • Asset not depreciable for accounting or for tax. Depreciation could be allocated for the accounting purposes, yet not allocated for tax purposes. E.g., depreciation of R&D as an asset is allocated in accounting, but not for tax purposes. Depreciation could be allocated for the tax purposes, yet not allocated for accounting purposes. E.g., when using revaluation model for investment assets, depreciation is not allocated in accounting, but has to be allocated for tax purpose. Both of the use cases create a gap between tax value and book value.
  • Asset has been impaired or revalued. As described later in this article, an asset could be impaired or revalued which changes its book value. However, it doesn’t change its tax value. Therefore, the resulting depreciation amounts for accounting and tax could be different even if the depreciation methods and parameters are the same. Which in turn lead to a gap between tax value and book value.
  • There could be a combination of all the factors.

The generic algorithm for the deferred tax that we described above can handle all of the use cases. However, it needs to know the depreciation amount for tax. Of course, an accountant would like to know the depreciation amount for tax as well. Which brings us to a requirement to duplicate all of the depreciation related asset state variables for taxes (except for the (accumulated) tax depreciation that we implemented earlier). We will also need to duplicate depreciation method (support) variable. Both depreciation method for accounting and depreciation method for tax shall be nullable to account for the fact that depreciation is not allocated for either accounting or tax purposes. We will not need to duplicate description, as the description field could include both. We will not need to duplicate costs account, as the depreciation for tax amount is not included in the accounting transaction.

To sum it all up, we will need three more asset state variables:

  • Tax service life months – same as service life months but for tax.
  • Tax service life custom – same as service life custom but for tax.
  • Tax salvage value – same as salvage value but for tax.

Moreover, to reflect depreciation as a source document we need an extra (support) field:

  • Tax depreciation method – same as depreciation method but for tax.

Asset state variables can be mutated by other operations. Therefore, they belong to the abstract/common table (if you choose to use Class Table Inheritance model later on). The support variables are different thing. Depreciation methods, description and costs account are specific to the depreciation operation. Therefore, we can opt to use Class Table Inheritance model over Single Table Inheritance model, i.e. exclude those fields into a separate table. However, I would opt not to do that for the following reason:

  • Depreciation operations are the most common operation type as it’s common to allocate depreciation every month in accounting practice;
  • There are only four fields that could be excluded; out of them one (costs account) that could be abstracted for use in multiple operations;
  • The total fixed asset operation entries count will be small. At max not so average company will have 200 operation asset units, which brings us to 2.400 depreciation operations plus around 600 operations of other types per year, which brings us to 30.000 entries per 10 years.

Good ol’ Occam’s razor: Entities should not be multiplied without necessity. As you will soon see, these depreciation related support variables are the only ones that could be extracted into a separate table.

In simple terms, I do not see neither a particular gain nor loss in this case. It is a trade-off between extra join and normalization.

Major Repairs, Impairment & Impairment Reversal

Another fixed-asset operation closely related to the acquisition is major repairs, e.g., a reconstruction of a building. If the result of the repairs is either a (significant) value increase or an extended service life of the asset (or both) the acquisition costs should be increased and service life extended. E.g., if a major repair extends the equipment service life by two years and its value is $30,000, the operation could be represented like that:

Description

Operation Type

Δ

Acquisition costs account

Accumulated depreciation account (-)

Service life months

Service life custom

Salvage value

Initial Balance:

$162,000

-$2,600

59

0

$6,000

Major repair of the equipment

Major Repair

$30,000

$0

24

0

$0

Balance:

$192,000

-$2,600

83

0

$6,000

Obviously, not all of the assets could be repaired by their nature. While pretty much all of the plant assets and investment assets could be repaired, only some of the intangible assets and none of the financial assets can. E.g., you can repair some software, but you cannot repair shares or bonds.

As well as acquisition and acquisition costs operations, the major repair operation does not have a dedicated (source) document type. Typically, it’s going to be an invoice. However, wage sheet, inventory (e.g. spare parts) discard or general contract could also be valid documents.

For now, the major repair operation fits the previous schema, but here comes a new operation – impairment of assets. In order to provide realistic financial information, business assets should be tested for impairment when a situation occurs that causes the asset to lose value. Impairment losses can occur for a variety of reasons:

  • when an asset is badly damaged (negative change in physical condition)
  • the asset’s market price has been significantly reduced
  • legal issues have had a negative impact on the asset etc.

To put it into simple words – building before fire has different value than after fire.

Value of the impairment is another asset state variable. It is actually a balance of impairment account, which is a contra account, just like the one for depreciation. The reasoning behind its use is the same – to reflect the decreased value of an asset yet to allow for the analytics, i.e. to distinguish initial acquisition costs and further changes.  Assuming the major repair happened after a fire and only recovered (not extended) service life the previous example could look like that:

Description

Operation Type

 

Δ

Acquisition costs account

Impairment account (-)

Accumulated depreciation account (-)

Service life months

Service life custom

Salvage value

Initial Balance:

$162,000

$0

-$2,600

59

0

$6,000

Fire

Impairment

$0

-$50.000

$0

0

0

$0

Major repair of the equipment

Major Repair

$0

$30,000

$0

0

0

$0

Balance:

$162,000

-$20.000

-$2,600

59

0

$6,000

                 

As well as the depreciation operation (and unlike acquisition), the impairment operation owns/controls all the ledger transaction and has a dedicated source document – impairment evaluation. It could be internal or external document. In both cases, the document content is the same – evaluation of either a fair value of the damaged asset itself, or the costs of repairs that are required to repair the asset (e.g. quotes, certified professional evaluation etc.). A ledger transaction for an impairment document looks like that:

Dr Impairment loss account

$50,000

 
 

Cr Accumulated impairment account (-)

 

$50,000

       

The loss account could be pretty much any costs (expenses) account. As the impairment operation owns the (whole) transaction, the loss account should be stored along with the other operation data. We can recall that the depreciation operation also requires reference to a depreciation costs account. Moreover, in both cases the referenced account is used to balance the ledger transaction. Therefore, we can abstract the account reference for both operations as a corresponding account.

A major repair could reverse material impairment. E.g., building caught fire and then was repaired. However, the impairment could also be determined by events that do not manifest as a physical damage. E.g., sharp drop in market prices. In such cases, the impairment could be eliminated without (real) financial costs. E.g., market has recovered. To handle such use cases we need one more operation – impairment reversal. It behaves exactly like impairment but backwards. I.e. you would debit Accumulated impairment account and credit Impairment loss account for the amount restored. Obviously, it doesn’t require any extra fields as it acts on the same data. Obviously, you can only revert impairment if it exists, i.e. the Accumulated impairment account balance (credit) after reversal cannot rise above zero as impairment means decrease of asset’s value (negative).

Now comes the taxes. There are two tax related aspects for a depreciation operation:

  • Impairment never decreases tax value of the asset. Therefore, it always increases gap between tax value and book value.
  • Major repairs might increase tax value of the asset. Law allows treating major repairs as increased tax value in general. However, there are exceptions. Not all types of expenses could be used to increase tax value. Major repair after impairment that only recovers the asset shall not increase tax value. In both cases, there might be a gap between tax value and book value.

The generic algorithm for the deferred tax that we described above can handle both of the use cases. In case of impairment, an accountant wouldn’t need to specify any extra data: the tax rate is in the company’s profile and the tax value/book value difference equals to the impairment amount. In case of major repairs, an accountant shall manually specify the change of the tax value. However, as it is actually used rarely, there should be means to expressly indicate intent to override equality (e.g. visually – a checkbox).

Assuming 15% tax rate the previous example with deferred tax impact could look like that:

Description

Operation Type

Δ

Acquisition costs account

Impairment account (-)

Deferred tax asset account

Initial Balance:

$162,000

$0

$0

Fire

Impairment

$0

-$50.000

$7,500

Major repair of the equipment

Major Repair

$0

$30,000

-$4,500

Balance:

$162,000

-$20,000

$3,000

Revaluation & Revaluation Surplus Utilization

Next comes a revaluation operation. Revaluation of fixed assets is the process by which the carrying value of fixed assets is adjusted upwards or downwards in response to major changes in its fair market value. Accounting standards typically require fixed assets to be initially recorded at cost but they allow two models for subsequent accounting for fixed assets, namely the cost model and the revaluation model:

  • In the cost model, the carrying value of fixed assets equals their historical cost less accumulated depreciation and accumulated impairment (losses). There is no upward adjustment to value due to changing circumstances.
  • In revaluation model, an asset is initially recorded at cost but subsequently its carrying amount is increased to account for any appreciation in value.

The difference between the cost model and the revaluation model is that the revaluation model allows both downward and upward adjustment in value of an asset while cost model allows only downward adjustment due to impairment loss.

The revaluation operation is kind of extended impairment operation – does the same but both ways. Therefore, the impairment operation owns/controls all the ledger transaction and has a dedicated source document – revaluation (report). It could be internal or external document. In both cases, the document content is the same – evaluation of a fair value of the asset itself (e.g. quotes, certified professional evaluation etc.).

The revaluation operation could be applicable to all of the asset types except for the investment assets and the financial assets, subject only to the accounting model.

Now comes the taxes. The revaluation result (increase or decrease in book value) never changes tax value. Therefore, a revaluation always creates a gap between tax value and book value. The generic algorithm for the deferred tax that we described above can handle this use case by itself, as all the data is known: the tax rate is in the company’s profile and the tax value/book value difference equals to the revaluation result.  However, this particular case is exceptional, because it affects the revaluation related asset state variable – Revaluation surplus account.

E.g., if the asset was previously impaired by $20,000, the total asset book value after the revaluation shall be increased by $48,000 and the expected tax rate is 15%, a ledger transaction for a revaluation document looks like that:

Dr Accumulated impairment account (-)

$20,000

 

Dr Appreciation account

$28,000

 

Dr Deferred tax expenses/revenue account

$3,000

 
 

Cr Revaluation surplus account

 

$23,800

 

Cr Deferred tax liability account

 

$4,200

 

Cr  Deferred tax asset account

 

$3,000

 

Cr Impairment loss account

 

$20,000

That's a max coverage transaction, i.e., it includes all the possible ledger entries for the revaluation operation. Actual transaction might not include all the accounts subject to the asset state. As you can see it uses the same accounts, the impairment does, plus two extra accounts that are actually new asset state variables:

  • Appreciation account – an actual appreciation ledger account balance that holds the increased portion of an asset’s value.
  • Revaluation surplus account (aka revaluation reserve) – an actual revaluation surplus account balance. It is an equity account even though is used in association with assets. It can also be used (changed) by equity operations, e.g. by increasing company stock capital by a value of the revaluation surplus.

The Impairment loss account is the same (semantically) as in the impairment operation. Therefore, we can use the support field – corresponding account.

As you can see from the example, if revaluation increases asset’s value, the increased portion is distributed between Deferred tax liability account and Revaluation surplus account. I.e. instead of standard deferred tax transaction:

Dr Deferred tax expenses/revenue account

$4,200

 
 

Cr Deferred tax liability account

 

$4,200

       

We use custom revaluation specific deferred tax transaction:

Dr Appreciation account

$4,200

 
 

Cr Deferred tax liability account

 

$4,200

       

While the Revaluation surplus account is credited for the remaining amount of value increase. I.e. in this case the algorithm is as follows:

  • Calculate book value increase (atop of the acquisition value) portion;
  • Multiply it by tax rate to find out the deferred tax amount of the increased portion;
  • Reduce revaluation surplus by the deferred tax amount of the increased portion;
  • Calculate the impairment recovery amount (if an asset was impaired before revaluation, i.e. its value was reduced below acquisition value);
  • Multiply it by tax rate to find out the deferred tax amount of the recovered portion;
  • Add standard deferred tax transaction entries for the deferred tax amount of the recovered portion.

The algorithm does not require any extra assets state variables or support variables. It only provides for different handling of the same data.

The revaluation operation has a common object with equity operations, i.e., the Revaluation surplus account is both part of the asset state and part of an equity state. Its balance is subject to mutations by both assets and equity operations. Therefore, we will need a special (technical) asset operation – revaluation surplus utilization. The only asset state variable affected by the operation obviously will be the Revaluation surplus account. We will discuss this operation in more details in the next articles in the series dedicated to equity operations.

Consider the previous example. Assume the company intends to switch to revaluation model and carries out a revaluation exercise, which estimates the fair value of the equipment to be $187,400. The carrying amount at the date is $139,400 (= $162,000 - $20,000-$2,600) and revalued amount is $187,400 so an upward adjustment of $48,000 is required. Than the company decides to utilize $20,000 to increase its stock capital. The resulting operations can be represented like that (irrelevant variables skipped for brevity, wouldn’t fit a screen):

Description

Operation Type

Δ

Acquisition costs account

Impairment account (-)

Appreciation account

Revaluation surplus account

Deferred tax liability account

Deferred tax asset account

Initial Balance:

$162,000

$0

$0

$0

$0

$0

Fire

Impairment

$0

-$50.000

$0

$0

$0

$7,500

Major repair of the equipment

Major Repair

$0

$30,000

$0

$0

$0

-$4,500

Revaluation

Revaluation

$0

$20.000

$23,800

-$23,800

-$4,200

-$3,000

Stock capital increase

Revaluation Surplus Utilization

$0

$0

$0

$20,000

$0

$0

Balance:  

$162,000

$0

$28,000

-$3,800

-$4,200

$0

Investment Revaluation

If the revaluation model is used in respect of investment assets or financial assets, a revaluation operation is fundamentally different. In this case, the transaction looks like that:

Dr  Appreciation and impairment account

$28,000

 
 

Cr Financial revenues account

 

$28,000

       

Alternatively, if the change is negative:

Dr Financial expenses account

$28,000

 
 

Cr  Appreciation and impairment account

 

$28,000

       

In contrast with a “standard” revaluation, the asset impairment account is not used. The same Appreciation and impairment account can have both debit and credit balance to reflect value both over and below acquisition value. Though you do not need a separate asset state variable (asset state account) for that, it means that different logic is applied for the same data – appreciation account is also used for impairment.

Even though the operation is still officially referred to as revaluation, to reflect the peculiarity of this use case we need a dedicated fixed assets operation type. The operation does not require any additional asset state variables or supporting fields. (revenue/expenses account can be handled by the generic corresponding account field)

The overall impact on the deferred taxes is the same as for the base revaluation. However, this time we don’t have to handle the deferred tax amount of the increased portion. Therefore, the generic algorithm for the deferred tax that we described above can handle this use case normally.

Depreciation of Revaluation Surplus

As you can see, when using asset revaluation, a part of asset’s value is (could be) a revaluation surplus/gain, which is a part of total (book) value of the asset and as such shall also be depreciated.

Therefore, in order to represent a depreciation of appreciation (revaluation surplus) we need one more asset state variable:

  • Accumulated depreciation for appreciation account – a balance of accumulated depreciation for appreciation, i.e. of the increased portion of an asset’s value. It is used by the depreciation operation exactly the same as accumulated depreciation account but for the depreciation of the surplus.

One more caveat to the depreciation of appreciation is a requirement to sort of depreciate the revaluation surplus, i.e. the equity created by the asset. It is required in order for the equity to match backing asset value. The straightforward way to do that is to debit revaluation surplus account instead of a depreciation costs account. However, accounting standards (at least in some jurisdictions including Lithuania) requires different approach:

  • Depreciation for both of the acquisition costs and appreciation is allocated in usual way: you credit respectively accumulated depreciation account, accumulated appreciation depreciation account, and debit some depreciation costs account.
  • To “depreciate” the revaluation surplus you debit revaluation surplus account and credit retained earnings account, which is also an equity account that is “normally” used for retained earnings after the closing entry is made at the end of a period.

Even though the second part of the appreciation depreciation looks excessive, it is not. You should recall that the revaluation surplus could be used by an equity operation as well. Therefore, the revaluation surplus is not always equal to appreciation. E.g. if the entire revaluation surplus has been used to increase the stock capital of the company, you would not need the second part of the appreciation depreciation transaction at all.

The (reference to) retained earnings account is a support field in the context of fixed term assets (like depreciation costs account). Even though, it is an equity account and as such – a permanent account, it is (a) affected by the closing entry; and (b) its balance change is always equal to the revaluation surplus account change (with opposite sign). The (reference to) the same account is also used in discard and sale operations (coming next). Therefore, it belongs to the abstract/common table (if you choose to use Class Table Inheritance model).

Discard, Sale & Reversal of Sales

Discard and sale are universal operations just like acquisition. You can discard or sell any type of assets. Though there might be individual exceptions due to legal issues, e.g. typically you cannot sell licences.

From the transaction point of view, asset’s discard and sale are actually the same operation with respect to the asset’s state. In both cases a company no more owns an asset, which means, that all of the asset’s accounts balances shall be set to null (including deferred tax accounts). Obviously, to do that we:

  • Within the asset’s state context, add deltas which absolute value equal to the current balances but with an opposite sign.
  • Within the transaction context, add a credit ledger entry for debit balance and a debit ledger entry for credit balance.

In case of the previous example, the transaction to discard the equipment would look like that:

Dr Fixed-assets discard costs account

$187,400

 

Dr Accumulated depreciation account (-)

$2,600

 

Dr Deferred tax liability account

$4,200

 

Dr Revaluation surplus account

$3,800

 
 

Cr Acquisition costs account

 

$162,000

 

Cr Appreciation account

 

$28,000

 

Cr  Deferred tax expenses/revenue account

 

$4,200

 

Cr Retained earnings account

 

$3,800

The asset discard operation is in control of the whole ledger transaction. Therefore, we also need a special document type to be the owner of the transaction – asset discard statement.

In the discard process some spare parts, scrap metal or similar stuff could be recovered. The latter stuff is qualified as inventory in accounting. Therefore, the asset discard statement shall (could) also be a parent of inventory acquisition operations. This aspect of the asset discard statement will be implemented in the future articles in the series. Basically, the inventory acquisition operations entered by an accountant will provide the value (debit balance) of the inventory recovered while the fixed-assets discard costs amount will be reduced by the (recovered) inventory value.

Furthermore, if an asset is lost due to someone’s fault or the asset was insured, the company can have legitimate expectation to recover some or all of the loss. In this case, some or all of the loss (book value of the asset) shall be attributed to notes receivable. Therefore, the asset discard statement shall (could) also be a parent of notes receivable. This aspect of the asset discard statement will be implemented in the future articles in the series. Basically, the notes receivable entered by an accountant will provide the value (debit balance) of the loss amount recoverable while the fixed-assets discard costs amount will be reduced by the recoverable amount.

The equipment sale transaction is incomplete to compare to the discard one. The asset’s sale could be formalized by multiple (source) document types, e.g., invoice, cash receipt, general contract etc. Which means that instead of the debit ledger entry into the Fixed-assets discard costs account there might be various entries that shall be handled by the parent operation. I.e. sales operation is not in control of the ledger transaction and consequently is attachable to an existing document of (almost) any type.

While discard is an irreversible operation, the sale is a reversible one. Counter party of an asset’s sales contract can terminate the contract and return the asset. The fact of reversal is obviously represented by reversing entries and deltas recorded by the sales operation, i.e. do the same, but with an opposite sign. The sales reversal operation just like its counterpart – sales operation – is not in control of the ledger transaction and consequently is attachable to an existing document of (almost) any type.

As you can see those three asset operations do not require any additional asset state variables or supporting fields. (Fixed-assets discard costs account for the discard operation is handled by the generic corresponding account field)

The generic algorithm for the deferred tax that we described above is not applicable for discard or sale as for those operations the deferred tax balance shall be zeroed out as described above.

Requalification as Inventory & Requalification Reversal

If a company decides to sell some asset in a near future (puts it out of service and starts looking for buyers), the asset should be requalified as an inventory as it no longer matches fixed asset definition. The action is similar to discard in a way that you need to zero out asset’s accounts. The semantical difference is that the requalified asset is not gone. Moreover, as opposed to the discard operation, the requalification is reversible – a company can change its mind. In such an event, the asset’s accounts balances should be restored. Therefore, we need two more asset operations. The operations also requires special types of (source) documents – asset requalification and asset requalification reversal. The (source) documents are composite ones and own (in full control) their transactions. Basically, what needs to be done is to zero out the asset’s ledger accounts and to move the resulting debit balance to an inventory account. (and vice versa for reversal) I.e. from the fixed asset’s point of view, the relevant portion of transaction will be the same as the sales transaction. Therefore, additional asset state variables or supporting fields are not required.

The inventory operations will be implemented in the future articles in the series. For now, we just need to know that both of the asset operations will have their counterparts in the inventory domain. An asset requalification document will be a parent for both asset requalification and inventory requalification acquisition operations. The transaction will be controlled by both of the operations, i.e. the asset requalification operations knows how to discard asset’s accounts balances and the resulting debit balance while the inventory requalification acquisition operation knows how to handle the debit balance. The same is true for the reversal document.

As the requalification operation demonstrates a company’s intent to sell an asset and the asset sale operation is universal, the requalification operation is universal as well (applicable to all of the assets types).

The generic algorithm for the deferred tax that we described above is not applicable for requalification as for this operation the deferred tax balance shall be zeroed out just like for sale or discard.

Split & Consolidation

Split of the asset happens when a company decides for some reason to treat a part of an asset as a separate asset. E.g., a company performed a legal procedure to divide a building into several premises and decided to sell some of the newly formed premises. Actually, it is a composite operation:

  • One discard operation that might also be partial, e.g. hive off a part of premises, yet the main premises continues to exist as a legal entity even though with smaller area;
  • One or more acquisition operations for the newly formed assets.

Even though those discard and acquisition operations are very similar to the ones discussed previously, they have substantial differences:

  • Casual discard operation moves the book value of the assets to costs, while split discard operation does not. Actually, it makes no transaction in the ledger. The new assets used to be part of the old (remaining) asset, therefore, they are of the same nature and respectively are accounted within the same ledger accounts, i.e. no ledger account balance change;

Casual acquisition operation only records acquisition costs change, while split acquisition operation “inherits” all of the “parent” asset state (a certain percentage of state variables). If a “parent” asset was depreciated, all of its parts were depreciated. The same goes for the rest of the operations. This fact shall be reflected within the newly formed assets.

To sum it all up, to implement asset split operation we need two “dedicated” operations: split discard and split acquisition. The split operation is also a special type of (source) document – asset split statement. It could be internal (e.g., a decision to somehow dismantle an equipment) or external (e.g. some legal certificate regarding real estate). Either way, it is a document that specifically formalizes the split.

One might ask a question, why wouldn’t we implement a field amount. The reason is that a fixed term assets are not fungible. You cannot exchange one building for another because each of them is uniquely identified. Fixed term asset split is an exception not a general property of a fixed asset. Therefore, it shall be handled exceptionally and not generalized for all of the operations. Actually, I’ve made this mistake in the current version of my accounting application. This “feature” is among top three most criticized by accountants.

Consolidation of assets happens when a company decides for some reason to treat two or more assets as a single asset. E.g., a company performed a legal procedure to consolidate few premises within the same building into single premises. It’s a composite operation very similar to the split operation:

  • Two or more discard operations that are always “full” as the consolidated assets cannot continue to exist – they become indistinguishable part of the new asset;
  • One acquisition operation for the newly formed (consolidated) asset.

    The consolidation discard and acquisition operations are almost identical to their counterparts from the split operations with the following differences:

  • A general ledger transaction might be required if the initial assets were accounted in different ledger accounts;
  • The discard of the initial assets is always “full” as they cease to exist as individual entities;

The remaining service life of the new asset should be specified by an accountant as there is no precise way to calculate it using different remaining service lives of the initial assets (and there is no reason why they should be same). The same is true for the remaining service live for tax purpose.

To sum it all up, to implement asset consolidation operation we need pretty much the same things as for the split operation: two “dedicated” operations – consolidation discard and consolidation acquisition – and a special type of (source) document – asset consolidation statement. You might be tempted to reuse the same asset operation types, but you shouldn’t. First, the types are cheap, just another application defined enumeration value within the same database field. Second, operation types are informative. It’s nice to see in the operation listing how exactly the asset was acquired or discarded.

Both split and consolidation operations are of technical nature. They do not alter company’s financial state by themselves and only allow for different treatment of different asset’s parts in the future. As such, they are applicable for all of the asset types.

The generic algorithm for the deferred tax that we described above is not applicable for split and consolidation, as these operations have no financial impact.

Transfer of Balance & Miscellaneous

As discussed previously, (almost) every accounting entity has to implement a way to “initialize” its state when an accounting process is started for an already existing and functioning company. We have implemented ledger accounts balance transfer as a ledger transaction. Same way we implement fixed-term asset transfer – as an asset operation that trivially allows an accountant to set initial values of all the state variables. Functionally it is the same as split acquisition and consolidation acquisition. The only difference is that for the latter state variable values are calculated while for the balance transfer acquisition state variable values are entered manually by an accountant (copy/pasted, imported from an excel file etc.). Therefore, at first glance, we don’t need to implement any new asset state variables or support data fields. Unfortunately, the first glance is deceptive. In this case, we are dealing not with a real acquisition (not even a synthetic one like split or consolidation) but with a transfer of data about assets that were acquired long ago. Which brings us to the following use case requirements:

  • An actual acquisition date is required;

  • An initial operational state is required.

The first requirement is pretty obvious – a real acquisition date of assets should be provided in business and tax reports otherwise they would show false data. The second requirement is due to the rules for depreciation allocation. Normally, depreciation is only allocated starting from the next month after the asset become operational (a company started using it). However, if we transferred data about an asset at January 1st and the asset is already operational, depreciation shall be allocated for January as well. Hence, an indicator is required whether the asset, which data has been transferred, was already operational at the transfer date.

If we were to implement a “normal” operation, that would mean requirements for support data fields. However, in this case, we are dealing with an extraordinary operation (document) that can only happen once per company’s database lifetime. Hence, it is more practical to place those data fields to the static (common) asset data.

The generic algorithm for the deferred tax that we described above is not applicable for transfer of balance. An accountant shall manually specify deferred tax balances (if any).

The final asset operation to implement is “miscellaneous”. As discussed previously, situations happen when an accountant has to deal with extraordinary documents (events) that cannot be handled by any dedicated accounting application functionality. It might also be true for assets operations. E.g., there might be some accountant’s (data entry) errors that cannot be fixed retrospectively. Therefore – miscellaneous, an operation that trivially allows an accountant to manipulate an asset’s state manually. It doesn’t have a dedicated (source) document and could be attached to pretty much any existing document.

The generic algorithm for the deferred tax that we described above can (and should) handle this operation by itself. The deferred tax is always a difference between tax value and book value multiplied by tax rate. Therefore, whatever are the manual changes, the change in deferred tax can always be calculated in consistent way.

Fixed-Assets Database Schema

In the previous chapter, we discussed various assets operations and identified both assets state variables and support data fields for assets operations, which already allows to easily design a database table named by convention fixed_asset_operations:

Field

Description

id

A standard synthetic primary key. An asset operation doesn’t have any field or even a combination of fields that could be used as a natural key.

document_id  

An id of the (source) document that the operation belongs to (foreign key).

fixed_asset_id  

An id of the asset that the operation acts on (foreign key).

asset_operation_type

An application defined enumeration of base asset operation types. We have identified the following 23 base types:

  • Acquisition
  • Acquisition Costs
  • Depreciation
  • Turn Operational
  • Turn Non-Operational
  • Major Repairs
  • Impairment
  • Impairment Reversal
  • Revaluation
  • Revaluation Surplus Utilization
  • Investment Revaluation
  • Discard
  • Sale
  • Sales Reversal
  • Requalification
  • Requalification Reversal
  • Split Discard
  • Split Acquisition
  • Consolidation Discard
  • Consolidation Acquisition
  • Balance Transfer Acquisition
  • Miscellaneous
  • Extended (to indicate, that the extended type is used)

extended_asset_operation_type_id  

An asset operation type defined by an extension of the application. (if the operation was created by an application extension) Discussed in the first article.

delta_acquisition_costs  

Asset state variable delta.

Mutates variable – acquisition costs, which is a debit balance of acquisition costs account, which is an assets account, i.e. total balance will always be positive (or zero).

delta_accumulated_depreciation  

Asset state variable delta.

Mutates variable – accumulated depreciation, which is a debit balance of accumulated depreciation account, which is a contra account, i.e. total balance will always be negative (or zero).

delta_service_life_months  

Asset state variable delta.

Mutates variable – service life in months. Obviously – positive total value only. (or zero if fully depreciated)

delta_service_life_custom  

Asset state variable delta.

Mutates variable – service life in custom units (units of activity). Obviously – positive total value only. (zero if fully depreciated or Units of Activity depreciation method is not used)

delta_salvage_value  

Asset state variable delta.

Mutates variable – salvage value. Obviously – positive total value only. (or zero)

delta_tax_value  

Asset state variable delta.

Mutates variable – tax value (of the asset), has no backing account. Total balance will always be positive (or zero).

delta_tax_value_depreciation  

Asset state variable delta.

Mutates variable – tax value depreciation, i.e. depreciation calculated by (tax) law, has no backing account. Total balance will always be negative (or zero), as it should reduce tax value.

delta_tax_service_life_months  

Asset state variable delta.

Mutates variable – service life in months for tax purpose. Obviously – positive total value only. (or zero if fully depreciated)

delta_tax_service_life_custom  

Asset state variable delta.

Mutates variable – service life in custom units (units of activity) for tax purpose. Obviously – positive total value only. (zero if fully depreciated or Units of Activity depreciation method is not used)

delta_tax_salvage_value  

Asset state variable delta.

Mutates variable – salvage value for tax purpose. Obviously – positive total value only. (or zero)

delta_deferred_tax_liability  

Asset state variable delta.

Mutates variable – deferred tax liability, which is a debit balance of deferred tax liability account, which is a liability account, i.e. total balance will always be negative (or zero).

delta_deferred_tax_asset  

Asset state variable delta.

Mutates variable – deferred tax asset, which is a debit balance of deferred tax asset account, which is an asset account, i.e. total balance will always be positive (or zero).

delta_impairment  

Asset state variable delta.

Mutates variable – impairment, which is a debit balance of impairment account, which is a contra account, i.e. total balance will always be negative (or zero).

delta_appreciation  

Asset state variable delta.

Mutates variable – appreciation, which is a debit balance of appreciation account, which is an assets account, i.e. total balance will always be positive (or zero), the exception being an investment revaluation.

delta_revaluation_surplus  

Asset state variable delta.

Mutates variable – revaluation surplus, which is a debit balance of revaluation surplus account, which is an equity account, i.e. total balance will always be negative (or zero).

delta_accumulated_appreciation_depreciation  

Asset state variable delta. Mutates variable – accumulated depreciation of appreciation, which is a debit balance of accumulated depreciation of appreciation account, which is a contra account, i.e. total balance will always be negative (or zero).

corresponding_account_id  

Operation support data field. Reference to an account that is used within the context of a particular operation type, e.g. depreciation costs account for depreciation, assets impairment loss account for impairment, assets discard costs account for discard etc.

retained_earnings_account_id  

Operation support data field. Reference to the (current) retained earnings account that is used by a particular operation (depreciation, discard etc.)

deferred_tax_expenses_account_id  

Operation support data field. Reference to the deferred tax expenses (revenues) account that is used by a common deferred tax algorithm. Name is shortened due to the length restrictions.

deferred_tax_rate  

Operation support data field.

Tax rate applied when calculating deferred tax.

depreciation_method_type  

Operation support data field.

An application defined enumeration of depreciation method types:

  • Straight Line;
  • Reducing Balance;
  • Sum of the Year' Digits;
  • Units of Activity.

Those are historically very stable, therefore, could be defined as an ENUM as well.

Could be null if depreciation is not allocated for accounting purpose (but allocated for tax purpose).

tax_depreciation_method_type  

Operation support data field.

An application defined enumeration of depreciation method types like the depreciation_method_type but for tax purpose. Could be null if depreciation is not allocated for tax purpose (but allocated for accounting purpose).

depreciation_description  

Operation support data field.

A generated natural language description of depreciation calculations done by a particular depreciation method implementation.

operation_comments  

Operation support data field.

Accountant’s internal comments regarding the operation. As some of the asset operations are attached to an existing (source) document, an accountant shall be able to leave some comments within the operation itself.

inserted_at, inserted_by, updated_at, updated_by  

Standard audit trail fields as defined in the first article in this series. As some of the asset operations are attached to an existing (source) document, their changes wouldn’t be reflected in the parent document audit trail fields. Therefore, we need audit trail fields here.

As it was discussed before, the fields – depreciation_method_type, tax_depreciation_method_type and depreciation_description – could be refactored into a separate table. Though I opt not to.

Next, we need to identify static (common) data fields for a fixed-asset as an entity. There are a few of such fields:

  • Trivial fields: name (to display in lookup lists), description and inventory number.
  • As discussed previously, two data fields are required due to the transfer of balance: acquisition date and operational state at transfer.
  • Two more data fields are required due to legal requirement to keep assets classification by groups defined by law: legal group name and code. Those are trivial text fields specified manually by an accountant. In Lithuania, you are only required to keep the valid (current) classification. There is no requirement to keep the history of assignments to legal groups. Those are also not used in any other way but the current reporting. Therefore, no need to implement a versioned list (like we did for person profiles).
  • Finally, two more fields are required for safeguarding business rules – depreciation methods for both accounting and tax purposes. As mentioned above, some types of fixed assets cannot be depreciated either for accounting purpose or for tax purpose (or both). Therefore, the depreciation method fields are nullable to reflect the fact that an asset is not depreciable.

The static asset’s data entry will always be paired with an acquisition operation (acquisition, split acquisition, consolidation acquisition or balance transfer acquisition) that has audit trail fields. Therefore, we do not need audit trail fields in the fixed assets static data table.

To sum it all up, we end up with the following fixed_assets table:

Field

Description

id

A standard synthetic primary key. An asset doesn’t have any field or even a combination of fields that could be used as a natural key.

acquisition_date

A date that the asset was (actually) acquired. Can only differ from the acquisition (source) document date if the document is a transfer of balance.

asset_name

A (short) name of the asset for display in lookup lists.

asset_description

An arbitrary description of the asset. (if any)

inventory_no

An inventory number assigned to the asset by the company.

fixed_assets_group_id

An id of the assets group that the asset belongs to (foreign key).

legal_group_name

A name of some law-defined assets group that the asset belongs to.

legal_group_code

A code of some law-defined assets group that the asset belongs to.

depreciation_method_type

An application defined enumeration of depreciation method types to apply for accounting purpose:

  • Straight Line;
  • Reducing Balance;
  • Sum of the Year' Digits;
  • Units of Activity.

Those are historically very stable, therefore, could be defined as an ENUM as well.

The depreciation method should be same within an assets group and is defined at group level as well. However, grouping is not always employed (e.g. if a company has a few assets) and exceptions happen as well. Therefore, the (current) depreciation method shall be tracked at asset level as well. There is no need to track its changes as they are reflected by the depreciation operations themselves. Null value indicates that the asset is not depreciable for accounting purpose.

tax_depreciation_method_type

Same as depreciation_method_type but for tax purpose.

is_operational_at_transfer

A flag indicating that the asset is already operational, i.e. depreciation shall be allocated for the transfer month. Can only be used (set to true) if the acquisition (source) document is a transfer of balance.

The tricky part is the references to the ledger accounts, which balances (portion of balances attributable to a particular asset) are asset state variables: acquisition costs account, accumulated depreciation account, impairment account, appreciation account, revaluation surplus account, accumulated depreciation of appreciation account, deferred tax asset account and deferred tax liability account. Those can change, e.g., when moving a particular asset from plant group to investments group etc. Therefore, we need to implement a versioned list as we did for person profiles in the third article with a slight difference. Change of the accounts (one or several) involves a ledger transaction – you have to move the balance of the old account to the balance of the new account. As explained in the second article in the series, moving of account balance means two ledger entries:

  • If the balance of the old account is debit, we add a credit entry with the amount equal to the balance. If the balance of the old account is credit, we add a debit entry with the amount equal to the balance.
  • We add a ledger entry into the new account of the same type and amount as the balance of the old account.

If we (potentially) need a (controlled) transaction, we need a (source) document as well, because a transaction is always a child of some document. In this case, all of the entries within the transaction is (shall be) strictly controlled by the account(s) change operation. Therefore, we need a dedicated (owned) document type – asset accounts change – as well.

Another subject, that is closely related to the account changes and needs to be tracked, is the asset type (group: plant assets, investment assets, intangible assets or financial assets) and valuation model (cost model or revaluation model, in other words, whether to use revaluation model). Changes of those asset properties are important for the validation of operations (e.g. revaluation vs. investment revaluation) and for the grouping of the assets in reports for different periods. Typically, changes of those asset properties are accompanied by the account changes. Therefore, the versioned list shall also include those properties.

To sum it all up, we end up with the following fixed_asset_accounts table:

Field

Description

id

A standard synthetic primary key. A collection of asset’s state account references doesn’t have any field or even a combination of fields that could be used as a natural key.

document_id

An id of the (source) document that the accounts change operation (including acquisition) belongs to (foreign key).

fixed_asset_id

An id of the asset that the accounts change operation acts on (foreign key).

acquisition_costs_account_id, accumulated_depreciation_account_id, impairment_account_id, appreciation_account_id, revaluation_surplus_account_id, appreciation_depreciation_account_id,

<code>deferred_tax_asset_account_id,

deferred_tax_liability_account_id   

References to the (new) ledger accounts which balances (a portion attributable to a particular asset) store asset state variables. (foreign key). acquisition_costs_account_id can never be null. The rest of the accounts could be set to null, as they are not always required. E.g., assets under development do not need any account but the acquisition costs. However, an account can only be set to null if its balance is zero. Otherwise, the balance gets orphaned.

asset_type

An application defined enumeration of asset types as defined by the applicable accounting standards:

  • plant assets
  • investment assets
  • intangible assets
  • financial assets

is_revalued

A bit flag that indicates whether the asset could be revalued, i.e. revaluation model applicable (vs. cost model).

The final touch is the grouping of the fixed assets. It is defined exclusively by the company. It is not required to keep a history of the group changes and assignments. Therefore, we need a simple table for them. Except for the trivial fields, like name and description, we also need to add “policy fields”. Lithuanian accounting standards (probably in other jurisdictions as well) require that the same valuation (cost/revaluation model) and depreciation methods shall be used for all the assets in the same group as well as that a group could only contain assets of the same type. Accounting standards also allow setting a different capitalization value for a particular asset group.

To sum it all up, we end up with the following fixed_asset_groups table:

Field

Description

id

A standard synthetic primary key. An asset group doesn’t have any field or even a combination of fields that could be used as a natural key.

group_name

A (short) name of the asset for display in lookup lists.

group_description

An arbitrary description of the asset. (if any)

depreciation_method_type

An application defined enumeration of depreciation method type to apply for accounting purpose:

  • Straight Line;
  • Reducing Balance;
  • Sum of the Year' Digits;
  • Units of Activity.

Those are historically very stable, therefore, could be defined as an ENUM as well.

Null value indicates that the assets within the group are not depreciable for accounting purpose.

tax_depreciation_method_type

Same as depreciation_method_type but for tax purpose.

asset_type

An application defined enumeration of asset types as defined by the applicable accounting standards:

  • plant assets
  • investment assets
  • intangible assets
  • financial assets

capitalization_value

A capitalization value that an asset must have (or exceed) in order to be treated as an asset (capitalized) within the group. Null value indicates that the capitalization value does not deviate from the company level value.

is_revalued

A bit flag that indicates whether the assets within the group could be revalued, i.e. revaluation model applicable (vs. cost model).

inserted_at, inserted_by, updated_at, updated_by

Standard audit trail fields as defined in the first article in this series.

The resulting database schema diagram for the fixed-assets infrastructure (relevant portion):

Image 1

Conclusions

In this article, we have developed a database schema for the fixed-assets accounting infrastructure that allows for full-scale fixed-assets accounting as required by Lithuanian accounting standards.

In this article, we also described the following 17 (source) document types:

  • balance transfer statement
  • closing entry
  • accounting note
  • miscellaneous documents
  • fixed assets operational statement
  • fixed assets non-operational statement
  • fixed assets depreciation
  • fixed assets impairment
  • fixed assets impairment reversal
  • fixed assets revaluation
  • fixed assets investments revaluation
  • fixed assets requalification
  • fixed assets requalification reversal
  • fixed assets discard
  • fixed assets split
  • fixed assets consolidation
  • fixed assets accounts change

In this article, we also identified four candidates for company’s default accounts enumeration (described in the third article in this series):

  • summary account
  • current retained earnings account
  • previous retained earnings account
  • deferred tax expenses (revenues) account

In this article, we also identified one case for audit routine:

  • Auditing method should check for discrepancies between deferred tax rate in the asset operations and (corporate) tax rate in the relevant company’s profile version.

In this article, we also added two new fields to the company_profile_versions table (described in the third article in this series) to be used along with fixed assets:

  • fixed_assets_capitalization_value DECIMAL(20, 2) UNSIGNED NOT NULL
  • corporate_tax_rate DECIMAL(5, 4) UNSIGNED NOT NULL

Finally, in this article, we also added a new field to the company_profile table (described in the third article in this series) to define a company’s policy on changing documents after entry closing (application defined enumeration):

closing_policy TINYINT UNSIGNED NOT NULL

I should highlight that the article does not provide full business logic description for each of the entity, only the portion that is relevant to the database design. There are a way more business rules in fixed asset accounting that should be respected while developing the business layer of the application. E.g.:

  • The chronological order of the operations should be preserved as most of the operations directly or indirectly generate ledger transactions using the asset state variable subtotals. If you insert an operation before an existing one, the transaction of the latter will be rendered invalid. Hence, you need a mechanism to resolve operation dependencies.
  • As about a half of the fixed assets operations do not own (source) documents and are attached to already existing ones, you also need a mechanism to resolve whether a particular operation can be attached to a particular document type etc.

At the moment, I haven’t made my mind regarding the next topic. The remaining asset types that shall be implemented are:

  • Loans (not yet sure, whether to include it in base functionality)
  • Inventory (fungible and individual)
  • Notes payable and cash

History

  • 16th September, 2019: Initial version
  • 18th September, 2019: Reuploaded create script (in initial upload forgot to add fields to company profile tables)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Business Analyst Linden
Lithuania Lithuania
I'm a lawyer in a law firm. Programing is my hobby.

Comments and Discussions

 
QuestionGood simple great work. Pin
JohnJKN19812-Mar-23 10:14
JohnJKN19812-Mar-23 10:14 
GeneralThank you! Pin
Lukann4-Jan-23 19:58
Lukann4-Jan-23 19:58 
GeneralYou have done a great effort Pin
Muhammad Mutahar21-Dec-22 6:31
Muhammad Mutahar21-Dec-22 6:31 
PraiseVery Informative and Educative Pin
Damfodeky11-May-21 11:52
Damfodeky11-May-21 11:52 
QuestionGreat stuff! Do you have sample data? Pin
Michael Shparber1-May-20 23:59
Michael Shparber1-May-20 23:59 
AnswerRe: Great stuff! Do you have sample data? Pin
Niemand253-May-20 22:15
professionalNiemand253-May-20 22:15 
GeneralRe: Great stuff! Do you have sample data? Pin
Member 1501508311-Dec-20 22:24
Member 1501508311-Dec-20 22:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.