...

Investment Adjustments (Control table E600-E603, E760 and E770)

by user

on
Category: Documents
2

views

Report

Comments

Transcript

Investment Adjustments (Control table E600-E603, E760 and E770)
Proven Practice
Investment Adjustments
(Control table E600-E603, E760
and E770)
Product: Cognos Controller
Areas of Interest:
Control tables for Investment adjustment
Combining Investment Adjustments and
standard method in one database
Investment Adjustments (Control table E600-E603, E760 and
E770)
2
Copyright
Your use of this document is subject to the Terms of Use governing the
Cognos software products and related services which you have licensed or
purchased from Cognos. The information contained in this document is
proprietary information of Cognos Incorporated and/or its licensors and is
protected under copyright and other applicable laws. You may use the
information and methodologies described in this document 'as is' or you may
modify them, however Cognos will not be responsible for any deficiencies or
errors that result from modifications which you make. Copyright 2006 (c)
Cognos Incorporated. All Rights Reserved.
You can print selected pages, a section, or the whole book. Cognos grants
you a non-exclusive, non-transferable license to use, copy, and reproduce the
copyright materials, in printed or electronic format, solely for the purpose of
providing internal training on, operating, and maintaining the Cognos
software.
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected]
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
3
Contents
1 INTRODUCTION ............................................................................................ 4
1.1 PURPOSE .........................................................................................................4
1.2 CONSOLIDATION MODELS ......................................................................................4
1.3 APPLICABILITY ...................................................................................................4
2 INVESTMENT ADJUSTMENTS ........................................................................ 5
2.1 BACKGROUND ....................................................................................................5
2.2 COGNOS CONTROLLER .........................................................................................5
2.2.1 Restrictions in Controller 8.1 .........................................................................6
3 CONFIGURATION FOR INVESTMENT ADJUSTMENTS ................................... 7
3.1 ACCOUNT STRUCTURE ..........................................................................................7
3.2 REFERENCE PERIOD .............................................................................................7
3.3 DATA ENTRY OF INVESTMENTS ................................................................................7
3.4 COMPANY STRUCTURE ..........................................................................................7
3.5 CONTROL TABLES E600-E603 ...............................................................................8
3.5.1 E600 – Investment Adjustments, Result .........................................................9
3.5.2 E601 – Investment Adjustments, Currency Conversion .....................................9
3.5.3 E602 – Investment Adjustments, Other ........................................................ 10
3.5.4 E603 – Investment Adjustments, Equity Method............................................ 10
3.6 CALCULATION OF TABLE E60X .............................................................................. 11
3.7 CONTROL TABLE E760 ....................................................................................... 12
3.8 CONTROL TABLE E770 ....................................................................................... 13
3.9 SUB CONTROL TABLE HT02 ................................................................................. 13
3.10 INVESTMENT ADJUSTMENTS AND GOODWILL ........................................................... 14
4 REPORT IN GROUP/RECONCILE - SHAREHOLDINGS AND EQUITY ........... 15
5 INVESTMENTS AND FIRST PERIOD IN CONTROLLER. ............................... 17
6 HOW TO COMBINE TWO CONSOLIDATION METHODS ............................... 19
6.1 DESCRIPTION OF OUR SOLUTION ........................................................................... 19
6.2 AUTOMATIC JOURNALS AND CONSOLIDATION TYPES .................................................... 19
6.3 CONSOLIDATION TYPES ...................................................................................... 19
6.4 ACCOUNTS FOR SHARES IN SUBSIDIARIES ................................................................. 20
6.5 ANALYZE ........................................................................................................ 20
6.5.1 IAS (Standard model) – Consolidation type L1............................................... 21
6.5.2 DK (Danish model) – Consolidation type LE .................................................. 21
6.6 CLOSING VERSIONS AND JOURNAL TYPES .................................................................. 21
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
1 Introduction
1.1
Purpose
The Purpose of this document is to provide good advice when creating
control tables according to the model used in the Netherlands and Denmark.
This document also describes how to combine Investment adjustments with
the Standard model in one database.
1.2
Consolidation models
There are 3 consolidation models Controller.
• The Latin model used mainly in Belgium, Spain and France.
• The Standard model used mainly in Sweden, Switzerland and the
United Kingdom. The standard model uses the acquisition register
with the investment elimination templates as basis for the
eliminations.
• Investment adjustment model, used mainly in the Netherlands,
Denmark, Iceland and Brazil.
1.3
Applicability
The customer example is done in Cognos Controller 8.1MR1 and SQL
platform. Customer is using elimination according to standard configuration
as well.
Cognos Proprietary Information
4
Investment Adjustments (Control table E600-E603, E760 and
E770)
5
2 Investment Adjustments
2.1
Background
A Danish customer, Client 1, wants adjustments and eliminations according to
both Danish GAAP and the Standard model. This hasn’t been possible in
Controller versions earlier than 8.1. In Controller 8.1MR1 there is an iniparameter to use to activate this new function. In Controller 8.2 this will be a
new function. Consolidation type LE uses the Danish model and Consolidation
type L1 uses the standard model. The solution done for the customer can be
found in chapter 6.
In Denmark and the Netherlands, among other countries, the consolidation
process starts with reconciliation between the parents account for Investments
and the account Total Equity for the subsidiaries. The amount on the parents
account for Investments should be updated and show the booked value of the
Total Equity, including result and currency conversion differences for the
subsidiaries. The value of the account should be updated in the parent’s books
at least once a year.
In addition to changes in the result and currency conversion differences, there
can be changes in the different reserves in the Equity of the subsidiary. This
period change will be booked on the parent company on the account
Investments with the counter account as one of the Equity accounts.
How often the reconciliation is made depends on the customer, it varies from
once a month to once a year.
2.2
Cognos Controller
For adjustments of the changes in the equity of the subsidiaries, an automatic
journal type 30 is calculated by using the control tables E600-E603. The sub
control table HT02 is used for the reference period. This period refers to when
the parent companies make the adjustments in their general ledger.
You can reconcile the investments in the report: Group/Reconcile Shareholdings and Investments/Reconcile – Shareholdings and Equity.
For elimination of Investments at the parent, control table E770 is used.
For elimination of Equity at the subsidiaries, control table E760 is used.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
6
2.2.1 Restrictions in Controller 8.1
- The consolidation type has to be strictly legal. OK Controller 8.2
- If a specification of investment and/or equity is used, a reconciliation
difference will occur between CB/OB in Company/Reconcile. The Report Status
for parent companies has to be set manually. OK Controller 8.2
- Cross-owned companies are not handled. OK Controller 8.2
- Special treatment of negative equity according to method 2 and 3 is not
handled.
- New investments during the year must be adjusted with a work around (see
chapter 5). OK Controller 8.2
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
7
3 Configuration for Investment Adjustments
3.1
Account structure
The account (13110) for investments should have the investment code I and
inter company code I. If a separate account is used for the elimination of
investments, this account should have the inter company code I. Accounts in
the P&L (80110) for Income from participation and Income from associated
companies should have the intercompany code I.
3.2
Reference period
Values for the equity of subsidiaries have to be entered in the first reference
period. The values should be in the xdb table and on the same journal type as
the values reported for the actual period, normally REPO. If, for example the
previous month is the reference period, then the month preceding the first
period to be consolidated in Controller needs to be updated with values on the
equity accounts. The values are only entered in local currency. See also chapter
5.
3.3
Data entry of investments
Investments are entered on the parent company as intercompany balances,
with the subsidiaries as counter companies. When consolidating the first period
in Controller, these values needs to be the total of the subsidiaries equity
accounts entered in the first reference period, see reference period above.
3.4
Company structure
The parent in every subgroup has to be marked as parent. The structure has to
be strictly legal, meaning that the parent and the subsidiaries have to meet in
the same subgroup.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
3.5
8
Control tables E600-E603
Changes done in all control tables:
Total Equity: Add account number for Total Equity.
From account: The subsidiaries accounts (which is used as the base for the
booking of the change in equity). The accounts in this column need to be
included in the account for Total Equity entered above.
To account: The parent accounts (which accounts that the change in equity
should be booked to on the parent company).
Change types:
T=Total change
This code is used when you want to book the total change in equity, no matter
if it is a real change or a change due to currency conversion. Example E600,
E603 – see 3.5.1.
N=Real change of equity
This code is used when you want to book the real change (that is also a
change in local currency) in equity. No change due to currency conversion will
be included. Example Control table E602 – see 3.5.3
If you configure control table E601 with the currency conversion accounts, you
should configure E602 with the other equity accounts and change code T (total
change), to receive the real change.
C=Change due to currency conversion
This code is used when you want to book the change in equity that occurs due
to different currency rates in reference period and actual period. The currency
difference will be calculated with conversion code B (closing rate) and M
(average rate) only. Example Control table E601 – see 3.5.2.
You can also configure the control table for currency conversion with the
currency conversion accounts in the equity specifications and use change type
T (total change). The advantage with this example is that you will present a
currency conversion that has used all conversion codes defined and not only B
and M rate as change type C uses.
Please note that in the report Trial Balance Contribution version BASE will
include etyp 30. In other reports only Repo and manual journals are included in
BASE on company level.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
9
3.5.1 E600 – Investment Adjustments, Result
This table will book the subsidiaries total net result (20990, you can not use
the net result account in P&L,9 , the bookings will not be correct) on the
parent company with etyp 30.
3.5.2 E601 – Investment Adjustments, Currency Conversion
This table will book the subsidiaries currency conversion (change type =C) on
equity accounts on the parent company with etyp 30.
NOTE! – Column “To account (OB)” should not be filled in for tables E60x.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
3.5.3 E602 – Investment Adjustments, Other
This table will book the real change in equity.
NOTE! – Column “To account (OB)” should not be filled in for tables E60x
3.5.4 E603 – Investment Adjustments, Equity Method
E603 is run for associated companies that are consolidated with Equity
Method.
NOTE! – Column “To account (OB)” should not be filled in for tables E60x
Cognos Proprietary Information
10
Investment Adjustments (Control table E600-E603, E760 and
E770)
3.6
11
Calculation of table E60X
E60X adjustments are calculated with this formula (if the reference period is
0512 and the actual period is 0603):
- Subsidiaries Equity period 0512AC
+ Subsidiaries Equity period 0603AC
= Adjusted values (in local currency)
Or you can write the formula as:
=SUM(-Subs Equity 0512AC+Subs Equity 0603AC).
The Control sum shown in the Shareholdings and Equity Reconciliation Report
is calculated like this: =SUM (- Investment value Parent 0603AC+ Subs Equity
0512AC in report)
The Difference amount shown in the report is calculated like this: =SUM
(Control value+(-Subs Equity 0512AC+Subs Equity 0603AC))
The currency conversion to other currencies is done according to the
account structure.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
3.7
12
Control table E760
Elimination of equity at the subsidiaries, main accounts and specification
accounts must be defined in the control table.
This table can be compared to table E105 (which is used in standard
elimination methods).
Use a Contribution version in the Control table including BASE and etyp
30+70+75. The contribution version will be used as basis for Controllers
calculations.
NOTE! – The contribution and the offset account in E760 must be the same
as in E770.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
13
3.8
Control table E770
Elimination of Investment account at the parent company!
This table can be compared to table E100 (which is used in standard
elimination methods).
Use a Contribution version in the Control table including BASE and etyp
30+70+75. The contribution version will be used as basis for Controllers
calculations.
NOTE! – The contribution and the offset account in E770 must be the same
as in E760.
3.9
Sub control table HT02
In the sub control table, HT02, you define how often the parent companies
adjust their booked value in their general ledger systems. This determine how
often E600 should be run during the year and which reference period to use.
The table always includes 12 months even if the general ledger is updated once
a year.
In this example E600 runs every month and the previous month is the
reference period. This means that the parent company adjusts their booked
value every month also in the general ledger system.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
If the parent company adjust her bookings once a year all Periods shall use
Reference period = N112.
3.10 Investment Adjustments and Goodwill
The amount on the account “Shares in Subsidiaries” (13110) should only
include the value of the equity in the subsidiary. If there is goodwill in the
acquisition, this should be booked on a separate account, both in Data Entry
and in the Investment register.
Cognos Proprietary Information
14
Investment Adjustments (Control table E600-E603, E760 and
E770)
15
4 Report in Group/Reconcile - Shareholdings and Equity
When you choose Consolidate by steps/Automatic journal – Acquisitions
Calculations, E600 will run as the first automatic journal, with E-type 30.
Before you run this automatic journal, you have to check that the total equity
for the subsidiary in the reference period (xdb) and the amount on the
investment account Shares in (xdb) for the parent are reconciled. For this
purpose, use the report in Group/Reconcile – Shareholdings and
investments/Reconcile – Shareholdings and Equity.
In this report, the All Companies – Only One Level check box has been selected
and all subsidiaries connected to the chosen group are shown together with the
parent company from the subgroup below, because that company is a
subsidiary to the parent in the chosen group.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
16
Verification has to be 0. This means that “total owned equity of the subsidiary”
in the reference period 0010AC is equal to the amount on account “Shares in
subsidiary”.
The report also shows the difference for the period, per accounts specified in
the control table and a total difference that will be booked with automatic
journal 30 on the account Shares in subsidiary in the Investment register.
NOTE: If the verification is not 0 (zero), you have a difference in the previous
period and this has to be corrected before you run E600 for this period.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
17
5 Investments and first period in Controller.
This solution is only valid for 8.1 and prior versions. In the next release, 8.2,
this will be handled automatically.
If an acquisition is done for example in 0512, the reference period, in this case
0412, must be updated with the same amount on the subsidiaries equity
accounts. In 0512 the new acquisition is included in the values from the GLsystem.
Example Company 20100 is new 0512. Last years equity (0412=reference
period) must be entered as a company journal.
The reason why the reference period must be adjusted, is that otherwise would
E60x book the whole acquisition on the parent like a change in equity in the
subsidiary, which would be wrong.
The best way to do this adjustment of the reference period, is by creating a
special journal type for this kind of adjustments and enter a company journal
on the subsidiary to book the equity. This journal type must be included in the
closing version used in the control tables for E60x.
When you run reports for 0412, you do not use this closing version/journal
type. I.e the journal type should not be included in a Closing version customer
uses for analyzing the result.
This journal type should not be included in the closing version in General
configuration, tab 2. And therefore period (in this example = 0412) is still
locked for the closing version used for analysis.
The company journal should not be currency converted (shall only exist in local
amount). You must not reconsolidate the reference period (0412).
The company journal should not be copied to the next period or year.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
The picture above shows which values are to be entered in the reference
period both first time starting to use Controller and when investing in new
subsidiaries.
Cognos Proprietary Information
18
Investment Adjustments (Control table E600-E603, E760 and
E770)
19
6 How to combine two consolidation methods
6.1
Description of our Solution
It is possible to combine the Investment Adjustment and the Standard model in
one database. This solution only works from version 8.1 MR1 and onwards.
6.2
Automatic Journals and Consolidation Types
In 8.1 MR1 there is a hidden feature that makes it possible to exclude specific
automatic journals from specific consolidation types.
This is done by using a server preference, name: INVADJ_EXCLUDE_X,
parameter: xxxx, y1, y2, y3. (Where X = number, xxxx = automatic journal and
y1/y2/y3 = different consolidation types.) Note that each automatic journal can
be excluded from several consolidation types.
Example:
INVADJ_EXCLUDE_1 = E100,LE
INVADJ_EXCLUDE_2 = E105,LE
INVADJ_EXCLUDE_3 = E125,LE
INVADJ_EXCLUDE_4 = E150,LE
INVADJ_EXCLUDE_5 = E400,LE
INVADJ_EXCLUDE_6 = E800,LE
INVADJ_EXCLUDE_7 = HT01,LE
INVADJ_EXCLUDE_8 = E760,L1
INVADJ_EXCLUDE_9 = E770,L1
NOTE! This is only an example, not a template. This must be adjusted for each
customer.
6.3
Consolidation Types
There must be 2 legal consolidation types, one for the Investment Adjustments
and one for the Standard model. In this example LE is used for the Investment
Adjustments and L1 is used for the Standard model.
In Client 1 no consolidated values will be transferred between the different
consolidation structures LE (investment adjustment) and LI (standard model).
This is important to mention since we haven’t tested any cases where there are
such a transfer.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
6.4
20
Accounts for shares in subsidiaries
There must be 2 accounts for “Shares in Subsidiaries”, one for the Investment
Adjustment (IC-account) and one for the Standard model. This will also require
2 Balance sheet forms. As the Investment adjustments in this case are the
master in the LE-structure, a manual adjustment must be done on the Danish
account for shares to get the numbers correct on the IAS-companies (parent
companies that do not book investment adjustments in their general ledger
systems).
6.5
Analyze
Steps how to analyze the figures for L1 and LE on a high level.
As different group names are used in the different consolidation types, it is
quite easy to keep the 2 structures apart. Example group 1000 is named
LE1000 in the LE-structure and L11000 in the L1-structure.
Cognos Proprietary Information
Investment Adjustments (Control table E600-E603, E760 and
E770)
21
6.5.1 IAS (Standard model) – Consolidation type L1
1. Run the Trial balance for group 1000 in L1 (L11000).
2. Check that the offset account is zero (offset account used in E100-E105).
3. Check that the account “Shares in subsidiaries” is zero (13100).
4. Check that the account “Share capital” only has the value for the share
capital for the mother in the top group.
5. If the numbers are not correct, the reason is most likely errors in the
reported values or errors in the investment elimination template. I.e., the
reported share capital is not equal to the value entered in the investment
elimination template. Use the report Group/Reconcile/Investments to check
that.
6. If there are other differences, use Group/Reports/Journals across for analysis
and check sub group for sub group and the automatic journals.
6.5.2 DK (Danish model) – Consolidation type LE
1. Run the Trial balance for group 1000 in LE (LE1000).
2. Check that the offset account is zero (offset account used in E760 and
E770).
3. Check that the account “Shares in subsidiaries” is zero (13110).
4. Check that the total equity is the same value for the parent company in the
top group as the group itself.
5. If the numbers are not correct, the reason is most likely errors in the
reported value on the investment account. The total equity for the subsidiaries
in the reference period must be equal to the entered data on the investment
account in the parent company in the actual period.
6. If there are other differences, use Group/Reports/Journals across for analysis
and check sub group for sub group and the automatic journals.
6.6
Closing versions and journal types
We recommend that different Closing Versions and Journal Types are used for
the Investment Adjustments and the Standard model.
Cognos Proprietary Information
Fly UP