Consolidation of Year-to-Date Trial Balance (ConsoYTD-TB)

This is one of the most common consolidation practices in Hong Kong, when financial data on closure is presented by your affiliates, and the total amounts for each account are calculated on the balance sheet for the year, not the monthly movement. The compositions of the application are as follows: –

Master Maintenance

CalcRule File: A-ImportLedgerMaster.xlsx    

Demo Data: LedgerMaster.xlsx

Transaction Processing

CalcRule File: B-PrepareVoucher.xlsx 

Demo Data: 201412.xlsx, 201501.xlsx, 201502.xlsx, 201503.xlsx, 201504.xlsx, 201506.xlsx

The App & Demo Data Demonstrates the use of below Accounting Rules:-

Consolidation Methods

  • Full Consolidation

Data Transformations

  • Foreign Currency Translation
  • Reverse Voucher to Next Period

Data Posting Models

  • Data Amendment of Posted Voucher by Input Form
  • Calculate Balance Upon Reporting
  • Single Period Posting
  • Single Ledger Posting

Data Captured Levels

  • Trial Balance
  • Monthly Exchange Rate
  • Master File
  • 12 Periods a Year

Data Collection Templates & Different Layouts

  • Extract Data from Different Excel Layouts

Also See a Detail List of Accounting Rules

CalcRule File: A-ImportLedgerMaster.xlsx

Below CalRule file contain 4 different sheet of ETL settings. This is used to support your master file initial data import and subsequent amendments. If you want to process each master file independently, you can separate each ETL setting sheet by a separate CalcRule workbook.  Most of the CalcRule sheets are implemented FlexETL module, so sheet are named as 1.ETL, 2.ETL and etc. The suffix .Ledger, .ChartOfAccount, .Currency are mainly for your quick reference only.

Module

1. ETL – Load your master file workbook into memory as LamiBook(LedgerMaster) for subsequently use

2. ETL.Ledger – Extract the Ledger Master from LamiBook, and then load to DB.Conso.Master(Ledger) using Ledger as amend key

3. ETL.ChartOfAccount – Extract the Chart of Account from LamiBook, and then load to DB.Conso.Master(Account) using Account as amend key

4. ETL.Currency – Extract the Currency Master from LamiBook, and then load to DB.Conso.Master(Currency) using Currency and Period as amend keys

Demo Data: LedgerMaster.xlsx

Below demo data file contains 4 different sheet of demo data

Demo Data Sheet

1. #A-ImportLedgerMaster – it supports the system to identify the corresponding CalcRule file to process the data of the current data file.

2. Ledger – It contains two columns only, column Base Currency is to register the currency of Ledger.

3. ChartOfAccount – It contains two columns only, column Account Name is used to support joining account name for reporting.

4. Currency – It contains four columns only, Closing Rate and Average Rate are used to support foreign currency translation.

CalcRule File: B-PrepareVoucher.xlsx

Below, the CalcRule file contains 3 different sheets using the ETL module & the Conditional Action module:-

Module

1. 1.ETL.MainFlow – This is the main command flow of the CalcRule file which automate processing of data (Trial Balance) throughout 5 different blocks.

2. Conditional.Action – This is used to determine whether current period is last period, so voucher reverse to next year with Profit and Loss Account reversal to Retained Account.

3. ETL.ReverseEntry– It contains two different scenarios of reversal entry depend on whether current period is last period.

CalcRule File: B-PrepareVoucher.xlsx - Cont. & Refer to Above Workbook

Block

Block.1 Read Data for Initial Voucher Preparation – Reading the necessary master data from the Data Building and extracting data from Excel workbooks for subsequent processing blocks. This block also supports the insertion of an accounting period with reference to a specific date.

Block.2 Execute Foreign Currency Translation – Match either the closing exchange rate or the average exchange rate with the data table, referring to user-defined ranges of account codes (e.g. Balance Sheet Type Account will use closing exchange rate, Profit and Loss Type  Account will use average exchange rate. In addition, calculate Balance Amount by multiplying Original Amount with the mapped Exchange Rate.

Block.3 Balance Voucher Entry by Suspense Account and Exchange Reserve – The account balance multiplied by the closing exchange rate or the average exchange rate can lead to an imbalance of the voucher. This option also covers “what if the Original Amount does not balance”, so provide two steps to process your account balance. Each type of imbalance will be posted on separate account codes.

Block.4 Determine Method to Reverse Entry – In this block, a Conditional Action will be taken to determine whether the current period is the last one, so the voucher will be transferred to the next year with Profit and Loss Account reversal to the Retained Account.

Block.5 Post Voucher by Amend Keys – Above workbook are the settings using Ledger, Period & Form as amend keys. By default, if Ledger and Period are defined as columns for data posting, they should be defined as amend keys when the system splits the data table and write files to corresponding folders.

Demo Data: 201412.xlsx, 201501.xlsx, 201502.xlsx, 201503.xlsx, 201504.xlsx, 201506.xlsx

Below every demo data contains 2 different sheets:-

Demo Data Sheet

1. #B-PrepareVoucher – It supports the system to identify the corresponding CalcRule name to process the data of the current data file.

2. Data –  It contains monthly financial close data that conforms to the year-to-date trial balance format.

Note: The ETL setting %CurrentPeriod(Date,>>Dec) is assumed December as the year-end-month. The reversal voucher to effect for the data file “201412.xlsx” – relevant Profit & Loss Account balances will be reversed to Retained Account of 01/2015.

Web Framework

You Can Implement & Host any Front/Back-end Web Framework with Adopting FlexCalc as a Calc Engine

Data Scientist

Revolutionary Software with the 2020 Roadmap to Empower Business Users Performing as Data Scientist

Business Scenario

Providing Very Rich Information for Matching Your Business Requirements in Different Scenarios

Online Tutorial

Inspiring Your App Building Practices by Offering Comprehensive Guide with Many Use Cases