When you process many customer transactions, their payments do not always equal the statement balance. Applying the transactions in these scenarios can be time-consuming if you have to do it manually. The automation from Excel Importer significantly improves productivity.
You can import the customer remittance and let Excel Importer automatically match the lines to the open customer entries.
Importing the Customer Remittance
In our process below, we assume you know how to process cash receipts and apply entries using standard Business Central functionality. We have included some relevant links here in case you are still getting familiar with this.
The customer remittance gets imported from the Apply Customer Entries page and can be used either while processing a cash receipt journal or against a posted payment you posted without applying.
- To apply entries before posting the cash receipt journal, you must enter the cash receipt journal and then choose the Apply Entries action to open the Apply Customer Entries page.
- If you have already posted the cash receipt journal and did not apply it before posting, you can open the customer ledger entries page for the customer, select the payment and then choose the Apply Entries action to open the Apply Customer Entries page.
Apply Customer Entries
Once on the Apply Customer Entries page, you can choose the Import from Excel action to import the remittance advice. Concerning the image below, please note the following:
- The Remittance Status shows you the status of the imported remittance. Click on this status to open the Customer Remittance Lines page (or import from Excel if you still need to import the customer remittance).
- Choose the Import from Excel action to import remittance lines.
- The Customer Remittance Lines fact box shows you the status of the remittance lines. You can drill down on any of the totals shown here.
The following animation shows you how to import the customer remittance advice and how you can navigate to the Customer Remittance Lines.
- The Applies-to ID has been updated in Apply Customer Entries page.
- The Balance at the bottom of the page reflects zero because the payment is fully applied now.
Customer Remittance Lines
Here, you can review the remittance lines imported, how the app matched them and perform any manual handling required.
The app performs matching using references and amounts from the imported line to match against the customer entries. When you import the file, this matching gets run automatically. Some lines may not match for reasons such as:
- The remittance line does not contain the correct references (e.g. incorrect number of zeros or characters).
- Valid references, but the entries are no longer open, or there is more than one possible match.
- Incorrect customer.
The Matching Status and Matching Text can provide helpful tips about handling lines that are not matched.
|Unmatched||The line has yet to be matched to any open customer entries.|
|Matched||The line is matched to an open customer entry.|
|Matched - Under Paid||The line is matched to an open customer entry, but the remaining amount of the customer entry is higher than the amount paid as per the remittance.|
|Matched - Over Paid||The line is matched to an open customer entry, but the remaining amount of the customer entry is lower than the amount paid as per the remittance.|
|Possible Match (Amt.)||The line cannot match on references, but there are open entries for this customer with the same amount.|
|Possible Match (Ref)||The line cannot match using references, but there is more than one open entry for this customer that matches the references.|
|Possible Match (Different Customer)||Indicates that while there are no entries on the current customer account that match this line, there are entries on a different account. This is only useful when you have customers who hold multiple accounts with you and get paid from the head office. You can turn this off at a per-customer level or for all customers.|
The app will not consider any customer ledger entries where the Applies-to ID has a value.
If you find any issues with the lines, you can edit them on this page, delete the lines, correct the file, and reimport them. If you edit the lines on the page, you can run an Auto Match to match lines that are yet to be matched.
Choose this action to match the line to a customer ledger entry. This animation shows you how to perform a manual match.
Use this action to unmatch the line when you disagree with the match that has been done.
If you delete a matched remittance line, the app automatically removes the match from the customer ledger entry.
Transferring Customer Remittance Lines
Occasionally, companies receive payments from one entity and then need to distribute this payment to multiple entities. You can use the remittance transfer to handle this. This function allows you to transfer lines that you have specified to get transferred to a different customer. This feature ultimately reduces manual workarounds and eliminates the need for suspense accounts.
When you have imported a customer remittance with Excel Importer, it automatically matches the lines imported to the open customer entries. If a line does not automatically match using the references provided, it will also check open entries on other customers using the document number. Any entries meeting the criteria will be flagged as a possible match to a different customer.
You can review these lines, and if you agree with the suggestion, you can transfer the remittance lines to the suggested customer. If you transfer the lines, the following will occur:
- The remittance lines get moved to the new customer and get applied to related entries
- A journal gets posted between the original customer and the new customer. The entry in the new customer gets applied during the posting.
- A new remittance line gets added to the original customer. This line gets matched to the entry that was posted to the original customer.
The outcome of this process is:
- The consolidated payment gets fully applied to the original customer
- The portion of the payment applicable to other entities gets transferred to those entities and gets applied.
You can turn off this feature entirely or per customer. The setup for this is accessible from the customer remittance lines page.
This animation shows the process of transferring the entries:
If you navigate to the customer ledger entries, you can see the entries created and how the app automatically applied the entries:
- A debit was posted to Customer 20000, and the app applied this to the payment received from Customer 20000.
- A credit got posted to Customer 10000, and the app applied the credit to the open invoices on Customer 10000.
Post and Match Claims
Large customers may prefer to fully reconcile to your invoices and then deduct any claims they may have (e.g. for stock shortages or damaged goods). Therefore, their remittance advice might include claims that you need to investigate. The app allows you to handle this in the following way:
- For each unmatched claim, the app will:
- Raise a debit and credit on the customer account. This approach is followed so that the total amount the customer owes does not change.
- Apply the claim on the remittance to the credit posted to the customer.
- Create a Sales Credit Memo and apply this Sales Credit Memo to the debit that was posted to the customer.
- Posting the payment and applying this in full will now be possible.
- You can then send the sales credit memo for approval. The approval would have the claim details on the sales credit memo.
It may well happen that the sales credit memo needs to get reopened so that you can specify the actual item number, etc. The purpose of the sales credit is to allow you to add a workflow around this process.
The setup is available from the customer remittance lines page.
Customer Remittance Settings
Here, you can change settings specific to each customer.
|Import Template Code||Specifies the import template selected for this customer. This field gets updated if you select a different template when importing.|
|Block Transfer||Specifies that you want to prevent remittance line transfers from this customer to other customers. You configure remittance line transfers in the Remittance Setup below.|
- The app creates a default template the first time you try to import a remittance. This template has the code DEFAULT.
- If you select a different template when importing a remittance for customer, then the Import Template Code is updated for the customer so that this template is selected the next time you try to import a remittance for the customer.
Here, you specify the settings related to the remittance import. This setup is applicable for all customers.
|Claim Posting Nos.||Specifies the number series used when processing customer claims.|
|Reason Code||Specifies the Reason Code used when processing customer claims. You can override this if you enter a value on the customer remittance lines.|
|Source Code||Specifies the source code for the entries posted when processing customer claims.|
|Posting Description||This is the description used for posting and the lines created on the credit memos. You can optionally include %1, %2, %3, and %4 in the description, and these values are replaced with the document number, external document number, reason text and remittance line description, respectively.|
|Create Credit Memo||Specifies if the app should create a Credit Memo and link it to the reversal of the claim.|
|Credit Memo G/L Account||Specifies the G/L Account the app will use for the credit memo. You can manually change the account on the credit memo after it gets created.|
|Transfer Posting Nos.||Specifies the number series used when entries get posted while transferring remittance lines to new customers.|
|Reason Code - Transfer||Specifies a default reason code for transferring remittance lines to new customers. You can override this if you enter a value on the customer remittance lines.|
|Source Code - Transfer||Specifies the Source Code used when entries get posted while transferring remittance lines to new customers.|
|Posting Description - Transfer||Specifies the Posting Description used when entries get posted while transferring remittance lines to new customers. You can optionally include %1, %2, %3, and %4 in the description, and these values get replaced with the from customer number, from customer name, new customer number and new customer name, respectively.|
|Disable Auto Transfer||You can specify a value here if you do not want the auto-match to suggest possible matches to entries on different customers. This overrides the setup specific to customers.|
Microsoft Help Articles
Here are some links to Microsoft help that provide you with information about the standard functionality related to processing payments against customers:
- To fill and post a cash receipt journal
- To apply a payment to multiple customer ledger entries
- To apply posted customer ledger entries
This Remittance Import Guide shows you the detailed steps to create a cash receipt journal and import the remittance advice. The steps shown here are covered in the help above.