You Demand for Improving Last Mile of Accounting

Preparation of Monthly Consolidated Financial Statements

Integrated User Interface

Supports Comprehensive Management of Group Consolidation

Sharing Your Examples

10 + 7 =

One of Simplex Consolidation: Consol_YTD_TB

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

Innovation of Data Reconciliation

Designing to Solve Problems of Sizable Listed Retail Group

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)

Recognising Your Spreadsheet Data

Read_Form_Generate_SPX

This command can be used to test transformation of an Excel input form whether it can be exported a transaction list which follows the standard import format. In addition, it is optional to output the SPX transformation script.

Prerequisite Knowledge

Before learning how to use this command, users shall understand the standard import format of transaction file.

Command with Parameters
Read_Form_Generate_SPX “Y” “C:\LB\Form.xlsx” “Entry” “C:\LB\Form.xlsx” “Transaction”

Is Output SPX

Specify whether to output the SPX transformation script
e.g. Y or N

Source File Path

Specify full file path of an Excel Workbook which contains an entry form
e.g. C:\LB\Form.xlsx

Source Worksheet

Specify a worksheet name of the file
e.g. Entry

Target File Path

Specify target file path of the testing results of transformation
e.g. C:\LB\Form.xlsx

Source File Path: C:\LB\Form.xlsx    Source Worksheet: Entry

Is Output SPX: Y

Worksheet = Entry
Common Value = Analysis Version, Currency, Analysis Department, Account, Ledger, Analysis Office
Cross-Tab
{
Row = Analysis Product, Analysis Segment, Analysis Market
Column = Date
Value = Amount, Unit Price, Quantity
Value Header Exist = Y
}
Output Setting
{
Insert File = C:\LB\Form.xlsx , Transaction
}

Target File Path: C:\LB\Form.xlsx    Target Worksheet: Transaction

Some of Demonstration Videos

Conso 123 Webinar

Group Conso Revolution Moving to Multinational Learn More