DYNAMIC 3i FINANCIAL CONSOLIDATION METHODS

Dynamic3i offers consolidation at different levels. Simple General Ledger Account consolidation, Branch consolidation and Database consolidation. Combinations of these methods can also be used in order to facilitate multi-dimensional consolidations. Lets discuss these methods in further detail:
General Ledger Account Consolidation

Account consolidation is the simplest of consolidation methods. If one account is to consolidate into another account simply use the consolidation accounts in the G/L Account Maintenance – Account Information pop-up.

Consolidate Account 1

If no consolidating branch code was entered, then a consolidation account code may be entered. This account must be a valid statistical account. The General Ledger account will consolidate into this account within this branch.

Consolidate Account 2

If no consolidating branch code was entered, then a second consolidation account code may be entered. This account must be a valid statistical account. The General Ledger account will consolidate into this account within this branch.

Dynamic3i General Ledger Account Maintenance – GB5800

Branch Consolidation

Branch consolidation is slightly more involved. In order to do branch level consolidation the main Dynamic3i System Flag ‘Consolidation by Branch’ must be set. When defining a branch to Dynamic3i you can then also define what branch or branches it may consolidate into.

Within the General Ledger Branch Maintenance you can define up to two consolidation branch codes to which the General Ledger branch is consolidated. The consolidation branch 1 code should not be the same as consolidation branch 2 code.

Dynamic3i Branch Maintenance – GB5700.

Utilizing these consolidation branches at the Branch Level assumes a one to one General Ledger Account consolidation.

From within the General Ledger Account maintenance the Consolidate Branch can also be set.

Dynamic3i General Ledger Account Maintenance – GB5800

If a consolidation branch is entered the consolidation accounts (Consolidation Account 1 and Consolidation Account 2) will NOT be used.

If no consolidation branch is set the application will verify and use, if defined, the consolidate branch 1 and consolidate branch 2 defined on the branch maintenance (See above) for this branch and account combination. If the account does not exist in the branch it will automatically be created as a valid account to accommodate the branch account consolidation that was defined in the Branch Consolidation (GB5700).

The reason you can also define a consolidation branch here and not use Branch Consolidation (GB5700) is to allow for the situation where you only want certain accounts to consolidate up to another branch and not the whole branch chart of accounts.

Example: Using Branch Consolidation; branch 1 to 2, the entire chart of accounts from one branch is consolidated into another:

1-1 to 2-1

1-2 to 2-2

1-3 to 2-3

1-4 to 2-4

If I only wanted branch/account 1-3 only to now go to branch 5, I could use this consolidate branch when setting up account 1-3 and set it to 5. The ‘Consolidation Account’ if left blank will automatically default to the main account (1-3)

The result would be:

1-3 to 5-3

Notes:

The Consolidate Branch and Consolidate Account can be used together in those situations where a completely different account is required to be consolidated to. This situation often occurs in multi-branch/company situations where another branch may not have the same account code structure. Eg. one account in one branch means something totally different in another.

Note also that ‘chaining’ these definitions together across multiple branches and/or accounts is possible.

Dynamic3i provides a Chart of Accounts Validation application to verify that there are no endless loops in the consolidation. Eg. A branch and or account consolidating back onto itself.

Branch Consolidation with Inter-Branch Balancing

DYNAMIC 3i also handles the ability to consolidate branches, which operate in different currencies. Storing the period exchange rates as well as historical exchange rates as well as defining a Cumulative Translation Adjustment Account at the branch level allows for this. An example of this is outlined below.

Cumulative Translation Adjustment Account

Dynamic3i Branch Maintenance – GB5700.

On the General ledger Branch enter the ‘Cumulative Translation Adjustment’ account that the system will use when consolidating branches of different currencies. This account is used for inter-branch consolidation balancing. All of the converted currency amounts will be held in this adjustment account in order to accumulate any rounding that may occur. The NET rounding will be used to keep the branch balancing at zero. This account will be an account set up in a statistical branch. This field will allow for a null entry.

Example. You may have a USD branch and a CAD branch that you want to consolidate into a 3rd USD branch.

This means all your CAD accounts must be converted to USD. The cumulative translation account stores all the exchange amounts of all the accounts' currency conversion. After all accounts are converted, the balance in this account represents the net rounding error offset. This is the amount that keeps the branch balancing to $0.00.

Simple example. CAD branch has 1 journal.

1-1 $100 DB.

1-2 $33.33 CR

1-3 $66.67 CR.

NET 0.0

Exchange rate is .705.

1-1 $100.00 * .705 = $70.50 Cumulative Translation Account $29.50

1-2 $33.33 * .705 = $23.48- Cumulative Translation Account $9.85-

1-3 $66.67 * .705 = $47.00- Cumulative Translation Account $19.67-

NET .02+ NET .02-

Multi-Branch Transactions and Inter Branch Balancing

DYNAMIC 3i also allows for and balances multi-branch transactions. A simple Due From / Due To hierarchy is used to maintain the balance, and its corresponding general ledger accounts when transactions occur involving more than one branch. Defining a relationship between valid branches and accounts using Dynamic3i Inter-Branch Balancing table – GL7900 does this. Whenever there is activity on Inter-Branch – Due From Account a corresponding offsetting activity is done to the header Branch – Due To Account

Example.

The example below shows an accounts payable invoice to one branch of which a portion really belongs to another branch.

Dynamic3i Invoice Entry – AP1100.

Invoice for $300.00 to Branch 1

Distribution entry to allocate the $20.00 to branch ‘01’ results in allocating $280.00 to branch ‘1’ and $20.00 to branch ‘01’. A warning will appear stating that the G/L distribution branch does not match the invoice branch but the transaction will still be allowed in a multi-branch environment. With the above posting, branch 1 would be out of balance by $20.00 (A/P regular account (defined on currency file) gets hit with full invoice amount $300.00 and offset in A/P invoice distribution only gets $280.00)

By setting up the Inter Branch Balancing table as follows:

Dynamic3i Inter-Branch Balancing – GL7900.

When the posting of the $20.00 occurs to branch ‘01’- account, a transaction for $20.00 is automatically created to the Branch – Due To Account, in this case branch ‘1’ account interco.1.1.

The resulting A/P invoice update transactions from the above example would result in the postings below. The $20.00 posting to the defined inter-branch balancing account keeps branch ‘1’ in balance.

Dynamic3i Accounts Payable Update Report – AP4300.

Database Consolidation

Dynamic3i provides for consolidation at the database level. This is a very powerful feature of Dyanmic3i. Not only does it consolidate your data, it makes available for analysis of this data using all of the reports and inquiries that you would normally use for any one given database within your company. Think of it is a central repository for all disparate branches and legal company entities.

By combining database consolidation with account and branch level, Dynamic3i is able to handle any type of consolidation model that a company may have.

With database consolidation the sub-ledger data from a completely separate database is rolled-up into a master database that can then be used to query and run reports against.

An example could be a multi-national or multi-company environment. Three completely separate companies operating in Europe with a parent and separate company operating in Canada and one in the United States.

Each would have its own Financial Statements and corporate responsibility. The consolidated Canada view would see all aspects of each separate company either separate or as one. The Corporate view would see the total of Canada and USA or each individually ie. Total Corporate Budget, Corporate Sales, Inventory Values etc.

The simplicity of this is that you could access the Dyanmic3i suite of applications as say a corporate user (defined to point too the consolidated corporate database) and execute all your same reports and queries as if you were in your own branch/company realm. Dynamic3i is so powerful that the actual ‘Database Consolidation’ could be run at a pre-determined time (by use of Dynamic3i batch utilities and Job Streaming functionality) So as the corporate view is always available and up to date.

Setting up this functionality is very simple. The database administrator simply sets up and defines database links to the individual Dynamic3i databases that will be made available to consolidate. These definitions are done in the highest database realm that will hold the consolidation. Using our example this would be the Corporate View.

This Dynamic3i application located under ‘System Administration’ menu allows other Dynamic3i databases to be defined. The link name and description can be anything you choose. Using our example above three links could be set-up as

London – UK Corp., Switzerland – Swiss Corp. and Spain –Spain Corp. These definitions would be done in the ‘Corporate’ database.

The ‘Sequence’ is yet another powerful feature of Dynamic3i that allows the consolidation to be driven, if you will, by a controlling branch. Using our example lets say that London and Switzerland have/share the same customers. Each define their own customers as well but it is set that London is the most up-to- date data. Setting the London link as sequence 1 would ensure that data created in the corporate database would use London’s data first in the case of duplicates found in the Switzerland database. Subsequently for other link sequences on down the line.

Running the actual consolidation is done via ‘Consolidation Update – GB0450’

This application allows you to select what link (database) to consolidate and what tables you want consolidated. By default all items are selected. If for some reason you did not want to consolidate a certain database simply de-select the check box. Like wise if you did not want certain data like ‘Statistical Ledger or ‘Forecasts’.

Upon completion the consolidation database is available to view not only combined (consolidated) but also as its own branch. Ie. Using our example I could run a detailed trail balance for London (branch 1) and then run one for Switzerland (branch 2) as its data would be there also. Next I could run one for all branches giving the ‘Corporate Trial balance’