In this section we are going to build the Operational Chart of Accounts dimensions. All changes are made into staging dimension and synced the FIN OCoA dimension.
Menu: Apliqo Settings / Chart of Accounts
Building the OCoA Rollup from File
In this example we are going to load the OCoA sample file into the Chart of Accounts.
...
Add the Natural Accounts from ODBC Source
In this example we are going to load the natural accounts from an ODBC source. focus on defining the FIN OCoA dimension using the Business Chart of Accounts. The Business CoA is the chart of account the Apliqo FPM client uses for financial reporting. The Business CoA should include the all the Balance Sheet and Profit and Loss accounts. So that the sum of these account balances to zero. Using the Business CoA we can derive an Indirect Cash Flow rollup. To enable standard reporting and calculations within Apliqo FPM, the Business CoA is mapped to Fixed Accounts and KPIs accounts. Some Fixed Account must be mapped before the FIN OCoA dimension can be build.
The FIN OCoA dimension can have multiple account rollups. For example, the case business using a local CoA and needing to report using a Group CoA. Not all rollups have to include all accounts. The business my combine section of their chart for reporting totals and KPIs that do not exist in their Business CoA. However, at least one rollup must contain the all the natural accounts. This in known as the Management Trial Balance (C3_MGMTTB). Accounts missing from the Management Trial Balance are listed in the Unmapped Accounts popup.
Defining and changing the FIN OCoA dimension is done in a staging area. Once all the changes have been made in the staging area, the actual FIN OCoA dimension is synced with the staging area.
The following topic will be covered in this section.
| Table of Contents | ||
|---|---|---|
|
You can access the Chart of Accounts setup through the page: Administration > Chart of Accounts.
Automating the Operational CoA
Defining the Operation CoA rollup
The Balance Sheet rollup from level 1 to Total Accounts can be in the Balance Sheet.xlsx file. Here is the sample file:
| View file | ||
|---|---|---|
|
...
Adding the natural accounts from an ODBC source
Next, we are loading the natural accounts from an ODBC source. Using the SQL query statement:
| Code Block |
|---|
Select * from [vw.dim.coa] |
The query lists the natural accounts and their direct parent element in the in the OCoA Balance Sheet rollup loaded in the previous section. The query included includes the attribute information for the natural accounts.
Using the SQL query statement:
...
.
...
...
...
Unwind the Business CoA rollup.
The changes to the rollup can be review in the Operational CoA page.
...
Unwind the rollup
To allow for changes in the rollup we need to unwind the rollup before rebuilding the rollup for the data sourcesources. In the above example we are updating the rolling in two automation steps. Each step can unwind the rollup however we only need to unwind the existing roll once. This is where order of the automation steps is important.
In the first step that builds the rollup , Load Operational Chart of Account Hierarch, change the Rebuild Option should parameter to be set to Unwind and . The Unwind Consolidation parameter should be the top element consolidation of the rollup that is being builtthe Business CoA in the Operational CoA. In this case 9999 - Total - All Accounts Total.
...
Subsequent Automation step that rebuild the rollup automation steps should use the Rebuild Option parameter value of Add Component Only.
...
Fixed Accounts
We have only load the Operational Chart of Account to the Chart of Account staging area. Before The Operational CoA includes Fixed Accounts. These accounts serve various purposes, including reporting, planning, and KPI calculations. However, before we can build the FIN OCoA dimension we need , it is essential to map some the compulsory Fixed Accounts .There are a number of fixed accounts that can be mapped to your Opearational Chart of Account. They are used for reporting, planning and KPI calcuations. Before to the Business CoA. Currently, we have only loaded the Business Chart of Accounts into the Operational Chart of Account staging area. Once the Fixed Accounts are mapped, we can build the FIN OCoA dimension the compulsory Fixed Accounts must be mapped to the Operational Chart of Account.
...
.
...
Update Flags
Once the Fixed Accounts have been mapped the Automation account flags can be set to Update Flags in the Operational Chart of Accounts.
...
Syncing the FIN OCoA dimension
Syncing the dimension is when we take what Finally, we have build built the Operational CoA in the staging dimension and apply to the actual area.
Add an automation step to sync the FIN OCoA dimension.
...
Build the Operational Chart of Account (FIN OCoA) dimension.
Click on the [Process CoA Update] button to build the FIN OCoA dimension.
...
All Active Steps should be selected. Click on [Process CoA Update] button to execute.
Manually adding Accounts
...
Syncing the Accounts
Whenever the Operational CoA does not agree with the FIN OCoA dimension a warning icon will appear on the Operational CoA tab. Click the Process CoA Update button to sync the FIN OCoA dimension with the staging area.
...
Adding accounts
Accounts and Consolidations can be manually added to part of the Operational CoA that is not managed an Automation step. In this example, are going to add statistical accounts to the Chart of AccountsOperational CoA.
...
Repeat the process and same steps to add the following statistical accounts.
Quantity Consolidated
Quantity Unconsolidated
Memory Shipped in GB Consolidated
Memory Shipped in GB Unconsolidated
Click [Sync Dimension] Process CoA Update button to update the FIN OCoA dimension.
Moving accounts (Map).
The statistical accounts, Quantity Consolidated and Quantity Unconsolidated can be summed up into a consolidated element called Quantity using the Map function.
...
Remap the Memory Shipped in GB Consolidated and Memory Shipped in GB Unconsolidated statistical accounts to a new consolidation Memory Shipped under the Statistical Accounts rollup.
Adding the Cash Flow rollup
Now that we have built the Operational Chart of Account we can add the Cash Flow rollup. Here is the source data file:
| View file | ||
|---|---|---|
|
...
More Information
...