Training: Loading the Trial Balance
- Adam Davis
- Cristian Blana
- Andreas Alex
Menu: Administration / Data Integration / Data Processing / Automation
In our training thus far, we’ve established the Company, Cost Center, Customer, and Account dimensions along with their rollups. These dimensions serve as the metadata for the Training Apliqo FPM model. We are ready to load the Trial Balance from the external data source to the FIN General Ledger TB cube.
The Trail Balance represents the monthly movements of the natural accounts expressed as in debit and credits. Where the sum of the debits and credits in the Trial Balance always equals zero. Debit and Credit can be expressed in the data source in two different ways. Depending on the data source may influence how the Trial Balance is loaded.
As positive amounts in separate columns for debit and credit movements. The sum the debit column equals the sum of the credit column. In this case the debits can be loaded to the DR - Debit element and the credits can be loaded to the CR - Credit element in the FIN Consolidation Value Measure dimension. The CR - Credit element is given a weight of -1 in the TB - Trial Balance consolidation.
In a single column where, positive movements are debits and negative movements are credits. In This case the movements can be accumulated against the TB Load element which is a member of the TB - Trial Balance consolidation.
After the monthly Trial Balance is loaded for the company, the total of all accounts and the total of all business segments is check that it equals zero. Once the Trial Balance Check is passed, the trial balance is transformed for management reporting in the FIN General Ledger cube.
In the FIN General Ledger cube the sign of account changes for liability, revenue and expense accounts. In accounting movement in liability and revenue accounts are expressed in credits. A negative movement is an increase in these types of accounts. In Apliqo FPM we flip the sign so that an increase in these accounts is a positive movement. For expense account the accounting movement is a debit which is positive. In the FIN General Ledger cube the sign is flipped to a negative. Theses changes are driven by the TB_Mult attribute in the FIN OCoA dimension. To make up for this change of signage in the Balance Sheet a weight of -1 give to the Total Liabilities and Equity account. When reporting the Balance Sheet, all numbers are positive. When reporting the Profit or Loss, revenues are positive, and expenses are negative. An increase in revenues or decrease in expenses is an increase in net profit. This is considered intuitive to non-accountants.
Creating a configuration to load the trial balance.
For this training we are using the SQL-Lite database to load the trial balance. An ODBC connection (ApliqoFPM_ODS) has been setup to connect to the SQL-Lite database. A turbo Integrator (TI) process: }APQ.C3.FIN.Cub.GeneralLedgerTB.Update.From.ODS_Demo has been written to load the trial balance the Apliqo_Demo sqlite database.
Running the Training - Load TB a configuration
In this section we are going run the configuration to load the Trial Balance for the 2022 financial year.
Exercise: Run the Training - Load TB configuration process for period 2023-Jan to 2023-Jun.
Checking the Trial Balance
Select the Actuals tab on the Data Processing page.
Trial Balance Report
The Total GL Account row for the Consolidation Measure TB should be zero. TB means the account movement have been loaded in Debits (+) and Credits (-). The 9999 Total - All Account TB will not balance here because the signs movements of the liabilities, revenue and expenses accounts have not changed. There is no data in the Process Data (GL) as we have not run the GL Update process.
TB Check popup.
The TB Check is an overview of the trial balance check for all companies for the financial year. If company fails the trial balance check, a warning message will show the number of months that failed. Click on the company to view the discrepancies for that company. You have the following resolve the discrepancies.
You can resolve the discrepancy in the data source.
You can accept the discrepancy for the month by selecting your username in the User Pass column.
You can set a Tolerance for the Trial Balance Check. If the difference is between the +- of the tolerance the amount is accepted. This is a global setting that applies to all companies.
Click on Check Again to check the Trial Balance again.
Account Mapping
New accounts can be added by the custom process that loads the Trial Balance. Here is a snippet of code to add an account to the rollup.
Â
If( DIMIX( 'FIN OCoA', sElem ) = 0 & cCoaUnmapped @<> '');
## Add Element to FIN OCOA Staging Dimension
ExecuteProcess('}APQ.C3.Dim.CoAMap.Element.Create',
'pLogOutput', '1',
'pCoAID', 'FIN OCoA',
'pEle', [account code],
'pType', 'N',
'pPar', 'Total GL Accounts',
'pWeight', '1',
'pKPICoAID', 'FIN OCoA'
);
CellPutS( [account description], '}APQ C3 FIN CoA Map', 'FIN OCoA', [account code], 'Description', 'AttrValue');
## Add Element to FIN OCoA actual dimension
DimensionElementInsertDirect( 'FIN OCoA', '', [account code], 'N' );
DimensionElementComponentAddDirect(sDim, 'Total GL Accounts', [account code], 1 );
AttrPutS( [account description], sDim, [account code], 'Description' );
AttrPutS([account code] | ' - ' | [account description], sDim, [account code], 'Code and Description' );
EndIf;Any new accounts can be mapped into the Management Balance Sheet rollup in the Unmapped Accounts popup.
On the left select the account that is to be mapped. On the right, select the node in the chart of accounts the selected account is to be mapped to. Click on the > icon to add the selected accounts to the account node. Click on Sync to commit the changes to the FIN OCoA dimension.
More Information