IBM Cognos Proven Practices: Guidelines to General Configuration and Automatic Journals for IBM Cognos Controller

Nature of Document: Guideline; Product(s): IBM Cognos Controller; Area of Interest: Financial Management

How to set up both general configuration and automatic journals.

Share:

Maximilien de Chestret, Software Product Manager, IBM

Max is member of the IBM Cognos Controller Product Management Team. His focus is on automatic journals and investment accounting.



Annika Fahlgren, Financial Solutions Consultant, Financial Systems AB

Annika Fahlgren - Financial Solutions Consultant with Financial Systems AB. In her current role, she helps with consolidation solutions for customer, including how to efficienctly use automatic journals, standardized reporting, analyzing and reconciling currency translation, etc. Her prior role was with IBM Cognos Controller R and D team were she led the application development team in areas such as automatic journals for IBM Cognos Controller and was based in Stockholm, Sweden.



15 September 2010

Also available in Chinese

Introduction

Purpose

The Purpose of this document is to provide good advice when configuring IBM Cognos Controller regarding automatic journals in general.

There are often misunderstandings in this area, due to complexity and possibility of many configurations. The objective is to be a guide on how to set up configurations and control tables and to clarify this area and give a correct and complete information about the functionality as is possible.

Applicability

Automatic journals are elimination or calculations that are booked as journals on companies and groups. They can be based on either data in the period database (xdb) or the investment register (xacq).

Where the automatic journals are booked are affected by the consolidation type (legal or management) and how the company structure is built.

You activate and define basic configuration of the automatic journals in Maintain / Configuration / Automatic Journals / Define. Then you define which accounts to be involved in the calculation in Maintain / Configuration / Automatic Journals / Control Tables / Acquisition Calculations. Though, how the account structure is defined, with summations, reconciliation and conversion codes etc, also affects the automatic journals. Settings in General Configuration, mainly in the tab Reconcile 2 (or Reconcile if you run the old consolidation model) also have an impact on how the elimination are recorded.

This document also shortly describes how to set up the control tables depending on which of three consolidation set ups you select:

  • The Standard model used mainly in USA, Germany, Sweden, Switzerland and the United Kingdom. The standard model uses the investment register as basis for some of the elimination.
  • The Latin model used mainly in Belgium, Spain, Italy and France.
  • Investment adjustment model, used mainly in the Netherlands, Denmark, Iceland and Canada.

Exclusions and Exceptions

The functionality described is generally applicable in both the old and the new consolidation model. Though, all examples and recommendations are valid for only the new consolidation model, so there are sometimes differences compared to the old one (which is default in IBM Controller 2.3). Configuration tests have been done in IBM Controller 8 series.


General Configuration

General Information

In the menu item Maintain / Configuration / General you make settings for the whole system. For example, you make settings for the investment register in the second tab, and you configure signs in the tab Reconcile 1. In reconcile 2 you set up a number of important accounts that are used for example by the automatic journals. Read more about this below.

The Reconcile 2 Tab

Here you specify a number of accounts that are used by the automatic journals. This makes some transfers between accounts possible, without having to specify these within the control tables. By specifying OB and transfer accounts for net income it possible to roll net income to other accounts over year-end. Depending on how the account structure is built – with integrated accounts or not and with a specification of net income or not – this window should be configured in different ways.

Figure 1 shows the Reconcile 2 settings, which includes the account settings for Retained Earnings, Net Income, Currency Conversion, etc.
Figure 1 shows the Reconcile 2 settings, which includes the account settings for Retained Earnings, Net Income, Currency Conversion, etc.

Non-integrated Account Structure

In this database, the account structure is non-integrated, meaning there is an Analysis of Reserves with statistical accounts, where each EB (ending balance) reconcile with a main account. There are separate accounts for Net Income in P&L, BS (completely non-integrated). There is also a specification of Net Income, meaning net income from P&L (NETINCOME) will roll into the Net income BS (145000) as well in the Net Income spec (145000200). Next year the amount will be transferred to the retained earnings (137000) and will turn up as OB Net Income (145000OB) and also on the transfer accounts, with inverted sign in Net Income part (145000150) and with same sign in Retained Earnings part (137000150).

Figure 2 shows an account structure for retained earnings and result of Balance sheet
Figure 2 shows an account structure for retained earnings and result of Balance sheet

Explanation of the fields

Main Settings

  • Net Income in P&L Statement: Summation account, Income account (I)
  • Net Income in Balance Sheet: Usually a main account, type E, reconciled with NI in P&L. We recommend to use another account than for the P&L.
  • Retained Earnings in Balance Sheet: If the account structure is non-integrated, select the main account (type E) here. -Note- that there must be reconciliation between the main account and the EB in the specification. The specification accounts for retained earnings must be the « From accounts » and “to accounts” in the control tables, else nothing will be booked on retained earnings. If the Analysis of Reserves is integrated into the Balance Sheet (movements are main accounts), leave this box empty.
  • Previous Year Net Income in Balance Sheet: Enter a main account, type E, here, if the Analysis of Reserves is integrated into BS and you have a specific account in the BS for NI from previous year (unusual configuration).

Accounts for Analysis of Reserves

  • Use Transfer Accounts
    Select this box if the net income BS and the retained earnings are non integrated (recommended configuration). By selecting this, the fields for transfer accounts are open for update.
  • Opening Balance Retained Earnings This field is automatically updated from the account structure when the transfer accounts are filled in and the window is saved.
  • Opening Balance Net Income
    This field is automatically updated from the account structure when the transfer accounts are filled in and the window is saved.
  • Transfer Retained Earnings
    Here you fill in the specification account for Retained Earnings, on which you want previous year’s net income to be booked.
  • Transfer Net Income
    Here you fill in the specification account for Net Income, on which you want previous year’s net income to be counter booked (opposite sign compared to OB Net Income).
  • Net Income (non-integrated)
    If P&L is not integrated, this usually means that there is an Account with a statistical account (movement) for Net Income BS. Specify that account here and it will be updated from NI in P&L. If this is not the case, this field does not have to be filled in.

Differences

  • Currency Conversion Difference Account 1, Main Account
    Here you enter the account (balance type, usually E or L) on which you want the system to book any remaining* positive currency conversion difference. “Account 1” refers to conversion method 1 in the account structure. Note that any balance differences from the conversion, IC and IP are also booked here; and before Controller 8 also balance differences from automatic journals . *we recommend to calculate conversion differences through the configuration in the account structure, so in this account should appear only very small amounts if not it would mean that the conversion, IC and/or IP configurations are wrong.
  • Currency Conversion Difference Account 1, Non-Int. Account
    Here you enter the account (statistical type, usually S) on which you want the system to book any remaining positive currency conversion difference if the main account is analysed by movement (unusual). For more information, read above.
  • Currency Conversion Difference Account 2, Main Account
    Here you enter the account (balance type, usually E or L) on which you want the system to book any remaining* negative currency conversion difference. “Account 2” refers to conversion method 2 in the account structure. Note that any balance differences from the conversion, IC and IP are also booked here,and before Controller 8 also balance differences from automatic journals . *We recommend to calculate conversion differences through the configuration in the account structure, so in this account should appear only very small amounts if not it would mean that the conversion, IC and/or IP configurations are wrong.
  • Currency Conversion Difference Account 2, Non-Int. Account
    Here you enter the account (statistical type, usually S) on which you want the system to book any remaining negative currency conversion difference if the main account is analysed by movement (unusual). For more information, read above.
  • MNM Method Currency Conversion Difference
    Here you enter the account on which to book any remaining currency conversion difference when using the MNM method (must be normally a P/L account)
  • Other Difference for Automatic Journals, Main Account
    From Controller 8 series, any remaining balance difference in automatic journals is booked on this account. If this is not filled in, the difference will instead be booked on the account for currency conversion difference. It is recommended to have a specific account for this use (balance type, usually E or L). In this account should appear only very small amounts if not it would mean that the conversion or the acquisitions configurations are wrong.
  • Other Difference for Automatic Journals, Non-Int. Account
    Here you enter the account (statistical type, usually S) on which you want the system to book any remaining balance difference in automatic journals if the main account is analysed by movement (unusual). For more information, read above.

Consolidation Types and Company Structure

Per consolidation type, you must select if the acquisition calculation and intercompany elimination should be done in a legal way or in a management way.

Figure 3 shows the consolidation types and the different options
Figure 3 shows the consolidation types and the different options

Legal consolidation means that acquisition calculation (when counterparts are involved, e.g. for investment accounts) and Intercompany elimination are booked at the level where the companies meet in the structure. The booking can so occur in subgroups When the new consolidation engine is applied (per default in Controller 8 ) the booking will be done on subgroups when it involves companies belonging to different subgroups. Note that if the old consolidation engine is used, the elimination are always stored on companies, with a specific ktypkonc code telling to which group the elimination belongs and must be displayed. With the old consolidation engine none automatic journals are booked directly on subgroups.

Management consolidation means that elimination is always performed at the lowest level, regardless of where the counter company is located in the company structure.

Limitations with Management Consolidation

  • You can only handle groups that are owned at 100%.
  • You cannot handle multiple ownership
  • You can only book the elimination at the lowest level
Figure 4 Show an example of differences between management and legal consolidation type (with the new consolidation engine)
Figure 4 Show an example of differences between management and legal consolidation type (with the new consolidation engine)

In the management consolidation type the transaction 2 will be booked on C even if C is not in the same subgroup than the counterpart (A).

Though, a company structure can also be built in different ways express in this terminology:

  • Strictly legal structure: Legal consolidation type combined with a company structure that fully describes the legal ownership ( each holding and owned companies will be in the same subgroup)
  • Not strictly legal structure: Legal consolidation type combined with a company structure which can be built in any way, with companies connected in a non-legal fashion (e.g : a flat structure with multiple holdings)
  • Management structure: Management consolidation type combined with any company structure.

Examples of Strictly and Not Strictly Legal Structure

Strictly legal structures

Figure 5 shows an example of strictly legal structure
Figure 5 shows an example of strictly legal structure

Not strictly legal structure

Figure 6 is an example of a non-strictly legal structure
Figure 6 is an example of a non-strictly legal structure

Management Structure

Figure 7 shows an example of a management structure
Figure 7 shows an example of a management structure

Definition of Automatic Journals

There are a lot of predefined Automatic Journals (see section #7) that allow you to run the main consolidation elimination and calculations. In the menu item Maintain / Configuration / Automatic Journals / Define you must activate the predefined automatic journals/control tables that you want to use.

But you can create your own automatic journals for instance for:

  • Non-Equity Elimination's (for example, dividends (see section 8.1), provision)
  • Re-classifications
  • Intercompany profit on sales of assets

This section will allow you to understand the different available options and will help you to create your own rules.

Automatic Definition

The definition tab consists of a header with a number of options and a matrix in which you specify which columns to display in the control table and which calculation rules etc that should be used.

Figure 8 shows the define tab for the Automatic journals
Figure 8 shows the define tab for the Automatic journals

You must first activate the predefined control tables that you want to use.

If you want to create new automatic journals then you must enter:

  • Automatic Journal Code – Cannot start with E or IC or IP (only for predefined ones)
  • Automatic Journal Type – You can use your own automatic journal type (only beginning with letters) or existing one.

Then you must define the basic settings that are valid for the whole automatic journal. Note that if there are several automatic journals (rules) that are booked on the same automatic journal type, they must all have the same basic settings in AJ – Define, such as level, category and alternative currency conversion. With a mix, you may get an expected result, as only one set of base settings will be used for all automatic journals with the same AJT. You must keep in mind that the settings are linked to the automatic journal type and not to the automatic journal code.

Selections in the header

There are some check boxes in the AJ Define window that need to be explained.

Level

The level makes it possible to use automatic journals on lower levels as calculation basis. By default the level is 1. Be aware that the levels are only valid for the xdb-based automatic journals (AJ other than E1XX), as the different programs within consolidation run in a specific order:

  • Reconciliation
  • Currency conversion of base values.
  • Intercompany balances.
  • Intercompany profit.
  • Investment adjustments (AJT 30), which is an adjustment of reported values rather than an automatic journal.
  • Calculations based on the investment register, inclusive currency conversion of that step.
  • Automatic journals based on xdb, level 1, inclusive currency conversion of that step.
  • Automatic journals based on xdb, level 2 if exists (etc..), inclusive currency conversion of that step.
  • Calculation of indirect minorities and equity (AJT 66,67,68 and 97)
  • Automatic journals to handle OB when structural changes (E300). Also notice that a level alone does not decide what will be the calculation basis. To be sure that you get what you want as basis, you must select in the control tables a contribution version source containing the AJ (including base or not) on which you want to calculate the new AJ.

Category

This is a technical concept that describes what kind of elimination it is and how it should be handled. Some important details:

  • Category 1 is usually used by user-defined automatic journals.
  • Category 23 or 24 must be used for user-defined automatic journals with cc indicator (booking the journal on the counter company). The percentage used comes from the original company at the level where the parties meet.
  • Category 24 is used in most cases where counter company is involved. For multiple owned companies the elimination is booked on all levels where the counter companies meet, and journal nr 10000 reverts eliminations on lower levels.

The percentage used comes from the original company at the level where the parties .Note that If the cc indicator is not selected and you don't need a booking when the company meets then category 1 must be selected. But you must select a rule where the full matrix is filled in. If only the column none is filled in there will be eliminations only at group level.

Enable Calculation of Change in Structure

This check box must be selected if E300 is used and you want to get the effect of structural changes on a specific account movement. Its recommended to select this check box for all AJ if E300 is activated.

Alternative Currency Conversion (calculation in all currencies)

If not selected, the automatic journal will be created in local currency and then currency converted (so using the currency code of the To account). If selected, the automatic journal will be created directly in all currencies. The main reason to select this option is to get the correct calculation in case you have in From accounts, accounts coming from company journals in group currency or accounts with historical rates or accounts coming from the IC elimination at company level. We recommend to select always this option.

-Note- There are an number of predefined automatic journals that will keep the predefined currency conversion and therefore this option is dimmed:

  • E100-E150 are based on values entered in the investment register.
  • E200 and E210 are based on all reported values in all currencies.
  • E205, E215, E505, E515 and E705 for indirect calculations are based on reported values and other automatic journals. Therefore, these will always be based on currency converted values.
  • E800 is always calculated on converted values.

Exclude the Automatic Journal from the Specific Consolidation Types

This is a new check box available from IBM Cognos Controller 8.2. You have the possibility to exclude the use of one or several automatic journals from specified consolidation types. This is useful in configurations where you want for example one set of elimination in a legal structure, but a slightly different one in management structure. An example is found in the Proven Practices document regarding Investment Adjustments, where this functionality is necessary for management structures.

No General Configuration Bookings

This check box was earlier called “Alternative OB Method”. If selected, there will be no automatic transfers according to the information in General Configuration (totally – there will not even be any transfer of the net profit into the balance sheet/ equity specification). These accounts must instead be specified in the control tables. In some cases this option is needed. REQUIREMENT when the result of previous year in Repo must not or not be completely transferred to the retained earnings account specified in the general configuration.

Figure 9 is an example how differences are handle based on general configuration
Figure 9 is an example how differences are handle based on general configuration

In general if you want to book journals on another accounts than the ones specified in General Configuration, you must select this check box.

Roll OB Values

If selected, the OB values of the automatic journal will be rolled, even though the configuration of the control table says they should be calculated. This is useful for automatic journals affecting only net income, based on one or a few P/L accounts instead of the net income account.

Figure 10 shows how the OB roll forward works with the automatic journals
Figure 10 shows how the OB roll forward works with the automatic journals

The fields of the matrix:

Explanation of the headings:

Table 1 shows field definition for the automatic journals including field matrix along with the explanation of the headings
Column HeadingDescription
ColumnThe parameters (field names in the database) that define an automatic journal and can be show in the control table's columns, for example FROM ACCOUNT, TO ACCOUNT, sign, and GM indicator.
ShowSelected for the parameter in question as a column in the control table.
DefaultThe default value to apply for the automatic journal as a whole.
Header TextThe column heading displayed in the control table.
MandatorySelect for the parameter in question to be mandatory, which means that you cannot save the control table unless you complete this field.
Figure 11 shows all the fields available for the automatic journals
Figure 11 shows all the fields available for the automatic journals
Table 2 shows the column and the corresponding explanation of its purpose. This information is important as you define your automatic journals.
ColumnExplanation
Konto (Account)From Account (calculation basis). Summation accounts may be used.
Konto 2From Account 2 (if interval is used)
Tecken_ibSign for OB account. Not displayed, automatically updated.
Tecken_pfSign for calculation
Konto_ibOB account belonging to the TO account. Must be filled in if you have a movement in the TO account. (new since IBM Cognos Controller 8.1)
Konto_pfTo account (which the result is posted)
Typ Type of account for transfer of equity (used by E800)
R = Restricted Reserves
U = Unrestricted Reserves
I= Income Account
VardePriority regarding transfer of Equity (used by E800)
ExtraUse by E300 to differ between internal and external acquisitions and disposals
Blank = valid for all
I = Internal changes
E = External changes
Regel_idRule that sets calculation method in a matrix of company and counter company. Every rule consists of at least one calculation method.
gm_indGM indicator (cooperates with the rule and the parts in the calculation methods). It indicates if the rate type will be used in the calculation.
cc_indCounter Company Indicator. -NB- that category 23 or 24 must be used together with cc ind=C in order to get correct posting.
If blank, with any FROM account, the calculation based on the company's values is posted on the counter company or where the parties meet in the structure.
If C, with intercompany FROM account, the calculation based on the company's values is posted on the counter company or where the parties meet in the structure.
If C, with an non-IC FROM account, the calculation based on a company's values will be posted on the same company (as a result, the C has no impact in this case)
MselSelection method consisting of consolidation methods combined or not with the Parent check box
Dim1Specific dimension 1 code to post the automatic journal on
Dim2Specific dimension 2 code to post the automatic journal on
Dim3Specific dimension 3 code to post the automatic journal on
Dim4Specific dimension 4 code to post the automatic journal on
Konto_condCondition account. It indicates if the calculation depends on a condition specified in this specific account.
TaxTax Parameter
Blank = No tax calculations
T = indicates that the FROM account must be multiplied by the tax rate (Table HT01) of the country to which the company belongs to (specified in the company structure)
N = B-T
MethodNot in use
FtypChange type (used by investment adjustments)
T = total change
C = conversion diff
N = net change (T-C)
M = Movement
KontoaNot in use
KontobNot in use
KontocNot in use
KontodNot in use
flag 1Not in use
flag 2Not in use

The Conditions Tab

You can add some conditions to your automatic journal. A condition interacts with a coefficient or a condition account. The conditional tab is directly connected to the Define tab, which means that the defined condition is valid for a particular automatic journal.

Figure 12 shows the condition tab for the automatic journal
Figure 12 shows the condition tab for the automatic journal

Available Conditions:

  • None – by default no conditions
  • If > or < amount in FROM Account will be compared to the coefficient. If the condition is met, the result will be booked in TO Account in the control table.
  • If * or / , amount in FROM Account will be multiplied by or divided by the coefficient and the result will be booked in TO Account in the control table.

NOTE: If there is a Condition Account field in the control table the From Account will then be compared to the condition account, and if the condition is met (for example, the amount in the FROM Account>the amount in the condition account) then the result of the calculation will be booked in the TO Account.

Example of Use:

  • Re-classification of a negative result of an equity company to the Cost side (see 8.2)
  • Run specific AJ only on specific companies (creation of a coefficient account in the account structure, create your own rule with the field Konto_cond ticked set the condition * ,Fill in the control table and type the coefficient account in the column Konto_cond. Put the condition account in a data entry form. Enter 1 in data entry in the condition account if the AJ must be run. Don’t type anything if the AJ must not be run for the specific company.

The selections method tab

The selection methods are groups of consolidation methods (including the parent). There are a lot of predefined selection methods.

In the example below the selection method includes all companies consolidated with the purchase, proportional (split) and equity methods. It doesn’t include the companies with the field parent ticked in the company structure.

Figure 13 shows the selection mode, i.e consolidation method
Figure 13 shows the selection mode, i.e consolidation method

The Rules tab

A rule is a set of calculation methods depending on the consolidation method (note that parent is considered as a consolidation method). If you use the CC indicator the source company is the counter company (must read the matrix on the other side).

If you create a new rule, you must fill in the matrix with the calculation methods (see below) for the combinations you want. If in the control table, that will apply this rule, you don’t have IC accounts in from accounts then you need only to fill in the column None.

It’s the rule that will determine for each type of consolidation methods which calculation will be run.

Figure 14 shows the rules tab
Figure 14 shows the rules tab

The Calculations Methods Tab

A calculation method is a combination of amount type and rate type.

  • The use of the rate type will depend on the selection made in the GM indicator column in the control table (Blank,G or M).
  • The calculation will be: Amount type if the GM indicator in the control table is blank, Amount type*rate type if the GM indicator in the control table is G or Amount type * (1-rate type) if the GM indicator in the control table is M.
  • There are a lot of predefined calculation methods.
Figure 15 shows calculation methods
Figure 15 shows calculation methods

In this example If in the control table, the column GM indicator is blank. The amount of the From account will be booked in the To account. If a G is typed , then the amount of the From account multiplied by the direct owned % will be booked in the To account. If a M is typed , then the amount of the From account multiplied by 1- the direct owned % will be booked in the To account.

Automatic Journals – Tips and Tricks

  • Try to use predefined automatic journals
  • You can use the copy function to copy existing automatic journal and saving this format as new automatic journal
  • Put the rule as default to avoid having different rules in the same control table
  • Put the CC indicator as default if used
  • Options in the Define tab must be the same for all automatic journals rules using the same automatic journal , otherwise you may get an incorrect calculation

Control Tables

General Rules and Tips

Figure 16 shows automatic journal control table
Figure 16 shows automatic journal control table
  • Journal number: Don’t forget to fill in the journal number in control tables you want to use. Unique journal number is strongly recommended. E300 needs a completely unique number, so select nr 99 (remember it can be mixed up with the IC control table numbers as well).
  • Offset account: For the AJ which are posted on the counterpart, you must mention the Offset account which will balance the posting. Valid for rules : E100-115,E150,E600-E605,E770-E775.

Note that if you want you can instead type it inside the control table.

  • Journal type: You have the possibility to book also the automatic journal in a manual journal type (unusual)
  • Closing version: If you mention a closing version the calculation will be done only on the manual journals part of this closing version. Needed if the customer books some manual post consolidation journals (journals that must not be affected by the consolidation. E.G.: a reclassification of the minority to the group share)
  • Contribution version: If you mention a contribution version, the calculation will be done only on the automatic journals part of this contribution version. Needed if you want to calculate an automatic journal based on other automatic journals.

Note that if you calculate the split method directly in the Intercompany balance control table, you must enter in the rule E200 (split calculation) a contribution version without the AJ 35 (AJ for intercompany elimination) to avoid that the split calculation is done twice.

It is recommended that posting automatic journals to both main accounts and belonging specification accounts in the same control table. Though, if the customer for some reason wants to separate these and have different control tables , one for booking on main accounts and one for booking on detail accounts, this is possible if both control tables use the same automatic journal type and just have different journal numbers. The reconciliation program only cares about automatic journal type, not journal number.

If you get an imbalance or other problem with an automatic journal, start by checking the base values. If you have reconciliation errors there, you will also get problems in the calculations.

If the From account is a summation account and the To account is blank, elimination will be booked on the same detail accounts as the calculation basis. Note that all summations matter! This means that you might get bookings on accounts that you didn’t intend, depending on the summation structure.

”Adjust OB for automatic journals” is not possible to use for automatic journals based on the investment register when the new consolidation model is applied.

OB Functionality

Opening Balance in From Accounts

How opening balances are created by the automatic journals depend on how the control tables are configured:

  • If the from account is a main account, the OB value will be rolled from the previous year.
  • If movement accounts are used and the OB account is set as from account, the OB value will be calculated, using the selected calculation basis of the automatic journal and the actual ownership percentage. Calculated OB will always generate a correct CB. E300 takes care of the OB changes. It's the recommended configuration.
  • If movement accounts are used and the OB account is left out, the OB value will be rolled. With a change in ownership CB may not be correct as OB roll and movements are calculated – not recommended. Even with the configuration that gives calculated OB values, you can get them rolled instead by using the check box “Roll OB Values” in the menu item Automatic Journals – Define.

The best practice is that all control tables based on the period database (xdb), i.e. E400 and higher numbers, should include the lines OB -> OB in order to get the correct elimination. This is valid both if E300 is used or not. Without the OB line and E300, OB values will be rolled in, and if there has been a change in ownership percentage the closing balance will then be incorrect.

New column for OB :

  • What is new when using the new consolidation engine ( from Controller 8), is that a column for OB accounts is visible in the control tables. This must be used to make it work in all cases, so a step in the upgrade process from the old consolidation engine, is to update the control tables. Note that if you have user-defined control tables, you must begin by selecting Show for konto_ib in the grid in Automatic Journals – Define.
  • This OB column must be filled in when you have specifications (movements) in the To account.

The OB column must not include any account:

  • if the To Account is a Main/Head Account
  • if the To Account is an Income or a Cost Account

Note that if not filled in when the To account is a movement account :

  • Then the movement account in the To account will be considered as a head/main account and will get no OB the next year.

Control table E300:

  • In order to get both OB and CB correct (when there are structural changes), control table E300 should be used. E300 calculates the difference between the rolled-in and the calculated OB and books it on the accounts specified in E300 (you can have four different account movements depending on the type of structural changes). It needs a line with OB to OB (OB account as from account and to account) in order to work. If a company is sold and you want the OB values and the belonging sales values to be booked in order to get correct values for the group on each account, you must sell out the company in the company structure and have E300 activated and configured.

Calculation Logic in the Controller Tables

Control tables based on the investment register (xacq)

  • Control tables numbered E100 – E150.
  • The automatic journals do not need the use of OB accounts as From Accounts and no OB accounts exist in the investment register. Mind that you always use detail accounts in the investment and then these accounts feed into any main accounts via the control tables.

In the old consolidation engine (Olkomo), which is valid in Controller 2.3 and may be used in 8.x, OB values for acquisition values are created as follows:

  • Usually rolled in from the previous year in the period database (xdb).
  • The first year (using startperiod) they are calculated from data in xacq.
  • If transfer accounts are used, P&L bookings from last year are booked into OB net income and roll into OB retained earnings the following year.
  • When selling internally, there should be no effect on the top level. How to handle accumulated depreciation is described under E125.
  • When restructuring, you may get OB problems if the value previous year is stored on a different ktypkonc (e.g. LE2500) than the elimination current year (e.g. LE2000).
  • If you for some reason get an incorrect OB value (usually in a converted currency) and can’t adjust this in the investment register, you are able to use the functionality “Adjust Opening Balances for Automatic Journals”. Though, this is not possible for P&L accounts.

In the new consolidation engine (Nykomo), which is default in Controller 8.x, OB values for acquisition values are created as follows:

  • Always created within xacq (in order to better handle structural changes).
  • All details regarding company, counter company, currency etc is stored in the table xacq_conv.
  • The table xacq_conv makes it possible to handle OB values when restructuring and to get the correct conversion rate. A transfer is made from xacq_conv to xdb, where the elimination is booked on the correct level if legal consolidation is selected.
  • If transfer accounts are used, P&L bookings from last year are booked into OB net income and roll into OB retained earnings the following year.

Note that you do not have the option to adjust OB values, as the functionality “Adjust Opening Balances for Automatic Journals” only writes into xdb and the OB values are created within the investment register.

Note that you may get problems if you enter acquisition values for old acquisitions on both retained earnings and P&L accounts.

Control tables based on period database (xdb)

Here are the control tables numbered E200 and up. Some general rules:

  • Special control tables cannot have any accounts specified as they affect all accounts (E200 and E210) or are based on other automatic journals (E900). “Indirect” control tables cannot have any accounts specified as they use the data from “direct” control tables (E215, E505, E515, and E705).
  • The rest of the control tables, whether predefined or user-defined, should if possible have OB accounts and movement accounts as “From accounts”.
  • Regarding how OB values are created, see the part about this.

To describe how the calculations are done depending on how the control tables are configured, we here introduce some new concepts:

  • Head account: an account not directly part of any OB/CB structure; an account containing accumulated values (BS). Note: OB accounts are also handled as main accounts!
  • Change account: describes the period’s change. P&L accounts and specification (movements) accounts (excluding OB).

Note that a change account must store data – can not be the difference between CB and OB (can not be a summation account). With an incorrect account configuration you can get the expected result in the first year, but will end up with problems in either year 2 or 3.

The rules look as follows:

  • Case refers to the different examples
  • Type refers the relationship i.e. Head account to head account, head account to change account, change account to head account, etc.
  • Stored amount refers to how the data stored in IBM Cognos Controller
  • Trans Amount refers to how the transaction amount is stored in IBM Cognos Controller
Table 3 provides an explanation on how head and change accounts work with automatic journals (break down)
Case TypeStored as AmountStored as Trans. Amount
1H1 -> H2Amount H10
2C1 -> C2Amount C1Amount C1
3H1 -> C2Amount h1 (Y0) minus trbelopp C2 (Y-1)Amount H1 (Y0)
4C1 -> H2Amount C1 (Y0) plus amount H2 (Y-1)0
5C1+H1 -> H2Amount H1 (Y0) plus amount C1 (Y0)0

Calculation of automatic Journals affected by head and change account

  • H = Head Account
  • C = Change Account
  • 1 = FROM account
  • 2 = TO account
  • Y0 = Current Year
  • Y-1 = Previous Year

Issues/comments:

  • In xdb, there is the column “belopp” for amount and “trbelopp” meaning transaction amount. Trbelopp is usually used for inter-company data (IC), but is also used by automatic journals when the “From account” is a head account.
  • Do not combine head and change accounts going to a change account! That will give unexpected results.
  • Note that if there is an error in the control table, all lines with the same to account (even if there are no values in the “wrong” lines) need to be corrected before it will work properly.
  • In the new consolidation engine (Nykomo), the control tables for acquisition calculations should be configured in the same way whether or not E300 is used. The recommendation is to always use specifications if possible, that is, use movement accounts as from accounts. Main accounts can be used as from accounts, but can create problems in certain situations (if a company is sold/stops reporting values but remains in the structure, OB values will roll in).
  • Summation accounts are practical to use as “From accounts”, either when eliminating all these accounts or when the “To account” is a head account.
  • Don’t forget to add in the From account the To accounts which must be based of the calculation at the next level (e.g.: consolidated reserve,currency conversion,...)
  • In a not strictly legal company structure, user-defined automatic journals using counter company information (CC Ind) may get problems with currency conversion if not the whole automatic journal is defined with CC Ind, but only some of the accounts. Therefore CC Ind should be valid for the whole automatic journal.

Analysis Tools

In IBM Cognos Controller 8.2 there is a new option under Verify Structures for Automatic Journals. This is good to run when you have configured or changed a control table, as it can find different kinds of errors. There is a written guide about this feature in proven practices.

Note that error code 03 (Invalid mix of change and head accounts as from accounts when the to account is a change account) must absolutely be corrected as it will give wrong values on the year 2 and 3.

  • Error code 04 is only a warning and must not be corrected.
  • Running an older version of IBM Cognos Controller, you can also get information whether anything is wrong in a specific control table. You then use the server preference Trace_acctype. Write for example TRACE_ACCTYPE = E700 and run consolidation. An Microsoft Excel sheet called TraceAccTypeE700.xls will be created, so search for that. In the from columns you see how the intervals nest up all accounts and which are the to accounts. Only lines which may be a problem are part of the list. In the column “ktotyporig” you find the account type of the original type and in “ktotyppf” the account type of the target account. M will stand for movement (change) while H for head.

Different Consolidation models

Standard Consolidation model, using the investment register

In the standard configuration, the automatic journals are based partly on the investment register, partly on the period database, depending on the calculations involved. This configuration, which is the original one, is commonly used in the Nordic countries, UK, US, Germany, Switzerland and also in Asia.

Table 4 shows the control tables involved, based on the investment register (Standard Investment Model)
Control Table #DescriptionAutomatic Journal Type
E100Elimination of investments, parent1
E105Elimination of investments, subsidiary1
E110Elimination of investments, associated company1
E115Elimination of investments, joint venture company1
E120Depreciation of surplus value in investment, parent8
E125Depreciation of surplus value, subsidiary8
E130Depreciation of surplus value, associated company65
E135Depreciation of surplus value, joint venture company65
E150Currency Conversion difference in investments18
Table 5 - The control tables involved, based on the period database
Control TableDescriptionAutomatic Journal Type
E200Elimination of proportional companies40
E210Elimination of 100% equity + joint venture60
E215Elimination of 100% equity + joint venture (indirect)66
E300Structural changes-adjusting opening balances20/respective AJT
E400Transfer of untaxed reserve (mainly Swedish)70
E410Booking of Deferred Taxes75
E500Equity share in associated companies61
E505Indirect Equity in associated companies67
E510Equity share in joint venture companies62
E515Indirect Equity in Joint Venture companies68
E700Minority Share90
E705Indirect Minority97
E800Transfer between restricted and unrestricted equity (Swedish)80
E900Re-booking due to complex ownership21/respective AJT

Note that it exists also the rule E106 “Elimination of investments, all” which is a combination of E105, E110 and E115. The purpose of E106 is to use the investment register but get an automatic journal in the Latin way. Probably nobody has used it, as the Latin users prefer working with intercompany accounts in xdb for investments instead of using the investment register.

Note that E505 and E515 must not be activated at the same time, as you will then get double elimination of indirect equity share. This is problem if you have both E groups and J groups and either work in the old consolidation engine (2.3 or later) or when having a management consolidation type in the new consolidation engine.

Latin Consolidation model, based on the period database

In the Latin configuration, the automatic journals are based only on the period database. This configuration is commonly used in the Latin countries such as Belgium, France,Italy and Spain. The investment register is not used and so instead of storing investment data in the investment register, this is stored on IC accounts on the owner. Several automatic journals are based on this investment data, but booked on different companies and with different calculation rules.

There is also a specific account called Consolidated Reserves which keeps the net equity of the subsidiaries, excluding net income. So the value after elimination on this account is the contribution of a subsidiary to the group.

Note that it will give exactly the same results companies by companies as the standard model. It’s more a question of habit, in these countries they don’t want to re-book in the investment register, data already booked in the reported values.

Table 6 shows the control tables usually involved for Investments (Latin Investment Model)
Control TableDescriptionAutomatic Journal Type
E200Elimination of proportional companies40
E210Elimination of 100% equity + joint ventures60
E215Elimination of 100% equity + joint ventures (indirect)66
E300Structural changes-adjusting opening balances20/ respective AJT
E500Equity Share in associated companies61
E710Minority share on equity90
E715Minority share on investment10
E750Transfer of equity to consolidated reserves50
E770Elimination of investments, parent (xdb)10
E775Elimination of investment, all subsidiaries (xdb)10
E900Re-booking due to complex ownership21/respective AJT

Note that the minority calculations E710 and E715 use the calculated ownership %, while E700 uses the direct ownership %. In the old consolidation engine, using E705 (standard model) gives wrong indirect minority when there are multiple owners with minorities as Controller will calculate a weighted percentage. This is solved with the new consolidation engine, as you there consolidate level by level.

The Latin model however, gives a correct minority share in both consolidation engine when you have multiple owned companies.

Investment adjustment model

This consolidation model used mainly in the Netherlands, Denmark and Canada adjust the owner’s investment account to be in line with the total equity of its subsidiaries. There are some specific control tables to handle this adjustment and also some other control tables are configured somewhat differently compared to in the standard and the Latin consolidation models.

Note that the investment register is not used in this model.

Table 7 shows the control tables usually involved (Investment adjustment Model)
Control TableDescriptionAutomatic Journal Type
E200Elimination of proportional companies40
E210Elimination of 100% equity + joint ventures60
E215Elimination of 100% equity + joint ventures (indirect)66
E300Structural changes-adjusting opening balances20/ respective AJT
E500Equity share in associated companies61
E505Indirect equity in associated companies67
E600Investment adjustment, result30
E601Investment adjustment, currency conversion30
E602Investment adjustment, other30
E603Investment adjustment, equity method30
E700Minority Share90
E705Indirect minority97
E760Transfer of equity, investment adjustments50
E770Elimination of investments, parent (xdb)10
E900Rebooking due to complex ownership (Nykomo)21/respective AJT

A thorough presentation of this model is found in the Proven Practice document about Investment Adjustments.

Note that it will give the same result as the standard and latin model at group level but not at company level! (as the result of the subsidiary is transferred to the owner)


Pre-defined Automatic Journals

Automatic journals based on the investment register

Available Automatic Journals:

E100 – Elimination of investment, Parent (AJ 1)

The purpose is to eliminate the investment on the parent and counter-book on an offset account. Enter a journal number (1 for example). Enter all detail accounts for investments (if there is a specification), used in the investment register, as From accounts. OB lines should not be needed as From accounts, as investments should never be booked on OB accounts in the register. You can use a summation account as From account, but not when the To account is a change account. Don’t forget to enter the correct OB account in the OB column when the To account is a change account.

Figure 17 shows an example of a Set up for E100
Figure 17 shows an example of a Set up for E100
  • 245000 is the investment account and is detailed by movements
  • 140000 is the offset account (Equity account)
  • E100 eliminates the investment amount entered into the investment register, no matter if the subsidiary is owned with the Purchase, Equity or Split method
  • The automatic journal type 1, number 1 will be booked on the owner

The elimination will be done based on the values entered in the Investment Register (here in local currency).

Figure 18 shows the investment register and the account information
Figure 18 shows the investment register and the account information

E105 - Elimination of investments, subsidiary (AJ 1)

The purpose is to eliminate the acquisition values on the subsidiary and counter-book on an offset account as in E100. Enter a journal number (another one than for E100, 2 for example). Enter all detail accounts for the acquisition values, used in the investment register, as From accounts. These are equity accounts and accounts for goodwill and surplus value in buildings etc. OB lines should not be needed, as acquisition values should never be booked on OB accounts in the register.

If you use a summation account as From account and leave the To account empty, the elimination will booked on the same details accounts as those summing into the summation account. Don’t forget to enter the correct OB account in the OB column when the To account is a change account (but not a P&L account).

Tip: use an other offset account than E100 so it will be easier to analyse it (specially if a owner is also a owned company). Create a total offset account which is a summation of both offset . This total account must equal to zero at group level if not there is probably an error in the configuration of the control tables.

Figure 19 shows E105 setup, including account, version, offset account, etc.
Figure 19 shows E105 setup, including account, version, offset account, etc.
  • As the OB accounts don’t hold values in the investment register, the usual summation account (here accounts ending by EB are including OB) can be used as From account for elimination on its specifications (movements).
  • In this example 100000 is the capital, 136000 the retained earnings ,200000 the goodwill and 141000 the offset account.
  • The amounts entered into the investment register, except the investment account, will be eliminated on each company consolidated with the Purchase or the Split Method.
  • The automatic journal type 1, number 2 will be booked on the owned company.

E110 and E115 – Elimination of investments, associated company or joint venture company

The purpose is to eliminate the acquisition values on associated companies/joint ventures and counter-book on the same offset account as in E105. Enter a journal number (another one than for E100 and E105, 3 for example) What differs from E105 is that you want the elimination to be booked on the specific accounts for “equity share in associated companies”.

Enter all detail accounts for the acquisition values, used in the investment register, as From accounts. These are equity accounts and accounts for goodwill and overvalue in buildings etc. OB lines are not needed, as acquisition values should never be booked on OB accounts in the register.

Figure 20 shows E110, including account, version, etc.
Figure 20 shows E110, including account, version, etc.

In this example 248000 is the equity share in associated companies account (assets account).

E120 and E125 – Depreciation of surplus value in investment, parent or subsidiary

The purpose is to create depreciation posts based on the surplus value amounts entered into the investment register, on which you set a percentage. No offset account is used. E125 is commonly used and booked on the subsidiary. E120 books the depreciation on the parent and cannot be combined with E125. It has limitations regarding currency – you cannot, for example, enter accumulated depreciation in any other currency than the selected one (LC or the selected GC).

Enter all surplus value accounts used in the investment register, as From accounts.

Figure 21 shows E125 set up, including account, version, etc.
Figure 21 shows E125 set up, including account, version, etc.

Here the account for surplus value i, 200000, goes into P&L cost account for depreciation 390000and counter-books on assets specification account 209000300 and main asset depreciation account 209000.

When starting up IBM Cognos Controller, or when selling internally, you need to enter accumulated depreciation. You do that in the Investment register by standing on the line with surplus value and a percentage filled in, and selecting the “Depreciation details” button.

Figure 22 shows depreciation details
Figure 22 shows depreciation details

Just make sure that the date in the depreciation register is the last day on the previous year. The amount will be booked as OB accumulated depreciation and counter-booked on retained earnings according to General Configuration.

E130 – Depreciation of surplus value, associated company or joint venture company

E130 is quite similar to E125 – the difference is that you steer the depreciation to an asset account for equity and, in some cases, to an account for restricted equity share (otherwise to retained earnings). Accumulated depreciation is handled as for E125.

E150 – Currency conversion difference in investments

The purpose of E150 (AJT 18) is to be a complement to E100/E105/E110 (AJT 1) so that the offset account is zero on the group. You configure E150 differently, depending on whether you store acquisition values in local currency (LC) or group currency (GC) . This selection must be done in the general configuration/General 2

Acquisitions in LC

What E150 does here, is to book the difference between the investment value in the parent's currency (that could use some historical rates mentioned in the investment register) and that value in LC * B rate. In the control table, you have the investment account (shares in subs) as from account, and the account for currency conversion difference as to account, and the offset account filled in (in the header or as to account). It is the offset account that should be adjusted to get zero on the group level.

Figure 23 shows the setup for E150
Figure 23 shows the setup for E150

In the investment register the value in Local currency is booked:

DT Capital 1200
CT Investment in Sub 1200

An historical value of 640 is booked against the account Investment in subsidiaries in the investment register.

The subsidiary (B) has an other LC than the GC and is owned at 80%.

Figure 24 shows the journal across when the acquisitions are set up in local currency
Figure 24 shows the journal across when the acquisitions are set up in local currency

Here below you will find the result in GC, the historical value of the capital is 800, the closing rate is 0.5 and the average rate is 0.70.

Note that offset in GC is the offset at LC*the closing rate, the difference with the historical rate is booked in the currency conversion difference account set in the control table E150.

Table 8 shows currency translation based on the investment set up in the local currency
In GC = Parent CurrencyParent Base ValueAJT 1Sub base valueAJT 1 (10)AJT 18AJT 90Total
Shares in subs640-640000
Other assets120014202620
Total Assets1840-64014202620
Share Capital1400800-640-1601400
Retained Earnings100100
Result140100-20220
Offset-640600400
Currency conversion diff-8040-401664
Total Equity1640-64082000-1641656
Minorities164164
Liabilities200600800
Total Equity and Liabilities1840-64014200002620

Note that the difference in investments, etyp 18, is booked on the subsidiary.

Acquisitions in GC

What E150 does here, is to book the difference between the share capital in the parent's currency/GC and that value in LC * B rate. In the control table, you have the account for share capital (100000) as from account, and the account for currency conversion difference (100000TD) as to account. It is the share capital account (100000) that should be adjusted, so that only the parent’s share capital is left on the group.

Figure 25 shows E150 set up
Figure 25 shows E150 set up

In the investment register the value in Group currency is booked:

DT capital 640
CT Shares in Sub 640

Table 9 shows how the example of $640 is handle using the automatic journals and having the investment information stored as the group currency
In GC = Parent's currParent Base ValueAJT 1Sub Base ValueAJT 1 (10)AJT 18AJT 90Total
Shares in subs640-640000
Other assets120014202620
Total1840-64014200002620
Share Capital1400800-640-1601400
Retained Earnings100100
Result140100-20220
Offset-64064000
Currency conversion diff-8016-64
Total Equity1640-64082000-1641656
Minorities164164
Liabilities200600800
Total Equity & Liabilities1840-64014202620

Note that in this specific example no AJT18 is needed to adjust the capital.

Note that historical rates in the investment register are not useful at all when acquisitions in GC are used. Even if you enter historical rates, the investment in group currency will always be converted at the closing rate (B rate).

It is recommended that the investment information in the investment register is entered in the local currency and activate E150 to handle currency differences.

Automatic journals based on the period database

The following automatic journals are based on the period database.

  • E200 – Elimination proportional companies (AJT 40)

This control table need only to be activated and having a journal number. Also fill in the appropriate contribution version source (should include AJT 70 if E400 is used and AJT 75 if E410 is used and AJT 30 if E6xx is used, should not include AJT 35 if the split is calculated in the IC control table). It eliminates the external part of companies consolidated with the proportional (split) company (so only valid for S companies).

Figure 26 shows E200 setup
Figure 26 shows E200 setup
Figure 27 shows the elimination entry for E200
Figure 27 shows the elimination entry for E200
  • E210 – Elimination 100% equity + joint ventures (AJT 60)

E210 is the same type of control table as E200, with no lines to fill in. Just activate and select a journal number. 100% of the calculation basis will then be eliminated on AJT 60 for associated and joint venture companies (so only valid for E and J companies).

Figure 28 shows E210 set up
Figure 28 shows E210 set up
Figure 29 shows the elimination entry for E210
Figure 29 shows the elimination entry for E210

E215 – Elimination 100% equity + joint ventures, indirect (AJT 66)

E215 is an “indirect” automatic journal, meaning it is based on all other elimination and therefore calculated late in the process. It is needed when the old consolidation engine and the standard model is applied, but in the new consolidation engine it only applies for management consolidation types. 100% of the calculation basis will be eliminated on AJT 66.

E300 – Structural changes – adjusting opening balances (AJT 20)

E300 is a special automatic journal that handles structural changes between years and treat OB accordingly:

  • OB values are kept when a company is sold and reversed with the movement specified in the column disposal. When a company is acquired any OB will be eliminated and replaced by the movement specified in the column investment. This is to get no reconciliation differences between OB and LY CB.
  • When a company’s ownership changes (due to internal/external sales/disposals), E300 fixes that OB is rolled in and that the change of amount (for example minority) is reflected on the movement specified in E300 in the column changes in ownerships
  • Change of consolidation method between years is also handled to get a correct OB when changing for example the consolidation method from Purchase to Equity.
  • Journal number 99 is recommended in order not to risk mixing up with the journal numbers of other control tables.
  • If a disposal or acquisition is handled, E300 handling of base values will be booked on AJT 20, with E300’s journal number. For AJT values, E300 will book on the same AJT but with the journal number of E300.
  • If E300 handles a change in ownership or of consolidation method, the adjustment will be booked on the original AJT and also with the original journal number. You will see a value on the account entered in E300 in the column “Account change in own%” or “Account change method”.
  • From 8.2 selling of groups and inserted/removed groups are handled by E300.
  • E300 is triggered by a change in ownership and/or method in table xkstrucs.
Figure 30 shows an Example of changes in own %:
Figure 30 shows an Example of changes in own %:

Thanks to this E300 correction, it will correct the reconciliation between the OB and the CB of LY (on AJ1 i have on OB an amount of -800 and on AJ90 on OB an amount of -200).

Figure 31 shows an example of full disposal of a company/group
Figure 31 shows an example of full disposal of a company/group

E300 counter books the value of OB on a movement disposal.

Figure 32 shows an example of changes in the consolidation method
Figure 32 shows an example of changes in the consolidation method

E300 counter books the value of any OB as none Balance sheet values of equity company must be consolidated.

Figure 33 shows a Configuration of the control table for E300
Figure 33 shows a Configuration of the control table for E300
  • For each OB account you specify which movement accounts to use for changes due to investment, disposal, change in ownership or change in method. If you want to separate internal and external sales/acquisitions, use the B/E/I column and fill in E for external and I for internal with the appropriate accounts. In this example we use only one movement for all type of structural changes , to use four different movements will increase the account structures but will give a better analysis.
  • Note that E300 doesn’t handle automatically any recalculation of the result due to changes of ownership during the year (piecemeal acquisitions/disposals, full disposals/acquisitions).
  • Also remember that the movement accounts used in E300 should have the same currency translation code as their OB account. The reason is to get the same amount, in all currencies to be booked (so that a sale ends up in zero).

E400 – Transfer of untaxed reserves (mainly Swedish – AJT 70)

E400 reallocates untaxed reserves (that must not exist on groups) into a tax part and a part going into restricted reserves (or non-restricted with new IFRS rules). It uses tax rate(s) from sub control table HT01.

Figure 34 shows E400 (Transfer of untaxed reserves)
Figure 34 shows E400 (Transfer of untaxed reserves)

The B/T/N column is used to specify whether the amount should be taken at 100% (blank), multiplied with the tax rate (T) or the rest (N) that is 100% minus tax rate.

Figure 35 shows the country, account and tax rates
Figure 35 shows the country, account and tax rates

The first line, with blanks in the country and account columns, states the general tax rate to use. Exceptions are created by adding lines per country (here, the general tax rate for Sweden is 28%) and/or account.

E410 – Booking of deferred taxes (AJT 75)

E410 allows to calculate deferred taxes by using tax rates mentioned in table HT01; The tax rate is indicated by country and even by account (if the column account is blank then the same tax rate is applicable for all accounts mentioned in the E410 control table). The connection with the companies is done thanks to the field country that must be filled in the company structure. The amount in the From account will be multiplied by the tax rate and booked in the To account. You can duplicate the rule E410, if you want to have deferred taxes as assets and liabilities or specific ones on Automatic journals (use the same category and journal but different journal numbers).

Note there are no period stamps for the tax rates. So in case of tax changes, don’t forget to correct them if you want to reconsolidate previous periods.

Figure 36 shows E410 setup
Figure 36 shows E410 setup
Figure 37 shows the tax table
Figure 37 shows the tax table

E500 – Equity in associated companies (AJT 61)

This control table calculates the equity share in associated companies (only valid for Equity companies). The equity share is booked both on the assets side (account 248000 in this example) and on the equity side). The direct owned percent is multiplied with the amounts on the From accounts (equity including net income and translation difference).

If you take into account the investments owned by the equity company, the account investments must be in From account and be deducted of the account equity share in associated companies.

Figure 38 shows an extract of the configuration for E500
Figure 38 shows an extract of the configuration for E500
Figure 39 shows an example of calculations using the equity method
Figure 39 shows an example of calculations using the equity method

E505 – Indirect equity in associated companies (AJT 67)

E505 is an “indirect” automatic journal, meaning it is based on all other elimination and therefore calculated late in the process. It is needed when the old consolidation engine and the standard model is applied, but in the new consolidation engine it mainly applies for management consolidation types.

The indirect equity percentage (perceq in table xkstrucs) is multiplied with the amount of the company’s base values and elimination.

E505 is connected to E500, from which the accounts are taken for the calculation. The elimination is booked on AJT 67.

Figure 40 shows E505 and the control table
Figure 40 shows E505 and the control table

E510 – equity share in joint venture companies (AJT 62)

This control table works in exactly the same way as E500, but books an equity share for joint venture companies instead. By using both E500 and E510 it is possible to get equity shares for associated companies booked on other accounts than those for joint venture companies. The elimination is booked on AJT 62.

Figure 41 shows E510 and the control table
Figure 41 shows E510 and the control table

E515 – Indirect equity in joint venture companies (AJT 68)

This control tables works in exactly the same way as E505 and is connected to E510, from which the accounts are taken for the calculation. The elimination is booked on AJT 68.

Please note that you must not have both E505 and E515 active, as that will generate double elimination. The problem is that both control tables are triggered by the field for indirect equity in the company structure. This means that in the old consolidation engine, you cannot combine groups with E and J consolidation method.

Figure 42 shows E515 and the control table
Figure 42 shows E515 and the control table

E600-603 – Investment adjustments (AJT 30)

The purpose of these control tables is to revaluate the investment of the owner in order to be in line with the equity of the subsidiary. It will transfer the equity (result) at direct owned%, to the account investment and equity (result) of the owner.

A thorough presentation of this model is found in the Proven Practice document about Investment Adjustments.

These tables are not used by the standard and latin model.

E700 – Minority Share (AJT 90)

The purpose of this control table is to calculate the minority share, based on equity including net income and translation difference.

This control table is only used when you consolidate according to the standard model or the investment adjustment model. The direct owned % is used (ownership on first level in the company structure), taking 1-direct owned %.

If you consolidate according to the latin model you must use the control table E710 instead.

Figure 43 shows an extract of the control table for E700
Figure 43 shows an extract of the control table for E700

EquityXX is a summation of the specifications (movements) on equity accounts (net income BS not included), 136000 and 137000 are both retained earnings account but the last one is only used for company journals. Note that the account 100000 (capital) is also part of the control table (not part of this screenshot) and follows the same logic than the retained earnings accounts.

The accounts 145000XXX are specifications on the net income BS and NETINCOME is the Profit and Loss result (summation account). 150000 is the summation of the translation differences on equity accounts.

In this configuration there are three accounts for minorities, one on equity (139000), one on result (145100) and one on translation difference (150100) which give an accurate analysis of the minorities.

480000 is the account for minorities on Profit and Loss.

Figure 44 shows an example with 20% of minority calculation
Figure 44 shows an example with 20% of minority calculation

E705 – Indirect Minority (AJT 97)

This is an “indirect” automatic journal, such as E505, based on all other automatic journals. It has impact in the old consolidation engine and also in the new consolidation engine when using the standard or the investment adjustment model with a non strictly legal structure or management structure. E705 is connected to E700, from which the accounts are taken for the elimination, which is booked on AJT 97.

Figure 45 shows E705 and the control table
Figure 45 shows E705 and the control table

An example of when AJT 97 would be useful in the new consolidation engine, is when you build a flat structure that should mirror the legal, hierarchical one. Note that the indirect minority (percmin in table xkstrucs) must be set on the first level in order to activate AJT 97.

Example: in the legal consolidation type, company D owned at 70% by group K2 and K2 is owned at P80% by group K1. D's equity is 1000 and no other values. In LE etyp 90 books -300 on D and the consolidated value of K2 is then 700. Etyp 90 on K2 is -140 (-700*20%), so K1's consolidated value is 560. The management consolidation type or a flat structure has percdown 70 and percmin 20. Etyp 90 will book -300 (base 1000 as basis * 30%) and etyp 97 will book -140 (base 1000 + elim -300 as basis * 20%), ending up in 560, which is the same value as in LE.

Note that for the latin model the amount of 560 will be booked directly on etyp 90 (no booking on etyp 97) as it comes from the rule E710 which uses the calculated owned % and not the direct %.

E710 – Minority share on equity (AJT 90)

E710 is used only in the Latin model. It calculates minority share based on equity (including the net income and the translation difference) on the company, but what differs from E700 is that E710 uses the calculated ownership % instead of the direct ownership %. The elimination is booked on AJT 90.

Figure 46 shows E710 and the control table
Figure 46 shows E710 and the control table

This control table removes the minority share from the account 138000 consolidated reserves (where the equity accounts are transferred via E750) to the account minority on equity (139000).

So the From accounts are specifications on equity accounts (EQUITYXXX), on result (145000XXX) ,the translation difference on equity (150000) and the net income of the Profit and Loss. And also the specifications on the consolidated reserves (if there are subgroups with minorities, the minority share must be also calculated on the consolidated reserves from the subsidiaries linked to the subgroup).

In this configuration there are three accounts for minorities, one on equity (139000), one on result (145100) and one on translation difference (150100) which give an accurate analysis of the minorities.

480000 is the account for minorities on Profit and Loss.

If you have minorities (indirect) on equity and split companies, the amounts will be multiplied by the direct owned % multiplied by 1 – (calc owned% divided by the direct owned%)) as indicated in the calculation methods of the rule RD (rule of E710).

Figure 47 shows an example with 20% of minorities
Figure 47 shows an example with 20% of minorities

E715 – Minority on investments (AJT 10)

The purpose of this control table is to calculate the minorities on the investment of the owner (if the owner is not owned at 100%). The booking is done on the account minority of the subsidiary (use of the CC indicator C). This control table is only used when you consolidate according to the latin model.

Figure 48 shows E715 and the control table
Figure 48 shows E715 and the control table

In from accounts you have the specifications on the investment (240000XXX) and also on the account counterpart goodwill (143000XXX).

As the latin model doesn't use the investment register, the goodwill is booked in a company journal (normally by the owner) and the manual adjustment will look like:

DT Goodwill
CT counterpart goodwill

If there are some minorities share on the owner , minorities will be calculated on the account counterpart goodwill and be added in the account Minorities on equity (139000) of the subsidiary or be removed if they are calculated on the account investment. It's balanced with the account consolidated reserves (138000).

If the owner is an equity or split company, the amount will be multiplied by the direct owned % multiplied by 1 – (calc owned% divided by the direct owned%) according to the calculation method of the rule RD (rule of E715).

E750 – Transfer of equity to consolidated reserves (AJT 50)

E750 is used in the Latin model. It transfers all companies’ (except the parent’s) equity (except net income) into the account consolidated reserves, which is an account where you find the final contribution of all subsidiaries to the group total. The elimination is booked on AJT 50.

The column Selection Method is open, meaning you can handle for example subsidiaries and associated companies in different ways. The column GM indicator is also open. Leaving it blank (probably most common), means for P (purchase) companies that the full amount will be transferred to consolidated reserves. Setting G means that the amount will be multiplied with the calculated ownership %.

Depending on how E750 is configured, other control tables may have to be modified. If the subsidiary is an equity or split, the amount will be multiplied by the direct owned %.

In this configuration (the most common) 100% (if Purchase company) will be transferred to the consolidated reserves (138000). The from accounts are equity accounts (net income BS not included).

The selection method S2 (all companies except parent).

Figure 49 shows E750 and the control table configuration
Figure 49 shows E750 and the control table configuration
Figure 50 In this example you can see that 100% of the capital is transferred to the account consolidated reserves via the AJ 50
Figure 50 In this example you can see that 100% of the capital is transferred to the account consolidated reserves via the AJ 50

E760 – Transfer of equity, investment adjustments (AJT 50)

This control table is only used when you consolidate according to the investment adjustment model and therefore not explained here. Please read about it in the separate Proven Practices document about Investment Adjustments.

The purpose is to eliminate the equity at direct owned % via an offset account.

E770 – Elimination of investments, Parent (AJT 10)

E770 is used in the Latin setup together with E775, and also in the Investment Adjustment model.

It can be compared with E100 as it eliminates the investment on the owner. Offset account should be filled in to balance the booking.

Figure 51 shows E770 and the control table configuration
Figure 51 shows E770 and the control table configuration

240000XXX are the specifications on the account Investments in subsidiaries, 140000 is the offset equity account. Selection method is open in case you want to book elimination of different types of companies on different accounts (note this applies to the owning company and not the owned one). If the owner is an equity or split, the amount will be multiplied by the direct owned %.

Figure 52 In this example the owner is a parent company
Figure 52 In this example the owner is a parent company

E775 – Elimination of investments, all subsidiaries (AJT 10)

E775 is used in the Latin setup together with E770. The purpose of this control table is to transfer 100% of the investment of the owner to the account consolidated reserves of the subsidiary.

It can be compared with E105 as it eliminates the investment on the owned company. Though, here the owner’s investment account is the calculation basis for the elimination on the owned company (use of the CC indicator C). Offset account should be filled in to balance the booking.

Tip: use an other offset account than E770 so it will be easier to analyse it (specially if a owner is also a owned company). Create a total offset account which is a summation of both offset . This total account must equal at zero at group level if not there is probably an error in the configuration of the control tables.

Figure 53 shows E775 and the control table configuration
Figure 53 shows E775 and the control table configuration
  • In from accounts you have the specifications on the investment (240000XXX) and also on the account counterpart goodwill (143000XXX).
  • The specifications on investments (240000XXX) are booked in the subsidiary in the consolidated reserves (138000) and balanced thanks to the offset account (141000).
  • The specifications on counterpart goodwill (143000XXX) are removed on the consolidated reserves (138000) and booked on the account counterpart goodwill of the subsidiary.
  • Selection method is open in case you want to book elimination of different types of companies on different accounts.
  • If the owner is an equity or split, the amount will be multiplied by the direct owned %.
  • In this example you can see that 100% of the investment of the parent (amount of 640) is transferred to the account consolidated reserves of the subsidiary via the offset account (AJ 10).
Figure 54 - need alt text from Paul Young
Figure 54 - need alt text from Paul Young

E800 – Transfer between restricted and unrestricted equity (Swedish, AJT 80)

This control table transfers equity between restricted and unrestricted equity, so that unrestricted equity does not hold more than is allowed to be distributed as dividends.

Note that as IFRS does not differ between restricted and unrestricted equity, this control table will probably be used less in the future.

Figure 55 shows E800 and the control table configuration
Figure 55 shows E800 and the control table configuration

Note that the equity main accounts are used as from accounts (must not be movement accounts). In the R / U / I columns you set whether the account is defined as restricted reserves (R), Unrestricted reserves (U) or net income (I). If you have several accounts within the same category, you set priorities within the category. A line with priority 1 will be eliminated first in the calculation.

E900 – Re-booking due to complex ownership (New Consolidation Engine)

This control table is used by the new consolidation engine when you have a company structure with multi-ownership (same company linked to several subgroups). It has journal number 10000 and can not be deactivated. When ownerships meet (the company will get a consolidation method A at this level in the table xkstrucs), the calculations on lower levels are reversed on each automatic journal type with journal number 10000 (the base values are reversed on AJT 21) and new journals are created.

Figure 56 is a sample of a company hierarchy
Figure 56 is a sample of a company hierarchy

Note that in case of multi-ownership the percentage taken will be always the direct percentage even if in the calculation method it's specified the calculated percentage.


Non predefined automatic journals-some examples

Elimination of dividend

Purpose:

To eliminate automatically the dividend received during the year against a retained earnings account.

Settings in this example:

The dividend received account is an intercompany income account. Note that the intercompany code can be “I” account type (You don’t need to know what’s the transaction amount).

Figure 57 shows the account set up for dividends
Figure 57 shows the account set up for dividends

The result of the balance sheet and the retained earnings are non integrated.

All the movements (statistical accounts) are summed in the EB which is reconciled with the main account. The OB is copied from the EB of LY (code J).

Figure 58 shows the account and movements for Retained Earnings and Result
Figure 58 shows the account and movements for Retained Earnings and Result

To get correct movements on the net income BS and the retained earnings, it’s important to use the transfer accounts in the general configuration. Note that this movement 150 must have a conversion code L (average rate of LY) to get the same value than the Net Income OB.

Figure 59 shows the reconcile 2 tab
Figure 59 shows the reconcile 2 tab

Creation of the Automatic journal and adding in the relevant contribution versions

Creation of the AJ “ED” (can be any combinations of letters, figures are only for predefined AJ).

Figure 60 shows the automatic journal types
Figure 60 shows the automatic journal types

Add this AJ “ED” in the relevant contribution versions.

Definition of the rule

Creation of the non predefined rule C100.

  1. In AJ type box select the AJ created, in this example ED.
  2. Select level 1 ( must be calculated at the same level than the predefined AJ).
  3. As the source account (dividend received) is an Intercompany account that must be eliminated where the company and counterpart company meet, the category selected must be 23.
  4. Select Active, Enable calculation of Change in structure (if you use E300) and Alternative currency conversion (not necessary here but we recommend to select it by default).
  5. The following columns must be shown in the control table: konto,tecken_pf,konto_ib,konto_pf and gm_ind.
  6. In column regel_id type R5 (purchase and parent: amount type:amount,rate type: direct owned %;split :amount type:amount*direct owned %,rate type: 100%).
  7. In column msel type S7 (all except equity/joint venture).
Figure 61 shows the Definition of the control table
Figure 61 shows the Definition of the control table
Figure 62 shows the control table set up
Figure 62 shows the control table set up

In the closing version source you are not obliged to enter something (in this example I have entered a closing version without a journal post consolidation).

Here are the definitions of the account part of the control table. As mentioned before it’s non integrated accounts and there is one account for the retained earnings (137000) and one account for the result of B/S (145000) . But to get correct movements on minorities, there is in this example only one account for minorities which includes the result of the year and the translation difference as there are no automatic transfer mechanism for the minorities through the general configuration like for the group accounts.

420000 : dividend received, I/C income account
137000: retained earnings, B/S account
137000120: retained earnings/dividend paid, statistical account
139000: reserves minorities, B/S account
139000120: reserves minorities/dividend paid, statistical account
139000200: reserves minorities/result of the year, statistical account
480000: minorities on P/L, cost account

Figure 63 shows the reserve minority account with its movement accounts
Figure 63 shows the reserve minority account with its movement accounts

Result of the automatic elimination

The amount of dividend to be eliminated is 80.

The company B (company who has received the dividend) is a purchase company owned at 80%.

Figure 64 journals by journal report (First Year)
Figure 64 journals by journal report (First Year)

Note than thanks to the general configuration an amount of 64 will be booked also in the net income of the B/S (145000) and in the net income of the B/S /result of the year (145000200).

Figure 65 represents the Second year (journals by journal report)
Figure 65 represents the Second year (journals by journal report)

The retained earnings /dividend paid of last year is transferred to the retained earnings/OB.

The result B/S of last year is transferred to the retained earnings/transfer thanks to the general configuration.

The result B/S /result of the year of last year is transferred to the result B/S /OB.

The result B/S of last year is transferred to the result of B/S /transfer thanks to the general configuration.

Third year:

None booking as the movement 150 will be transferred to OB.

In order to understand better what’s the issue, you will get when you have more than one minority accounts, one for the reserves (139000) and one for the result (145100), the same exercise has been done with the following control table.

Figure 66 shows the control table for C100 elimination of dividend
Figure 66 shows the control table for C100 elimination of dividend

145100: result of minorities, B/S account

145100200: result of minorities/result of the year, statistical account

First Year:

Figure 67 shows the journals report
Figure 67 shows the journals report

Note than thanks to the general configuration an amount of 64 will be booked also in the net income of the B/S (145000) and in the net income of the B/S / result of the year (145000200).

Second year:

No new dividend.

Figure 68 shows the journals report
Figure 68 shows the journals report
  • The retained earnings /dividend paid of last year is transferred to the retained earnings/OB.
  • The result B/S of last year is transferred to the retained earnings/transfer thanks to the general configuration.
  • The result B/S /result of the year of last year is transferred to the result B/S /OB.
  • The result B/S of last year is transferred to the result of B/S /transfer thanks to the general configuration.
  • The reserves minorities /dividend paid of last year is transferred to the reserves minorities/OB.
  • The result minorities /dividend paid of last year is transferred to the result minorities/OB.

Note that for these two last accounts we should have an automatic update also to the transfer accounts to get 0 in the ending balance but unfortunately there are not such options in the general configuration as we have for the group accounts (137000 and 145000) and so we will get reconciliations issues (EB <> CB).

Figure 69 shows the journals report (elimination of the dividend)
Figure 69 shows the journals report (elimination of the dividend)

You will get one remaining movements on OB.

Reclassification of the loss of equity companies by using the conditions

The purpose is to reclassify a negative P/L result of equity cies (490000 calculated via E500) to a cost account (390000).

Figure 70 Create a contribution version which contains the AJ61 (E500)
Figure 70 Create a contribution version which contains the AJ61 (E500)
Figure 71 Create an AJ in Automatic journal types tab where will be posted the negative result on equity (N6 in example)
Figure 71 Create an AJ in Automatic journal types tab where will be posted the negative result on equity (N6 in example)
Figure 72 Add this new AJ (N6) in the contribution versions that are relevant
Figure 72 Add this new AJ (N6) in the contribution versions that are relevant
Figure 73 Create a rule in automatic journals/define/ rules to retrieve 100% of value only valid for equity companies
Figure 73 Create a rule in automatic journals/define/ rules to retrieve 100% of value only valid for equity companies
Figure 74 Create an AJ (C500) which will reclassify the negative result coming from E500; The Automatic journal type must be the one you created before (N6) and must be at level 2 (meaning that it will be calculated after the other AJ set at level 1)
Figure 74 Create an AJ (C500) which will reclassify the negative result coming from E500; The Automatic journal type must be the one you created before (N6) and must be at level 2 (meaning that it will be calculated after the other AJ set at level 1)

Go in condition tab and enter <0.

Figure 75 shows the condition tab and setting < 0
Figure 75 shows the condition tab and setting < 0

Go in maintain/configuration/control tables/acquisition calculation and select C500.

Enter a journal number and select as contribution version source AJ61 (Contribution version containing only the AJ61).

In from account enter the account 490000 (result of equity companies) which has been calculated at level 1 in the AJ61.

In to account with the sign – enter the account 390000 (loss of equity companies) and with the sign – enter the account 490000.

So if the condition <0 is met (the account 490000 has a negative amount) the result will be reclassify as a loss.

Figure 76 shows the Control table
Figure 76 shows the Control table
Figure 77 In this example the net income is -200 and the equity company is owned at 20%.The AJ61 will book an amount of -40 in 490000 and this amount will be reclassified in 390000 via the AJ N6.
Figure 77 In this example the net income is -200 and the equity company is owned at 20%.The AJ61 will book an amount of -40 in 490000 and this amount will be reclassified in 390000 via the AJ N6.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=518283
ArticleTitle=IBM Cognos Proven Practices: Guidelines to General Configuration and Automatic Journals for IBM Cognos Controller
publish-date=09152010