For a large and complex business, corporate bank reporting can be painful.
A large number of accounts, each containing a multitude of transactions, can make the process of analysing cash flows unwieldy and time consuming.
This equally applies to completing the actuals component of a forecast model, where the data needs to be classified to be used for actual vs forecast and variance analysis.
Further to our recent post which discussed how electronic bank statements enable automated bank reporting, here we’ll review how this data can be re-worked and re-modelled once it is loaded into specialised cash flow forecasting and bank reporting software.
Introduction to transaction mapping rules
While electronic bank statements contain all relevant transactional data, it is in a raw format and therefore still has to be reworked, categorised, and grouped, before it can be used for reporting or forecasting purposes.
For example, if you were handed 30 pages of bank account statements, then were immediately asked the total value of a particular subset of supplier payments, it would take time to sift through each statement, identify each line which matched what you were looking for, and then totalling them up.
Mapping rules are an automated way of providing predetermined parameters to the system so that each transaction is categorised as soon as it comes in.
How do mapping rules work?
In CashAnalytics, the first stage of creating a mapping rule is to determine the transaction type (i.e. debit or credit).
Next, the rule is built around one or all of the standard fields contained within a bank account statement.
To illustrate, let’s take a look at the earlier example where we were looking for the total of payments to a particular subset of supplier payments.
When creating a new mapping rule in CashAnalytics, you would follow the steps below:
- Select the type of transaction (in this case, as it’s a supplier payment, it would be a credit).
- Create a rule to map to one or all of the standard fields contained within the bank account statement. CashAnalytics will look for whatever string of information is entered in these fields and match it to transactions containing this string. (Here, you could load the suppliers name, or category, or any other distinguishing information contained in the transactional data which identified it as the type of transaction you were looking for. Dependent on your bank file type, you may alternatively/additionally be able to use a transaction code).
- Tick “Mark Matched” if you wish to separate this transaction so it will be ignored for other rules and will not map into a sheet.
- Select the line item in the forecast model you want the rule to map to (in this case it will be a line item under the supplier payments section).
- Depending on how the type of transaction is recorded, if it is standardised you may wish to tick the “Exact” box to indicate the rule should only apply to an exact match of the information string provided.
- Once all of the information has been input, the number of transactions that match the criteria will be shown here.
Benefits of transaction mapping
Building mapping rules such of these greatly cuts down on the amount of admin work involved in both populating the actuals column on a forecast sheet, and with formatting data for bank account reporting.
This type of mapping process allows the forecast model to always remain up to date with the latest actual cash flow data.
Additionally, the flexibility of the way mapping rules are built in CashAnalytics means that a range of different parts of the transactional data can be used to classify the transaction, either with a single string or specific bank transaction codes.
Furthermore, having the transactions aligned and mapped to your forecast model, speedily populating the actuals, means that more time can be spent on forecast vs actual variance reporting and analysis.
How to put it into practice
Our advice, when setting up new clients with CashAnalytics, is to try to resist the urge to map all transactions ahead of the first reporting cycle.
Instead, you can simply run a report in the system which highlights unmapped transactions and build mapping rules for them after the first few cycles.
By following this process, the system will “learn” how each transaction should be classified, and the number of unmapped transactions will get fewer.
As with all elements of forecasting and reporting, the best results are achieved by making continual improvements, rather than trying to aim for a perfect launch.
About CashAnalytics
As a dedicated cash flow forecasting and bank reporting software provider, CashAnalytics use a range of automation technologies and analytics tools to improve our clients’ cash forecasting and bank reporting processes.
If you have any questions about our software, or would like to see a demonstration where we can run through exactly how CashAnalytics can help treasury and finance teams add real value to their business, please contact us directly or request a demo now.
