CashFlow

Definition:

Cash flow is the amount of cash and cash-equivalents being transferred into and out of a business.
Cash-Flow Management involves record management of the inflows and outflows of monetary transactions. This is a dashboard in which we give the user a visual representation of the inflows and outflows
AMC (Asset Management Company) has many types of cash-flows happening in various departments. Cashflows can be of type: borrowings by the AMC, subscription, redemption by investors etc. These cash flows need to be reconciled. The monitoring of these cash flows is done using the dashboard.

Scope of the Problem:

SheetKraft team was reached out for reconciliation as the AMC’s Fund management system is used only to feed the cash flow, but validation is not executed in this fund management system. Validations include - Matching source data with correct label, value date to match the cashflow date, Segregation of relevant data using filter and lookup.
There are more than 20 Sources of data which is then integrated to form a cash flow dashboard. All the sources have multiple File System, formats and validations. When the integration is performed, the user is bound to make an error which can cause an incorrect entry in system and thus display false data

Before SheetKraft:

Dedicated AMC personnel were responsible for collection of data from various sources which contribute in cashflow. For example: Borrowing information, borrowing repayment, various subscription and redemption data, various taxes paid, /maturing securities, trades executed and many more.
Many calculations and transformations on the data had to be performed manually before the cleaned data can be used for further steps.
After Collecting all the data, these files are then consolidated manually in excel files in the form of cash inflow and cash outflow and it is uploaded in treasury management system.
A small mistake in data error had a huge impact on the overall report. For Example, In one of the incidents, one of our clients ran into an error when the validation check went wrong. During the manual process, user manually uploads redemption transaction as a subscription, here redemption means when outflow of cash whereas subscription refers to inflow of cash. 
Due to an error made in labelling the transaction, incorrect data was reflected in redemption as the redemption request was labelled incorrectly as subscription and an incorrect inflow and outflow of cash was captured in the report. As per company policy all the redemptions in debt scheme need to be met by EOD. By the time they realized the mistake, the market was closed, and redemptions were still pending.
To meet the redemption borrowing was made and loss were dealt due to a miss in validation of data before uploading in system.

After SheetKraft:

• Using Rapid application framework SK, an application was built which interfaced with various sources of data. The need to collate data from multiple sources and maintain a single workbook is eliminated as sheetkraft can integrate all the data from its source. Thus avoiding any possibility of manual error where user enter incorrect records.
• There is a wide range of sources which is integrated to form a cash flow from data from emails, reports from various departments, Zip files, accessing files from shared drives and many more.
• Validation is one of the most important part of the process eg: validations on value date, Cashflow date, borrowing date, Borrowing repayment date etc. The activity validates each record as per the cashflow date. When the user used to perform these validations manually before SheetKraft, there were instances reported when critical validations were missed resulting in costly errors.
• To make things more complex, opening balances for 200+ accounts needed to be calculated wherein the logic to calculate opening balance for each of these accounts can vary. Additional accounts for future with a different formula for opening balance calculation also needed to be considered.
• Therefore, rather than building in a static formula, all the formulas to calculate Opening balance for each account was stored in a database in the form of text. Now, each time the opening balance needs to be calculated, the associated formula for the respective account number will be spooled from the database. Rights for adding and editing entries in database is given to specified users.
• Apart from data validation, validation of files from various sources is also in place. Missing files are prompted to user in the form of warning allowing user to be notified of the missing data.
• Netoff is also calculated with the help of sheetkraft where once all the transactions for the day is completed the surplus or deprived cash position of accounts are brought to zero and the amount is transferred to or from a primary bank account.
• With the help of Sheetkraft a daily activity of almost 2 hrs has been brought down to a single click activity which takes less than a minute to complete

Impact:

• Aversion of High Risk as manual errors such as previously mentioned are totally eliminated.
• With automation all the validation checks can be easily done