Project Overview
Developed a dynamic Excel-VBA tool for an insurance provider to automate and optimize the generation of 20-sheet financial reports, reducing processing time from days to under 30 seconds. The tool included customizable controls, dynamic formatting, and error-free formula handling. It enabled flexible reporting for monthly, quarterly, and yearly formats with minimal manual effort.
The Challenge
Our client had an established process for generation of monthly and quarterly Financial packs for submission to top management as well as IRDA respectively. Unfortunately, the file used for generation of these multi sheet documents are large, prone to human error and time taking. Just opening the file each time used to take few mins. Hence, it took the client few days to generate the reports.
Requirements: The team approached us to develop a tool which is dynamic & flexible in nature, reduces the generation time of reports to few seconds & should be easy to manage the content of each sheet.
The team approached us to develop a tool which is dynamic & flexible in nature, reduces the generation time of reports to few seconds & should be easy to manage the content of each sheet.
Managing 20 sheets of data with 24 months of data in each sheet, while retaining its independence and keeping the file dynamic and future ready.
Our Solution
Our team went to the drawing board with all the requirements and created an excel-VBA based architecture which supported the dynamic nature of output files. We created control parameters to switch all input fields like whether a sheet should be generated or not, Set Print Area on sheets , Set Serial no’s, rounding etc.
Each sheet parameters were further dynamically managed in the control sheet by creating custom formulas, formatting style, section headers and rows were analyzed and classified into categories with each specific category serving a particular way of summing the data. Thus, the control sheet once configured allowed dynamically updating, linking rows, summing and applying custom formulas to each row in individual sheet. Column data which represented monthly financials was automatically adjusted based on the input file data.
For the IRDA pack, we took a different approach by providing a template file as it needed a connectivity with the generated MIS file. Also, it provides a flexibility to pick data based on Monthly/qtrly/yearly format for each sheet. Subtotal / Total rows in each sheet will retain the formulas to allow for easy updating in the output files for any miscellaneous changes.
The entire process of picking the data from the input files and generating the output files was controlled using VBA code. The generation time for creating each file was reduced to less than 30 sec from few hours/days earlier with zero errors.