Statement Import

See previous W5 version guide

PURPOSE

This document explains ways to update electronic bank statements into CS Lucas system.

WHY IS THIS IMPORTANT?

Importing electronic bank statements into the system enables easy and automated update of the closing balance in the Actual Cash module.

PROCEDURE

MANUAL – Import From Designated Folder

1. Select Cash Management > Actual > Cash Balance. Click on E-Stmt button.

blank

2. Click on Import button.

blank

3. Statement Import screen displays. Click on Read button.

blank

4. System will read the bank statement file that is available in the pre-defined location (This configuration is set up by CS Lucas under Maintenance > Configuration) and display the total activities/ movement by SSI and VDate on the screen. In this example, MT940 bank statement for 2 Jan 2017. If there are no errors, system will display the Load button.

blank

5. Click on the Load button to update the statement for the given SSI and date to the system.

6. Click Yes to confirm saving.

blank

7. System will display that the selected files have been saved successfully.

blank

8. Click on the Cancel button to return to the E-Statement screen.

9. To view the bank statement activities loaded, select SSI, From Date and To Date, then click on Refresh button.

blank

blank

blank

10. Click on the Cancel button to return to Actual Cash screen to verify the balances updated in CS Lucas system. Balance update from the Actual Cash screen can be disabled by setting Auto Balance Update to Yes in accounting centre SSI details, see Accounting Centre SSI Set Up.

blank

blank

blank

11. To view change history for actual cash balance of a particular SSI, Click on History button.

12. On the Actual Cash History page, select the accounting centre, SSI and dates. Click on Refresh button. This page shows all the changes done for this SSI’s Actual Cash for the dates selected. You can also export this table to excel by clicking Action > Export CSV.

blank

MANUAL – MT940 File Import

13. Select Cash Management > Actual > Cash Balance. Click on E-Stmt button.

blank

14. Click on Import.

blank

15. Click on Read File checkbox.

blank

16. Select MT940 from the dropdown field.

blank

17. Click Choose File to browse for the file to read.

blank

18. Click Read File button.

blank

19. System will read all the activities available in MT940 file and display on the import screen by VDate. Amount displayed is the total activities/movement by SSI and VDate. If there are no errors, system will display the Load button.

blank

Note: For first MT940 import of new bank account, please ensure there is previous day closing available in the system before the system allows to import. See FAQ03 below for more details.

20. Click on Load button to import the file.

21. Click Yes to confirm saving.

blank

22. System will display that the selected file has been saved successfully once completed.

blank

MANUAL – Excel File Import

23. Select Cash Management > Actual > Cash Balance. Click on E-Stmt button.

blank

24. Click on Import.

blank

25. Click on Read File checkbox.

blank

26. Click on Template button to download the Excel import template.

blank

27. Save file on your computer and open it.

blank

28. Fill out the fields in the import template. Enter SSI short name, statement number, sequence number, VDate, Amount, Narrative 1 and Narrative 2.

blank

29. Save the file when completed.

30. To import the bank statements via Excel, select Excel from the dropdown field.

blank

31. Click Choose File to browse for the Excel import file to read.

blank

32. Click Read File button.

blank

33. System will read all the activities available in Excel file and display on the import screen. If there are no errors, system will display the Load button.

blank

34. Click on Load button to import the file.

35. Click Yes to confirm saving.

blank

36. System will display that the selected file has been saved successfully once completed.

blank

SCHEDULED IMPORT

37. Using the scheduled function, system will read the files at a particular time that is setup in the configuration. It will then save all the closing balances to the system. If the system encounters any errors during this process e.g. unmatched account numbers, it will send these errors to the authorised user by email. To allow a user to receive these error messages, access right 17510 (Receive MT940 Auto Load Error Logs) would need to be assigned to them.

FREQUENTLY ASKED QUESTIONS

FAQ01. What would happen if the account number in the MT940 file does not match the SSI account numbers set up in the CS Lucas system?

System will show “Error: Account number unmatched.” When this SSI is set up, try to read the MT940 messages again.

blank

FAQ02. What would happen when importing MT940 file if there are two SSIs in the system with the same account number and currency in the CS Lucas system?

System will show “Error: More than 1 SSI found with same account number.” System will also display the SSIs with the same account number and currency. Resolve this duplicate SSI and try to read the MT940 messages again.

blank

FAQ03. How to commence importing MT940 manually into the system for a new bank account?

To commence importing MT940 manually for new bank account, previous day closing balance must be updated in the system before importing.

For example, to import MT940 file for GBP bank account “7667434” for value date 30 Dec 2016. You need to first update a previous day closing balance on 29 Dec 2016.

blank

Click Save Balance.

If there is no previous day closing balance available and you are trying to do a manual import, you will see the following error message.

blank

FAQ04. What are the validations in place in CS Lucas to ensure the completeness of bank activities loaded from MT940 statement?

When MT940 statement is used to update the actual cash, CS Lucas requires that the MT940 statement to faithfully reflect the actual bank statements. Otherwise, the actual cash balance computed by summing the individual cash activities imported into CS Lucas will not tally with the physical bank statement. This can lead to serious error in cash planning. Bank reconciliation exercise becomes meaningless.

To ensure MT940 statement consistency, CS Lucas performs the following checks:

a) The Opening balance (Field 60F) in the MT940 statement tally with the previous closing available balance (current value date balance) in CS Lucas system. If there is a discrepancy in reconciling the closing available balance, CS Lucas system will then cross-reference it with the closing ledger balance (Field 62F, as provided in the statement) stored within the CS Lucas system.

Failure on this check will cause the file to be rejected.

Corrective actions:

1) Recommended. Request from the bank a MT940 statement that correct the inconsistency of the opening balance.

2) Disable MT940 load and allow actual bank balance to be manually imported to correct the previous day closing balance in CS Lucas. This will result in lost bank activities of previously loaded MT940 statements if bank balances on those dates are manually changed.

b) Opening balance in the MT940 bank statement + the sum of total cash activity reflected in the MT940 statement = closing balance in the MT940 statement.

This is merely checking that net movement in the MT940 bank statement is fully represented by all and only those items contained in the same MT940 bank statement.

Failure on this check will cause the file to be rejected.

Corrective actions:

1) Recommended. Request from the bank a MT940 statement that correct the inconsistency between the opening balance, the closing balance and the activity.

2) Disable MT940 load and allow actual bank balance to be manually imported to correct the previous day closing balance in CS Lucas. This will result in lost bank activities of previously loaded MT940 statements if bank balances on those dates are manually changed.

c) MT940 bank statements are sequentially controlled. CS Lucas check for consistency in the sequence. Statement loaded out of sequence will be rejected.

Corrective actions:

1) Recommended. Request from bank a MT940 statement that maintains a correct sequencing and load a null MT940 if necessary to avoid skipping sequence.

2) Switch off the validation of MT940 sequence number check in CS Lucas. When this is done, the cash activities in CS Lucas cannot be guaranteed for completeness.

RELATED INFORMATION

E-Statement

Cash and Ledger Balance

CHANGE HISTORY

blank

Categories:

0 Comments

Leave a Reply

blank

Your email address will not be published.



    Close Bitnami banner
    Bitnami