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.
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
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.