Purchase Consolidation for Global Metal Buying Offices (PurchaseConso-Metal)

FlexCalc is not restrict to implement by finance department. It can extend to provide services to other departments including sales, marketing, procurement, internal audit & etc.

This application is designed to provide data consolidation to the procurement department when they encounter operational problems associated with multiple procurement offices — different units of measurement, different currencies of purchase contracts and different base currencies of regional offices. In addition, this application is also useful for the internal audit department when they are required to carry out certain audit plans for procurement activities.

Below tutorials are highly correlate to this app:-

  • Solving Steep Learning Curve of ETL (1a)
  • Recyclying Unstructured Data (1b)
  • Foriegn Currency Transaction (1c)
  • Dual-base Currency (1d)
  • Standardise Unit of Measurement (1e)

Master Maintenance

Master File: A-PurchaseConsoMaster-Metal.xlsx

Demo Data: Master.xlsx

Purchase Consolidation

CalcRule File: B-PurchaseConsolidation-Metal.xlsx

Demo Data: AustraliaData.xlsx, ChinaData.xlsx, IndonesiaData.xlsx

Raw Report


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

Consolidation Method

  • Full Consolidation

Data Transformations

  • Unit Code Mapping
  • Exchange Rate Mapping for Local & Group
  • Calculate Total Quantity
  • Calculate Total Transaction Amount with Transaction Currency
  • Calculate Total Local Amount with Local Base Currency
  • Calculate Total Group Amount with Group Currency

Data Posting Models

  • No Posting to Data Building


  • Output Raw Purchase Consolidation Report

Data Collection Templates

  • Any day, any month, any year

Also See a Detail List of Accounting Rules

CalcRule File: A-PurchaseConsoMaster-Metal.xlsx

Below, the CalcRule file contains 5 different sheets using the ETL module:-


  • 1.ETL.Ledger
  • 2.ETL.MainUnit
  • 3.ETL.SubUnit
  • 4.ETL.TranExRate
  • 5. ETL.ReportExRate


Demo Data: Master.xlsx

Below every demo data contains 5 different sheets:-

Demo Data Sheet

  • 1. Ledger
  • 2. MainUnit – It is used to support standardisation of unit of measurement.
  • 3. SubUnit – It is used to support standardisation of unit of measurement.
  • 4. TranExRate – It is used to support translation from transaction currency to local base currency.
  • 5. ReportExRate – It is used to support translation from local base currency to group currency.

CalcRule: B-PurchaseConsolidation-Metal.xlsx

Below ETL contains 3 different blocks:-

Block.1: Load Master Table to Memory

Block 2: Standardisation Process

Block 3: Output Standardisation Result

Setting this ETL does not configure the posting of the transaction in the data store, useful to supports your drafting consolidation.

Demo Data: AustraliaData.xlsx, ChinaData.xlsx, IndonesiaData.xlsx

Expected Result: ExpectedResult.xlsx

About the Sitemap


A Well-known Brand of FlexSystem, Driven by a Highly Successful 5-tier Enterprise App Server


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


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


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