Reconciliation

The user will set up specific recurring reconciliations that will, among other things, define the two source files to be reconciled to each other. Templates will be set up then for each source file to map the fields to be compared. In addition tolerances can be set up that will allow the system to do “close” matches: those that are not exact but are within the tolerances. The system can also check for transpositions and other “typos” on the source files.

Reconciliation Types

Setting up reconciliation types allows the user to define, by a simple name, the specific reconciliation to be used. Associated with the Recon Type identifier is, among other things, a general definition of the two source files, referred to as A and B, and the fields to be reconciled.

Field Definitions

The user will define the fields which will be compared between A and B. The names for the fields are user-defined for ease of understanding. They do not have to be the same name that is used on either A or B since the names from an external source may be somewhat cryptic. The fields are described briefly with the type of field, e.g. character or number, and the field length for character data. For example there may be fields for Customer, Transaction Date, and Amount. These have the field types of Character, Date, and Number, respectively.

Severity Codes

Each of the reconciliation criteria explained below will have a severity code associated with it. An exact match will have a severity code of zero. Any close match or other match may have an arbitrary code set that is greater than zero. The system will report the reconciliation by severity code to allow for easier analysis of the reconciliation. Non-matches do not have a severity code and are grouped separately.

Reconciliation Criteria

For each field, the user will then set the reconciliation criteria. There can be multiple criteria per field. The system can find exact matches; close matches for numeric fields; matches due to transposition, and others. Using the example of fields above, we may require the Customer to be and exact match, the Data to be an exact match, but we may want to treat the Amount differently. We can set an Exact Match with a severity code of zero while a close match that is within a 0.50 range may have a severity code of one. A transposition error may have a severity code of five.

Define Import Templates

At this point the user will have to define templates for the two source files A and B. These files will be in either csv, text, spreadsheet, or MIMICS file format. The template is linked to the specific file name (or, for example, spreadsheet name). The system will examine the source file and will display the names of the fields available, e.g., spreadsheet column headings. The fields to compare that were already defined in the Recon Field Definitions will also be displayed. The user will simply map the available fields to the recon fields to compare, by dragging and dropping one to the other to complete the template. After this has been accomplished for both A and B, the system is set up to do recurring reconciliations of the two files whose templates were defined and according to the reconciliation criteria defined.

Unlimited Recon Types

This procedure can be followed to set up any number of reconciliations. There may be multiple reconciliations for multiple cash accounts; for positions in multiple portfolios; for General Ledger transactions on multiple GLs, and any number of other recons. The setup for a recon is done only once. A recon process can be changed if the recon criteria are changed, if the input file formats change, or if you want to change the process in any other way. Other than this you will not have to go back to revisit the reconciliation type setup or the template setup.

The reconciliation process has three basic steps: import source A, import source B, and run the reconciliation.

Import Using Template

Since the template describing the source files A and B have been set up, simply navigate to the file, such as a spreadsheet, select the template, and click Import. The system will notify you of how many records have been imported. If you want, you can click the Test button in advance for the system to scan your spreadsheet to ensure it is compatible to the template. This process is then done for the other source file. you will have then prepared the data for the reconciliation. The data to be reconciled is now “locked in” to the system so that any change to the spreadsheet, in this case, will not be reflected in the reconciliation. If someone changes the spreadsheet due to an entry error, the spreadsheet can be re-imported. This maintains the integrity of the data. In this way the specific instance of the reconciliation is related to the data imported and no one can change this data for the recon. When the recon is verified, the user knows that any change to the underlying spreadsheet will not affect the recon results (which can also be “locked in”).

Reconciliation Process

The reconciliation is accomplished by selecting the recon type and clicking the Reconcile button. After the reconciliation is accomplished according to the selection criteria, the system will pop up a message with a summary of the results. It will report on the matches according to severity code as well as the number of non-matches. This is done by source file. The system keeps a copy of the reconciliation, the results, and the respective source data on file. Any number of reconciliation can be done on the same data if desired, each being saved with a time stamp.

The reconciliation will report matched items, near matches, and non-matches. The user can verify each of the system’s matches and can manually match unmatched items. Notes can be made at verification in order to explain the reason for matches. When all individual items are verified, the user can indicate a verification of the entire reconciliation. At that time the recon is ready for approval. The approver can either approve or reject the recon, adding notes for the reason for rejection. This will allow for an analysis of the differences.

Optionally, a workflow processing feature is available through the MIMICS Process Flow module that will allow for routing of the recon through different parties depending upon issues discovered either by the system or by a user. Email and message alerts can be set up to notify the proper person of their task in the recon process. Each user has a dashboard to view their queue of tasks to be accomplished. The system also has a supervisory dashboard where all elements of the process can be viewed.

Verification

The Verification screen will show a grid with rows have a checkboxes that can be clicked to indicate verification. There is also a field in which the user can enter verification notes. When each row has been checked, the user can indicate that the full reconciliation has been verified and it will then be available to the approver.

The verifier may see an adjustment to the reconciliation. For example there may be two sets of records that are close matches by differing amounts. The verifier may want to match these differently. To do so the user drags on of the rows of, for example, A to the corresponding row of B. The other pair will be matched in the same way. In this way the system’s reconciliation can be overridden on a detail and even non-matches may be matched manually.

Approval

The approver will have a screen that is similar to the verifiers’, showing the notes and the changes made by the verifier. The approver will be able to approve or reject the reconciliation. The approver can also write notes regarding the reconciliation, especially if it is rejected.

Reconciliation Audit Detail Report

A report showing the results of the reconciliation can be printed. It shows the results by severity code. The exact matches and the close matches are grouped to easily see the match that was made. The non-matches are grouped by Source file. The information can be verified on this report or on the verification screen.

The system has additional reporting that describes the setup, reconciliation history, and much more.

Interfaces

The Reconciliation System interfaces with the MIMICS Process Flow module as well as any other MIMICS Systems with which the user wants to reconcile data.

Major Functions

• Unlimited Recon Types
• Multiple comparison file types
• Multiple “Close Match” methods
• Multiple severity levels of close matching.
• Manual override of matching
• Verification and Approval process

Security

• Full Password Protection
• Define Password Strength Required
• Password Protect by Feature
• Fully Encrypted data
• Other Standard Security Feauters

Reconciliation Type

• Unlimited number of Reconciliation Types
• Define the fields upon which reconciliation is to take place
• Set up Severity Levels for non-Equal matches
• Define matching criteria for all comparison fields
• Define multiple criteria with different severity levels for each field if desired
• Non-Equal matches by variance by amount, variance by percentage, transposition, misplaced decimal and others

Import Templates

• Define template for files to be reconciled
• File types of spreadsheet, CSV, text, or MIMICS
• Templates set up only once to be reused with new instances of the file
• Strip leading zeros and strip leading blanks
• Link template to actual file name
• System immediately recognizes source document fields that are available for reconciliation
• Drag and drop to map source file to fields to be compared
• After template is set up, the file instance can be imported to the recon system for recon process
• Any future instances of the file (e.g. weekly) can be imported using the template

Reconciliation Process

• Simple Process: Select Recon Type, Click Connect, Click Reconcile, view the results
• Reconciliation fields are compared on both source files using the mapping in the template.
• Reconciliation criteria defined for the fields are used in the reconciliation
• Summary results showing number of exact matches, number of non-matches, and number of non-exact matches
• Reconciliation instance along with the source reconciliation files saved for further verification and auditing.

Reconciliation Verification

• Verification screen is displayed to verify reconciliation by detail
• Grouping by severity code which also implies grouped matches and non-matches. Non-exact matches are then by severity code
• Verifier can override the system reconciliation results
• Manual matching is done by drag and drop
• Verifier can enter comments regarding a specific verification row
• Verifier can verify individual matches
• When all recon items are checked as verified, the verifier can verify the entire recon so it is available to the approver
• When the full reconciliation is verified, it is “locked” for approval processing

Reconciliation Approval

• Line items can be approved individually
• When all line items are approved, the entire recon can be approved
• The approver can reject the recon and can make notes on a line item
• Rejected recons can be reworked by the verifier
• When a recon is approved, previous instances of the same recon are deleted to clear space
• A history of all approved reconciliations is maintained for audit purposes

Reporting

• Reconciliation Types Report
• Reconciliation Audit Report
• Reconciliation Audit Detail Report
• Recon Statistics Chart
• Recon Delete Audit
• Mismatch Reconciliation Report
• Reconciliation Detail Report per Recon Type
• Import Audit Report
• Suggested Matches Report per Recon Type

Interfaces

• MIMICS Workflow Processing System
• MIMICS Portfolio System
• MIMICS Stockholder Accounting System
• All other MIMICS Systems