View our cash training sessions below

Background

• Cash reconciliation in the Duco Cash Module supports transactions as well as balances.
• Transaction and balance information relating to an Account are often referred to as Statements 
• The Cash Module follows a repository model, meaning it can continuously receive files and will try to match data in those files with whatever is currently unmatched in the process. Additionally, because of this design, it means you can run the process with only one side of data and do same sided matches.
• For any cash reconciliation, we must receive the following mandatory fields:

Cash Balances 

• Account number
• Currency
• Closing Balance
• Opening Balance (optional)
• Statement date (the statement date defines the accounting period the cash transactions apply to)

Activity/Transactions

• An account number
• An amount
• A value date
• A currency

Data Inputs

For information on configuring Data Inputs click here

For information on how we handle SWIFT MT950s click here

 

MT940/MT950 Background

Duco’s Cash module supports the uploading of MT940/950s. The product stores templates for these formats which can be selected when configuring the Data Inputs screen.

 

Process set-up - Data Inputs

• Balances and transactions must coexist in the same delimited file or the Data Prep module will be required to consolidate files. 
• Duco uses industry standard Regex methodology in order to extract balances from a file. In essence this is a unique line of code that uses pattern detection to identify the location of the Balances within your file. Regex is required to identify and extract opening and closing balance information, as well as which account, currency and date a balance applies to.
• You can read more about Regex here. You may find that at this step of the configuration you will require support from your Duco account manager.
• In the Data Inputs screen the names entered for each source need to use the exact same name as the Input in the Account Groups file, otherwise your Account upload will fail. It is important that it is spelled exactly the same, and note that it is case-sensitive.

 

Current Limitations

• A single input is either a SWIFT MT940 or MT950, not both. If you are loading a MT950 into a MT940 process, the file will not load and this error is shown:

Where this occurs, it is necessary to resubmit with the correct file. If a file contains both MT940 and MT950 swift messages, only the MT940s will load.

• The MT940 swift message has an additional field called tag 86. If you need to load both MT940s and MT950s for the same input this can be achieved by setting the Input for MT940s and amending block header 2 of any MT950s from 950 to 940. 

Before 

{1:F01TESTXXXX0085254623}{2:O9500128190813TESTLAXXX00852546231908130128N}{3:{108:MLN019224P140000}}{4:

:20:TEST

:25:051151989

:28C:00159/00001

:60F:C190809AED3150,37

:62F:C190812AED3150,37

:64:C190812AED3150,37

:86:BP2S PARIS                         511519  010

 

After 

{1:F01TESTXXXX0085254623}{2:O9400128190813TESTLAXXX00852546231908130128N}{3:{108:MLN019224P140000}}{4:

:20:TEST

:25:051151989

:28C:00159/00001

:60F:C190809AED3150,37

:62F:C190812AED3150,37

:64:C190812AED3150,37

:86:BP2S PARIS                         511519  010

 

Generic File - Balance Setup

• If balances are being used, check Enable balance.
• Statement date isn’t mandatory but If you are loading CSV files and no date is explicitly specified on the closing balance line, Duco will use the earliest value date to define the start date and the latest value date to determine the end date. Best practice is to supply a statement date.
• Duco can calculate an opening or closing balance for a statement through data load. You will need to provide either the opening or closing balance. For example, if you provide the closing balance, we will calculate: opening balance = closing balance - sum of transactions. Alternatively, if only an opening balance is provided we calculate the closing balance using the sum of transactions. If you are only loading a closing balance, check the Enable balance box for that balance and deselect the Enable balance box for Opening balance.
• Balance, Statement date, Currency and Opening/Closing balances are mandatory.

Sample file format and regex:

Opening Bal,(?<balance>.*),,,,,,,(?<date>.*),(?<account>.*),(?<currency>.*),

 

 

Opening Bal,(?<balance>.*),,,,,,,(?<date>.*),(?<account>.*),(?<currency>.*),

To read more on how to extract balances, click here

 

Fields

• Cross check all date and amount fields. Amount should be a decimal and not an integer.

 

File Loading/Matching triggers

• It is recommended that SWIFT messages are zipped and submitted at regular intervals.
• As data is loaded, the system will identify which Account Groups have new transactions and mark these as requiring matching (Pending). A matching job will be triggered and run the matching rules across all the Account Groups marked as requiring matching. 
• The Matching job will work through each Account Group individually. When matching is occurring on a particular account group, the user is not able to perform actions on those transactions. 

Changing the input on any process to a data prep process

In the March 2023 release, we have enabled the ability to change any existing reconciliation input to a Data Prep process. This will work on all cash, one-sided, and two-sided reconciliations. This will help reduce manual work, react quickly to the market changes, and unblock some key pain points below:

• React quickly to market or counterparty format changes
• Freedom to change upstream sources without losing previous run results
• Change existing inputs without creating a brand new process
• Reduce the number of reconciliations and consolidate into single process fed from data prep
• Support the upcoming SWIFT ISO20022 Cash MT940/MT950 migration

Please note we are continuing to make this complex user journey easier, and appreciate any feedback on how we can make these transitions of inputs easier for our users. Please feel free to contact your Account Director or Success Manager with any inputs.

For more information on how to change a data input to a data prep process, read the instructions here. Please note to follow these directions explicitly, as mistakes made here could lead to data loss.

Submissions

Global Submissions

This screen is a high level way to track if Duco has received all the files for reconciliation and what process the file(s) have been directed to. 

This screen will show all files submitted to Duco via SFTP. A user can check from this screen that both Ledger and Statement files have been received. 

Click here to read more about monitoring Submissions.

 

Process Level Submissions 

Any files delivered either manually or via SFTP will initially hit the Process level. This screen will sort by the latest file received. Users should tick ‘Submissions with Error’ to filter the files that require investigation. For more detail on this topic click here 

 

 

Each file will inherit one of the following statuses:

Queued

The file is in the process of being validated.

Failed 

Duco cannot parse the file therefore no statements are loaded into the Process. The source file should be checked against the Data Input dropdown.

Loaded with Errors

The file has loaded but there are issues within the file and therefore not all the data may have loaded as expected. Duco will provide a high level explanation on why a file has errored out. Click on the ID and a line reference in the error message will indicate why the file has not been cleanly loaded. 

Loaded

The file has been successfully loaded. 

Note although the submission status shows as loaded, there are still maybe validation issues that need to be investigated through the Statements screen

Statements & Balance Flows

The Statements screen fulfills two functions:

1. Allows a user to track opening and closing balance flows on a day by day, statement-by-statement basis
2. Provides a mechanism to review and repair or delete statements but it is only possible to amend the statement balance. 

Statement Processing

Whenever a statement is loaded, Duco performs the following checks:

Within a statement Duco checks that the statement is internally consistent (between balances and transactions): closing balance = opening balance + sum of transactions

We also check that there are no missing statements. In particular, Duco checks that: current statement’s opening balance = previous statement’s closing balance

A file may contain a number of statements and some statements may need some intervention in order for the transactions to be loaded into the reconciliation. Every statement that is loaded will inherit one of these statuses:

1. Queued for Processing - the statements are in the process of being validated
2. Processing - the validation has been successful and the transactions are being matched together with other unmatched transactions. Each statement will then be assigned a status of:
1. All exceptions resolved - all transactions in this statement are matched
2. Unreviewed transactions - matching has run, some transactions in this statement are unmatched
3. Unprocessed transactions - the matching run failed so these transactions will be included in the next matching run
4. Marked as duplicate - this statement is recognised as a duplicate of another statement
5. Ungrouped - the account is not yet included in any account group

Click here to read more on this topic.

Statement Ordering

• For each submission:
• Duco checks the closest or nearest opening balance to the last closing balance received for that account and Account Group. If there is no balance Duco then checks the opening balance stored on the Account Group within Settings.
• If a statement has loaded out of sequence, this can be changed using the Reorder Statement function 

• Best practice is to populate the ‘Opening Balance’ in the accounts with the latest closing balance (see legacy system migration) to ensure the statements load in the correct way.

Click here to read more about uploading cash statements.

Statements with Errors

The Statements with errors checkbox will highlight those statements where there are integrity issues with the statements or gaps between day - day balance flows. All these statements will be highlighted in red. 

To understand why a statement has been flagged in this way a user should click anywhere in the red box and this will provide a description as to why Duco is flagging this as an issue.

Incorrect Opening Balance

This error indicates there is a difference between the previous closing balance and the statement opening balance. To fix this, a missing statement will either need to be loaded or a transaction plugged from within this statement.

Amending the opening balance and plugging a transaction

 

Click on the ID to open up the statement, then amend the opening balance and add a reason to explain why the difference is being posted.

Duco automatically generates a transaction for the difference and the Statements screen will reflect the change in opening balance

 

 

Underlying transactions are inconsistent with balances

This error indicates the net transactions in the statement do not equal the difference between the opening and closing balances.  

The options to address this are to:

• Delete and resubmit a new statement
• Amend the closing balance within the statement to generate a transaction 

Amending the closing balance

Clicking on the ID will open up the statement where there is a difference of 1 highlighted.

A user review amends the closing balance and adds a reason:

 

Statements Marked as Duplicate

Duco identifies a duplicate statement by validating if there is an identical row loaded with the same opening and closing balance value, transaction amount, value date and statement date. A user can cross check this against the previous balances loaded and Delete the statement, or ‘Mark as not duplicate’. If several statements need to be unmarked as a duplicate, all statements should be unmarked. The matching process should be re-run and this will potentially auto-match any unmatched transactions.

 

Ungrouped

Any new accounts where statements have been loaded into Duco but do not have an Account Group associated (e.g. new client account) will need to be reviewed and a decision is required to either delete the statement or link to an Account Group. 

In the Ungrouped tab in the main transactions screen.

If an account needs to be associated with an Account Group, the recommendation is to open the Accounts in general settings and filter by the Submission ID.

Associating accounts to an Account Group can be linked in the following way:

Select the Accounts to be linked together to form an Account Group:

When this is complete, the transactions will disappear from the Ungrouped tab. In order for the transactions to be integrated into the Rec, the matching process will need to re-run.

Filtered Transactions

Transactions that are filtered do not appear in the GUI for Cash

 

Reversing incorrect file loads

There may be a scenario when a file has been incorrectly submitted into the reconciliation and this file needs to be reversed or backed out so a new file can be submitted. 

The way this can be achieved is by opening each individual statement from the statement screen, click Delete statement and this will automatically unmatch any matched transactions and then perform a soft delete on all the transactions on this statement

Account Groups

For more detail on this topic, click here

• As mentioned before, the Input column on Accounts should be spelled exactly the same as in the Data Inputs settings. 
• Duco provides a sample download that you can modify and upload. 
• If you reload accounts with the Overwrite option turned on, you will erase all Balance and Transactions data imported so far.  This could be very handy when testing, and you will find yourself doing it often if you want to undo a load you performed incorrectly. To prevent overwriting Account Groups in Production, ensure Change Control is enabled. 
• When uploading Accounts, make sure any labels you plan to use are already configured in Duco otherwise this will cause an error.
• Account Creation - tick ‘create accounts automatically’ so that ungrouped accounts are automatically populated on data load. This is important because you are able to  monitor new accounts appear in your feeds and decide if these should be grouped and processed or set to status ‘Ignore’  link these together in the Accounts screen
• Decide on a standard Account Group naming convention because this is what a user will use on a daily basis. Providing a standardised structure is best practise (looks cleaner) and it can aid the user as a method to filter Account Groups using different parameters e.g.  - show me all my monthly recs, or currencies. This can also be used to save on UI space, e.g. using the Account Group as a filter instead of using Currency.

 

• For example: JMP999-GBP-NOSTRO-D
  • Fund Name / Account Identifier
  • Currency
  • Account Type (e.g. Capital Account, Nostro, Income)
  • Defines if an Account Group is reconciled, Daily (D), Weekly (W), Monthly (M)

 

Field	Field Use
Status	This defines if the Account needs to be reconciled within the process. Data will be processed fully if set to Active. If set to Ignore all transactions and balance data will be filtered out.
Account Group	The ‘Rec Bucket’. Duco terminology used to group more than one account number together for matching. You can only match within an Account Group
Input	Defines Input, use Ledger or similar for internal feeds and Statement/External feeds. Again, it must be spelled precisely as you defined it in the Data Input screen.
Account number	This is the account number the transactions are being booked to. The Ledger account will always appear at the top of the Account Group
Currency	The currency the account trades in. Note we only match in this currency and not a base currency equivalent
Opening balance	Refer to the legacy system migration section, but this field controls statement balance flow on errors in the Statements screen.
Bank name	Free format field - 255 Alphanumeric
Beneficiary	Free format field - 255 Alphanumeric
Auto-match tolerance	Cash amounts can be auto-matched within a currency tolerance (refer to Write Off section)
Allow manual  transactions	Various options: Set to ‘No’ if you want to prevent a user creating write-offs Controls the side on which the system will create a write-off. Best practise is to create all write-offs on the Ledger side, so set to ‘Yes’.  If set to Yes on both sides then it will create the write-off on the side so the Amount is positive.
Manual match tolerance	Options are: Defines the value a user can manually match with a difference  e.g. if a value of 100 is set, a user can select 2 transactions up to a difference of 100 to create a net item. If the selected items are over the defined tolerance, a user will not be able to match. If you want to prevent a user from being able to create write-off items on certain Account Groups set to 0.
Labels	Useful if you want to categorise Account Groups for different rec teams. A way to potentially organise work.
Matching type	Used to control the accounting method we apply to matching logic Opposite Sign - double entry accounting, so a Ledger Credit matches to a Statement Debit Same Sign - like for like accounting, so a Ledger Credit matches to a Statement Credit

 

Mapping Match Fields

For more detail on match fields click here

• Account, Amount, Currency and Value Date are required fields for cash.
• Therefore, when you load in a generic data set, after you added the field(s) to be matched, if it is one of those mandatory fields, you cannot use the generic field name that was in the file - you must effectively “map” the file-header’s column name to Duco’s mandatory field. To do that, edit the match field name and select the appropriate field name from the drop down list.
• Until you have mapped all of the required fields in your Match Fields screen, you will not be able to proceed. You will receive a “Process Settings are Invalid” error.
• The Description field is also Duco-standard and can be similarly mapped, but it is not required.

 

Account

Amount 

When using a standard MT940/950 message, Amount will need to be transformed using NRL

Value Date 

 

Match Fields Best Practices

• As Reported Fields are not currently supported in Cash, all columns containing data you would like presented in the results will need to be mapped into the reconciliation as match fields. Information-only fields can then be ignored using your rule sets, on the assumption they are only required for viewing, not matching, or for use with Workflow rules.
• Many clients like to leverage NRL to create a field that clearly shows the side and sign of a transaction. For example  LC (Ledger Credit), LD (Ledger Debit), SC (Statement Credit), SD (Statement Debit). 

• To perform a match using extracted reference information from different reference fields, they will need to be mapped into a separate match field. e.g.

• Every time you add a match field you will need to reload the data to see the change.
• Reference data tables can be used to enrich data sets, for example:
• Enrich security or legal entity identifiers to a common standard
• Enrich counterparty names to a common standard
• Calculate base currency equivalent values
• Enrich transactions with netting identifiers from separate netting files to be used in matching and roll-up rules rules.
  
  

Matching

• Multi pass is usually needed for Cash  
• Matching is only supported in the Account Group’s currency
• For clarity it is better to name each pass in a way that reflects the specific data being matched
• Your pass naming convention will be displayed in the Auto-matched results, giving the user an indication of how the transactions where matched

• The Mandatory rule in Rules and Rule Sets will have a subtle impact on a Cash process, as that ability can explicitly override a less-strict rule that might also apply. For example:

  • A Default ruleset would have Optional on both ISIN and SEDOL fields. Then ISIN Match ruleset would have Ignore on SEDOL and Mandatory on ISIN.

• Cash works in the same way as a generic two-sided process, where in order to match multiple transactions to one transaction, a roll-up rule rule must be configured. A roll-up is a transaction that is generated because a group of same sided transactions do not net to zero. This link explains how we currently handle this:

Roll-ups - Explained

Often good practice is to ensure we first deal with any cancelled transactions (1:1 same sided matches). Overall the following is a good approach to matching passes in Cash:

• 1:1 same sided matches
• 1:many same sided matches (these have to be handled using roll-up rules but should be an unusual scenario unless reconciling one side, e.g. internal wash account). 
• 1:1 perfect matches
• 1:1 matches with tolerances applied
• 1:many or many:many perfect matches (find match within roll-ups)

 

Example Passes

1:1 Same Side Matching

• Matching scope - in this case only consider ledger items
• Matching rules (Rule Sets) - Match on Amount and Value Date. Ignore all other fields.

1-M Same Sided Matching

• This rule will potentially roll up lots of same sided items unless specific criteria or a conditional rule is specified. In this example, a condition has been applied to only use this rule against a selection of accounts. This has been defined as a match field.
• Matching scope - in this case only consider ledger items
• Matching rules (Rule Sets) - Match on Amount and Value Date. Ignore all other fields.

 

1:1 Perfect Match

• Matching scope - in this case only considers ledger and statement items
• Matching rules (Rule Sets) - Match on Amount, Reference 2 and Value Date. Ignore all other fields.

1:1 Perfect Match with Account Group Auto-Match Tolerance

When checked Apply amount tolerance uses the tolerance defined at the Account Group level to determine if a roll-up should be generated. 

 

1:M Perfect Match - Find Many - 1 matches using roll-ups

This feature allows the system to try and find matches where there is no unique key to bind them together. This is used to help find the matches that typically would have to be found by manual matching. It does this by trying permutations within subsets of transactions (as configured by roll-up rules) that net to zero.  

 A roll-up rule will need to be applied for this function to work but it can be quite a generic one, e.g Ledger, Account Group. Note as this feature can logically try millions of permutations, it has some logic that will abandon trying all permutations if it will take too long. It is therefore recommended to try and define roll-up rules that will limit the number of transactions to be tested. 

 

Matching scope - in this case only consider ledger and statement items

Matching rules (Rule Sets) - Match on Amount, Reference 2 and Value Date. Ignore all other fields.

Find matches within roll-ups - this should be checked.The roll-up rule is used to attempt to group the type of transactions together to then aggregate and compare to the statement. 

 

Rules and Rule Sets

• We recommend leaving the Default rule set blank.
• Bear in mind that Fuzzy matching is not currently available for Cash.

Write offs

Often, the value of payments and receipts does not exactly tally with what is recorded in the books of records

• i.e estimated foreign transaction fees or exchange rates)
• These discrepancies are identified through the reconciliation 
• i.e We know the Internal and External transaction represent the same event but there is a small amount difference
• In order to keep the book of records correct the amount difference needs to be captured in the book or records
• In order to ensure auditability and keep the reconciliation integrity checks robust the difference also needs to be:
• Captured in the reconciliation to justify the match (i.e make up the difference)
• Captured as a contra in order to match the pending new event once book and captured in the next feed

To handle these scenarios Duco can create write-offs. (Refer to the auto-match tolerance description in the Account Groups section for more detail on how this is controlled). 

It is also possible (and best practice) to roll-up individual write-offs into a consolidated, single write-off by configuring the below write-off roll-up rule. Best practice is to then move all write-offs to a Pending status as they will typically match off when the write-offs in Duco are subsequently posted to the book of records.

 

 

Identifying a consolidated write off

The consolidated write-off can be identified by filtering on the Statement ID column for ‘Write-off roll-up’. Click on the View Roll-ups to see what transactions make up the constituent value. 

Identifying matches with a write off

Duco has used a match pass to automatically match a set of transactions that is within tolerance. The Statement ID displays as ‘Tolerance match’ and the Description field will contain the reference ‘Automatically created for match with tolerance’.

In the example above, the auto-matched transactions look the same but a small difference has been created and is reflected in the group of matched transactions. 

 

 

 

Workflow

Exception Status Tabs

Unreviewed

Displays all unmatched transactions where the exception is an Unreviewed status. If no workflow rules are used, this is the default status for all new exceptions.

Under Review

• Displays all unmatched transactions where the exception has either been actioned for ‘start review’ by a user, or automatically marked as ‘under review’ by a workflow rule. 
• Manually created net Items will automatically move to Under Review 

Pending

Displays all unmatched transactions where the exception is a pending status. A pending status is typically applied to transactions that are expected to be unmatched. For example forward valued transactions, write-off pending their journal updates etc

Resolved

Displays manual match groups including same sided matches. If a user matches two or more transactions from the same side (e.g. a statement credit of 10 and a statement debit of 10) Duco will create a net item of 0, which generates a resolved exception

Transaction Tabs

• Auto-matched - will return all matched transactions
• Manually-matched - will return a count of transactions matched by a user
• Unmatched - will return all unmatched transactions
• Recent transactions - display all unmatched transactions plus any recently matched transactions (match date plus 30 calendar days)
• All transactions - all transactions - will return matched and unmatched transactions 
• Ungrouped - returns transactions that have been loaded but do not have an Account Group associated.

Workflow audit 

Cash reconciliations often have a high degree of compliance and audit oversight and therefore being able to demonstrate and review the full history of transactions is important. If an exception has workflow actions applied and is subsequently matched or rolled-up, and then broken apart, users can see the full workflow history prior to being matched.

Views 

Best practice is for each user to configure standard views for each of the tabs above, focusing on showing the columns that will assist the typical jobs conducted from that view i.e. manual matching and allocation, review of matches, investigations etc. These can be saved either as bookmarks, or in some instances saved searches. 

Bookmarks

Using filtering and sorting you can prepare a variety of ‘canned’ views that it can be useful to pivot to. Saving these views as bookmarks can be a considerable time saver.

Examples of useful bookmark views include: 

• Proofing: In Proof, Out of Proof
• Accounts: Ungrouped Accounts, Ungrouped Transactions, Linked Groups
• Reconciler: Unmatched assigned to SPECIFIC GROUP, Unmatched P1 Account Groups
• Admin: Audit Log, Ungrouped/Duplicate Statements, Process Settings

  

Note that Duco automatically updates all bookmarked pages to show the latest results.

 

Search 

Another way to save a particular view is to use the Search feature.

This will allow you to select a field/s for use in your search, which you can then filter and sort on. This can be a much easier way of searching your results where you have a large number of columns you’d otherwise have to scroll through.

 

 Transaction centric attributes fill the main display v exception attributes

• Unmatched Tab - default view should contain these attributes:

Account Group, L/S, Amount, Currency, Value Date, Entry Date, Ref1, Ref2, Ref3, Tran Code, Age, Group, Labels, Workflow, Comment, Account, Roll-ups, Description, Linked Transaction, Business Statement Date,  Statement ID, Load date, Account Labels, ID

Order by Account Group, Amount (Abs desc)

• Automatched Tab - default view should contain these attributes:

Account Label, Account Group, Status,  L/S, Amount, Currency, Value Date, Ref1, Ref2, Ref3, Tran Code, Entry Date, Matching Pass, Account, Statement ID, Roll-ups, Load date, Group, ID

• Manually matched Tab - default view should contain these attributes:

Account Group, Status,  L/S, Amount, Currency, Value Date, Entry Date, Ref1, Ref2, Ref3, Tran Code, Matching Pass, Statement ID, Roll-ups Group, ID, Account Label

• Unreviewed Tab - default view should contain these attributes:

Workflow, Group, Assigned to, Labels, Comment, Age, Account Group, L/S, Amount, Currency, Value Date, Ref1, Ref2, Ref3, Tran Code, Account, Roll-ups, Description, Linked Transaction, Business Statement Date, Statement ID, Load date, Account Labels, ID

 

User Actions

All the following functions are relevant to cash and are already covered as part of the standard documentation. 

• Assign to user
• Allocate to group
• Start review
• Stop review
• Mark as pending
• Review further
• Apply labels
• Remove labels
• Comment
• Split

• Pin - this function is used when you are dealing with high volumes of exceptions spread across multiple pages. You can pin an item from page one, go to page 2 and pin so you can consolidate all your transactions into one page to perform an action e.g. match

Manual Match

• Manually match - this function allows a user to change the status on unmatched transactions to a matched state. This function works in parallel to the settings stipulated in the Account Group. (see section on Accounts for the manual matching tolerance) 
• In order to manually match the transactions:
  • The transactions must be assigned to a Group that the user is also a member of. 
  • The transactions must not be assigned to a different user. Note a user with Exception allocator can change the user and group assignment of any transaction. 
  • The transactions must be in an Unreviewed or Under review state
  • The user must have the Manual Matcher permission for the process

Manual Match Control

You are able to set specific criteria on the match fields within your cash reconciliations that will need to have equal ‘value’ to be able to manually match the break. This will help to support more accurate reconciliations and improve control/ governance over manual matching.

For instance, if you choose the value date to be a control field, then Duco will not allow manual matching of records if one record has a value date ‘10-10-22’ and another one has ‘20-10-22’ in the controlled field.

The fields are configurable per reconciliation process so that each process can have different criteria of controls.

Manual Netting 

Netting is a term used when matching transactions on the same side. If the transactions net to zero then the result will be a ‘match’ with the workflow status transitioning to ‘resolved’.

If the transactions do not net to zero then the manual matching tolerance will be used, and if permitted the transactions are bulked together and will show as a manually created netted transaction with underliers. The netted transaction will be assigned to the user who conducted the net and the status will be Under review. 

Break Apart

• Break Apart allows a user to break apart a set of matched transactions. Match status transitions from Auto-matched to Unmatched and Workflow status to Under Review allocated to the user who unmatched.

 

Staging Copy & Change Control

Staging copies support clients when they need to try and test configuration changes without impacting a production process and its associated results. Note that the staging copy:  

• Will inherit the parent permissions
• Will copy all other parent setting except the Accounts which will need to be re-imported
• Will not copy across the results data
  

Change control can be used in conjunction with staging copies to create an approval flow. Here it is required that configuration changes are applied to staging copies only, and from there promoted to the parent process through a maker/checker authorisation. 

 

Exceptions workflow

• Best practice for Cash workflow can include the following:
• A group responsible for ensuring the reconciliation takes place (data loaded correctly, accounts are in proof, manual matching). Typically individual users are responsible for specific sets of Accounts
• Depending on the size of the operation, the reconcilers or a separate allocation team will be responsible for ensuring that any outstanding items are allocated to the correct business owners 
• Business owners responsible for identifying and rectifying any issues that caused the break / transactions being unmatched 
• In order to support the above, clients can use a combination of reference data tables and NRL/Workflow rules in order to automatically allocate any unmatched transactions to the relevant group. For example based on the Account, product codes, reference codes, counterparty names etc.  

Reference data table:

Match field:

 

Workflow rules:

Note that Cash does not currently support Calculated Results, however, NRL rules on the Match fields screen can be used to achieve similar results.

 

Account Summary

The Accounts menu takes users to the Accounts summary which is the central control for the overall status of the Reconciliation of Accounts across all Cash Processes. 

• It provides a high level summary of the reconciliation and an aggregated view for each side of the reconciliations (as per any Account grouping and input sides)
• The Account Group (the relationship between ledger and statement account numbers) can be found here, with a hyperlink to open the Recent transactions screen.
• Auto Matching - provides the status of the Account Group
• Idle - No data loading, matching not running
• Pending - New data has arrived and the Account Group is in a queue pending matching
• Matching - Account Group is currently undergoing matching
• Failed - An error occurred during matching for the account group. User will need to raise a support ticket.
• Last auto-matched - last time matching ran
• Account number - the ledger and statement account number. The top row in each account group reflects the ledger account number
• Process - Process name
• Labels, Currency, Bank name, Beneficiary - all information derived from the Accounts table
• Latest statement date - the latest closing balance statement date
• Opening balance - the latest received opening balance 
• Closing balance - the latest received closing balance
• Unreconciled positive / negative -  sum of unmatched transactions
• Proof - Calculation to justify any difference between the closing balance of each side of the reconciliation. This value should be zero with any other value representing a possible discrepancy that needs investigating. Please see below for further details as to how this works. 
• Adjusted balance - closing balance minus sum of unreconciled transactions
• Number of exceptions - number of unmatched transactions with hyperlink to open the unmatched transactions tab
• Statements - hyperlink to the statements screen
• Account Reviewed button - enables you to easily document and evidence when someone has reviewed and verified the accuracy or state of Cash Account level reconciliations. Find more details here.

Note any Accounts that are Ungrouped are not displayed in this screen

The detail on this screen can be downloaded to a spreadsheet either through the Spreadsheet button or the Account Summary API. 

 

Proofing

• What does an Account Proof mean?
• Accounting practice that justifies any differences between the Balance records on internal books of records vs those reported by external parties through the identification and tracking of unreconciled (and therefore not recorded on the other side) transactions.
• If the balance reported in internal Books is different to that reported by a counterparty then this should only be caused by:
• Transactions booked and reported by the counterparty but not reflected in Our Books and therefore Unreconciled Their transactions
• Transactions booked and reported on Our Books but not reflected by the counterparty records and nd therefore Unreconciled Our transactions
• Proof calculation is different depending on whether the Accounts are being managed as double entry (opposite sign) or mirrored (same sign):
• Double Entry - Internal Side reflected as a contra (opposite sign) to the actual flow
• Mirrored - Internal Side reflected sd per the actual flow

Proof Balance Example - Double Entry/Opposite Sign

Example from the Global Accounts screen within Duco:

 

 

 

 

 

 

UI, XML and Excel visibility windows

Cash uses a repository model and therefore all the loaded data is kept within a single database table. As data loads over weeks, months and years this repository of data builds up.

In order to optimise the user experience when interacting with the UI as well as exporting results there are three visibility windows that can be independently set per process:

• UI - visibility_window_size
• Excel Export - visibility_window_size_excel_export
• XML Export - visibility_window_size_xml_export

The exports and UI will also show all unmatched transactions but the visibility window will restrict the scope of Matched transactions exported/shown. For the UI the ‘All Transactions’ tab will always show all transactions regardless of the visibility window. 

The value can be set to 1 or more representing how many days (based in UTC time) of matched data will be included. For example, setting to 1 will only show transactions that are matched today, 2 matching today and yesterday. As this is based on UTC, clients operating in different timezones need to consider that a setting of 1 will include transactions matched since the last 00:00 UTC and 2 will be matched since the 00:00 UTC before last etc. 

For example:

• Client in New York had a setting of 1 on their Excel export running at 6pm on Tues 4th February 2020 EST 
• All Unmatched transactions
• Matched between 7pm Monday 3rd to 7pm Tuesday 4th EST
• Client in New York had a setting of 2 on their Excel export running at 6pm on Tues 4th February 2020 EST 
• All Unmatched transactions
• Matched between 7pm Sunday 2nd to 6pm Tuesday 4th EST 

Further illustration:

Window Setting / Time zone	1	2
EST	From previous 19:00 to now	From 1 before previous 19:00 to now
EDT	From last 20:00 to now	From 1 before previous 20:00 to now
HKT	From last 17:00 to now	From 1 before previous 17:00 to now
NZST	From last 13:00 to now	From 1 before previous 13:00 to now

Using the exported data for reporting 

Best practice is for clients to set the window for the Excel or XML window to 2 to ensure they always get a minimum 24 hours worth of matched transactions (as per above) plus all the unmatched data. 

Client will be able to use the loaded_date, and match_at date_time, value dates, entry dates, aging started at….. etc to filter and slice and dice the export as necessary.

Note - not having date and time on Loaded might be an issue.

 

Testing / Build Mode

Clearing loaded data 

• As the data in Cash is stored in a repository, with each submission of data adding to the last when doing initial builds and testing, there may be a need to clear down the data and load again.
• Overwriting the account static will delete all the transactions and balances. This will be required if a change is made to support any changes to match fields, because the match field is transformed on data load.

 

Running a Process

• Match Transactions - this will match all unmatched transactions - useful if you are testing Rule and Rule Set changes
• Re-match transactions (Settings - General Settings) - this will unmatch and re-match all cash transactions across a process. 

 

Account Group Proofing

Confirm no Account Groups are out of Proof. In theory if checks 1 - 5 have been completed all Account Groups should be in Proof. 

Reconcile Account Groups

When all the data checks have been completed the reconciler can start to review the transactions that have not matched. Transactions that have not been automatically matched will either need to be manually matched or allocated to Groups for investigation. 

The work should be organised using the Accounts Proofing screen. Consider applying Account Group labels in order to prioritise the Account Groups that need to be worked on first. 

• Select the group of accounts they own using the Account Label filter. In this example P1 is priority 1, P2, priority 2 etc

• Check and investigate missing statements
• Click on exceptions and this will open the Unmatched tab, filtered by Account Group
• Identify and perform manual matching, focus on 1-1 matches first and then M-M matches / create net items
• Add notes / apply labels / allocate exceptions to different Groups 
• Hit back on the browser and this will bring you back to the Proofing screen 
• Work on the next Account Group

 

Run matching only on unfinished accounts

Matching within Cash runs on an Account Group by Account Group basis as new data is submitted. There are times however when matching might need to be triggered manually, for example after grouping an Account, deleting statements or if matching fails on a particular Account Group. Rather than running matching manually for all Accounts or waiting for the next submission you can trigger matching for just those Accounts. 

            

 

This can help in two ways:

1. If you have a large set of data, running matching on all accounts could take some time.
2. You may have some other activities happening on your other accounts, and running matching could interfere.

 

Updating a cash process in production

Please be mindful when updating the settings of live cash processes with Megadata enabled, especially those receiving thousands of transactions on a daily basis.

Updating the match fields requires the backend changes which may cause latency in the entire platform if the process is receiving submissions at the same time.

In case changes need to be made to the match fields, including adding or removing fields, plan this action for the end of a workday or the beginning of the weekend to avoid slowing down the system. We also suggest to do the changes via a staging copy and raising a change request.