In this section we 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.

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:

Adding the natural accounts from an ODBC source

Next, we are loading the natural accounts from an ODBC source. Using the SQL query statement:

Select * from [vw.dim.coa]

The query lists the natural accounts and their direct parent in the Balance Sheet rollup loaded in the previous section. The query includes the attribute information for the natural accounts.

Unwind the Business CoA rollup.

To allow for changes in the rollup we need to unwind the rollup before rebuilding the rollup for the data sources. 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, Load Operational Chart of Account Hierarch, change the Rebuild Option parameter to be Unwind. The Unwind Consolidation parameter should be the top consolidation of the Business CoA in the Operational CoA. In this case 9999 - Total - All Accounts Total.

Subsequent automation steps should use the Rebuild Option parameter value of Add Component Only.

Fixed Accounts

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, it is essential to map the compulsory Fixed Accounts 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.

Update Flags

Once the Fixed Accounts have been mapped the account flags can be set.

Image Modified

Syncing the FIN OCoA dimension

Finally, we have built the Operational CoA in the staging 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.

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 Operational CoA.

Repeat the same steps to add the following statistical accounts.

• Quantity Consolidated

• Quantity Unconsolidated

• Memory Shipped in GB Consolidated

• Memory Shipped in GB Unconsolidated

Click 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:

More Information