Automated Financial Reporting and Consolidation in Just ONE Month? Is It Possible? Yes! Yes, it's entirely feasible to implement Automated financial reporting and consolidation across all group companies in a remarkably short timeframe—if you're committed and allocate the necessary resources.
For insights into the essence and advantages of automated reporting, refer to my previous article 'Understanding Automated Financial Reporting'.
This article details the steps to rapidly implement automated financial reporting and consolidation across the entire group. Before diving into that, let's explore whether it's feasible to capture a Big picture of the Group's financial performance despite the data quality challenges, then summarize the necessary resources for the automated reporting implementation and understand whether there is the best timing to start.
Table of Contents:
3) Best Time to Start Implementation
Balancing the Big Picture and the Details
Often, when the goal is to automate financial reporting and consolidation to see the Big picture of the Group's financial performance every day, we're met with numerous challenges. These might include outdated ERP or accounting systems that need updating or replacement, data format inconsistencies across various entities, and concerns about the overall quality of accounting data. A typical response to these challenges? Diving deep into resolving these challenges one by one, which often delays our main objective of automation and the Big picture, further complicating the reporting process.
The more strategic approach is to balance both the Big picture and the Details. Many may not realize that it's entirely possible to establish automated financial reporting and consolidation using:
The current accounting and ERP systems already in place within Group companies, even if they are outdated, and
The current financial data available from Group entities, even if they are not perfect.
This is where advanced reporting systems come into play. The reporting systems can handle automated data integration with the existing accounting and ERP systems, rigorous data validation, currency translation, and even improvement of the low quality imported data, thus ensuring daily unified reporting and consolidation across the entire Group. Once this foundational setup is in place and the Big picture of the Group's financial performance is accessible daily, then it's the right moment to shift focus to refining details and improving data quality for individual legal entities.
Essential Resources for Implementing Automated Reporting and Consolidation
Project Leader: A dedicated finance individual from the Group finance team should allocate roughly 70% of their time to lead this project. This individual should be skilled at handling the Group's chart of accounts, dimension structure, reporting requirements, and consolidation complexity, and should be skilled in communicating with the Local finance teams of the Group legal entities.
Group CFO: If not a Project leader, the Group CFO will need to be partly involved, especially in endorsing decisions related to the Group COA, dimension structure, data import tactics, and currency translation strategies.
Advanced Reporting & Consolidation System: It's vital to have an advanced reporting and consolidation system that provides daily data access and is compatible with current accounting and ERP systems. Once set up, this system should enable automated reporting, consolidation, and currency translation while continuously validating data quality to strengthen confidence in reporting. Seek a system that is ready for setup without incurring additional development time or costs.
While it's possible to implement automated group reporting and consolidation within ERP systems, it usually extends beyond a month and might necessitate comparatively more developmental efforts and resources.
For illustrative purposes, I'll reference Emfino - an automated reporting, consolidation, and budgeting system. The system is ready for immediate setup and use regardless of your global location.
Local Finance Teams: Local finance teams should allocate a minimal amount of their time to produce sample reports from their accounting or ERP systems, which will be essential for data imports to the new reporting system. If there's a need for data mapping, these teams may assist the Project lead in this task.
IT specialists: Automated data export setups will be required to transfer necessary reports from local accounting or ERP systems to the new reporting platform. Depending on the deployment and management of these systems, the involvement of designated IT specialists will be needed.
Best Time to Start Implementation
Start TODAY! Truly, there's no better or worse time throughout the year to initiate the automation of your reporting processes. Be it the beginning or the end of the year, allocate just one month to implementation. You'll soon find up-to-date data for the current year and data from one or two preceding years for comparison right within the system. Compile any report you need and enjoy the convenience of consolidated data. .
Steps to Fastest Implementation of Automated Reporting and Consolidation
Let's start from the point where you've selected the reporting system, it's been deployed on server and stands ready for set-up, a project leader is geared up to take charge, and you've secured the buy-in from all key stakeholders. The primary stakeholders in this context are the finance teams of the Group and its subsidiaries, along with Group IT support.
1) Prepare the Group Chart of Accounts (Group COA)
In the context of automated financial reporting, a Group COA plays a pivotal role. It provides the foundation upon which the imported financial data of separate legal entities are validated and processed by the reporting system. The Group COA, complete with detailed account descriptions, has to be imported in the reporting system.
For detailed guidelines on creating an effective Group COA, refer to our previous article: 'Designing a Powerful Group Chart of Accounts (Group COA) for Effective Automated Financial Reporting'.
While your Group COA is foundational, it doesn't need to be perfect from the start. As you engage with the data from different legal entities, adjustments will likely be needed.
Example 1
If your company hasn't designed a Group COA yet, Emfino Demo system offers a sample Group COA covering various industries, complete with detailed account descriptions. This might serve as a foundation for your own Group COA. Additionally, Emfino team is keen to assist businesses in crafting their ideal Group COA.
2) Prepare the Group Dimension Structure
The significance of the Group Dimension structure parallels that of the Group COA. Initially, lay out the logic and primary usage of dimensions in your Group reporting. You can refine and expand on the details as you progress.
Example 2
Emfino Demo features a comprehensive dimension structure, covering multiple industries, complete with detailed descriptions. And Emfino team will assist in designing a perfect dimension structure if needed.
3) Define Data Import Formats
This step can be performed in parallel with the first two. For Emfino reporting system, minimum required data imports include:
Transaction Report: The most detailed report from accounting or ERP system capturing all transactions for the selected period. Required fields include: transaction date, supporting document number, partner name, accounts applied for transaction entry, amounts in both Local and original currencies, dimensions used, comment field, and booking number. Ideally, each transaction should have a direct link to its scanned supporting document or invoice, ensuring accessibility for users.
Trial Balance Report: A report that illustrates the opening balances for the specified period, the turnover of the accounts, and their closing balances.
Initiate the process by requesting each legal entity to provide sample transaction and trial balance reports from their accounting or ERP systems, typically in Excel format. The project lead evaluates these samples for completeness. If they meet the criteria, they're forwarded to Emfino team to configure the unique import format for each entity. Once set up, the system is ready for data uploads.
4) Prepare Advanced Mapping in Reporting Systems
The subsequent step involves the task of mapping of each legal entity's chart of accounts (Local COA) and Local dimension structure to the Group COA and Group dimension structure within Emfino. Even if data from Local entities appear less than perfect or lack detailed elements, no worries. Advanced mapping offers a variety of ways to enhance both historical and newly imported actual data until the implementation of Group COA. Specifically, it:
Adds or adjusts necessary dimensions in the transactions
Adds partner names if there is none in transactions
Moves intra-group transactions into separate Group accounts for consolidation and reconciliation
Transfers IFRS adjustments into distinct accounts
Splits transactions from a single Local account to multiple Group accounts based on set criteria like partner names, comments, document numbers, and dimensions.
This approach offers the advantage of minimal disruption to the accounting records while still ensuring compliance with Group COA standards. However, advanced mapping should be considered only a short-term solution for data improvement. This approach necessitates a detailed rule-set and could become burdensome to maintain. Therefore, an implementation of the Group COA at a later stage is encouraged to reduce reliance on such temporary
solution.
Examples 3-7
Examples of Advanced Mapping Rules in the Reporting System: 1) To add dimensions
Account mapping: If the Local account is '7310', map it to the Group account '76300 Social contributions - Salary expense ' and add the Group dimension 'ADOTH' (Example 3).
2) To adjust dimensions
Account mapping: If the Local account is '8800 Corporate Income Tax', map it to the Group account '79500 Corporate Income Tax' (Example 5).
Cost centre mapping: If the Local dimension in any account is 'OTHER', map it to the Group dimension 'OTOTH' (Example 6).
Advanced mapping adds to the previous definitions: If the Local account number is '8800' AND the Local Cost centre is 'OTHER', then map to the Group dimension 'OTCIT' instead (Example 4). Similar for account '8810'. 2) To move intra-group transactions into separate Group accounts and adjust dimension
Account mapping: If the Local account is '5310 Trade Creditors', map to the Group account '55100 Trade creditors' (Example 8).
Advanced mapping adds: If the Local account number is '5310' AND Partner name is 'Coast Group Ltd', then map to the Group account '55101 Trade Creditors - Group' instead (Example 4). Similar for account '7910', only the advanced mapping defines to apply the dimension 'ADOTH'. 3) To transfer IFRS adjustments into distinct Group accounts
Account mapping: If the Local account is '7100 Change in active portfolio provisions', map to the Group account '73800 Change in active portfolio provisions - CIT deductible' (Example 7).
Advanced mapping: If the Local account number is '7100' AND the Comment field contains text 'IFRS', then map to the Group account '97360 Change in provisions - IFRS adjustment' instead (Example 4). 4) To split transactions from a single Local account to multiple Group accounts based on the Text in comments field
Account mapping: If the Local account is '7220 Loan interest expense - Investor Platform', map to the Group account '73340 Loan interest expense - Investors' (Example 7).
Advanced mapping: If Local account number is '7220' AND Comment field contains text 'WHT', then map to the Group account '73343 WHT on Loan interest - Investors' instead (Example 4). 5) To split transactions from a single Local account to multiple Group accounts based on the Partner name
Account mapping: If the Local account is '7620 Internet and Hosting expenses', map to the Group account '76400 Internet and Hosting expenses' (Example 8).
Advanced mapping: If the Local account number is '7620' AND the Partner name is 'HR Systems', then map to the Group account '76401 IT Licences subscription' instead (Example 4).
5) Configure Currency Translation
Advanced reporting systems offer various automated currency translation strategies for instant local-to-reporting currency conversion. Strategies can be tailored for each account in the Group COA and consistently applied across all Group entities. Consequently, a crucial next step involves configuring these currency strategies for every account in your Group COA, as well as input of historical exchange rates if required.
Refer to our blog post: 'Emfino Super Feature #3 - Foreign Currency Translation'
Example 9
6) Initiate Data Imports in the Reporting System
For legal entities with mapping in place and data import formats configured, data import can commence. Post-import, you can analyze the entity's data using predefined reports within the reporting system. In order to enhance the obtained data quality, refine the advanced mapping or adjust the currency translation settings.
Initial Data Imports - Manually from Excel: Determine the date range of historical data you'd like to include in the reporting system. Each legal entity can have a distinct start date. Let's say you decide to import data from the beginning of 2021 for a particular legal entity. Request the entity to provide the Transaction report starting with January 1st of 2021 to the present day, along with the January 2021 Trial Balance report in Excel format. Once data uploaded and if they pass system data validation checks, these data becomes instantly accessible via the system's built-in reports.
Continuous Data Imports - Automated: For further continuous data imports it is necessary to engage IT specialists. Their task is to set-up an automated daily data exports from Local accounting or ERP systems either directly into Emfino or to a server location from which Emfino can fetch and import the data. Such automated processes of data integration are commonly known across various ERP systems. Automated data imports ensure that data in the reporting system match the actual accounting data on daily basis and eliminate any involvement of manual work, such as accountants or controllers manually importing transactional reports.
For Group companies with minimal activity or very few monthly transactions, it is possible to continue with a manual import of data only at the end of the month.
Example 10
7) Configure Data Validation and Import Checks
This is a critical feature often missed by many systems. Automated, built-in data validation checks guarantee the quality of data right from the first import. Advanced reporting systems can detect errors, inconsistencies, missing data, even silent adjustments to historical accounting records. At this stage, only minimal configuration is required for these validation checks to function effectively. Later, as the focus shifts to enhancing data quality for individual legal entities, custom checks tailored to each entity can be defined.
Refer to our blog post: 'Emfino Super Feature #2 - Data Validation and Import Checks'
Example 11
8) Setup Consolidation
This step is a delightful reward. After configuring consolidation, you'll be able to view consolidated results in any of the reports.
Refer to the blog post: 'Emfino Super Feature #1 - Advanced Automated Consolidation'
Begin by defining various consolidations, incorporating the relevant legal entities. To ensure the automated elimination of intra-group transactions and balances, and to activate the intra-group reconciliation tool, it is necessary to select the appropriate processes in the Group COA.
For consolidation adjustments where automated elimination is not possible, generate consolidation bookings. These bookings can be automated; for example, to eliminate an unrealized profit from the sale of a fixed asset to another Group company, you can set an automated consolidation adjustment booking each month, removing the unrealized profit from fixed assets, accumulated depreciation, and depreciation in the P&L.
Prepare and import a Split Report - a report that can be formulated in any spreadsheet, identifying the split of the opening Balance Sheet balances by Partners and/or dimensions, at least for Intra-group accounts, to ensure effective consolidation. Once the opening split is imported into the system, in subsequent months, the system will calculate the splits of balance sheet items by Partners and/or dimensions itself.
Examples 12 - 17
9) Define Reports
With 'Report Builder' feature you can easily set-up any custom reports. The reporting system facilitates automated solutions for Regulatory, IFRS, GAAP, Group and Management reporting purposes. Implement comment fields to provide context for variances. All available reporting templates, along with your custom-built reports, can be viewed with consolidated data.
Example 18
10) Integrate IFRS, GAAP or Group Adjustments
The automated reporting process becomes more efficient if IFRS, GAAP and Group adjustments are booked directly into the reporting system for all relevant legal entities. Furthermore, the capability to automate these bookings in the system through predefined rules and formulas offers a substantial advantage. Once the adjustment bookings are defined, all reports allow to select IFRS, GAAP, or Group reporting type in addition to Statutory.
11) Implement Monthly Closing
Implement a multilevel monthly closing process to ensure that:
Legal entities have both completed their monthly closing and performed the necessary imports of required data, providing a formal sign-off upon completion.
Group controllers have reviewed and provided their sign-off on the data submitted by the entities.
The system becomes locked, preventing further data imports for the closed periods.
Entities may only import adjusted data for closed periods if the system is re-opened by group controllers, thereby guaranteeing that no unmonitored adjustments to historical data can occur.
12) Add Users
This is a very delightful step. It's finally time to introduce additional users to the automated Group reporting and consolidation! The reporting system provides tailored access permissions for data administration, viewing, budgeting, and variance commenting based on legal entities, system modules, reports, chart of accounts (COA), and dimensional structures, ensuring a secure and efficient data management environment.
The Next Steps
Having executed the aforementioned steps, an automated daily reporting and consolidation process is now established within the Group, supported by the robust data validation capabilities of the reporting system. You now have a Big Picture view of the Group's performance, allowing to move on with the Details.
Implementation of New Group COA Across the Group:
While the established automated reporting does provide insights, it's built on data of variable quality and leans on Advanced Mapping—a strategy not viable long-term. This requires further improvements in data quality through the implementation of group accounting and reporting requirements across all legal entities.
Discover how to implement Group requirements in the article: 'Successful Implementation of a New Group Chart of Accounts (Group COA) Across the Group'.
Upon implementing Group requirements, you can set end dates for Advanced Mapping by legal entity, and the reporting system will proceed with data validation against the Group COA and dimension structure. If an accounting or ERP system is replaced for any legal entity, only the data source for the reporting system needs adjusting - a straightforward setup.
Expand Usage of the Reporting System:
With increasing data quality requirements, expand further usage and benefits of the reporting system:
Defining Custom Data Validation Checks: Delight your financial controllers by allowing them to concentrate on data analysis while the system takes care of catching the errors in imported data.
Incorporating New Data and Import Sources: Initiate imports of other vital data necessary for reporting and KPIs beyond accounting data. Integration capabilities extend to various systems and BI tools.
Implementing Activity-Based Costing: Move towards accurate activity-based costing and precise profitability analysis by deploying automated and rules-based cost allocation.
Starting Budgeting: Budgeting within the reporting systems aligns with actuals and automated reporting, while the flexibility of custom user permissions guarantees a secure and customized budgeting experience for every team member.
With this article, we aimed to demystify the implementation process of automated reporting and consolidation, illustrating that it’s not an endless marathon of complexities, but rather, a highly attainable and even enjoyable sprint when approached correctly. The secret to a swift, successful implementation lies in prioritization, comprehensive stakeholder buy-in, and deploying the right tools.
コメント