Consolidation

Consolidation of Different Currencies of Trial Balance

Consol_YTD_TB

This command can be used to calculate monthly movement and relevant foreign currency translation based on your monthly year-to-date trial balance. Account balance and analysis account balance tables can be built for ledger(s) of current workspace automatically to support your preparation of consolidated financial statements with compliance of the standard  "IAS 21 - The Effects of Changes in Foreign Exchange Rates".

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to use the commands "Import_Master" and "Import_Transaction". In addition, an understanding of the standard "IAS 21 - The Effects of Changes in Foreign Exchange Rates" can empower user to verify the processing results.

Command with Parameters

e.g. Consol_YTD_TB "C:\LB\Trial Balance.xlsx" "Data"  "C:\LB\Setting.xlsx"

 

Note: Prior to use this command, users shall use the command "Import_Master" to configure Ledger, Chart of Account, Analysis Category and Currency.

Source File Path

Specify full file path of an Excel Workbook which contains a month-end trial balance
e.g. C:\LB\Trial Balance.xlsx

Source Worksheet Name

Specify worksheet name of the source file which contains the trial balance
e.g. Data

Setting Worksheet Name

Specify full file path of an Excel Workbook which contains a worksheet "Parameter"

e.g. C:\LBSetting.xlsx

Note: Based on the above setting, the imported trial balance shall be included the column of "Analysis 1" "Analysis 2" to support relevant data amendment. In addition, users shall also learn how to implement analysis path for better performance when the number of analysis category is more than one type. Other default settings per row 1,2,4 are to support some of the exceptional requirements.

Relevant Case Study

Download the file IAS21.zip

Reconciliation

Comparison and Reconciliation of Data Set

Compare_and_Reconcile_Spreadsheet

This command can be used to output detail data reconciliation results based on two reports configured by query formula. It supports reconciliation of drill down results for every pair of LedgerBase Query formulas lq() if calculated balances of these lq() pairs are not identical.

Prerequisite Knowledge

Before learning how to use this command, users shall understand how to create a ledger with imported transactions and configure report using lq() formula.

Command with Parameters
Compare_and_Reconcile_Spreadsheet “C:\LB\Reconciliation\TB01.xlsx” “C:\LB\Reconciliation\TB02.xlsx” “Trial Balance” “C:\LB\Reconciliaton\Report.xlsx” “191,191,191” “vou” “des(All)vou(All)”

Source File Path 1

Specify full file path of first Excel Workbook which contains lq() formulas
e.g. C:\LB\Reconciliation\TB01.xlsx

Source File Path 2

Specify full file path of second Excel Workbook which contains lq() formulas
e.g. C:\LB\Reconciliation\TB02.xlsx

Worksheet Name

Specify a common worksheet name for both files which contain lq() formulas
e.g. Trial Balance

Output File Path

Specify full file path of an Excel Workbook to output reconciliation results
e.g. C:\LB\Reconciliaton\Report.xlsx

Font Color

Specify R,G,B, color code for specific area of the reconciliation results
e.g. 191,191,191

Reconcile Key

Define query formula field(s) as reconciliation key(s)
e.g. vou

Other Fields (Optional)

Retrieve data by lq() formula fields for display information only
e.g. des(All)vou(All)