Product documentation
After the lines have been imported and split, the reconciliation can be started via [Functions, Reconciliation]. Per line, the system searches through the open invoices of the identified customer or vendor and tries to find invoices based on the invoice number(s) in the field Note, and the invoice amount(s). For lines of type 'Ledger' it tries to find the correct ledger account and financial dimensions.
Banking supports 8 main reconciliation scenarios:
1. Customer payment is settled against one or more invoices
2. Customer direct debit is settled against a bridging transaction
3. Customer direct debit is cancelled, and the customer invoice(s) are reopened
4. Vendor payment is settled against one or more invoices
5. Vendor payment is settled against a bridging transaction
6. Vendor payment is settled against a bridging transaction - batch
7. Transactions get assigned to a ledger account and financial dimensions
8. Processing of unapproved vendor invoices
Depending on the result a color indicator will appear in the grid:
|
|
Customer |
Invoice number |
Amount |
Discount |
Match |
 |
= or X |
X |
X |
|
No |
 |
= |
= |
X |
|
User decides |
 |
= |
X |
= of one invoice |
|
User decides |
 |
= |
= / X |
= |
after cash |
User decides |
 |
= |
= / X |
= |
= |
Automatic |
If the icon is white, no customer or vendor has been found, or the reconcile function has not been executed yet.
When the reconciliation is executed the bank journal lines will get a color code:
| Color | Description |
| Green | Invoices have been found by invoice number and the amount match is 100%. See note below |
| Blue (K) | Invoice has been found by invoice number and exact match on amount minus cash discount but outside cash discount period |
| Yellow (F) | Invoice number has been found by invoice number, but amount did not match |
| Orange (B) | No invoices have been recognized, but the received amount has an 100% match on the amount of one open invoice without taking into account the setup of penny differences or cash discounts |
| Red | No match at all |
If the system could make a green match, the found invoices are settled with the bank transaction.
If no invoices have been recognized by invoice numbers, but the amount received matches the total open amount of all invoices, the colour will also be green.
Filter
On the top the color codes filters can be switched on. Standard, all colors are active, so all records are shown. By switching off green and white, the user can focus on the exceptions, i.e. the bank transactions that are not matched completely with invoices.
Important
Partial payments
In addition to the standard logic there are additional parameters on the journal name (General ledger > Journal Setup > Journal names) which will control the settlement and the colors in case of partial payments.
| Field | Description |
| Partial Settlements |
| Settle |
No: Standard behavior as described above Yes: In case a Yellow reconciliation match has been determined (=payment differences not equal to cash discounts), this parameter will mark the recognized invoice(s) for settlement. The Mark Green parameter will determine whether the line will be set to Green in certain scenario’s. |
|
Mark green
Never Penny difference Overpayment /under payment Always
|
Parameter will be enabled when Settle is set to Yes
Color remains yellow and need to be marked green manually. If the difference is within the penny difference tolerance it will be marked green otherwise it remain yellow. If the difference is within the over/under payment tolerance it will be marked green otherwise it remain yellow All journal lines will be marked green. This option will only be used if you just want to process the payments without further interaction with it, assuming that payment differences will be dealt with in another way after the journal has been posted. |
Example
1 Invoice of 100 Euro
Max penny difference of 0,02 Euro
Max under/over payment of 0,25 Euro
Cash discount 1%
There is a difference in the logic between a payment of 3 invoices via the payment note versus the same payment via import of the payment specification. Reason in case of the import via the payment specification the system knows exactly what the amount paid per invoice is, where as in the payment note you only have one payment for the total of the three payments.
E.g. When the parameter of max penny difference is set to 0,02 and a payment is done for 3 invoices with a difference of 0,01 per invoice the system will not be able to determine whether the difference is 0,03 for only one of the payment and therefor it will be marked yellow. In case of an import of the payment specification the split functionality will split the payment into three line and then all lines will be marked green.
Also it’s important to know that our solution respects the standard way Microsoft is calculating the settlement amount. When the payment is done within the discount period, the amount the system will be used to calculate the under/over payment against is the amount minus the cash discount amount. When the payment is done outside the discount period it’s the open invoice amount against which the system will calculate the under/over payment. This value is used for matching.
In some cases, the information sent on the bank account statement, is incorrect or incomplete. Invoice numbers may be abbreviated, or mistyped in the customer’s administrative system. On the payment tab, the field Note is filled with the payment reference that was specified by the customer.
Invoices are found based on information in this field. The way this field is interpreted, can be defined in the bank reconciliation settings. If the data is not correct, the user can change this field and choose manually [Functions, Settlement]. This functionality can also be helpful, if a user just wants to test the reconciliation, and not the import.
The function Settlement can be used as many times as necessary. Green lines remain green, and other lines may get a new status and color if the information from the bank account statement is corrected or adjusted.
If the match is green, the system will automatically settle the open transaction. If the user chooses [Functions, Settlement] again, the system will ask: Marked transactions exist. Remove them? Normally you will want to keep the already settled transactions, so the answer should be No. In the next screen, the user can define what lines to mark in the column Mark.
In some situations, open transactions must be settled against payments manually. Therefore, the user can manually set a line to green. When the settlement screen is closing, and open transactions are marked, the user is asked whether the according bank transaction line should be switched to green (regardless the previous color). If the user denies, the line gets color white (regardless the previous color).
If, for some reason the system did not set a line to green, but the user is convinced the data is correct, they can make the line green by clicking the 'Accept' button.
The user can reset a green line (settled) to the white (unsettled), if a correction needs to be made. Use the button 'Undo' for this.
Note
The tracing log functionality has been added to the Banking Journal to help the user understand why Microsoft Dynamics 365 for Finance and Operations found (or did not find) a customer, vendor or invoice. The logging can be activated in the Bank Reconciliation setup.
When 'Infolog' is selected, an infolog will appear in the journal after the reconciliation is run. When 'Table' is selected, the reconciliation log is available in the Journal Lines button Banking log.
If bank transaction lines have type ‘Ledger’, and the ledger account is filled, the Settlement function will put these lines to green.
Not all bank transactions refer to a customer or vendor. The solution also provides the possibility to define payment terminals.
These terminals are linked to ledger accounts, and on importing the bank statement the system will search for substrings referring to terminals and create lines against pre-defined ledger accounts.
This functionality can be used if you want the system to use a ledger account, based on a fixed description, like a POS-id, payment terminal, or any other standardized bank transaction (interest, banking fees, etc).
On import of a bank statement (Import Account Statement (Transactions), the system compares the substring of the line identified with ":86" to the Payment Terminal table. If there is a match against a Payment terminal number, the system will create a line in the journal using the segmented entry as specified in field ‘Account’.
Example:
A Dutch MT940 file would hold information regarding the payment terminals:
In the payment terminal form one would link codes 0QB903, 634C03, 1H34MD, T775BT, S6TH9G, and 9GGJ1X to their respective ledger accounts.
As of release 1007.31.620, this functionality not only allows the specification of ledger accounts, but also of vendor and customers.
The last step in the process is posting the journal. The system will check the parameter All lines must be green on the journal header. If enabled, the journal can only be posted when all lines are marked green.
In case of a split journal all journals will be validated and posted together.
Occasionally, fully paid invoices have a small balance due to e.g. currency fluctuations. If the open amount of an invoice does not correspond with the amount in the grid, standard Microsoft Dynamics 365 for Finance and Operations asks the user if the amount in the grid needs to be adjusted. Usually, that is not desired. In Banking, the user is asked the same question, but if answered positively, the difference in amount is put in a new line of the grid, making it easy for the user to post it to a ledger account.
Example:
The imported bank statement line has a value of 100,-. The user manually settles the line against an open invoice of 98,-. Microsoft Dynamics 365 for Finance and Operations recognizes a difference of 2,- and will ask the user if they want the system to adjust the amount of the journal line. If the user answers ‘Yes’, a new journal line is created for 2,- and the original line is changed to 98,-. If the user answers ‘No’, nothing happens to the journal line, and the amount stays 100,-
As of release 10020.44.730.26272 fields are present for the original invoice currency and the (cross)rate used by the bank. And during reconciliation, those values used to set the amount and settle the invoice.
For CAMT053 fields <SrcCcy> and <XchgRate> are taken from section <AmtDtls> As of release 10020.44.730.26272 fields are present for the original invoice currency and the (cross)rate used by the bank. And during reconciliation, those values used to set the amount and settle the invoice.
For CAMT053 fields <SrcCcy> and <XchgRate> are taken from section <AmtDtls>
General ledger > Ledger Setup > Bank reconciliation > tab: Company recognition
As of release 10020.44.730.26272 there is new tabpage in the set-up that maps invoice-formats to companies. E.g. INV-###### would map to company DEMF and FAC-###### would map to FSRI. After importing the Bank Statement, a routine ‘recognize company’ is called, and using the invoice format in the payment note, it determines the original company of the invoice. The company on the journal line is replaced with this original company of the invoice.
The ‘Reconcile’ routine now uses the company from the journal line to find invoices in that company and the invoice is settled in the original company. On posting the journal, Dynamics processes everything and creates 2 journals (one in the current company, and one in the original company of the invoice)
The system uses the grace period of the method of payment (as identified on the open invoice), when determining if customer cash discounts were properly taken. The grace period is added to the cash date on the open invoice. If the payment date is within this calculated date, the system will flag the line as green. Otherwise it will flag the line as cyan.
Example:
If no open transactions are marked during manual settlement, the user is not prompted if the green color should be set. A workaround would be, to manually mark a transaction for settlement, confirming that the record should be marked green, and afterwards unmark the transaction again.
When the Creditor reference information field is populated on the bank statement, the information used to match invoices is not longer done based on the information from the payment note info of the bank journal, instead it will use the value from the Creditor reference information and it search for invoices where the payment Id equals the value of the Credit reference information. If not found there is no fall back.
This logic is only executed if the Payment note configuration has not been defined on the bank account of the bank journal.