Use the bank statement import to match bank statement transactions to cash book items and accounts payable payments. This will automate the process of marking the items as reconciled to eliminate or reduce this manual step in your bank reconciliation.
In this article:
- Configure the Bank Statement Import Format
- Assign the Bank Statement Import Format to the Bank Account
- Import the Bank Statement
- Match Bank Transactions
- Troubleshooting
- Post the Bank Statement
Configure the Bank Statement Import Format
To import bank statements, a bank statement format must be set up. The bank statement import format is where each column in the bank file is mapped to the appropriate field. This only needs to be created once unless the bank import file structure changes. Two formats are provided: BAI and USI Standard Bank Statement Import. These can be copied and edited or you can create a new import format:
- Click the Cash Book Import Formats link from the Main Menu. The Cash Book Import Formats screen opens.
- Click the Add button. The Add Cash Book Import screen opens.
- Enter the necessary information:
- Description - Name for import file format.
- Fixed - Used with files that do not use comma or tab as delimiters.
- No - Select if using a comma (csv) or tab (txt) delimited file.
- Yes - Select if the import file is not delimited and the field length is determined using the Start and End Positions in the import format.
- Quoted - Select if the delimiters used in your import file are quotation marks.
- Delimited - Determines how each field is separated (comma or tab) in the import file.
- CSV - Select if using a csv (Excel) file.
- None - No delimiter is used in the file and the file is a fixed length.
- Tab Delimited - Select if using a .txt file.
- Export/Import File Name - Default file name for the import file.
- Select the Details tab. This is is where you map each column in your import file to the appropriate field.
- Click Add. The Add Cash Book Import Detail screen opens.
- Enter the necessary information:
- Order - Numeric value to represent the order the line is in the file. For example, first line is 10, second line is 20, etc.
- Column Label - Column name for the column.
- <Empty Field> - Indicates no information is in the column.
- <Specify Constant> - Indicates a specific value is in the column. You enter the value into the Value field.
- Fill Factor - Determines how empty values fill if there are not enough digits in the import file. Although multiple options are available, the below are usually the only ones used for importing files:
- Blanks - A blank value is used for missing digits. Typically used for text fields.
- Blanks +/- Left - No zeros are added for missing digits and a positive or negative sign shows to the left of the value (+100 or -100). Typically used for decimal values.
- Zeros - Values show without any positive or negative signs or decimal points. Typically used for non-decimal numeric values.
- Zeros +/- Left - Zeros are used for missing digits and a positive or negative sign shows to the left of the value (+000000100 or -000000100). Typically used for decimal values.
- Value - Value for the <Specify Constant>. Only available if <Specify Constant> is selected in the Column Label drop-down.
- Start Position - Beginning position to determine the value to import. For example, if the import value is 3692458 and you enter a 4 into the Start Position field, the import will start at the 2 as it is the fourth number in the value.
- End Position - Ending position to determine the value to import. For example, if the import value is 3692458 and you enter a 6 into the Start Position field, the import will end at the 5 as it is the sixth number in the value.
- Show Decimal Point - If checked, include the decimal point in numbers.
- Number of Decimals - Number of decimals in the value.
- Click OK.
- Repeat steps 5 - 7 for each column in your import file.
- Click OK.
Assign the Bank Statement Import Format to the Bank Account
The bank statement import format must be assigned to the bank account. You can assign a different bank statement import format to each bank account or the same import format to all bank accounts.
- Click the Bank Accounts link from the Main Menu. The Bank Accounts screen opens.
- Edit the Bank Account to which you would like to assign a bank statement import format.
- In the Bank Statement Import field, select the applicable Bank Statement Import format.
- Click OK.
- Repeat steps 2-4 for each Bank Account that you wish to assign a Bank Statement Import format.
Import the Bank Statement
- Click the Bank Statement/Cash Book Transaction Imports link from the Main Menu. The Bank Statement/Cash Book Transaction Imports screen opens.
- Click the Tools button.
- Select Import. The Import Statements screen opens.
- Select Bank Statement Transaction from the Import Type drop-down.
- Select the bank account for the bank statement from the Bank Account drop-down.
- Click the Choose Files button in the File Name field. The Open screen opens.
- Select the bank statement file.
- Click Open. You return to the Import Statements screen.
- Click OK. A message displays when the bank statement is successfully imported.
Match Bank Transactions
Accounts Payable and Accounts Receivable Batches
After you successfully import the bank statement, match bank transactions from your Accounts Payable and Cash Book batches.
- Click the Bank Statement/Cash Book Transaction Imports link from the Main Menu. The Bank Statement/Cash Book Transaction Imports screen opens.
- Select the bank statement import to match.
- Right-click and select Edit. The Edit Bank Statement Import screen opens.
- Select the Tools button.
- Select Match Bank Transactions.
- Review the transaction matches.
- Click OK.
To un-match a transaction:
- Click the Bank Statement/Cash Book Transaction Imports link from the Main Menu. The Bank Statement/Cash Book Transaction Imports screen opens.
- Select the bank statement import with the item to unmatch.
- Right-click and select Edit. The Edit Bank Statement Import screen opens.
- Expand the Bank Statement Detail section.
- Select the line to unmatch.
- Right-click and select Tools -> Clear Match.
To manually match a transaction:
- Click the Bank Statement/Cash Book Transaction Imports link from the Main Menu. The Bank Statement/Cash Book Transaction Imports screen opens.
- Select the bank statement import with the item to match.
- Right-click and select Edit. The Edit Bank Statement Import screen opens.
- Expand the Bank Statement Detail section.
- Select the line to match.
- Right-click and select Tools -> Matching. The Match Transaction screen opens.
- Select the transaction to match it to.
- Click the Select button.
Troubleshooting
If you encounter a scenario where multiple deposits were created in Cash Book but the bank file has a single line or a single deposit was created in Cash Book but the bank file has multiple lines, the bank statement import item can be removed and the items reconciled manually.
To remove a bank statement import item:
- Right click the item
- Mark for Delete
- Click Save
- Mark items as Reconciled manually (See Bank Reconciliations)
Cash Book Transaction Types
You may have miscellaneous transactions such as interest received, bank fees, interest paid that is stated in your bank statement but not recorded. You can manage these using cash book transaction types.
- Within the Bank Statement/Cash Book Transaction Imports screen, select the line to not recorded
- Right-click and select Edit. The Edit Bank Statement Transaction Detail screen opens.
- Select the cash book transaction type to assign to the item from the Transaction Type drop-down.
- Click OK.
A Cash Book transaction will be created for this item. Ensure this and any relevant journals are posted prior to completing a Bank Reconciliation.
Post the Bank Statement
Click Tools and Post to post the file. Alternately, bank statements are updated to a Posted status once all the bank statement detail lines have been individually posted.
Comments
0 comments
Please sign in to leave a comment.