Annual financial reporting automation, amplifying efficiency, accuracy & profits

Client Overview

As one of India's leading diversified financial services conglomerates, the Client provides a broad range of financial products and services globally, catering to a substantial and differentiated client base, including corporations, institutions, and individuals.

Project Summary

In order to prepare final annual financial report, client’s nearly 40 departments were taking more than 4 months to generate required reports from multiple excel files for multiple business entities. The final report had more than 500 pages, above 150 excel tables and lots of text content.

Customer needed an innovative, efficient & accurate solution to generate the Annual financial reports on time with greater control over user’s input.

Earlier Report generation process was inefficient:

The client was following around 150 excel templates, distributed to multiple business entities to fill, and submit the note disclosures. Respective members from each team were doing data evaluation and later confirming the data submission.

Upon submission, the reporting team manually generating report in a word document from submitted excel sheets’ data. Adding to the complexity, multiple persons worked on different report sections and finally consolidating to a single report.

The manual report generation, erroneousness, inadequate control and data complexity was making this whole process effort & Cost intensive and time consuming.

Goals & Challenges

Client’s top management had several brainstorming discussions with Ray Business Technologies’ (RBT) BFSI SMEs over current processes, workflow and complexities to generate Annual Financial Report.

• Major difficulty in preparing Annual Financial report, accurately and on time, was due to fewer control over distributed business entities participation, collaboration on data & input and manual consolidation of notes & data validation
• The AFR included more than 150 tables and above 500 pages
• Data entry from different teams was not in standard template. 
• The data was collected from 600 plus excel sheets.
• There were inter connections between excel sheets for data validation. 
• More than 100 employees from different business entities are updating the data.
• Data validation is very time consuming
• Lots of man hours are burned to generate the final report.

This helped RBT & Client to set Goals and Objectives for the project:

• Simplify and standardize the data entry templates.
• Build a Data management application to distribute, Consolidate all financial disclosure notes. So, everything can be managed from a single place. 
• Provide an option for business entities to pick the required templates from a master list.
• Avoid multiple copies of excel file distributions and track the version history and update history
• Automate the report building using the financial disclosure notes as source. 
• Automate the data validation and provide a dashboard to review the validation result.
• Export the generated report into word or pdf in a printable format. 

Solution:

RBT continued to work with client’s stakeholders to understand their views on current processes & proposed solution to address their specific needs to achieve project objectives.

• Used SharePoint, Power Apps, Power automates and Power Bi paginated report to automate the report building
• Prepared standard template for all 150 business notes and stored in SharePoint list and protected using SharePoint roles and security.
• Excel template are locked with a password and only data entry fields are allowed to edit and all formula and template columns are protected from unauthorized changes. 
• A power app dashboard is provided, a User interface using Canvas, So the users can select the appropriate templates and add to their entity to start updating the notes. 
• The selected templates are copied to appropriate entity folder and access is restricted to only the respective entity members. We are used SharePoint roles to restrict access. 
• The access allocation is automated using power automate from backend. 
• We have used power automate to copy the selected notes from master library to appropriate entity folder based on selected template and confirmation from user. 
• Created SharePoint list aligned to the note templates to store data entry from users. 
• We have created multiple power flows to extract and insert data from excel files to appropriate SharePoint Lists
• Created an automated data validation logic using power flows and data events + schedulers and inserted the validation result to a SharePoint list and A power bi report will use this table as data source and generated a validation result dashboard. 
• Multiple sub reports are build using Power bi paginated reports and consolidated all reports into a single report. 
• Page layout for each report canvas, content sections and tables are arranged to make sure we can export and print report properly.

Benefits/Business Case

The business solution, built on extensive BFSI vertical experience & innovative approach, using Office 365, Power Apps, Power automates and Power Bi, significantly reduced relevant manual efforts to generate Annual Financial Reports.

All stakeholders’ efforts were aligned to the standard process. Every flow within the process is controlled from SharePoint. Report generation became a few clicks job, once all data is ready.

Data collection is monitored and guided using SharePoint and PowerApps. Data transformation is automated using Power Automate and BI.

The new system amplifying efficiency, accuracy & profits for the client.

About Ray Business Technologies Private Limited:

Ray Business Technologies Private Limited is a Global Information Technology (IT) Services and Solutions company. We are a CMMI Level 3 company, providing Business & IT Solutions to Enterprises worldwide, enabling them to utilize available resources effectively and manage the operations. Our excellent team of SMEs & Technology Professionals work with enterprise clients in North America, Latin America, Australia, Europe, Middle East and Asia.