We do the math, So you
can do what matters
can do what matters
Go from Data to Decision in a snap
Integrate with almost any data source and deploy hassle-free automations with SheetKraft.
Integrate with almost any data source and deploy hassle-free automations with SheetKraft.
Reconciliation Processing
Millions of transactions now reconciled within a few minutes
From 8-10 hours to less than 15 Minutes:
Reconciliation between transaction data and bank statements
Reconciliation between transaction data and bank statements
1 week to 30 minutes per month:
Reconciliation between brokerage sent by R&T agent and brokerage firms
Reconciliation between brokerage sent by R&T agent and brokerage firms
1 man year to few minutes:
Reconciliation between purchase order and invoices raised by the vendor
Reconciliation between purchase order and invoices raised by the vendor
- 12 man hours to 30 minutes per day: Bank reconciliation with 40 banks
- 120 man-days annual savings: PMS brokerage reconciliation with CAMS
- 8 man-hours to 3 minutes a day: Purchase order invoice reconciliation
Regulatory Reporting
Stay up-to-date with frequent regulatory requirements with 100% accuracy
- 6 Man-Hours per day to Few Seconds: High-Risk Regulatory Reports at Primary Dealership - PDR I, II, III & IV
- 10 Man-Hours per day to Few Minutes: Capital Provision for Unhedged Foreign Currency Exchange Reports at Banks
- 4 Man-Hours per day to Few Minutes: TER Change Reports in Asset Management
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Objective:
Merchant Discount Rate (MDR) is deducted for every transaction done using the payment gateway’s service. But as per schemes initiated by the bank, merchants who satisfy the criteria are eligible for reversal of the entire MDR deducted earlier on a monthly basis. This means that the net MDR deducted is zero. Hence, the name Zero MDR.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
Merchant Discount Rate (MDR) is deducted for every transaction done using the payment gateway’s service. But as per schemes initiated by the bank, merchants who satisfy the criteria are eligible for reversal of the entire MDR deducted earlier on a monthly basis. This means that the net MDR deducted is zero. Hence, the name Zero MDR.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
Before SheetKraft:
- The operations team was dependent on the central DB team to extract data from various database tables.
- Data is extracted from table 1 by specifying some eligibility criteria in the where clause of SQL query. After data from table 1 is extracted, it is passed in some other SQL query and executed again. This is done 7 to 8 times to arrive at the final dataset of eligible merchants. Extracting this data from the database tables is quite a tedious task as it takes a very long time for the queries to execute.
- After fetching the data, the team had to apply various checks & filter conditions in order to generate all the reports. Since the transaction volume is large the team faces issues in generating the files since they’re dependent on excel.
SheetKraft’s Approach:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
- The activity will autorun at a specific date and at a specific time every month which is pre-defined by the business team.
- The process defined using SheetKraft will extract data from the various database tables and perform multiple calculations & checks along with graphical formatting of the output data.
- The output file which consists of the list of eligible and ineligible merchants for MDR cashback will be shared with the business team and the TTUM file which is generated will be directly uploaded in the Finacle system & shared with stakeholders via email.
- Emails will be sent to eligible as well as ineligible merchants with details of transactions for which they’ll be getting the MDR cashback or the file with details of transactions & appropriate rejection remarks due to which cashback was not given.
Overall Impact:
- Manual process involves high risk and high chances of error i.e., data being misplaced or some formulas with incorrect references might be used which is eliminated after onboarding SheetKraft.
- The operations team required around three days per month for the report generation but after onboarding SheetKraft it can be generated in 30 minutes.
- This in turn saves a good amount of time since no manual intervention is needed and also eliminates the risk of errors.
- Reports with summarized details of transactions and timely processing of MDR cashback encouraged merchants to accept various digital payments and this, in turn, increased transaction volumes for the payment gateway.
Financial Modelling
Customised Solutions for Complex Calculations
General Ledger Reconciliation - Completely eliminates the dependency of excel files for storing a large amount of PO data that consists of data inconsistencies.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
These are some of the significant problems with manual general ledger reconciliation:
- Huge Data Volume – A large amount of data saved in excel slows down the process and takes a lot of time to generate the desired output.
- Varied Dataset – The data is stored in different files having multiple formats, making it exhausting to compile or compare.
- Complex transaction relations – Due to complicated internal processes and various data sources and formats, transaction relations turn into a labyrinth – one-to-one, one-to-many, many-to-many.
Dealing with such cases involves sophisticated logic.
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Our team quickly created a plan to build a customized application for their general ledger reconciliation process - one that would give them the much-needed automation features and flexibility.
The impact that SheetKraft had was to considerably reduce the overall time spent on this by our client.
Brokerage and Commission Calculation
Access Information Quickly whether as an Email, Dashboard
or PDF Reports
or PDF Reports
- More than 3 Hours to Less than 3 Minutes per Day: Reduction in Manual Effort using Fast and Accurate Performance Dashboards
- 2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
- 100% No Manual Errors
- 8 Hours to 15-30 Minutes: Entire End-to-End Process of the Delegation of Power Systems
Operational Reporting
Effective, Results-Driven, Complete Reporting
24 Man-Hours to 15 Minutes Monthly: Total Effort Saving in Claims Processing
Claims are processed on the completion of the said event for which the policy was initially bought such as death or illness. Claims are tagged as pending, closed, rejected, or approved based on their status and claim request reason. Only approved claims are taken forward for processing, and the claim amount is decided by a set of predetermined rules set by the IRDAI. These rules differ based on the type of ownership, industry, age, and other demographics. IRDAI guidelines require insurance companies to settle the claims within 30 days of claim approval.
Challenge
- Manual processing claims within a fixed period (generally 3 days) for insurance companies is challenging.
- As rule sets differ for policies, the accurate calculation of the claim amount is prone to errors.
Before SheetKraft
Data dumps containing the list of claimed policies, claim amounts, and claim reasons are used to create the report. After creation, the same is sent to IRDAI to check for probable errors. Any errors highlighted by IRDAI are to be corrected within 3 days. Manual processing of policies is error-prone, and this often leads to multiple to and fro between IRDAI and the insurance company.
The SheetKraft Methodology
- The data dumps containing the list of claims requested are fetched by the system. Policies are tagged as pending, approved, closed, and rejected based on their status and request reasons.
- Predefined sets of logic for each policy category are fetched, and calculations regarding claim amount, Industry type, and MFI Non-MFI tag is performed.
- Policies are then collated to create a consolidated report ready to be used by the stakeholders.
After Sheetkraft Automation
SheetKraft automated reports are accurate, thus reducing the multiple to and fro between IRDAI and the insurance company to correct any probable errors.
Impact of SheetKraft
Sheetkraft has been able to successfully create the application to generate accurate Monthly Reports in a scheduled, hassle-free manner.
Total Effort Savings: From 24 man-hours monthly to 15 min per month.
2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
Objective
The basic objective of Budget Utilization Automation is to provide an end-to-end automated solution to generate on-demand interactive dashboards and detailed reports of the utilization of each budget code at individual budget owners, teams and organization levels.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
Before SheetKraft
- All the data, required for generating these reports at the organizational level and for different teams, is spread across various tables in the spend management software. This data includes POs (purchase order) raised against an expense, invoices received for all services availed and payments made from each budget code. Users must manually fetch this data from multiple tables and then structure it in a standardized format.
- After that, the user calculates the Allocated Amount, PO Amount, Utility Amount, Consumed Amount, Unconsumed Amount and Invoice Amount for the organisational level and each team/department level.
- Processing complexities include many invoices raised against a single PO (Purchase Order), payment made against an invoice without a PO issued etc., which is easily missed by users in manual calculations.
- Due to extensive data, it was challenging to gather the data from multiple sources, structure it in a standardised format and perform analysis in excel.
- This process is performed only once a month, but it takes 1-2 days to complete.
After SheetKraft
- Using SheetKraft, all the data required for generating these reports and dashboards are directly fetched from the spend management software (Expensing DB).
- The whole process is set as per pre-defined rules and logic provided by the business users and is hosted on a web UI
- It hardly takes any time to generate the required reports.
- Business users are given access to the SheetKraft UI, and with a click of a button, users can generate these reports on demand.
- The manual process was time-consuming, but after using SheetKraft, the users can get the budget utilisation reports within minutes.
Overall Impact of Automation
- Dependence on different teams to generate an organisation-level utilisation dashboard removed with SheetKraft automation.
- Monthly 2 man-days of efforts reduced to a simple click of a button.
- Any chances of manual errors in computation and validation checks are eliminated.
3 Hours to 7-10 minutes: Analysis of Credit Limit Decrease
Why is Credit Limit Decrease important?
The limit decrease will save the lender from monetary downfall. Lenders make the limit decrease often by analyzing the client’s credit reports and scores. Therefore timely reviews on customer’s credit history will eliminate financial risks.
Steps used in executing the process manually:
- Data collation : The information such as credit history data, current balance and specific card issued along with their relative limits allocated is received from various sources and combined together for further calculation.
- Data computation : Checks and validations are applied as per the company’s predefined limit policies and the latest limit values are provided.
- Latest limit upload : Currently approved limit values for all users are stored and updated in the bank system for the bank’s review.
Problems with Manual process
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
- Data discrepancies : Exact figures are required for bank reviews, errors in numbers will surely mislead interpretation.
- Heavy data volume : Processing time is directly proportional to the amount of data. More the data, more the consumption.
- Varied dataset : Data from different sources do not follow a specific structure. Data structuring takes time and effort.
Problems with Manual process
- Automated Credit Limit Decrease process got rid of all the difficulties faced by the bank, also making it resource-free, error-free, and faster.
- Huge amount of data can be stored in the database and the latest data can be retrieved when required.
- Personalized solutions were provided leading to data interpretation cut-off from the bank’s end.
How did SheetKraft help with automation?
The customized application was built satisfying the much-required automation as well many other features. One of those was graph reports of each customer showcasing their usage history of credit cards. These reports were auto-mailed to the senior management, which was used for a better view as compared to figurative data outputs produced via a manual process
The customized application was built satisfying the much-required automation as well many other features. One of those was graph reports of each customer showcasing their usage history of credit cards. These reports were auto-mailed to the senior management, which was used for a better view as compared to figurative data outputs produced via a manual process
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Limit Monitoring
Generate Exposure Reports Related to any Breaches and Trends
100% Error-free Daily Reports:
Total Expense Ratio Reconciliation
Total Expense Ratio Reconciliation
Total Expense Ratio – A Case Study
One of our leading AMC clients was reconciling and updating the Total Expense Ratio Reports manually. They often had to rework their reports because of a high error-rate. Fixing those errors took a major chunk of their time.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
The Total Expense Ratio Manual Process
As per the SEBI guidelines, AMCs and mutual fund houses must update their Total Expense Ratio daily. This ratio is then to be updated on their website, which means even a marginal difference needs to be updated. This is either done in-house by the AMC/mutual fund house or by a third-party vendor.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values for today are reconciled with values for all the previous dates from the start of the month. Data points such as validation checks, matured schemes, and new schemes are reconciled. Any difference in previous data needs to be reported and forwarded to the concerned AMC departments.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
Automatic Total Expense Ratio Reporting With SheetKraft
After process logic is fed into SheetKraft’s reconciliation module, the process has become extremely easy.
Once the Total Expense Ratio report is received from the AMC’s in-house team or third-party vendor is received via an email. SheetKraft automatically identifies the email. This is done using unique keywords set as the subject line of the email.
The attached Total Expense Ratio report is automatically is downloaded. The database values are simultaneously pulled and SheetKraft compares these values.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
Once the report is approved, SheetKraft automatically uploads the Total Expense Ratio report on the website.
Data Repository
Consolidate Data from Across Departments to a Single Location
2 weeks to 2 minutes:
Brokerage Statement Downloads to Data Warehouse
Brokerage Statement Downloads to Data Warehouse
The process for downloading brokerage statement involves CAMS sending a link to download account statement which is valid only for 15 days. If distributor forgets to download the report within the expiry date, then distributor reaches out to the Relationship Manager to get the account statement by forwarding their request to CAMS. Relationship manager then reaches out to CAMS for the link against the specific distributor. CAMS take a turnaround time of around 4 days to mail the file to the broker.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Solution designed by SheetKraft
CAMS send the link to distributors; this is copied to a generic email id and SheetKraft picks up these emails and downloads the pdfs from the links and stores them in the client’s data warehouse.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Use Cases
Reconciliation Processing
Millions of transactions now reconciled within a few minutes
From 8-10 hours to less than 15 Minutes:
Reconciliation between transaction data and bank statements
Reconciliation between transaction data and bank statements
1 week to 30 minutes per month:
Reconciliation between brokerage sent by R&T agent and brokerage firms
Reconciliation between brokerage sent by R&T agent and brokerage firms
1 man year to few minutes:
Reconciliation between purchase order and invoices raised by the vendor
Reconciliation between purchase order and invoices raised by the vendor
- 12 man hours to 30 minutes per day: Bank reconciliation with 40 banks
- 120 man-days annual savings: PMS brokerage reconciliation with CAMS
- 8 man-hours to 3 minutes a day: Purchase order invoice reconciliation
Regulatory Reporting
Stay up-to-date with frequent regulatory requirements with 100% accuracy
- 6 Man-Hours per day to Few Seconds: High-Risk Regulatory Reports at Primary Dealership - PDR I, II, III & IV
- 10 Man-Hours per day to Few Minutes: Capital Provision for Unhedged Foreign Currency Exchange Reports at Banks
- 4 Man-Hours per day to Few Minutes: TER Change Reports in Asset Management
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Objective:
Merchant Discount Rate (MDR) is deducted for every transaction done using the payment gateway’s service. But as per schemes initiated by the bank, merchants who satisfy the criteria are eligible for reversal of the entire MDR deducted earlier on a monthly basis. This means that the net MDR deducted is zero. Hence, the name Zero MDR.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
Before SheetKraft:
- The operations team was dependent on the central DB team to extract data from various database tables.
- Data is extracted from table 1 by specifying some eligibility criteria in the where clause of SQL query. After data from table 1 is extracted, it is passed in some other SQL query and executed again. This is done 7 to 8 times to arrive at the final dataset of eligible merchants. Extracting this data from the database tables is quite a tedious task as it takes a very long time for the queries to execute.
- After fetching the data, the team had to apply various checks & filter conditions in order to generate all the reports. Since the transaction volume is large the team faces issues in generating the files since they’re dependent on excel.
SheetKraft’s Approach:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
- The activity will autorun at a specific date and at a specific time every month which is pre-defined by the business team.
- The process defined using SheetKraft will extract data from the various database tables and perform multiple calculations & checks along with graphical formatting of the output data.
- The output file which consists of the list of eligible and ineligible merchants for MDR cashback will be shared with the business team and the TTUM file which is generated will be directly uploaded in the Finacle system & shared with stakeholders via email.
- Emails will be sent to eligible as well as ineligible merchants with details of transactions for which they’ll be getting the MDR cashback or the file with details of transactions & appropriate rejection remarks due to which cashback was not given.
Overall Impact:
- Manual process involves high risk and high chances of error i.e., data being misplaced or some formulas with incorrect references might be used which is eliminated after onboarding SheetKraft.
- The operations team required around three days per month for the report generation but after onboarding SheetKraft it can be generated in 30 minutes.
- This in turn saves a good amount of time since no manual intervention is needed and also eliminates the risk of errors.
- Reports with summarized details of transactions and timely processing of MDR cashback encouraged merchants to accept various digital payments and this, in turn, increased transaction volumes for the payment gateway.
Financial Modelling
Customised Solutions for Complex Calculations
General Ledger Reconciliation - Completely eliminates the dependency of excel files for storing a large amount of PO data that consists of data inconsistencies.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
These are some of the significant problems with manual general ledger reconciliation:
- Huge Data Volume – A large amount of data saved in excel slows down the process and takes a lot of time to generate the desired output.
- Varied Dataset – The data is stored in different files having multiple formats, making it exhausting to compile or compare.
- Complex transaction relations – Due to complicated internal processes and various data sources and formats, transaction relations turn into a labyrinth – one-to-one, one-to-many, many-to-many.
Dealing with such cases involves sophisticated logic.
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Our team quickly created a plan to build a customized application for their general ledger reconciliation process - one that would give them the much-needed automation features and flexibility.
The impact that SheetKraft had was to considerably reduce the overall time spent on this by our client.
Brokerage and Commission Calculation
Access Information Quickly whether as an Email, Dashboard
or PDF Reports
or PDF Reports
- More than 3 Hours to Less than 3 Minutes per Day: Reduction in Manual Effort using Fast and Accurate Performance Dashboards
- 2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
- 100% No Manual Errors
- 8 Hours to 15-30 Minutes: Entire End-to-End Process of the Delegation of Power Systems
Operational Reporting
Effective, Results-Driven, Complete Reporting
24 Man-Hours to 15 Minutes Monthly: Total Effort Saving in Claims Processing
Claims are processed on the completion of the said event for which the policy was initially bought such as death or illness. Claims are tagged as pending, closed, rejected, or approved based on their status and claim request reason. Only approved claims are taken forward for processing, and the claim amount is decided by a set of predetermined rules set by the IRDAI. These rules differ based on the type of ownership, industry, age, and other demographics. IRDAI guidelines require insurance companies to settle the claims within 30 days of claim approval.
Challenge
- Manual processing claims within a fixed period (generally 3 days) for insurance companies is challenging.
- As rule sets differ for policies, the accurate calculation of the claim amount is prone to errors.
Before SheetKraft
Data dumps containing the list of claimed policies, claim amounts, and claim reasons are used to create the report. After creation, the same is sent to IRDAI to check for probable errors. Any errors highlighted by IRDAI are to be corrected within 3 days. Manual processing of policies is error-prone, and this often leads to multiple to and fro between IRDAI and the insurance company.
The SheetKraft Methodology
- The data dumps containing the list of claims requested are fetched by the system. Policies are tagged as pending, approved, closed, and rejected based on their status and request reasons.
- Predefined sets of logic for each policy category are fetched, and calculations regarding claim amount, Industry type, and MFI Non-MFI tag is performed.
- Policies are then collated to create a consolidated report ready to be used by the stakeholders.
After Sheetkraft Automation
SheetKraft automated reports are accurate, thus reducing the multiple to and fro between IRDAI and the insurance company to correct any probable errors.
Impact of SheetKraft
Sheetkraft has been able to successfully create the application to generate accurate Monthly Reports in a scheduled, hassle-free manner.
Total Effort Savings: From 24 man-hours monthly to 15 min per month.
2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
Objective
The basic objective of Budget Utilization Automation is to provide an end-to-end automated solution to generate on-demand interactive dashboards and detailed reports of the utilization of each budget code at individual budget owners, teams and organization levels.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
Before SheetKraft
- All the data, required for generating these reports at the organizational level and for different teams, is spread across various tables in the spend management software. This data includes POs (purchase order) raised against an expense, invoices received for all services availed and payments made from each budget code. Users must manually fetch this data from multiple tables and then structure it in a standardized format.
- After that, the user calculates the Allocated Amount, PO Amount, Utility Amount, Consumed Amount, Unconsumed Amount and Invoice Amount for the organisational level and each team/department level.
- Processing complexities include many invoices raised against a single PO (Purchase Order), payment made against an invoice without a PO issued etc., which is easily missed by users in manual calculations.
- Due to extensive data, it was challenging to gather the data from multiple sources, structure it in a standardised format and perform analysis in excel.
- This process is performed only once a month, but it takes 1-2 days to complete.
After SheetKraft
- Using SheetKraft, all the data required for generating these reports and dashboards are directly fetched from the spend management software (Expensing DB).
- The whole process is set as per pre-defined rules and logic provided by the business users and is hosted on a web UI
- It hardly takes any time to generate the required reports.
- Business users are given access to the SheetKraft UI, and with a click of a button, users can generate these reports on demand.
- The manual process was time-consuming, but after using SheetKraft, the users can get the budget utilisation reports within minutes.
Overall Impact of Automation
- Dependence on different teams to generate an organisation-level utilisation dashboard removed with SheetKraft automation.
- Monthly 2 man-days of efforts reduced to a simple click of a button.
- Any chances of manual errors in computation and validation checks are eliminated.
3 Hours to 7-10 minutes: Analysis of Credit Limit Decrease
Why is Credit Limit Decrease important?
The limit decrease will save the lender from monetary downfall. Lenders make the limit decrease often by analyzing the client’s credit reports and scores. Therefore timely reviews on customer’s credit history will eliminate financial risks.
Steps used in executing the process manually:
- Data collation : The information such as credit history data, current balance and specific card issued along with their relative limits allocated is received from various sources and combined together for further calculation.
- Data computation : Checks and validations are applied as per the company’s predefined limit policies and the latest limit values are provided.
- Latest limit upload : Currently approved limit values for all users are stored and updated in the bank system for the bank’s review.
Problems with Manual process
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
- Data discrepancies : Exact figures are required for bank reviews, errors in numbers will surely mislead interpretation.
- Heavy data volume : Processing time is directly proportional to the amount of data. More the data, more the consumption.
- Varied dataset : Data from different sources do not follow a specific structure. Data structuring takes time and effort.
Problems with Manual process
- Automated Credit Limit Decrease process got rid of all the difficulties faced by the bank, also making it resource-free, error-free, and faster.
- Huge amount of data can be stored in the database and the latest data can be retrieved when required.
- Personalized solutions were provided leading to data interpretation cut-off from the bank’s end.
How did SheetKraft help with automation?
The customized application was built satisfying the much-required automation as well many other features. One of those was graph reports of each customer showcasing their usage history of credit cards. These reports were auto-mailed to the senior management, which was used for a better view as compared to figurative data outputs produced via a manual process
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Limit Monitoring
Generate Exposure Reports Related to any Breaches and Trends
100% Error-free Daily Reports:
Total Expense Ratio Reconciliation
Total Expense Ratio Reconciliation
Total Expense Ratio – A Case Study
One of our leading AMC clients was reconciling and updating the Total Expense Ratio Reports manually. They often had to rework their reports because of a high error-rate. Fixing those errors took a major chunk of their time.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
The Total Expense Ratio Manual Process
As per the SEBI guidelines, AMCs and mutual fund houses must update their Total Expense Ratio daily. This ratio is then to be updated on their website, which means even a marginal difference needs to be updated. This is either done in-house by the AMC/mutual fund house or by a third-party vendor.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values for today are reconciled with values for all the previous dates from the start of the month. Data points such as validation checks, matured schemes, and new schemes are reconciled. Any difference in previous data needs to be reported and forwarded to the concerned AMC departments.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
Automatic Total Expense Ratio Reporting With SheetKraft
After process logic is fed into SheetKraft’s reconciliation module, the process has become extremely easy.
Once the Total Expense Ratio report is received from the AMC’s in-house team or third-party vendor is received via an email. SheetKraft automatically identifies the email. This is done using unique keywords set as the subject line of the email.
The attached Total Expense Ratio report is automatically is downloaded. The database values are simultaneously pulled and SheetKraft compares these values.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
Once the report is approved, SheetKraft automatically uploads the Total Expense Ratio report on the website.
Data Repository
Consolidate Data from Across Departments to a Single Location
2 weeks to 2 minutes:
Brokerage Statement Downloads to Data Warehouse
Brokerage Statement Downloads to Data Warehouse
The process for downloading brokerage statement involves CAMS sending a link to download account statement which is valid only for 15 days. If distributor forgets to download the report within the expiry date, then distributor reaches out to the Relationship Manager to get the account statement by forwarding their request to CAMS. Relationship manager then reaches out to CAMS for the link against the specific distributor. CAMS take a turnaround time of around 4 days to mail the file to the broker.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Solution designed by SheetKraft
CAMS send the link to distributors; this is copied to a generic email id and SheetKraft picks up these emails and downloads the pdfs from the links and stores them in the client’s data warehouse.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Reporting in Multiple Formats
Access Information Quickly whether as an Email, Dashboard or PDF Reports
More than 3 Hours to Less than 3 Minutes per Day:
Reduction in Manual Effort using Fast and Accurate Performance Dashboards
Reduction in Manual Effort using Fast and Accurate Performance Dashboards
A prominent Life Insurance client creates a consolidated policy details file for due policies at a branch level monthly. This dataset is used to create branch, region and zone level dashboards for daily tracking of % of targets achieved and strike rate % which are integral in assessing the performance and efficiency of branches.
These daily reports reflect the expected monthly dues, the target set for a particular branch and the % of the target achieved as well as the % of total expected dues collected. These reports highlight the branches that have a below-average achieved % and strike rate so that measures can be taken to improve the performance of these branches.
These daily reports reflect the expected monthly dues, the target set for a particular branch and the % of the target achieved as well as the % of total expected dues collected. These reports highlight the branches that have a below-average achieved % and strike rate so that measures can be taken to improve the performance of these branches.
Challenge
- These dashboards are created and mailed to the Branch heads daily. The timely and accurate calculation and tracking of these performance metrics is a difficult task.
- The complexity of the dashboards combined with the processing of the huge set of raw data makes the whole process prone to manual errors.
Methodology
- Mapping of monthly targets basis zone, region, and the branch to the consolidated file containing details of due policies.
- Identification of policies whose premium was collected daily.
- Creation of zone, region, and branch level dashboards to display metrics as total dues expected, target set, dues collected, target achieved %, and strike rate %.
- Storage of premium payment(revival) history of policies daily.
- Mail notifications to Branch heads of underperforming branches.
Impact of SheetKraft
- Maintenance of a centralized database of premium payment data.
- The system has driven a fast and accurate generation of performance dashboards.
- Identification of underperforming branches and daily notification to the respective branch heads.
- Reduction in manual effort from >3 hours/day to <3 mins/day.
2 Man-Days Monthly to a Click of a Button:
Budget Utilisation Reports on one dashboard
Budget Utilisation Reports on one dashboard
Objective
The basic objective of Budget Utilization Automation is to provide an end-to-end automated solution to generate on-demand interactive dashboards and detailed reports of the utilization of each budget code at individual budget owners, teams and organization levels.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The aging dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the aging dashboard monthly will help to identify the problems before they become a cash-flow crunch to business. This is used in Asset management companies.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The aging dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the aging dashboard monthly will help to identify the problems before they become a cash-flow crunch to business. This is used in Asset management companies.
Before SheetKraft
- All the data, required for generating these reports at the organizational level and for different teams, is spread across various tables in the spend management software. This data includes POs (purchase order) raised against an expense, invoices received for all services availed and payments made from each budget code. Users must manually fetch this data from multiple tables and then structure it in a standardized format.
- After that, the user calculates the Allocated Amount, PO Amount, Utility Amount, Consumed Amount, Unconsumed Amount and Invoice Amount for organizational level and each team/department level.
- Processing complexities include many invoices raised against a single PO (Purchase Order), payment made against an invoice without a PO issued etc., which is easily missed by users in manual calculations.
- Due to large data, it was difficult to gather the data from multiple sources, structure it in a standardized format and perform analysis in excel.
- This process is performed only once a month, but it took 1-2 days to complete.
After SheetKraft
- Using SheetKraft, all the data required for generating these reports and dashboards are directly fetched from the spend management software (Expensing DB).
- The whole process is set as per pre-defined rules and logic provided by the business users and is hosted on a web UI
- It hardly takes any time to generate the required reports.
- Business users are given access to the SheetKraft UI and with a click of a button, users can generate these reports on demand.
- The manual process was time-consuming but after using SheetKraft the users can get the budget utilization reports within minutes.
Overall Impact of Automation
- Dependence on different teams to generate organization level utilization dashboard removed with SheetKraft automation.
- Monthly 2 man-days of efforts reduced to a simple click of a button.
- Any chances of manual errors in computation and validation checks are eliminated.
8 Hours to 15-30 Minutes:
Entire End-to-End Process of the Delegation of Power Systems
Entire End-to-End Process of the Delegation of Power Systems
A human resources management system (HRMS) is a form of software application used by businesses that combines a number of systems and processes to ensure the easy management of human resources, business processes and data. Few examples of these functions are storing employee data, managing payroll, recruitment, employee performance management, etc.
The main objective of this process is the management of officials in the bank and the powers allotted to them along with handling the financial and economic risks at the wider level.
The main objective of this process is the management of officials in the bank and the powers allotted to them along with handling the financial and economic risks at the wider level.
Problem Statement
- The idea behind the Delegation of Power (DOP) system is that there has to be a single official in the bank (or a board of directors) who is responsible to approve proposals or delegate powers to responsible officials for exercising the right to approve proposals for making investments loans incurring expenditure discounting receivables, etc.
- This process includes a cumbersome amount of data discrepancies when maintained manually since the number of employees is large and the hierarchy level is complex. If not maintained properly, it can directly affect the grading system of the bank.
- Involved resources need an ample amount of time for file preparation as data is discrete and distributed.
- If required inputs (details regarding delegation) are not received on a timely basis then it leads to delays in official tasks & can have a domino effect on financials.
Before SheetKraft
End-to-end processing of this process requires the intervention of three teams as follows:
End-to-end processing of this process requires the intervention of three teams as follows:
- IT team acquires data of employee details information from HRMS DB on a daily basis and sends this file to VMT.
- VMT (Vendor Management Team) receives mail requests of employees from any retail /corporate branch of the bank for ID creation/modification/rectification. VMT team compares the new data with the old data for analyzing changes that occurred in the employee hierarchy. Based on the HRMS DB output, DOP creation is initiated for the file with the latest requests.
- The operations team on a monthly basis provides master dump data to the VMT team. This dump contains information about different level heads and power limits allocated as per employee job profile. VMT team uses these files for DOP creation.
- Since the data received from various employees is confidential, the IT team makes cryptographic codes for the outputs using encryption software. And then the VMT team uploads encrypted outputs onto the HR system of the bank.
After SheetKraft
- SheetKraft extracts the employee data from HRMS DB and master data from SheetKraft DB for processing of HRMS & DOP outputs.
- Activity logic is set as per pre-defined rules provided by VMT users using SheetKraft.
- After applying checks & calculations as per pre-defined logic SheetKraft prepares encrypted outputs as well uploads them in the HR system.
- Using SheetKraft, the entire end-to-end process is completed in 15-30 minutes whereas the manual process used to take around 8 hours.
Overall Impact
- Manual hindrance for performing the check conditions has been automated leading to no manual errors.
- As end-to-end activity run is scheduled for auto-run on a daily basis, user dependence is extinct.
Use Cases
Reconciliation Processing
Reconciliation Processing
Millions of transactions now reconciled within a few minutes
From 8-10 hours to less than 15 Minutes:
Reconciliation between transaction data and bank statements
Reconciliation between transaction data and bank statements
1 week to 30 minutes per month:
Reconciliation between brokerage sent by R&T agent and brokerage firms
Reconciliation between brokerage sent by R&T agent and brokerage firms
1 man year to few minutes:
Reconciliation between purchase order and invoices raised by the vendor
Reconciliation between purchase order and invoices raised by the vendor
- 12 man hours to 30 minutes per day: Bank reconciliation with 40 banks
- 120 man-days annual savings: PMS brokerage reconciliation with CAMS
- 8 man-hours to 3 minutes a day: Purchase order invoice reconciliation
Regulatory Reporting
Regulatory Reporting
Stay up-to-date with frequent regulatory requirements with 100% accuracy
- 6 Man-Hours per day to Few Seconds: High-Risk Regulatory Reports at Primary Dealership - PDR I, II, III & IV
- 10 Man-Hours per day to Few Minutes: Capital Provision for Unhedged Foreign Currency Exchange Reports at Banks
- 4 Man-Hours per day to Few Minutes: TER Change Reports in Asset Management
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Objective:
Merchant Discount Rate (MDR) is deducted for every transaction done using the payment gateway’s service. But as per schemes initiated by the bank, merchants who satisfy the criteria are eligible for reversal of the entire MDR deducted earlier on a monthly basis. This means that the net MDR deducted is zero. Hence, the name Zero MDR.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
Before SheetKraft:
- The operations team was dependent on the central DB team to extract data from various database tables.
- Data is extracted from table 1 by specifying some eligibility criteria in the where clause of SQL query. After data from table 1 is extracted, it is passed in some other SQL query and executed again. This is done 7 to 8 times to arrive at the final dataset of eligible merchants. Extracting this data from the database tables is quite a tedious task as it takes a very long time for the queries to execute.
- After fetching the data, the team had to apply various checks & filter conditions in order to generate all the reports. Since the transaction volume is large the team faces issues in generating the files since they’re dependent on excel.
SheetKraft’s Approach:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
- The activity will autorun at a specific date and at a specific time every month which is pre-defined by the business team.
- The process defined using SheetKraft will extract data from the various database tables and perform multiple calculations & checks along with graphical formatting of the output data.
- The output file which consists of the list of eligible and ineligible merchants for MDR cashback will be shared with the business team and the TTUM file which is generated will be directly uploaded in the Finacle system & shared with stakeholders via email.
- Emails will be sent to eligible as well as ineligible merchants with details of transactions for which they’ll be getting the MDR cashback or the file with details of transactions & appropriate rejection remarks due to which cashback was not given.
Overall Impact:
- Manual process involves high risk and high chances of error i.e., data being misplaced or some formulas with incorrect references might be used which is eliminated after onboarding SheetKraft.
- The operations team required around three days per month for the report generation but after onboarding SheetKraft it can be generated in 30 minutes.
- This in turn saves a good amount of time since no manual intervention is needed and also eliminates the risk of errors.
- Reports with summarized details of transactions and timely processing of MDR cashback encouraged merchants to accept various digital payments and this, in turn, increased transaction volumes for the payment gateway.
Financial Modelling
Financial Modelling
Customised Solutions for Complex Calculations
General Ledger Reconciliation - Completely eliminates the dependency of excel files for storing a large amount of PO data that consists of data inconsistencies.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
These are some of the significant problems with manual general ledger reconciliation:
- Huge Data Volume – A large amount of data saved in excel slows down the process and takes a lot of time to generate the desired output.
- Varied Dataset – The data is stored in different files having multiple formats, making it exhausting to compile or compare.
- Complex transaction relations – Due to complicated internal processes and various data sources and formats, transaction relations turn into a labyrinth – one-to-one, one-to-many, many-to-many.
Dealing with such cases involves sophisticated logic.
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Our team quickly created a plan to build a customized application for their general ledger reconciliation process - one that would give them the much-needed automation features and flexibility.
The impact that SheetKraft had was to considerably reduce the overall time spent on this by our client.
Brokerage and Commission Calculation
Brokerage and Commission Calculation
Access Information Quickly whether as an Email, Dashboard
or PDF Reports
or PDF Reports
- More than 3 Hours to Less than 3 Minutes per Day: Reduction in Manual Effort using Fast and Accurate Performance Dashboards
- 2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
- 100% No Manual Errors
- 8 Hours to 15-30 Minutes: Entire End-to-End Process of the Delegation of Power Systems
Operational Reporting
Operational Reporting
Effective, Results-Driven, Complete Reporting
24 Man-Hours to 15 Minutes Monthly: Total Effort Saving in Claims Processing
Claims are processed on the completion of the said event for which the policy was initially bought such as death or illness. Claims are tagged as pending, closed, rejected, or approved based on their status and claim request reason. Only approved claims are taken forward for processing, and the claim amount is decided by a set of predetermined rules set by the IRDAI. These rules differ based on the type of ownership, industry, age, and other demographics. IRDAI guidelines require insurance companies to settle the claims within 30 days of claim approval.
Challenge
- Manual processing claims within a fixed period (generally 3 days) for insurance companies is challenging.
- As rule sets differ for policies, the accurate calculation of the claim amount is prone to errors.
Before SheetKraft
Data dumps containing the list of claimed policies, claim amounts, and claim reasons are used to create the report. After creation, the same is sent to IRDAI to check for probable errors. Any errors highlighted by IRDAI are to be corrected within 3 days. Manual processing of policies is error-prone, and this often leads to multiple to and fro between IRDAI and the insurance company.
The SheetKraft Methodology
- The data dumps containing the list of claims requested are fetched by the system. Policies are tagged as pending, approved, closed, and rejected based on their status and request reasons.
- Predefined sets of logic for each policy category are fetched, and calculations regarding claim amount, Industry type, and MFI Non-MFI tag is performed.
- Policies are then collated to create a consolidated report ready to be used by the stakeholders.
After Sheetkraft Automation
SheetKraft automated reports are accurate, thus reducing the multiple to and fro between IRDAI and the insurance company to correct any probable errors.
Impact of SheetKraft
Sheetkraft has been able to successfully create the application to generate accurate Monthly Reports in a scheduled, hassle-free manner.
Total Effort Savings: From 24 man-hours monthly to 15 min per month.
2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
Objective
The basic objective of Budget Utilization Automation is to provide an end-to-end automated solution to generate on-demand interactive dashboards and detailed reports of the utilization of each budget code at individual budget owners, teams and organization levels.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
Before SheetKraft
- All the data, required for generating these reports at the organizational level and for different teams, is spread across various tables in the spend management software. This data includes POs (purchase order) raised against an expense, invoices received for all services availed and payments made from each budget code. Users must manually fetch this data from multiple tables and then structure it in a standardized format.
- After that, the user calculates the Allocated Amount, PO Amount, Utility Amount, Consumed Amount, Unconsumed Amount and Invoice Amount for the organisational level and each team/department level.
- Processing complexities include many invoices raised against a single PO (Purchase Order), payment made against an invoice without a PO issued etc., which is easily missed by users in manual calculations.
- Due to extensive data, it was challenging to gather the data from multiple sources, structure it in a standardised format and perform analysis in excel.
- This process is performed only once a month, but it takes 1-2 days to complete.
After SheetKraft
- Using SheetKraft, all the data required for generating these reports and dashboards are directly fetched from the spend management software (Expensing DB).
- The whole process is set as per pre-defined rules and logic provided by the business users and is hosted on a web UI
- It hardly takes any time to generate the required reports.
- Business users are given access to the SheetKraft UI, and with a click of a button, users can generate these reports on demand.
- The manual process was time-consuming, but after using SheetKraft, the users can get the budget utilisation reports within minutes.
Overall Impact of Automation
- Dependence on different teams to generate an organisation-level utilisation dashboard removed with SheetKraft automation.
- Monthly 2 man-days of efforts reduced to a simple click of a button.
- Any chances of manual errors in computation and validation checks are eliminated.
3 Hours to 7-10 minutes: Analysis of Credit Limit Decrease
Why is Credit Limit Decrease important?
The limit decrease will save the lender from monetary downfall. Lenders make the limit decrease often by analyzing the client’s credit reports and scores. Therefore timely reviews on customer’s credit history will eliminate financial risks.
Steps used in executing the process manually:
- Data collation : The information such as credit history data, current balance and specific card issued along with their relative limits allocated is received from various sources and combined together for further calculation.
- Data computation : Checks and validations are applied as per the company’s predefined limit policies and the latest limit values are provided.
- Latest limit upload : Currently approved limit values for all users are stored and updated in the bank system for the bank’s review.
Problems with Manual process
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
- Data discrepancies : Exact figures are required for bank reviews, errors in numbers will surely mislead interpretation.
- Heavy data volume : Processing time is directly proportional to the amount of data. More the data, more the consumption.
- Varied dataset : Data from different sources do not follow a specific structure. Data structuring takes time and effort.
Problems with Manual process
- Automated Credit Limit Decrease process got rid of all the difficulties faced by the bank, also making it resource-free, error-free, and faster.
- Huge amount of data can be stored in the database and the latest data can be retrieved when required.
- Personalized solutions were provided leading to data interpretation cut-off from the bank’s end.
How did SheetKraft help with automation?
The customized application was built satisfying the much-required automation as well many other features. One of those was graph reports of each customer showcasing their usage history of credit cards. These reports were auto-mailed to the senior management, which was used for a better view as compared to figurative data outputs produced via a manual process
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Limit Monitoring
Limit Monitoring
Generate Exposure Reports Related to any Breaches and Trends
100% Error-free Daily Reports:
Total Expense Ratio Reconciliation
Total Expense Ratio Reconciliation
Total Expense Ratio – A Case Study
One of our leading AMC clients was reconciling and updating the Total Expense Ratio Reports manually. They often had to rework their reports because of a high error-rate. Fixing those errors took a major chunk of their time.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
The Total Expense Ratio Manual Process
As per the SEBI guidelines, AMCs and mutual fund houses must update their Total Expense Ratio daily. This ratio is then to be updated on their website, which means even a marginal difference needs to be updated. This is either done in-house by the AMC/mutual fund house or by a third-party vendor.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values for today are reconciled with values for all the previous dates from the start of the month. Data points such as validation checks, matured schemes, and new schemes are reconciled. Any difference in previous data needs to be reported and forwarded to the concerned AMC departments.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
Automatic Total Expense Ratio Reporting With SheetKraft
After process logic is fed into SheetKraft’s reconciliation module, the process has become extremely easy.
Once the Total Expense Ratio report is received from the AMC’s in-house team or third-party vendor is received via an email. SheetKraft automatically identifies the email. This is done using unique keywords set as the subject line of the email.
The attached Total Expense Ratio report is automatically is downloaded. The database values are simultaneously pulled and SheetKraft compares these values.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
Once the report is approved, SheetKraft automatically uploads the Total Expense Ratio report on the website.
Data Repository
Data Repository
Consolidate Data from Across Departments to a Single Location
2 weeks to 2 minutes:
Brokerage Statement Downloads to Data Warehouse
Brokerage Statement Downloads to Data Warehouse
The process for downloading brokerage statement involves CAMS sending a link to download account statement which is valid only for 15 days. If distributor forgets to download the report within the expiry date, then distributor reaches out to the Relationship Manager to get the account statement by forwarding their request to CAMS. Relationship manager then reaches out to CAMS for the link against the specific distributor. CAMS take a turnaround time of around 4 days to mail the file to the broker.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Solution designed by SheetKraft
CAMS send the link to distributors; this is copied to a generic email id and SheetKraft picks up these emails and downloads the pdfs from the links and stores them in the client’s data warehouse.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Reporting in Multiple Formats
Reporting in Multiple Formats
Access Information Quickly whether as an Email, Dashboard or PDF Reports
More than 3 Hours to Less than 3 Minutes per Day:
Reduction in Manual Effort using Fast and Accurate Performance Dashboards
Reduction in Manual Effort using Fast and Accurate Performance Dashboards
A prominent Life Insurance client creates a consolidated policy details file for due policies at a branch level monthly. This dataset is used to create branch, region and zone level dashboards for daily tracking of % of targets achieved and strike rate % which are integral in assessing the performance and efficiency of branches.
These daily reports reflect the expected monthly dues, the target set for a particular branch and the % of the target achieved as well as the % of total expected dues collected. These reports highlight the branches that have a below-average achieved % and strike rate so that measures can be taken to improve the performance of these branches.
These daily reports reflect the expected monthly dues, the target set for a particular branch and the % of the target achieved as well as the % of total expected dues collected. These reports highlight the branches that have a below-average achieved % and strike rate so that measures can be taken to improve the performance of these branches.
Challenge
- These dashboards are created and mailed to the Branch heads daily. The timely and accurate calculation and tracking of these performance metrics is a difficult task.
- The complexity of the dashboards combined with the processing of the huge set of raw data makes the whole process prone to manual errors.
Methodology
- Mapping of monthly targets basis zone, region, and the branch to the consolidated file containing details of due policies.
- Identification of policies whose premium was collected daily.
- Creation of zone, region, and branch level dashboards to display metrics as total dues expected, target set, dues collected, target achieved %, and strike rate %.
- Storage of premium payment(revival) history of policies daily.
- Mail notifications to Branch heads of underperforming branches.
Impact of SheetKraft
- Maintenance of a centralized database of premium payment data.
- The system has driven a fast and accurate generation of performance dashboards.
- Identification of underperforming branches and daily notification to the respective branch heads.
- Reduction in manual effort from >3 hours/day to <3 mins/day.
2 Man-Days Monthly to a Click of a Button:
Budget Utilisation Reports on one dashboard
Budget Utilisation Reports on one dashboard
Objective
The basic objective of Budget Utilization Automation is to provide an end-to-end automated solution to generate on-demand interactive dashboards and detailed reports of the utilization of each budget code at individual budget owners, teams and organization levels.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The aging dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the aging dashboard monthly will help to identify the problems before they become a cash-flow crunch to business. This is used in Asset management companies.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The aging dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the aging dashboard monthly will help to identify the problems before they become a cash-flow crunch to business. This is used in Asset management companies.
Before SheetKraft
- All the data, required for generating these reports at the organizational level and for different teams, is spread across various tables in the spend management software. This data includes POs (purchase order) raised against an expense, invoices received for all services availed and payments made from each budget code. Users must manually fetch this data from multiple tables and then structure it in a standardized format.
- After that, the user calculates the Allocated Amount, PO Amount, Utility Amount, Consumed Amount, Unconsumed Amount and Invoice Amount for organizational level and each team/department level.
- Processing complexities include many invoices raised against a single PO (Purchase Order), payment made against an invoice without a PO issued etc., which is easily missed by users in manual calculations.
- Due to large data, it was difficult to gather the data from multiple sources, structure it in a standardized format and perform analysis in excel.
- This process is performed only once a month, but it took 1-2 days to complete.
After SheetKraft
- Using SheetKraft, all the data required for generating these reports and dashboards are directly fetched from the spend management software (Expensing DB).
- The whole process is set as per pre-defined rules and logic provided by the business users and is hosted on a web UI
- It hardly takes any time to generate the required reports.
- Business users are given access to the SheetKraft UI and with a click of a button, users can generate these reports on demand.
- The manual process was time-consuming but after using SheetKraft the users can get the budget utilization reports within minutes.
Overall Impact of Automation
- Dependence on different teams to generate organization level utilization dashboard removed with SheetKraft automation.
- Monthly 2 man-days of efforts reduced to a simple click of a button.
- Any chances of manual errors in computation and validation checks are eliminated.
8 Hours to 15-30 Minutes:
Entire End-to-End Process of the Delegation of Power Systems
Entire End-to-End Process of the Delegation of Power Systems
A human resources management system (HRMS) is a form of software application used by businesses that combines a number of systems and processes to ensure the easy management of human resources, business processes and data. Few examples of these functions are storing employee data, managing payroll, recruitment, employee performance management, etc.
The main objective of this process is the management of officials in the bank and the powers allotted to them along with handling the financial and economic risks at the wider level.
The main objective of this process is the management of officials in the bank and the powers allotted to them along with handling the financial and economic risks at the wider level.
Problem Statement
- The idea behind the Delegation of Power (DOP) system is that there has to be a single official in the bank (or a board of directors) who is responsible to approve proposals or delegate powers to responsible officials for exercising the right to approve proposals for making investments loans incurring expenditure discounting receivables, etc.
- This process includes a cumbersome amount of data discrepancies when maintained manually since the number of employees is large and the hierarchy level is complex. If not maintained properly, it can directly affect the grading system of the bank.
- Involved resources need an ample amount of time for file preparation as data is discrete and distributed.
- If required inputs (details regarding delegation) are not received on a timely basis then it leads to delays in official tasks & can have a domino effect on financials.
Before SheetKraft
End-to-end processing of this process requires the intervention of three teams as follows:
End-to-end processing of this process requires the intervention of three teams as follows:
- IT team acquires data of employee details information from HRMS DB on a daily basis and sends this file to VMT.
- VMT (Vendor Management Team) receives mail requests of employees from any retail /corporate branch of the bank for ID creation/modification/rectification. VMT team compares the new data with the old data for analyzing changes that occurred in the employee hierarchy. Based on the HRMS DB output, DOP creation is initiated for the file with the latest requests.
- The operations team on a monthly basis provides master dump data to the VMT team. This dump contains information about different level heads and power limits allocated as per employee job profile. VMT team uses these files for DOP creation.
- Since the data received from various employees is confidential, the IT team makes cryptographic codes for the outputs using encryption software. And then the VMT team uploads encrypted outputs onto the HR system of the bank.
After SheetKraft
- SheetKraft extracts the employee data from HRMS DB and master data from SheetKraft DB for processing of HRMS & DOP outputs.
- Activity logic is set as per pre-defined rules provided by VMT users using SheetKraft.
- After applying checks & calculations as per pre-defined logic SheetKraft prepares encrypted outputs as well uploads them in the HR system.
- Using SheetKraft, the entire end-to-end process is completed in 15-30 minutes whereas the manual process used to take around 8 hours.
Overall Impact
- Manual hindrance for performing the check conditions has been automated leading to no manual errors.
- As end-to-end activity run is scheduled for auto-run on a daily basis, user dependence is extinct.
Use Cases
Reconciliation Processing
Millions of transactions now reconciled within a few minutes
From 8-10 hours to less than 15 Minutes:
Reconciliation between transaction data and bank statements
Reconciliation between transaction data and bank statements
1 week to 30 minutes per month:
Reconciliation between brokerage sent by R&T agent and brokerage firms
Reconciliation between brokerage sent by R&T agent and brokerage firms
1 man year to few minutes:
Reconciliation between purchase order and invoices raised by the vendor
Reconciliation between purchase order and invoices raised by the vendor
- 12 man hours to 30 minutes per day: Bank reconciliation with 40 banks
- 120 man-days annual savings: PMS brokerage reconciliation with CAMS
- 8 man-hours to 3 minutes a day: Purchase order invoice reconciliation
Regulatory Reporting
Stay up-to-date with frequent regulatory requirements with 100% accuracy
- 6 Man-Hours per day to Few Seconds: High-Risk Regulatory Reports at Primary Dealership - PDR I, II, III & IV
- 10 Man-Hours per day to Few Minutes: Capital Provision for Unhedged Foreign Currency Exchange Reports at Banks
- 4 Man-Hours per day to Few Minutes: TER Change Reports in Asset Management
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Objective:
Merchant Discount Rate (MDR) is deducted for every transaction done using the payment gateway’s service. But as per schemes initiated by the bank, merchants who satisfy the criteria are eligible for reversal of the entire MDR deducted earlier on a monthly basis. This means that the net MDR deducted is zero. Hence, the name Zero MDR.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
MDR reversal vouchers and a summary report are to be prepared which shows the list of merchants that are eligible for MDR cashback as well as those merchants who are not eligible for the MDR Cashback with relevant rejection remarks. The reports are being generated on a monthly basis by the operations team. The whole process is complex since it involves data mapping from various sources & calculating the MDR reversal amount. If the MDR reversal amount is not calculated properly & the merchant is credited less or more than the actual amount then it can lead to poor customer service & financial discrepancies. The process takes the productive hours of the team to generate the reports. The client’s team who generates the reports proposed automation and chose SheetKraft as the automation partner in order to optimize the process.
Before SheetKraft:
- The operations team was dependent on the central DB team to extract data from various database tables.
- Data is extracted from table 1 by specifying some eligibility criteria in the where clause of SQL query. After data from table 1 is extracted, it is passed in some other SQL query and executed again. This is done 7 to 8 times to arrive at the final dataset of eligible merchants. Extracting this data from the database tables is quite a tedious task as it takes a very long time for the queries to execute.
- After fetching the data, the team had to apply various checks & filter conditions in order to generate all the reports. Since the transaction volume is large the team faces issues in generating the files since they’re dependent on excel.
SheetKraft’s Approach:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
This process is an end-to-end process and no human intervention is needed. Since various triggers are created using SheetKraft which are as follows:
- The activity will autorun at a specific date and at a specific time every month which is pre-defined by the business team.
- The process defined using SheetKraft will extract data from the various database tables and perform multiple calculations & checks along with graphical formatting of the output data.
- The output file which consists of the list of eligible and ineligible merchants for MDR cashback will be shared with the business team and the TTUM file which is generated will be directly uploaded in the Finacle system & shared with stakeholders via email.
- Emails will be sent to eligible as well as ineligible merchants with details of transactions for which they’ll be getting the MDR cashback or the file with details of transactions & appropriate rejection remarks due to which cashback was not given.
Overall Impact:
- Manual process involves high risk and high chances of error i.e., data being misplaced or some formulas with incorrect references might be used which is eliminated after onboarding SheetKraft.
- The operations team required around three days per month for the report generation but after onboarding SheetKraft it can be generated in 30 minutes.
- This in turn saves a good amount of time since no manual intervention is needed and also eliminates the risk of errors.
- Reports with summarized details of transactions and timely processing of MDR cashback encouraged merchants to accept various digital payments and this, in turn, increased transaction volumes for the payment gateway.
Financial Modelling
Customised Solutions for Complex Calculations
General Ledger Reconciliation - Completely eliminates the dependency of excel files for storing a large amount of PO data that consists of data inconsistencies.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
A leading bank performed general ledger reconciliation manually - it took a lot of time – several hours each day.
During the scoping process, we realized that one of the biggest challenges that our client was facing was a dependency on excel for a large amount of data.
We wanted to eliminate that and get the results quickly without the effort of fetching and compiling data from different sources.
These are some of the significant problems with manual general ledger reconciliation:
- Huge Data Volume – A large amount of data saved in excel slows down the process and takes a lot of time to generate the desired output.
- Varied Dataset – The data is stored in different files having multiple formats, making it exhausting to compile or compare.
- Complex transaction relations – Due to complicated internal processes and various data sources and formats, transaction relations turn into a labyrinth – one-to-one, one-to-many, many-to-many.
Dealing with such cases involves sophisticated logic.
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Benefits Of Automated General Ledger Reconciliation Automated general ledger reconciliation can get rid of all the complexities that are a part of the process and make it faster, easier, and error-free.
A good general ledger reconciliation automation software will also provide a customized solution based on unique reconciliation requirements.
Also, automatically run the reconciliation process in the background whenever needed to generate the output.
Additionally, automation software that can easily store large amounts of data in a database will be extremely useful to access easily whenever required.
How Did SheetKraft Help In Automating General Ledger Reconciliation Reports?
Our team quickly created a plan to build a customized application for their general ledger reconciliation process - one that would give them the much-needed automation features and flexibility.
The impact that SheetKraft had was to considerably reduce the overall time spent on this by our client.
Brokerage and Commission Calculation
Access Information Quickly whether as an Email, Dashboard
or PDF Reports
or PDF Reports
- More than 3 Hours to Less than 3 Minutes per Day: Reduction in Manual Effort using Fast and Accurate Performance Dashboards
- 2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
- 100% No Manual Errors
- 8 Hours to 15-30 Minutes: Entire End-to-End Process of the Delegation of Power Systems
Operational Reporting
Effective, Results-Driven, Complete Reporting
24 Man-Hours to 15 Minutes Monthly: Total Effort Saving in Claims Processing
Claims are processed on the completion of the said event for which the policy was initially bought such as death or illness. Claims are tagged as pending, closed, rejected, or approved based on their status and claim request reason. Only approved claims are taken forward for processing, and the claim amount is decided by a set of predetermined rules set by the IRDAI. These rules differ based on the type of ownership, industry, age, and other demographics. IRDAI guidelines require insurance companies to settle the claims within 30 days of claim approval.
Challenge
- Manual processing claims within a fixed period (generally 3 days) for insurance companies is challenging.
- As rule sets differ for policies, the accurate calculation of the claim amount is prone to errors.
Before SheetKraft
Data dumps containing the list of claimed policies, claim amounts, and claim reasons are used to create the report. After creation, the same is sent to IRDAI to check for probable errors. Any errors highlighted by IRDAI are to be corrected within 3 days. Manual processing of policies is error-prone, and this often leads to multiple to and fro between IRDAI and the insurance company.
The SheetKraft Methodology
- The data dumps containing the list of claims requested are fetched by the system. Policies are tagged as pending, approved, closed, and rejected based on their status and request reasons.
- Predefined sets of logic for each policy category are fetched, and calculations regarding claim amount, Industry type, and MFI Non-MFI tag is performed.
- Policies are then collated to create a consolidated report ready to be used by the stakeholders.
After Sheetkraft Automation
SheetKraft automated reports are accurate, thus reducing the multiple to and fro between IRDAI and the insurance company to correct any probable errors.
Impact of SheetKraft
Sheetkraft has been able to successfully create the application to generate accurate Monthly Reports in a scheduled, hassle-free manner.
Total Effort Savings: From 24 man-hours monthly to 15 min per month.
2 Man-Days Monthly to a Click of a Button: Budget Utilisation Reports on one dashboard
Objective
The basic objective of Budget Utilization Automation is to provide an end-to-end automated solution to generate on-demand interactive dashboards and detailed reports of the utilization of each budget code at individual budget owners, teams and organization levels.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The ageing dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the ageing dashboard monthly will help to identify the problems before they become a cash-flow crunch for business. This is used in Asset management companies.
Before SheetKraft
- All the data, required for generating these reports at the organizational level and for different teams, is spread across various tables in the spend management software. This data includes POs (purchase order) raised against an expense, invoices received for all services availed and payments made from each budget code. Users must manually fetch this data from multiple tables and then structure it in a standardized format.
- After that, the user calculates the Allocated Amount, PO Amount, Utility Amount, Consumed Amount, Unconsumed Amount and Invoice Amount for the organisational level and each team/department level.
- Processing complexities include many invoices raised against a single PO (Purchase Order), payment made against an invoice without a PO issued etc., which is easily missed by users in manual calculations.
- Due to extensive data, it was challenging to gather the data from multiple sources, structure it in a standardised format and perform analysis in excel.
- This process is performed only once a month, but it takes 1-2 days to complete.
After SheetKraft
- Using SheetKraft, all the data required for generating these reports and dashboards are directly fetched from the spend management software (Expensing DB).
- The whole process is set as per pre-defined rules and logic provided by the business users and is hosted on a web UI
- It hardly takes any time to generate the required reports.
- Business users are given access to the SheetKraft UI, and with a click of a button, users can generate these reports on demand.
- The manual process was time-consuming, but after using SheetKraft, the users can get the budget utilisation reports within minutes.
Overall Impact of Automation
- Dependence on different teams to generate an organisation-level utilisation dashboard removed with SheetKraft automation.
- Monthly 2 man-days of efforts reduced to a simple click of a button.
- Any chances of manual errors in computation and validation checks are eliminated.
3 Hours to 7-10 minutes: Analysis of Credit Limit Decrease
Why is Credit Limit Decrease important?
The limit decrease will save the lender from monetary downfall. Lenders make the limit decrease often by analyzing the client’s credit reports and scores. Therefore timely reviews on customer’s credit history will eliminate financial risks.
Steps used in executing the process manually:
- Data collation : The information such as credit history data, current balance and specific card issued along with their relative limits allocated is received from various sources and combined together for further calculation.
- Data computation : Checks and validations are applied as per the company’s predefined limit policies and the latest limit values are provided.
- Latest limit upload : Currently approved limit values for all users are stored and updated in the bank system for the bank’s review.
Problems with Manual process
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
Errors are obvious when a process involves manual intervention. Following are some of the notable problems with the manual process:
- Data discrepancies : Exact figures are required for bank reviews, errors in numbers will surely mislead interpretation.
- Heavy data volume : Processing time is directly proportional to the amount of data. More the data, more the consumption.
- Varied dataset : Data from different sources do not follow a specific structure. Data structuring takes time and effort.
Problems with Manual process
- Automated Credit Limit Decrease process got rid of all the difficulties faced by the bank, also making it resource-free, error-free, and faster.
- Huge amount of data can be stored in the database and the latest data can be retrieved when required.
- Personalized solutions were provided leading to data interpretation cut-off from the bank’s end.
How did SheetKraft help with automation?
The customized application was built satisfying the much-required automation as well many other features. One of those was graph reports of each customer showcasing their usage history of credit cards. These reports were auto-mailed to the senior management, which was used for a better view as compared to figurative data outputs produced via a manual process
3 Days to 30 Minutes: Zero Merchant Discount Rate Reports
Limit Monitoring
Generate Exposure Reports Related to any Breaches and Trends
100% Error-free Daily Reports:
Total Expense Ratio Reconciliation
Total Expense Ratio Reconciliation
Total Expense Ratio – A Case Study
One of our leading AMC clients was reconciling and updating the Total Expense Ratio Reports manually. They often had to rework their reports because of a high error-rate. Fixing those errors took a major chunk of their time.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
They approached our team to automate the process. Our team understood the process, client requirements, and reporting errors due to manual efforts.
The business logic was slightly tricky. The process also included automatically reading emails and fetching attachments and mobile operations among others.
But reconciliation is one of SheetKraft’s forte, so our team successfully fed the logic into the system.
The Total Expense Ratio Manual Process
As per the SEBI guidelines, AMCs and mutual fund houses must update their Total Expense Ratio daily. This ratio is then to be updated on their website, which means even a marginal difference needs to be updated. This is either done in-house by the AMC/mutual fund house or by a third-party vendor.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values in the file are then reconciled with the values stored in their database. This needs to be done for all the funds that the AMC has.
The values for today are reconciled with values for all the previous dates from the start of the month. Data points such as validation checks, matured schemes, and new schemes are reconciled. Any difference in previous data needs to be reported and forwarded to the concerned AMC departments.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
These departments would fix the data with correct values after checking the report. Once the modified Total Expense Ratio report is generated, it is uploaded to the website.
Automatic Total Expense Ratio Reporting With SheetKraft
After process logic is fed into SheetKraft’s reconciliation module, the process has become extremely easy.
Once the Total Expense Ratio report is received from the AMC’s in-house team or third-party vendor is received via an email. SheetKraft automatically identifies the email. This is done using unique keywords set as the subject line of the email.
The attached Total Expense Ratio report is automatically is downloaded. The database values are simultaneously pulled and SheetKraft compares these values.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
SheetKraft also checks for the new and matured fund scheme. The new schemes are added, and the matured ones are removed from the Total Expense Ratio report.
An updated report is generated for all the schemes. This report also highlights the value mismatch between the file received and the database which is then forwarded to the concerned department. The respective departments then correct the errors if any and approves them.
Post-approval, the report is sent in a set format. An interesting thing about this report is that it lets a user verify the data using their mobile devices. The error list is mentioned in the email, making it easy for the verifier to verify the data report.
Once the report is approved, SheetKraft automatically uploads the Total Expense Ratio report on the website.
Data Repository
Consolidate Data from Across Departments to a Single Location
2 weeks to 2 minutes:
Brokerage Statement Downloads to Data Warehouse
Brokerage Statement Downloads to Data Warehouse
The process for downloading brokerage statement involves CAMS sending a link to download account statement which is valid only for 15 days. If distributor forgets to download the report within the expiry date, then distributor reaches out to the Relationship Manager to get the account statement by forwarding their request to CAMS. Relationship manager then reaches out to CAMS for the link against the specific distributor. CAMS take a turnaround time of around 4 days to mail the file to the broker.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Distributors very often miss the expiry deadline of the link and this issue is very common in the industry and affects around 40,000 distributors associated with more than 350 branches and call centres for our client.
Solution designed by SheetKraft
CAMS send the link to distributors; this is copied to a generic email id and SheetKraft picks up these emails and downloads the pdfs from the links and stores them in the client’s data warehouse.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Now in a case where the distributor forgets downloading the statement within the expiration of the link and requests the relationship manager for the account statement; The relationship manager inputs the distributor details in the SheetKraft UI, SheetKraft processes the data and automatically emails the requested report to the distributor, This turnaround time once the distributor details are entered in the SheetKraft UI is around 4seconds.
Reporting in Multiple Formats
Access Information Quickly whether as an Email, Dashboard or PDF Reports
More than 3 Hours to Less than 3 Minutes per Day:
Reduction in Manual Effort using Fast and Accurate Performance Dashboards
Reduction in Manual Effort using Fast and Accurate Performance Dashboards
A prominent Life Insurance client creates a consolidated policy details file for due policies at a branch level monthly. This dataset is used to create branch, region and zone level dashboards for daily tracking of % of targets achieved and strike rate % which are integral in assessing the performance and efficiency of branches.
These daily reports reflect the expected monthly dues, the target set for a particular branch and the % of the target achieved as well as the % of total expected dues collected. These reports highlight the branches that have a below-average achieved % and strike rate so that measures can be taken to improve the performance of these branches.
These daily reports reflect the expected monthly dues, the target set for a particular branch and the % of the target achieved as well as the % of total expected dues collected. These reports highlight the branches that have a below-average achieved % and strike rate so that measures can be taken to improve the performance of these branches.
Challenge
- These dashboards are created and mailed to the Branch heads daily. The timely and accurate calculation and tracking of these performance metrics is a difficult task.
- The complexity of the dashboards combined with the processing of the huge set of raw data makes the whole process prone to manual errors.
Methodology
- Mapping of monthly targets basis zone, region, and the branch to the consolidated file containing details of due policies.
- Identification of policies whose premium was collected daily.
- Creation of zone, region, and branch level dashboards to display metrics as total dues expected, target set, dues collected, target achieved %, and strike rate %.
- Storage of premium payment(revival) history of policies daily.
- Mail notifications to Branch heads of underperforming branches.
Impact of SheetKraft
- Maintenance of a centralized database of premium payment data.
- The system has driven a fast and accurate generation of performance dashboards.
- Identification of underperforming branches and daily notification to the respective branch heads.
- Reduction in manual effort from >3 hours/day to <3 mins/day.
2 Man-Days Monthly to a Click of a Button:
Budget Utilisation Reports on one dashboard
Budget Utilisation Reports on one dashboard
Objective
The basic objective of Budget Utilization Automation is to provide an end-to-end automated solution to generate on-demand interactive dashboards and detailed reports of the utilization of each budget code at individual budget owners, teams and organization levels.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The aging dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the aging dashboard monthly will help to identify the problems before they become a cash-flow crunch to business. This is used in Asset management companies.
These dashboards will help budget owners and management to keep track of the total amount spent in a given time period and help to make better-informed decisions for further spending.
The aging dashboard will show the unpaid invoice balances along with the duration for which they have been outstanding. It helps to identify the customers that are late in paying their invoices. Tracking the aging dashboard monthly will help to identify the problems before they become a cash-flow crunch to business. This is used in Asset management companies.
Before SheetKraft
- All the data, required for generating these reports at the organizational level and for different teams, is spread across various tables in the spend management software. This data includes POs (purchase order) raised against an expense, invoices received for all services availed and payments made from each budget code. Users must manually fetch this data from multiple tables and then structure it in a standardized format.
- After that, the user calculates the Allocated Amount, PO Amount, Utility Amount, Consumed Amount, Unconsumed Amount and Invoice Amount for organizational level and each team/department level.
- Processing complexities include many invoices raised against a single PO (Purchase Order), payment made against an invoice without a PO issued etc., which is easily missed by users in manual calculations.
- Due to large data, it was difficult to gather the data from multiple sources, structure it in a standardized format and perform analysis in excel.
- This process is performed only once a month, but it took 1-2 days to complete.
After SheetKraft
- Using SheetKraft, all the data required for generating these reports and dashboards are directly fetched from the spend management software (Expensing DB).
- The whole process is set as per pre-defined rules and logic provided by the business users and is hosted on a web UI
- It hardly takes any time to generate the required reports.
- Business users are given access to the SheetKraft UI and with a click of a button, users can generate these reports on demand.
- The manual process was time-consuming but after using SheetKraft the users can get the budget utilization reports within minutes.
Overall Impact of Automation
- Dependence on different teams to generate organization level utilization dashboard removed with SheetKraft automation.
- Monthly 2 man-days of efforts reduced to a simple click of a button.
- Any chances of manual errors in computation and validation checks are eliminated.
8 Hours to 15-30 Minutes:
Entire End-to-End Process of the Delegation of Power Systems
Entire End-to-End Process of the Delegation of Power Systems
A human resources management system (HRMS) is a form of software application used by businesses that combines a number of systems and processes to ensure the easy management of human resources, business processes and data. Few examples of these functions are storing employee data, managing payroll, recruitment, employee performance management, etc.
The main objective of this process is the management of officials in the bank and the powers allotted to them along with handling the financial and economic risks at the wider level.
The main objective of this process is the management of officials in the bank and the powers allotted to them along with handling the financial and economic risks at the wider level.
Problem Statement
- The idea behind the Delegation of Power (DOP) system is that there has to be a single official in the bank (or a board of directors) who is responsible to approve proposals or delegate powers to responsible officials for exercising the right to approve proposals for making investments loans incurring expenditure discounting receivables, etc.
- This process includes a cumbersome amount of data discrepancies when maintained manually since the number of employees is large and the hierarchy level is complex. If not maintained properly, it can directly affect the grading system of the bank.
- Involved resources need an ample amount of time for file preparation as data is discrete and distributed.
- If required inputs (details regarding delegation) are not received on a timely basis then it leads to delays in official tasks & can have a domino effect on financials.
Before SheetKraft
End-to-end processing of this process requires the intervention of three teams as follows:
End-to-end processing of this process requires the intervention of three teams as follows:
- IT team acquires data of employee details information from HRMS DB on a daily basis and sends this file to VMT.
- VMT (Vendor Management Team) receives mail requests of employees from any retail /corporate branch of the bank for ID creation/modification/rectification. VMT team compares the new data with the old data for analyzing changes that occurred in the employee hierarchy. Based on the HRMS DB output, DOP creation is initiated for the file with the latest requests.
- The operations team on a monthly basis provides master dump data to the VMT team. This dump contains information about different level heads and power limits allocated as per employee job profile. VMT team uses these files for DOP creation.
- Since the data received from various employees is confidential, the IT team makes cryptographic codes for the outputs using encryption software. And then the VMT team uploads encrypted outputs onto the HR system of the bank.
After SheetKraft
- SheetKraft extracts the employee data from HRMS DB and master data from SheetKraft DB for processing of HRMS & DOP outputs.
- Activity logic is set as per pre-defined rules provided by VMT users using SheetKraft.
- After applying checks & calculations as per pre-defined logic SheetKraft prepares encrypted outputs as well uploads them in the HR system.
- Using SheetKraft, the entire end-to-end process is completed in 15-30 minutes whereas the manual process used to take around 8 hours.
Overall Impact
- Manual hindrance for performing the check conditions has been automated leading to no manual errors.
- As end-to-end activity run is scheduled for auto-run on a daily basis, user dependence is extinct.
We are right next door
in Mumbai.
in Mumbai.
The coffee is ok, the hours are decent
but the work is awesome.
but the work is awesome.
Join us in reimagining automation.
We are right next door in Mumbai.
The coffee is ok, the hours are decent but the work is awesome.
Join us in reimagining treasury automation.
collaboration
Let’s talk Business
Stay in touch
Address
501, Kushwah Chambers, Near Marol Metro Station, Makwana Rd, Gamdevi, Marol, Andheri East, Mumbai, Maharashtra 400059
collaboration
Let’s talk Business
Stay in touch
Address
501, Kushwah Chambers,
Near Marol Metro Station, Makwana Rd, Gamdevi, Marol, Andheri East, Mumbai, Maharashtra 400059
Near Marol Metro Station, Makwana Rd, Gamdevi, Marol, Andheri East, Mumbai, Maharashtra 400059