Mastering Multinational Financial Statementsby the Simple Configuration to Drive Calculating Current Month Movement
Barry Gibson (巴里~吉布森) is a qualified accountant and currently working as a foreign exchange trader at an international bank on a part-time basis. Before he joined the bank; he has over 20 years of finance and accounting experiences in some multinational corporations.
He deals with the operations of foreign currencies in depth throughout his career life. His achievements include 1) implementation of a file management and accounting system, 2) development of currency hedging strategy and related accounting procedures, and 3) enhancement of a financial model which support valuation for currency options.
With the further understanding of his career story in respect of professional accounting, although he is smart to deal with foreign currencies at the business contract level and associated transactions, he has some doubts in the management of a mass volume of financial statements denominated in different reporting currencies and underlying transactional currencies. At the end of the interview with Barry, he summarised there are three major pitfalls in the preparation of consolidated financial statements which involves different reporting currencies of foreign entities.
Multinational Group A: rely on using Excel Workbook to manage the preparation of group accounts which cannot support multi-user and proper audit trail
Multinational Group B: migrated all legacy systems into a single ERP software which triggers a problem of managing huge volume of data within a centralised database
Multinational Group C: implemented a complicated accounting software which specialises in the preparation of consolidation financial statements for statutory audit
Business Case A: Rely on Excel WorkbookAn Advanced Technology Group
Barry had joined the group at his early stage of accountancy career. The principal activities of the group are to manage a portfolio of communication technologies and production of custom communication solutions for clients. Every year the group acquires on average of 10 private companies in which most of them are incorporated in overseas.
Report to the Group Accounting Manager, Barry is responsible for managing a collection of financial statements and related version control with the reconciliation of differences between each version. His works are very mechanical and tedious because the number of oversea companies is over 1,000 which cover more than 30 countries.
The group accounting team of the headquarters has over 20 staff in which 15 staff directly involved in the process of preparation of monthly consolidated financial statements. All overseas finance offices are required to follow a standard chart of accounts, project codes and accounting policies. Other challenges of the financial close processes are frequently involving the following activities: –
Acquisition and disposal of companies
Amendment of financial statements by overseas finance offices
Addition of new account codes and project codes
Addition of new reports as requested by management
The disadvantages of the standard chart of accounts and project codes are significant. Firstly, headquarters sends notification emails in respect of new account codes and project codes to all overseas finance offices very frequently whenever any staff requests for new code. Secondly, some of them are required to follow local regulatory and management requirements; they have to maintain dual sets of account ledger for each company.
After the 2-year of mechanical works, he had proposed and completed an implementation a file management system which supports to automate sending alerts and keeping different versions of files and related data reconciliation. Financial close is reduced from 60 days to 45 days because the audit trail of the file management support to motivate the faster submission of financial statements by overseas finance offices as well as fewer amendments.
Business Case B: Unifying ERP SoftwareA Home Appliance Trading Group
After Barry leaving the technology company, he joined an international trading group as an Assistant Finance Manager. He is responsible for treasury operations of the international trade business which mainly focus on risk management for exchange rate risk, liquidity risk and credit risk of the international trade business. Below are key statistics of the business: –
Number of contractual currencies: 18
Number of contractual parties: 1,300
Number of multi-currency account code: 9,500
Number of item code of inventories: 1,800
Number of warehouses: 200
Number of employees: 850
Number of oversea offices: 120
Number of legal entities: 180
Within first year he joined the company, the management had decided to implement a Cloud ERP service to unify trading and accounting systems for all oversea offices. Key reasons for such move are as follows: –
Long lead time of financial close of individual entity and group of entities
Some offices their accounting software cannot support multi-currency
Some offices their trading software are not integrated with accounting software
Significant works are involved in the reconciliation inter-company transactions
High cost of managing IT infrastructure for overseas offices
Inventory system supports FIFO for individual warehouse level only
Barry is one of the members of the implementation team. The cloud computing service cannot offer customisation service, but allow users to select particular implementation plan. So, the implementation is very smooth and efficient. The first milestone of completion for a production run is less than a year.
After one year of using the ERP, the management requested an interim review of the effectiveness of the implementation. At the meantime, Barry was promoted to Group Internal Auditor and responsible for the review.
The review work covers interview of select staff and questionnaire to most staff. The ERP review committee had completed the review by delivered a review documentation to the management. The review has uncovered some of the following operational issues: –
Performance Issue: All overseas offices are forced to work within a centralised system and database. Any report enquiry users have to wait at least 5 minutes.
Accelerated Unit Cost: Whenever request to increase performance, always associated with accelerated monthly service charge (i.e. CPU Power, Disk Usage) rather than software improvement.
No Monthly Cut-off: The system must process any data amendment retrospectively (i.e. back-date amendment). Impact on monthly financial close is very significantly.
Ad hoc Report: Users rely on printing all relevant reports to Excel files as a data source for management reporting when management demand for high quality of reports.
Proposal for Improvement: By outsourcing to develop a database extractor to output the following data set in Excel: –
Double entry voucher list by entity
Year-to-date trial balance by entity
Stock-in and stock-out by warehouse
Year-to-date stock item balance
Reconciliation of any back-date transaction
Business Case C: Complex Accounting SoftwareA Multinational Marketing Group
At the end of 15-year of working in the accounting field, Barry successes in a final examination of a professional accounting body and acquired the professional qualification. He had an aggressive plan of career development for the next five years. By an opportunity, he joined one of sizable marketing group as a Chief Accountant for headquarters. He spent only three years to achieve his original plan; he became a Chief Finance Officer of the Asia-Pacific regional headquarters.
At the beginning of the employment, he was requested to improve the workflow of global financial consolidation. The global deployment of the new accounting software which specialise in the preparation of consolidated financial statements can support the group to reduce financial close from 45 days to 40 days.
A posted implementation report is written by Barry which summarises key issues of the software: –
The accounting software supports customisation but not allow users to effect any customization.
The software does support the calculation of monthly transactional movement which involves foreign currency translation.
It is very time consuming to document and confirm all relevant report layout and calculation rules before executing customisation works.
There are lacking of real end-user reporting tool, so users are remained rely on using Excel to prepare consolidated financial statements manually based on some raw output from the system.
Some users claim that the software does not care for traditional accounting practices.
The key successful of his contribution to the group is mainly attributed to an excellent financial modelling design and practice. The business has to manage scalable projects (e.g. construction, engineering and information technology) to support delivery of marketing solutions for clients. There are a significant price and exchange rate risks for these activities. Barry had designed and implemented a financial model of hedging to support monitoring to minimise associated risks. The new accounting software can offer help because the software support to export raw data to Excel. It becomes a valuable input for the financial model.
At the end of 3 years he had worked for the group, he was promoted to Chief Financial Officer of the Hong Kong regional headquarters which is responsible for overseeing financial management throughout Asia-pacific regions. The change of finance roles are summarised as follows: –
Moving from stationing at the Germany to Hong Kong with frequent overseas travelling
Shifting from group consolidation for historical to budgeting for prospective business
Changing from working with finance departments to working with business units
Barry realised that his previous implementation of the complicated accounting software cannot support the area of budgeting for business. The software vendor recommended alternative software. With his direction, IT department had developed some Excel Macros to support streamlining the process of budgeting. In the meantime, the UK headquarters had decided to source an alternative accounting software to cover a wider scope which supports end-user implementation on a continuous basis. The sourcing had not yet to finalise at the end of his employment with the group.
Barry had decided to retire from the senior executive position but maintain a consulting role with his employer. He continued his new career on a part-time basis – foreign exchange trader for an international bank. With the leveraging of his experience, he can enhance a financial model which support valuation for currency options for the international bank.
Conso 123 Apps: 002 Multi-Currency Group AccountsReal Apps with Sample Data and Reports
Step 1: Connect
Connect your data files which contain multi-dimensional year-to-date trial balance associated with different companies, business/ geographical segments, periods, currencies and etc.
Step 2: Calculate
Base on the trial balance to calculate current month transaction movement with related foreign currency translation for all accounts and analysis accounts. For any account configured as “P” profit and loss, the system can implement weight-average of exchange rate translation method (instead of simple average). Other accounts can be translated at a closing rate method automatically.
Step 3: Conclude
Offer user interface to assist configuring query formula for retrieving account by functional currency of subsidiary and presentation currency of consolidation.
To retrieve base (presentation) amount by base currency:
B01 to B10 are selected Ledgers
28008888 is a selected account code
01/2016 is a selected accounting period – first period of year 2016
CY is to specify retrieving current year-to-date balance
To retrieve original (functional) amount by specific currency:
rtype(amt) is selected functional amount as a result type
ccy(EUR) is a selected currency code
Whenever users are familiar with the setting up of formula in Excel, the use of the query formula function can allow end-users to configure ad hoc reports very quickly.
To support the automation of step 1 and step 2 of consolidation practices, the apps has implemented important recording functions such as Conso_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.
Accounting Practices for the Apps 001 you have learned basic accounting which includes Chart of Accounts and Double Entry.
For the Apps 002, it is designed to demonstrate how to automate the preparation of consolidated financial statements based on a determination of the following accounting practices: –
a. Adoption of useful financial data relevant for data capture
b. Evaluation of functional currency applied to each legal entity
c. Determination of accounts attributed to monetary assets and liabilities
d. Confirmation of presentation currency and year-end date for the consolidated financial statements
e. Understanding inter-company transactions and related eliminations
f. Differences between remeasurement and translation of foreign currencies
g. Selection of models to implement an approximation of actual exchange rates for accounts other than assets and liabilities
Accounting Practices – Useful Financial Data
Financial statements or trial balances of all entities within a group can be a primary data for the preparation of the consolidated financial statements.
Accounting Practices – Functional Currencies
The Hong Kong base entity implement Hong Kong Dollar as a functional currency while the Europe base holding company implement EURO as a functional currency.
Accounting Practices – Monetary Assets and Liabilities
Initially, both companies do not hold any non-monetary assets and liabilities. All foreign monetary assets and liabilities are required to remeasure by using closing exchange rates of each foreign currency. In year 2, the subsidiary acquired physical precious metals for the purpose of trading. It is recognised as inventory and classified non-monetary assets – because nothing committed contractual agreement with third-party in respect of the settlement of the precious metals are settled by any foreign currency.
Accounting Practices – Examples of Financial Statements of Individual Entity
Following are financial statements of a Holding before and after incorporation of a subsidiary. The holding company remitted EUR2,812,000 to Hong Kong. The amount of EUR812,000 is converted to Hong Kong Dollar of HKD7,000,000 and become a paid up capital of the subsidiary.
Accounting Practices – Presentation Currency and Year-end Date
The EURO base holding company choose EURO and December 31 as the presentation currency and year-end date respectively for the purpose of the preparation of consolidated financial statements.
Accounting Practices – Example of Year 1 Consolidated Financial Statements
Next step, please review working paper of the following consolidated financial statements which involve the handling of multi-currency for transactional, functional and presentation amounts respectively. The company group has only one holding and 100% wholly own subsidiary. Year 1 is to demonstrate the effect after incorporation of the subsidiary. Year 2 is extended to cover financial impacts after operating new business activities.
Accounting Practices – Inter-company Transactions and Related Eliminations
Refer the above example; there are two inter-company transactions 1) Investment in Subsidiary and 2) Amount Due from a Subsidiary are required to effect elimination of respective ending balance of year-end date. Before to confirm any elimination adjustment, it is necessary to reconcile any difference with referring to the balances of both transaction and presentation currency.
Accounting Practices – Remeasurement and Translation of Foreign Currencies
Translation of foreign currencies for the purpose of the preparation of consolidated financial statements shall be executed after the duly completed remeasurement of foreign currencies of each entity (holding and its subsidiary). Exchange differences arising from remeasurement shall be recognised as part of profit and loss account while translation differences shall be recognised as exchange reserve account.
Accounting Practices – Approximation of Actual Exchange Rates
It is no doubt it is practical to implement actual exchange rates for few capital transactions (e.g. Paid-up capital of the subsidiary). Whenever it is impractical to implement actual exchange rates for accounts other than assets and liabilities, it is a common practice to implement average exchange rates as an approximation given that there is no material deviation of measurement.
Below are different calculation models of the approximation of actual exchange rates:-
Yearly Simple Average Exchange Rates (Per Below Example)
Monthly Simple Average Exchange Rates
Monthly Weighted Average Exchange Rates (Apps 002)
Daily Closing Exchange Rates (Later Apps Publishing)
It is strongly recommended by Conso 123 to implement monthly weighted average exchange rates as a better approximation of actual exchange rates if the financial data of entities are year-to-date trial balances. If users prefer using vouchers which support drill down to voucher level, in some later apps publishing, Conso 123 can demonstrate how to implement daily closing exchange rates as the better approximation of actual exchange rates.
Preparation of the consolidated financial statements which involves the sophisticated implementation of foreign currencies obviously not a simple assignment. Current business case and related published apps are mainly deal with the essential story of multi-currency group accounts.
Although Barry achieved his greatest success of career development, he prefers working with foreign currencies for front-line trading business rather than back-office accounting function. His career success is mainly attributed to his excellent in designing financial model for foreign currency hedging and related valuation technique to support pricing strategy of future currency contracts.
He decided to retire from the works of professional accounting when he believed he cannot resolve the operational issues of remeasurement of foreign currencies (i.e. transaction currency/amount to functional currency/amount) and translation of foreign currencies (i.e. functional currency/amount to presentation currency/amount) for a sizable group of companies.
Without an existence and proper implementation of capable software, it is unlikely anyone can achieve a reasonable effectiveness of the Mastering Multinational Financial Statements.