Revolutionizing Financial Reporting Automation for Leading Financial Services Conglomerates: A Success Story
Customer is one of India's leading financial services conglomerates, offering a robust platform to a diversified client base across domestic and global geographies.
Customer’s continuous and single-minded focus is on understanding customers’ needs and offering the right financial solutions. Present in every financial life stage of a customer, helping them create wealth, grow wealth and protect everything, are their key lines of business.
The customer approached RBT to check the possibility to automate their annal financial reporting for multiple business entities. The final report is having more than 500 pages and above 150 tables and lots of text content. Now multiple employees are working more than 4 months to generate the report from multiple excel files. Customer needs a simple and accurate solution to generate the financial reports.
Current Report generation process
Customer follows around 150 business notes excel templates and will be distributed to multiple business entities to fill and submit the note disclosures. The respective members from each team will do the data evaluation and confirm the data submission. The reporting team will take the data from submitted excel sheets and manually prepare the report in a word document. Multiple persons will work on different report sections and finally get consolidated to a single report. It consumes lots effort and man hours.
Goals & ChallengesThe goal of the project was to automate the annual financial report building.
Challenges are as following:
- Data entry from different teams was not in standard template.
- There data was collected in around 600 excel sheets.
- There report include more than 150 tables and above 500 pages
- There were inter connection 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.
Goals are as following
- 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.
SolutionCustomer chooses Ray Business Technologies (RBT) to address their specific needs to achieve their goals.
- 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, 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.
The new solution reduced the manual effort to build the report and all aligned to a standard process. Everything is controlled from SharePoint. Report generation become 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 power bi. Now report building is a clear and easy job and customer is happy with the solution provided.
About Ray Business Technologies
Ray Business Technologies is a Global Information Technology (IT) Services and Solutions company. We are a CMMI Level 3 company, providing cutting-edge IT Solutions to Enterprises worldwide, enabling them to utilize available resources effectively and manage the operations. Our excellent team of Technology Professionals work with enterprise clients in North America, Latin America, Australia, Europe, Middle East and Asia.