Financial Close for Sale, Purchase & Cashflow Transactions (FinancialClose-Trading)

This is one of the most common business models in Hong Kong for small businesses. Many sizable groups at their headquarters usually offer autonomy for individual business units, business units are either green or mature, some units are either small or large. In fact, various units require a fast closing system to support their activities on a daily, weekly basis, as well as to support the rapid delivery of financial statements to their headquarters.

Master Maintenance

CalcRule File: A-FinancialCloseMaster.xlsx    

Demo Data: Master.xlsx

Mapping Maintenance

CalcRule File: B-ImportFinancialCloseMapping.xlsx

Demo Data: Mapping.xlsx

Transaction Processing

CalcRule File: C-PrepareFinancialCloseVoucher.xlsx

Demo Data: Transaction.xlsx

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

Data Transformations

  • Account Code Mapping
  • Sale Discount Mapping
  • Purchase Discount Mapping
  • Generate Double Entry by Transactional Data

Data Posting Models

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

Data Captured Levels

  • Sale Transaction
  • Purchase Transaction
  • Bank Book by Expense/Income
  • 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-FinancialCloseMaster.xlsx

Below CalRule file contain 3 different sheets 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.  Two CalcRule sheets are implemented FlexETL module, so sheet are named as 1.ETL and 2.ETL. The suffix .Ledger, .Account are mainly for your quick reference only.

Module

1. ETL.Ledger – Extract the Ledger Master from Excel Workbook, and then load to DB.Ledger.Master(Ledger) using Ledger as amend key

2. ETL.Account – Extract the Chart of Account from Excel Workbook, and then load to DB.Ledger.Master(Account) using Ledger & Account as amend keys

Demo Data: Master.xlsx

Below demo data file contains 3 different sheets of demo data

Demo Data Sheet

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

2. Ledger – contain three columns only, column Base Currency is to register the currency of Ledger

3. ChartOfAccount – contain three columns only, column Account Name is used to support joining account name for reporting

CalcRule File: B-ImportFinancialCloseMapping.xlsx

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

Module

1. ETL.Purchase – Extract the purchase discount mapping table from Excel Workbook, and then load to DB.Ledger.Mappsing(PurchaseDiscount) using Source Supplier Name as amend key.

2. ETL.Sales – Extract the sales discount mapping table from Excel Workbook, and then load to DB.Ledger.Mapping(SalesDiscount) using Source Customer Name as amend key.

Remaining sheets are used to defined account mapping table for Sales, Purchase, Bank & Revenue accounts.

Demo Data: Mapping.xlsx

Below demo data file contains 3 different sheets of demo data

Demo Data Sheet

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

2. PurchaseDiscount – contain three columns only, Source Supplier Name is a key for mapping with relevant transactions, Target Supplier and Target Discount Rate are used to return values for each mapped value of Source Supplier Name.

Other mapping sheets are worked as similar as the purchase discount sheet.

CalcRule File: B-PrepareVoucher.xlsx

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

Module

1.ETL.PurchaseVoucher – This module supports the calculation of discounts for the purchase according to the mapping table and prepares vouchers with a balance entry recognised as account payable (3300).

2. ETL.SalesVoucher – This module supports the calculation of discounts for the sales according to the mapping table and prepares vouchers with a balance entry recognised as account receivable (2300).

3. ETL.Cashflow & 4.Accrual – This module supports the capture and organise a special layouts of a daily cashflow working and to prepare all relevant vouchers according to the mapping table of account code mapping.

5.ETL.DTtoDB – This module is used to merge data tables generated from module 1-4, and to post vouchers to DB.Ledger.Fact(Voucher) by amend keys “Ledger” and “Period”.

Demo Data: Transaction.xlsx

Below every demo data contains 4 different sheets:-

Demo Data Sheet

1. #C.PrepareFinancialCloseVoucher – It supports the system to identify the corresponding CalcRule name to process the data of the current data file.

2. PurchaseInvoice & SalesInvoice –  It contains daily sales and purchase transaction.

3. BankBook – It contains a special banking book layout for recording daily cash flow transactions across the various analysis columns.

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