...

Guidelines to using Consolidation with Steps or Status - IBM Cognos

by user

on
Category: Documents
3

views

Report

Comments

Transcript

Guidelines to using Consolidation with Steps or Status - IBM Cognos
Guideline
Guidelines to using Consolidation
with Steps or Status - IBM Cognos
Controller 8X
Product(s): IBM Cognos Controller 8X
Area of Interest: Financial Management
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
2
Copyright and Trademarks
Licensed Materials - Property of IBM.
© Copyright IBM Corp. 2009
IBM, the IBM logo, and Cognos are trademarks or registered trademarks of
International Business Machines Corp., registered in many jurisdictions worldwide.
Other product and service names might be trademarks of IBM or other companies. A
current list of IBM trademarks is available on the Web at
http://www.ibm.com/legal/copytrade.shtml
While every attempt has been made to ensure that the information in this document
is accurate and complete, some typographical errors or technical inaccuracies may
exist. IBM does not accept responsibility for any kind of loss resulting from the use of
information contained in this document. The information contained in this document
is subject to change without notice.
This document is maintained by the Best Practices, Product and Technology team.
You can send comments, suggestions, and additions to [email protected]
Adobe, the Adobe logo, PostScript, and the PostScript logo are either registered
trademarks or trademarks of Adobe Systems Incorporated in the United States,
and/or other countries.
Intel, Intel logo, Intel Inside, Intel Inside logo, Intel Centrino, Intel Centrino logo,
Celeron, Intel Xeon, Intel SpeedStep, Itanium, and Pentium are trademarks or
registered trademarks of Intel Corporation or its subsidiaries in the United States and
other countries.
Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft
Corporation in the United States, other countries, or both.
Java and all Java-based trademarks and logos are trademarks of Sun Microsystems,
Inc. in the United States, other countries, or both.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ................................................................................................................ 4
APPLICABILITY ......................................................................................................... 4
EXCLUSIONS AND EXCEPTIONS ..................................................................................... 4
2
CONSOLIDATION BY STEPS.......................................................................... 4
2.1
2.2
2.3
2.4
2.5
DEFINITION ............................................................................................................ 4
STEPS TO CONSOLIDATE ............................................................................................. 4
RUN AUTOMATIC JOURNALS – INTERCOMPANY BALANCES.................................................. 12
RUN AUTOMATIC JOURNALS – INTERCOMPANY PROFIT ..................................................... 17
RUN AUTOMATIC JOURNALS – ACQUISITION CALCULATION ................................................ 22
3
CONSOLIDATION WITH STATUS ................................................................ 25
3.1
3.2
DEFINITION .......................................................................................................... 25
TROUBLESHOOTING ................................................................................................. 26
4
CONSOLIDATION BY STEPS OR BY STATUS ............................................... 27
4.1
OTHER ITEMS TO CONSIDER ...................................................................................... 27
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
4
1 Introduction
1.1
Purpose
This document provides guidelines to running consolidation by steps and
status or a combination of both.
1.2
Applicability
Controller 8.3 and higher.
1.3
Exclusions and Exceptions
There are no known exceptions and exclusions at the time this document was
created.
2 Consolidation by Steps
2.1
Definition
Consolidate by steps means that the steps in the consolidation process (that
is, currency conversion, elimination of intercompany balances, elimination of
internal profit, legal unit consolidation, elimination of acquisitions and
consolidation) must be started as separate functions. You consolidate all data
for the companies belonging to the specified group. The amounts are
summed to a group total, which is stored on the group code. There is no
reconciliation process and status handling involved when you are
consolidating by steps.
2.2
Steps to consolidate
If you run consolidation by steps then you must complete each step before
the next step is run.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
•
•
•
•
5
Run Currency Conversion
Run Automatic Journals – Intercompany Balances
Run Automatic Journals – Intercompany Profit
Run Automatic Journals – Acquisition Calculations
Consolidation
Currency conversion is almost always performed from local currency to all
currencies included in the consolidation structure. If the company is
connected to more than one consolidation structure and the groups have
different currencies, the company must be currency converted several times.
It should be noted there is an order which currency calculated. Steps
completed as part of the currency translation:
1. Currency conversion codes B, M, K, L and N are calculated for all
accounts using these.
2. Accounts with currency conversion code D are calculated.
3. Opening balances, currency conversion codes C and I, are
calculated.
4. Historical rates, currency conversion codes E, F, and G are
calculated.
5. The currency conversion differences, currency conversion codes O
and P are calculated.
6. All accounts are summed to summation accounts.
7. Currency conversion code A is calculated.
8. All accounts are summed to summation accounts.
9. Currency conversion code A2 is calculated.
10. All accounts are summed to summation accounts.
11. Values are copied from accounts with reconciliation rules to all
accounts with the currency conversion codes U, V, X and Z.
12. All accounts are summed to summation accounts.
13. Currency conversion code A3 is calculated.
14. All accounts are summed to summation accounts.
15. The final currency conversion difference, the balance item, is
calculated and booked using the rules in the general configuration
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
Steps to running currency conversion
1. Select the correct actuality
2. Select the from and to period
3. Select OB Actuality
4. Select OB Period
5. Select FROM Currency Type
6. Select TO Currency Type
7. Select either group or company
8. Select Consolidation Type
9. Select the group you want to convert, it can be top group or a
subgroup
Troubleshooting:
You should review the currency translation after it has been completed. The
currency translation is placed in batch and the run. You should review the
batch log by clicking Maintain > Batch Queue > Manage and then
opening to review the log.
There are different areas that you can focus on that may cause issues with
currency not working:
6
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
7
Account structures – if you have not identified the proper conversion
code and/or account type then this could cause issues (see the
currency rates below). You may also want to review your movement
account to ensure the opening, closing and movements are calculating
properly (see historical rates for additional information)
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
Currency Register – If you see the calculation not working then it is
always good to check the currency register. You can check the
currency register by clicking Group > Data Entry > Currency
Rates. If there are fields that do not have rates, then you are
required to update these rates and then re-run the currency
conversion.
8
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
9
Historical Rates – You may want to check the movement accounts to
ensure any accounts with E, F, G have the historical rates set up in
the historical rate register. You can access the historical rates register
by clicking Group > Data Entry > Historical Rates. After the
screen is open then you need to select the appropriate company,
form, etc. You may want to also check that the copying/opening
balances are working correctly by reviewing the opening balance of
the movement account.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
You may also want to review the General Configuration rules if any
exist on the Maintain > General Configuration > Conversion
tab.
10
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
11
•
You may also want to review the Reconcile 2 tab where the currency
translation differences are handled.
•
After you make the changes to currency rates, accounts, general
configuration then it is a good idea to do a database optimization.
This can be done by clicking Maintain > Database > Optimize.
You must be in single mode and no other users can be on the system.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
2.3
12
Run Automatic Journals – Intercompany Balances
You can use this function to perform automatic elimination of the amounts
that are also contained in the reconciliation report for intercompany balances.
The elimination takes place on the basis of the defined control tables.
Journals are created automatically. For legal consolidation types, you can
choose to book the elimination as group journals or as company journals in
Consolidation Types - Define. For management consolidation types, the
eliminations will be booked as company journals. Automatic group journals
are booked in the group currency on the adjustment company. If you work
with the consolidation model that was the default before the 8.1 release, the
automatic group journals are booked in the group currency on the group
company. Company journals are booked in the group currency on the
relevant company.
You can run the intercompany report to show differences between the
companies as part of the month-end close process. The report is available by
clicking Companies > Reports > Intercompany Balances or Group >
Reconcile > Intercompany Balances.
Troubleshooting:
If you encounter issues when you run the automatic journals-intercompany
balances, then you want to look at the following:
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
Check to see that intercompany table is active. This can be done by
clicking Maintain > Configuration > Automatic Journals >
Define. The required journal is IC (AJ35).
•
Review the rules of the intercompany control table to ensure the
proper accounts have been selected. This can be done by clicking
Maintain > Configuration > Automatic Journals > Control
Tables > Intercompany Balances.
13
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
14
NOTE: You may have multiple control tables depending on what you are
eliminating. It is important to review each table to ensure that it is working
correctly.
•
Review the Advanced tab on the Control Tables – Intercompany
Balances for more options around how the intercompany is to be
handled.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
15
Check configuration rules, especially on how to handle intercompany,
i.e. template. This can be done by clicking Maintain >
Configuration > General2.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
o
o
•
16
Use IC Account Template - Use the intercompany account
template to determine which counter companies are valid for a
specific intercompany account.
Allow Overriding the IC Account Template in Data
Entry - Overrides the intercompany account template in data
entry, that is, it is possible to enter data on counter companies
not included in the template.
For most cases the intercompany details should have been provided in
the additional data load or a second load. There should be no errors
around the incorrect intercompany counter company, because this would
be identified at the initial data importing phase.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
2.4
17
Run Automatic Journals – Intercompany Profit
You can use this function if you want to perform an automatic elimination of
the calculated intercompany profit presented in the reconciliation report
described above. Eliminations are only performed based on control tables set
as Active. The elimination takes place on the basis of the defined control
tables. Automatic journals are posted on:
•
•
The selling company - where the operating result is affected with the
period’s change of the intercompany profit reserve.
The buying company - where the internal inventory is adjusted with the
closing balance of the intercompany profit reserve.
All amounts are posted in the group currency. If you want to create a group
journal for intercompany profit elimination manually, you can create this
journal using the Group > Data Entry > Group Journals menu option.
If you want to check which automatic journals have been created, you can
print a journal report from the Group > Reports > Journals menu.
Click Show Valid Choices next to a text box to open a list of all available
items. Select the relevant item and click OK. The selected item appears in the
text box. To select several items, press the Ctrl key and click all codes you
want to select. When you click OK, the characters >> are displayed in the
text box to indicate that more than one item was selected.
Troubleshooting
• Confirm that the journals are active. This can be done by clicking
Maintain > Configuration > Automatic Journals Define. The
Automatic Journal should be 36.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
18
Confirm that the accounts are properly defined. This can be done by
clicking Maintain > Configuration > Automatic Journals > Control
Tables > Intercompany Profit.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
19
-You may be required to check each journal number to ensure it is correct.
•
Check the Control Tables – Intercompany Profit > Advanced tab to
see if there are any issues.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
20
Review the pricing table to ensure the rates are correct. You are allowed
up to 99 tables for gross margin percentage. The entry can be based on a
specific group.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
21
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
2.5
22
Confirm that various tables are marked active. You can also define when
the selected table of margins should be used. The available margin tables
are defined on the Maintain Tables tab. There are different settings on
how to use the margin tables for default margins and adjustment
margins; they are used regardless of whether the selling company has
reported an intercompany profit margin or not. They are used if the
company has not reported any profit margin. This only applies to default
margins. They are not used at all. The default margin may be used
independently of the adjustment margin and vice versa.
Run Automatic Journals – Acquisition calculation
The acquisition calculations are stored as automatic journals on each
company or group, in the period database. You define the number of the
journal in the control table. For each type of acquisition calculation there is a
specific automatic journal type. You can group the journal types into different
contribution versions to be able to generate reports for analysis purposes.
Troubleshooting:
• Confirm that the automatic journals have been activated.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
23
The automatic journals to be activated are determined on the method used to
eliminate the investment (Latin/Dutch/Standard). There is additional
documentation available around the setting up of the different methods to
handling the investment (see additional references at the end of this
document).
•
Check the investment register. This can be done by clicking Group >
Data Entry > Shareholders and Investments in Group Companies.
The areas to focus on are actual investment amount, companies and the
accounts.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
24
•
Review the automatic journal control tables to ensure all the accounts are
mapped correctly. This will also be dependent on the method used to
eliminate the investments. There is additional documentation available on
Latin/Standard/Dutch Method.
•
Check the general configuration by clicking Maintain > Configuration
> General 2.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
3 Consolidation with Status
3.1
Definition
You can use this function to consolidate values of the group. Consolidation
takes place from the lowest level.
25
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
26
Click Maintain > Status > Active to enable the status function. Then select
Group/Consolidate With Status. You can now enter information such as:
•
•
•
•
•
•
Actuality and period range
Actuality for the opening balance period
Consolidation type and group
Conversion method
Reconciliation and Status Options
Force Consolidation Regardless of Status
This option is not available if you run the consolidation model that was
default before the 8.1 version.
Schedule Jobs
The period for opening balance cannot be changed, since the selected period
and actuality is the determining factor.
Before you use Consolidate with Status, you have to create form sets and
update Maintain > Submission > Define for linking form sets and periods,
actualities and submissions so that only the reconciliations required for the
particular period are checked.
It is also recommended that you enter all sub-units, subsidiaries and group
balances relating to the previous year during the first year you consolidate
with Cognos 8 Controller. This means that you avoid differences and obtain a
correct investment rate for the currency conversion for foreign companies.
3.2
Troubleshooting
• You need to be in single mode to run the consolidation with status,
which means no one can be on the system, including using Excel addin link.
• Moving into a new Period, all of a sudden Elimination Journals are not
being executed between some companies. Consolidate with Status
(even with Ignore Invalid Reconciliations and Missing Data and
Force Consolidation Regardless of Status both selected).
Similarly, attempts to run Consolidation by Steps on Sub-Groups at
any level in the main Group may fail.
Error Message:
Only one top group is supported for each consolidation type.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
27
Solution:
Only one top group is supported for each consolidation type. When all
other top level Groups were deleted, the Eliminations for the one
remaining top level group worked again.
Steps:
1. Click Maintain > Company Structure > Define. Collapse all
groups using the minus signs at the top of each tree.
2. Scroll down the window and see if there are gaps between top
level groups (any at the bottom listed below Not Connected To
Consolidation Type can be ignored).
3. If there are gaps, identify which Groups can be deleted and keep
the relevant Top Level one.
4. Run Consolidate by Status for the relevant remaining Top Level
Group or a Group underneath it for which Intercompany
Transactions exist.
5. Select the Ignore Invalid Reconciliations and Missing Data
and Force Consolidation Regardless of Status check boxes to
test.
6. Follow the same checking of data around the automatic journals
and currency translation that is referred in the section
Consolidation by Steps.
4 Consolidation by Steps or by status
4.1
Other items to consider
• Status ensures that the consolidation steps are run in the correct
order (and starting from the bottom of the group and working up)
• There can be some confusion about submissions and locking of data.
If you lock sub 1 and then use a gl import spec to import data into
sub 2 then the accounts in sub 1 must not also be in forms in sub 2.
• The best practice is to run the consolidate with status, because you
want the system to do the work for you. An issue that can happen
for Controller is that for some reason it does not understand that a
new currency conversion is necessary for a company. This is usually
because of entered historical rates but it can also occur in other cases.
If that is the cast, you have to turn off status, run the currency
conversion for the company/companies and then turn on the status
and consolidate as usual.
Guidelines to using Consolidation with Steps or Status - IBM Cognos
Controller 8X
•
28
Consolidate by steps can be difficult because you have to consolidate
the groups in the right order.
Fly UP