QAD 2016 Enterprise Edition Training Guides > Financials-Advanced > Consolidation > Hands-On Exercises (US)
  PPT
Hands-On Exercises (US)
General Information
In this exercise, you will create a new consolidation entity (GR-CONS). In GR-CONS, you are going to consolidate the existing consolidation entity, 11NACONS.
Before you can do that, you have to consolidate the operational entities (10USACO, 11CANCO, and 12MEXCO) into 11NACONS.
Exercise 1: Consolidation Setup (US)
In domain 11CAN, create a new consolidation entity GR-CONS using the same address details as 11NACONS.
• Create all consolidation settings in GR-CONS:
• Daybooks for 11NACONS both in transient and primary layer
• Consolidation cycle: 100% 11NACONS
• Check all other settings as required.
• Validate the consolidation setup in 11NACONS
1 Log in to 11CAN domain, entity 11NACONS.
2 In Business Relation Create (36.1.4.3.1), create the following business relation:
 
Field
Data
Name
GRCONS
a On the Address Info tab, right-click and select Insert a New Row. Add the address details:
 
Field
Data
Address
Specify an address.
Zip
Specify the zip.
City
Specify a city.
Country Code
CA
Click OK
 
 
General Tab
 
Domain Restricted
No
Internal Entity
Yes
Intercompany
Yes
Intercompany Code
GRCONS
Customer/Supplier Compensation Allowed
No
b Click Save.
3 In Entity Create (36.1.1.2.1), create the following entity:
 
Field
Data
Entity Code
GR-CONS
Business Relation
GRCONS
Domain
11CAN
 
 
General Tab
 
Reverse P&L Revaluation
Yes
Consolidation Entity
Yes
4 Let the other fields default.
5 Click Save.
6 In User Domain/Entity Access Maintain (36.3.4), specify the following details:
 
Field
Data
User
demo
Domain
11CAN
Entity
GR-CONS
7 Click Search.
In the grid, select the Link and Default Domain fields for the user, domain, and entity code indicated.
8 Click Save.
9 In Role Membership Maintain (36.3.6.6), specify the following:
 
Field
Data
User
demo
Domain
11CAN
Role
SuperUser
Entity
GR-CONS
10 Click Search.
In the grid, select the Link and Default Role fields for the user, domain, and entity code indicated.
11 Click Save.
12 Log out, and log back in as:
User: demo
Password: qad
13 Change workspace to 11CAN-GR-CONS.
14 In Daybook Create (25.8.1.1), create the following daybook:
 
Field
Data
Daybook Code
ConsNAof
Description
ConsNAof
Daybook Type
Consolidation
Layer Code
Primary
Daybook Control
Financial
Daybook Group
If daybook groups are active, choose the default consolidation value from the lookup.
15 Click Save and Create, and create a second daybook with the following details:
 
Field
Data
Daybook Code
ConsNAtr
Description
ConsNAtr
Daybook Type
Consolidation
Layer Code
Trans-Approve
Daybook Control
Financial
Daybook Group
If daybook groups are active, choose the default consolidation value from the lookup.
16 Click Save.
17 In SAF Concept Create (25.3.7.1.1), create the following concept:
 
Field
Data
SAF Concept Code
ConsDef
Description
Consolidation Default
18 Click Save.
19 In SAF Code Create (25.3.7.2.1), create the following value:
 
Field
Data
SAF Code
SCD
Description
SAF Consolidation Default
SAF Concept Code
ConsDef
20 In SAF Structure Create (25.3.7.4.1), create the following structure:
 
Field
Data
Structure Code
Consol
Description
SAF Consolidation
21 Right-click in the grid and select Insert a New Row.
 
Field
Data
SAF Concept
ConsDef
Default SAF Value
SCD
22 Change to workspace 11CAN-11NACONS.
23 In COA Cross-Reference Create (25.3.14.1), enter the following data:
 
Field
Data
COA Cross Reference
GR-CONS-COA
Description
Consolidation cross reference
Active
Yes (Selected)
Type
Separate COA Dimensions
Target Domain
11CAN
Columns
All
24 In the grid, right-click and select Retrieve Defaults.
25 If needed, map GL accounts in source of type tax, open item, and cross company to a standard GL account in the consolidation/target COA.
26 Click Save.
27 Change Workspace to the source entity to be consolidated (GR-CONS).
28 In Consolidation Cycle Create (25.19.1.7), create the following cycle:
 
Field
Data
Entity Code
GR-CONS
Status
Initial
Cur Adj Translation Account
9890
Default Tax Code
0000000
29 Click Default Analysis, and specify the following details:
 
 
 
Field
Data
SAF Tab
 
Structure Code
Consol
SAF Code
ConsolDef
SAF
SCD
 
Cost Center Tab
 
Cost Center Code
Adm
 
Project Tab
 
Project
P01030
30 Click OK.
31 In the consolidation cycle grid, right-click and select Insert a New Row.
 
Field
Data
Percentage
100
Subsidiary Entity
11NACONS
Official Daybook Code
ConsNAof
Transient Daybook Code
ConsNAtr
COA Cross-Reference
GR-CONS-COA
32 Click Save.
33 Change Workspace to the source entity to be consolidated (11NACONS).
34 In Consolidation Period Cross-Ref Maintain (25.19.1.3), create the following period cross-references:
Consolidation Entity: GR-CONS
 
Cons Year
Source Year
Source GL Period
Cons Period
2009
2009
01
1
2009
2009
02
2
2009
2009
03
3
....
 
 
 
2010
2010
01
1
2010
2010
02
2
Etc...
 
 
 
35 Click Save.
36 In 11NACONS, open the consolidation cycle 11NACONS in Consolidation Cycle Modify.
37 Specify Gserv as the default sub-account.
38 Select the SAFs check box.
39 Click Default Analysis, and specify the following details:
 
 
 
Field
Data
SAF Tab
 
Structure Code:
Consol
SAF Code:
ConsolDef
SAF:
SCD
40 Click OK.
41 Save the consolidation cycle.
42 Change Workspace to subsidiary entity 10USACO.
43 In Consolidation Period Cross-Ref Maintain (25.19.1.3), ensure that period cross-references exist for the periods you are consolidating. The consolidation entity is 11NACONS.
44 In Entity GL Period Lock, lock the periods for which you are consolidating (GL calendar year 2013 and GL periods 1 to 6). The periods may already be locked.
45 In COA Cross Reference Modify, open cross-reference 10COACR. Right-click in the grid and select Retrieve Defaults. Save the record.
46 Change Workspace to subsidiary entity 11CANCO, and repeat the previous three steps. Use cross-reference 11COACR.
47 Change Workspace to subsidiary entity 12MEXCO, and repeat the same three steps. Use cross-reference 12COACR.
48 Change Workspace to 10CORPCONS.
49 Open the 10CORPCONS consolidation cycle in Consolidation Cycle Modify.
50 Change the status to Initial and save.
51 Change Workspace to the source entity to be consolidated (GR-CONS).
52 In Consolidation Cycle Modify (25.19.1.8), change the status to Operational, specify the value Gserv in the Sub-Account field, and save.
Exercise 2: Consolidation Run (US)
Run a consolidation in 11NACONS for the period 2013/03 to 2013/06
1 Log in to workspace 11CAN/11NACONS.
2 In Entity GL Period Lock, lock the periods for which you are consolidating (GL calendar year 2013 and GL periods 1 to 5). Leave period 6 open to all GL transaction types. The other periods may already be locked.
3 In Consolidation Create (25.19.2.1), create a trial consolidation in the transient layer:
 
Field
Data
From GL Period
2013/1
To GL Period
2013/6
Source Layer Type
Official
Target Layer Type
Transient
Subsidiary Entities:
 
Field
Data
Percentage
100
Official Daybook
CON-US-O
Entity Code
10USACO
Transient Daybook
CON-US-T
Activation
Yes
 
 
 
Field
Data
Percentage
100
Primary Daybook
CON-CA-O
Entity Code
11CANCO
Transient Daybook
CON-CA-T
Activation
Yes
 
Field
Data
Percentage
100
Primary Daybook
CON-MX-O
Entity Code
12MEXCO
Transient Daybook
CON-MX-T
Activation
Yes
4 Save the consolidation.
5 In Journal Entry View (25.13.1.3), enter Year = 2013, Period = 6 and Daybook Code: CON-US-T.
6 Click Search to review the transactions in 10USACO
7 Repeat the previous two steps with Daybook Code = CON-CA-T to review the transactions in 11CANCO.
8 Repeat the previous two steps with Daybook Code = CON-MX-T to review the transactions in 12MEXCO.
9 Review the transactions.
10 In Consolidation Delete (25.19.2.3), delete the trial consolidations:
 
 
Field
Data
From GL Cal Year
2013
To GL Cal Year
2013
Entity Code
10USACO
11 Click Search. Confirm in the new window.
 
Field
Data
From GL Cal Year
2013
To GL Cal Year
2013
Entity Code
11CANCO
12 Click Search. Confirm in the new window.
 
 
 
Field
Data
From GL Cal Year
2013
To GL Cal Year
2013
Entity Code
12MEXCO
13 Click Search. Confirm in the new window.
14 In Consolidation Create (25.19.2.1), create the consolidation again in the official layer.
 
Field
Data
From GL Period
2013/3
To GL Period
2013/6
Target Layer Type
Official
Source Layer Type
Official
Subsidiary Entities:
 
Field
Data
Percentage
100
Official Daybook
CON-US-O
Entity Code
10USACO
Transient Daybook
CON-US-T
Activation
Yes
 
 
 
Field
Data
Percentage
100
Primary Daybook
CON-CA-O
Entity Code
11CANCO
Transient Daybook
CON-CA-T
Activation
Yes
Field
Data
Percentage
100
Primary Daybook
CON-MX-O
Entity Code
12MEXCO
Transient Daybook
CON-MX-T
Activation
Yes
15 In Entity GL Period Lock, lock GL period 6.
16 In COA Cross Reference Modify, open cross-reference GR-CONS-COA. Right-click in the grid and select Retrieve Defaults. Save the record.
17 Log in to the consolidation entity GR-CONS (change workspace) to create the second consolidation.
18 In Entity GL Period Lock, lock GL periods 1 to 5 for the year 2013. Ensure that period 6 is unlocked and open to all transaction types.
19 Ensure that tax periods exist for the year (Tax Period Create/Tax Period Modify).
20 In Consolidation Create (25.19.2.1), create the following record:
 
Field
Data
From GL Period
2013/3
To GL Period
2013/6
Target Layer Type
Transient
Source Layer Type
Official
Subsidiary Entities:
 
Field
Data
Percentage
100
Primary Daybook
ConsNAof
Entity Code
11NACONS
Transient Daybook
ConsNAtr
Activation
Yes
21 Go to Journal Entry View (25.13.1.3) to review the created transaction.
22 Enter GL calendar year 2013 and daybook code ConsNAtr to search.
23 In Consolidation Delete (25.19.2.3), delete the trial consolidation you just created.
24 In Consolidation Create (25.19.2.1), recreate the same consolidation in the primary layer.
25 In Journal Entry View (25.13.1.3), review the transactions.
Hands-On Exercises (EMEA)
General Information
In this exercise, you will create a new consolidation entity (GR-CONS). In GR-CONS, you are going to consolidate the existing consolidation entity 22EMEACONS.
Before you can do that, you will have to consolidate the operational entities (for EMEA: 20FRACO, 21NLCO, and 22UKCO) into 22EMEACONS.
Exercise 1: Consolidation Setup (EMEA)
In domain 22UK create a new (consolidation) entity GR-CONS (use same address details as 22EMEACONS).
Create all consolidation settings in GR-CONS:
• Daybooks for 22EMEACONS both in transient and primary layer
• Consolidation cycle: 100% 22EMEACONS
• Check all other settings as required.
• Validate the consolidation setup in 22EMEACONS
Steps:
1 Log in to 22UK domain, entity 22EMEACONS.
2 In Business Relation Create (36.1.4.3.1), create the following business relation:
 
Field
Data
Name
GRCONS
a On the Address Info tab, right-click and select Insert a New Row. Add the address details:
 
Field
Data
Address
Address
Zip
Zip
City
City
Country Code
GB
Click OK
 
 
 
General Tab
 
Domain Restricted
No
Internal Entity
Yes
Intercompany
Yes
Intercompany Code
GRCONS
Customer/Supplier Compensation Allowed
No
b Click Save.
3 In Entity Create (36.1.1.2.1), create the following entity:
 
Field
Data
Entity Code
GR-CONS
Business Relation
GRCONS
Domain
22UK
 
General Tab
 
Reverse P&L Revaluation
Yes
Consolidation Entity
Yes
4 Let the other fields default.
5 Click Save.
6 In User Domain/Entity Access Maintain (36.3.4), specify the following details:
 
Field
Data
User
demo
Domain
22UK
Entity
GR-CONS
7 Click Search.
In the grid, select the Link and Default Domain fields for the user, domain, and entity code indicated.
8 Click Save.
9 In Role Membership Maintain (36.3.6.6), specify the following:
 
Field
Data
User
demo
Domain
22UK
Role
SuperUser
Entity
GR-CONS
10 Click Search.
In the grid, select the Link and Default Role fields for the user, domain, and entity code indicated.
11 Click Save.
12 Log out, and log back in as:
User: demo
Password: qad
13 Change workspace to 22UK-GR-CONS.
14 In Daybook Create (25.8.1.1), create the following daybook:
 
Field
Data
Daybook Code
ConsEMof
Description
ConsEMof
Daybook Type
Consolidation
Layer Code
Primary
Daybook Control
Financial
Daybook Group
If daybook groups are active, choose the default consolidation value from the lookup.
15 Click Save and Create, and create a second daybook with the following details:
 
Field
Data
Daybook Code
ConsEMtr
Description
ConsEMtr
Daybook Type
Consolidation
Layer Code
Trans-Approve
Daybook Control
Financial
Daybook Group
If daybook groups are active, choose the default consolidation value from the lookup.
16 Click Save.
17 In SAF Concept Create (25.3.7.1.1), create the following concept:
 
Field
Data
SAF Concept Code
ConsDef
Description
Consolidation Default
18 In SAF Code Create (25.3.7.2.1), create the following value:
 
Field
Data
SAF Code
SCD
Description
SAF Consolidation Default
SAF Concept Code
ConsDef
19 In SAF Structure Create (25.3.7.4.1), create the following structure:
 
Field
Data
Structure Code
Consol
Description
SAF Consolidation
20 Right-click in the grid and select Insert a New Row.
 
Field
Data
SAF Concept
ConsDef
Default SAF Value
SCD
21 Change to workspace 22UK-22EMEACONS.
22 In COA Cross Reference Create (25.3.14.1), enter the following data:
 
Field
Data
COA Cross Reference:
GR-CONS-EMEA-COA
Description
Consolidation cross reference
Active
Yes (Selected)
Type
Separate COA Dimension
Target Domain
22UK
Columns
All
23 In the grid, right-click and select Retrieve Defaults.
24 If needed, map GL accounts in source of type tax, open item, and cross company to a standard GL account in the consolidation/target COA.
25 Click Save.
26 Change Workspace to the source entity to be consolidated (GR-CONS).
27 In Consolidation Cycle Create (25.19.1.7), create the following cycle:
 
Field
Data
Entity Code
GR-CONS
Status
Initial
Rounding Diff Acc
9890
Default Tax Code
0000000
Default Sub-Account
Gserv
28 Click Default Analysis, and specify the following details:
 
Field
Data
SAF Tab
 
Structure Code
Consol
SAF Code
ConsolDef
SAF
SCD
 
Cost Center Tab
 
Cost Center Code
Adm
 
Project Tab
 
Project
P01030
29 In the consolidation cycle grid, right-click and select Insert a New Row
 
Field
Data
Percentage
100
Subsidiary Entity
22EMEACONS
Primary Daybook Code
ConsEMof
Transient Daybook Code
ConsEMtr
COA Cross-Reference
GR-CONS-EMEA-COA
30 Click Save.
31 Change Workspace to the source entity to be consolidated (22EMEACONS).
32 In Consolidation Period Cross-Ref Maintain (25.19.1.3), create the following period cross-references:
Consolidation Entity: GR-CONS
 
Cons Year
Source Year
Source GL Period
Cons Period
2009
2009
01
1
2009
2009
02
2
2009
2009
03
3
....
 
 
 
2010
2010
01
1
2010
2010
02
2
Etc...
 
 
 
33 Click Save.
34 In 22EMEACONS, open the consolidation cycle 22EMEACONS in Consolidation Cycle Modify.
35 Click Default Analysis, and specify the following details:
 
 
 
Field
Data
SAF Tab
 
Structure Code
Consol
SAF Code
ConsolDef
SAF
SCD
36 Click OK.
37 Save the consolidation cycle.
38 Change Workspace to subsidiary entity 20FRACO.
39 In Consolidation Period Cross-Ref Maintain (25.19.1.3), ensure that period cross-references exist for the periods you are consolidating. The consolidation entity is 22EMEACONS.
40 In Entity GL Period Lock, lock the periods for which you are consolidating (GL calendar year 2013 and GL periods 1 to 6). The periods may already be locked.
41 In COA Cross Reference Modify, open cross reference FR-EMEA. Right-click in the grid and select Retrieve Defaults to update the COA list.
42 In the header, change the Columns field to GL Account. Scroll to the end of the list in the grid and map any unmapped accounts. Save your changes.
43 Change Workspace to subsidiary entity 21NLCO.
44 Repeat the three steps taken for 20FRACO. In COA Cross Reference Modify, use cross-reference 21COACR.
45 Change Workspace to subsidiary entity 22UKCO.
46 Repeat the three steps taken for 20FRACO and 21NLCO. In COA Cross Reference Modify, use cross-reference 22COACR.
47 Change Workspace to 10CORPCONS.
48 Open the 10CORPCONS consolidation cycle in Consolidation Cycle Modify.
49 Delete the row that has 22EMEACONS.
50 Save your changes.
51 Change Workspace to the source entity to be consolidated (GR-CONS).
52 In Consolidation Cycle Modify (25.19.1.8), change the status to Operational, specify the value Gserv in the Sub-Account field.
53 Save your changes.
Exercise 2: Consolidation Run (EMEA)
Run a consolidation in 22EMEACONS to consolidate 20FRACO, 21NLCO, and 22UKCO into 22EMEACONS. The consolidation is run for the period 2013/03 to 2013/06.
• Create the consolidation in transient layer first and verify the postings.
• Delete the transient posting.
• Re-do the consolidation in the primary layer.
• Repeat the process in GR-CONS to consolidate 22EMEACONS into GR-CONS.
• Ensure that there are transactions in the periods to consolidate.
1 Log in to workspace 22UK/22EMEACONS.
2 In Entity GL Period Lock, lock the periods for which you are consolidating (GL calendar year 2013 and GL periods 1 to 5). Leave period 6 open to all GL transaction types. The other periods may already be locked.
3 In Consolidation Create (25.19.2.1), create a trial consolidation in the transient layer:
 
Field
Data
From GL Period
2013/1
To GL Period
2013/6
Target Layer Type
Transient
Source Layer Type
Official
Subsidiary Entities:
 
Field
Data
Percentage
100
Primary Daybook
CON-FR-O
Entity Code
20FRACO
Transient Daybook
CON-FR-T
Activation
Yes
 
Field
Data
Percentage
100
Primary Daybook
CON-NL-O
Entity Code
21NLCO
Transient Daybook
CON-NL-T
Activation
Yes
 
Field
Data
Percentage
100
Primary Daybook
CON-UK-O
Entity Code
22UKCO
Transient Daybook
CON-UK-T
Activation
Yes
4 Save the consolidation.
 
5 In Journal Entry View (25.13.1.3), enter Year = 2013, Period = 6 and Daybook Code: CON-FR-T.
6 Click Search to review the transaction in 22FRACO.
7 Repeat the previous steps with Daybook Code = CON-NL-T to review the transaction in 21NLCO.
8 Repeat the previous steps with Daybook Code = CON-UK-T to review the transaction in 22UKCO.
9 Review the transactions.
10 In Consolidation Delete (25.19.2.3), delete the trial consolidations:
 
Field
Data
From GL Cal Year
2013
To GL Cal Year
2013
Entity Code
20FRACO
 
 
Field
Data
From GL Cal Year
2013
To GL Cal Year
2013
Entity Code
21NLCO
11 Click Search. Confirm in the new window.
 
Field
Data
From GL Cal Year:
2013
To GL Cal Year:
2013
Entity Code:
22UKCO
12 Click Search. Confirm in the new window.
13 In Consolidation Create (25.19.2.1), create the consolidation—again in the official layer.
 
Field
Data
From GL Period
2013/3
To GL Period
2013/6
Target Layer Type
Official
Source Layer Type
Official
Subsidiary Entities:
 
Field
Data
Percentage
100
Primary Daybook
CON-FR-O
Entity Code
22FRCO
Transient Daybook
CON-FR-T
Activation
Yes
 
Field
Data
Percentage
100
Primary Daybook
CON-NL-O
Entity Code
21NLCO
Transient Daybook
CON-NL-T
Activation
Yes
 
Field
Data
Percentage
100
Primary Daybook
CON-UK-O
Entity Code
22UKCO
Transient Daybook
CON-UK-T
Activation
Yes
14 Save the consolidation.
15 Log in to the consolidation entity GR-CONS (change workspace) to create the second consolidation.
16 In Entity GL Period Lock, lock GL periods 3 to 5 for the year 2013. Ensure that period 6 is unlocked and open to all transaction types.
17 In Consolidation Create (25.19.2.1), create the following:
 
Field
Data
From GL Period
2013/3
To GL Period
2013/6
Target Layer Type
Transient
Source Layer Type
Official
Subsidiary Entities:
 
Field
Data
Percentage
100
Primary Daybook
ConsEMof
Entity Code
22EMEACONS
Transient Daybook
ConsEMtr
Activation
Yes
18 Go to Journal Entry View (25.13.1.3) to review the created transaction.
19 Enter GL calendar year 2013 and daybook code ConsEMtr to search.
20 In Consolidation Delete (25.19.2.3), delete the trial consolidation you just created.
21 In Consolidation Create (25.19.2.1), recreate the same consolidation in the official layer.
22 In Journal Entry View (25.13.1.3), review the transactions.