Bank Transaction - Bank Reconciliation

The Bank Transactions view lets the user import bank transactions from a .csv file downloaded from their bank into AMS360. During the import process, some transactions may be auto-matched to AMS360 payments and/or deposits. Then, from the Bank Center Bank Transactions view, you can manually match more of these transactions to AMS360 payments and deposits. These matched deposits and payments will automatically show as cleared in the unposted Bank Reconciliation Statement, thereby helping to complete the bank reconciliation process and eliminating the need to refer to paper or online statements. The bank balance with cleared / matched transactions applied is updated in the Transaction Summary section as often as you perform this function.

• What It Does:

  • Imports the .csv of bank transactions.

  • Suggests matches for rows that match AMS360 payments or deposits.

  • Confirms suggested matches or unmatches them, as you wish.

  • Manually matches them to payments or deposits.

• What It Does Not Do:

  • Handles Interest Earned or Bank Fees within Bank Reconciliation

  • Handles Amount Adjustments in Bank Reconciliation

  • Creates a new check.

• Locks

• Allows a single user at each bank to process the importation of bank transactions at the same time.

• Will not process the importation of bank transaction .csv files if Global Change Division Consolidation is in process once Consolidate button has been clicked for the division of the Bank Transaction specific bank. (Where that division is the From Division.)

• Will not process the importation of bank transaction .csv files if a user has the bank reconciliation form open for that specific bank.

A user who possesses full access to Bank Transactions security plus access to a bank will be able to add the "Bank Transactions" view to their Bank menu. This allows them to import bank transactions, confirm suggested matches, unmatch them, delete transaction rows, and manually match transaction rows to AMS360 transactions.

To add this security element to a user group:

• Go to Administration Center

• Select Security Group Setup

• Choose a security group that will be given this security task.

Security Task for Global Change Business Unit
Feature	Group	Task	Access	Default
Bank Center	Bank Transactions View	Bank Transactions	No Access, Full Access	Owner has Full Access; all others have No Access

The Summary section has the following information, the same as other Bank Views:

The Transactions Totals section has the following elements:

Heading	Item	Description
Match Summary	Matched Suggested Unmatched	Count of rows that are matched to an AMS360 transaction. Count of rows that have suggested matches to AMS360 transactions. Count of rows that are not matched to an AMS360 transaction.
Current Bank Balance	Currency amount	"Current Bank Balance" is the AMS360 Bank GL balance plus all uncleared deposits, less all uncleared payments. As a formula it is: AMS360 bank GL balance + sum of the unmatched deposits and payments + sum of the suggested deposits and payments; where payments are negative amounts and deposits are positive amounts.

The View Options section has the following filters for the grid:

Heading	Item	Description
Status	Matched Suggested Unmatched	Includes rows that are matched to an AMS360 transaction. Includes rows that have suggested matches to AMS360 transactions. Includes rows that are not matched to an AMS360 transaction.
Transaction Type	Currency amount	"Current Bank Balance" is the AMS360 Bank GL balance plus all uncleared deposits less all uncleared payments. As a formula it is: AMS360 bank GL balance + sum of the unmatched deposits and payments + sum of the suggested deposits and payments; where payments are negative amounts and deposits are positive amounts.
Date Range	From date and To date	Includes transactions with a GL date between these dates, including the From and To dates.

From your bank’s website, perform the following actions:

1. Select the bank account.

2. Select to download transactions.

   1. Select file type "CSV."

   2. Select the date range of the data you wish to download.

   3. Save the file on your local drive or wherever you can access it. (Hint: This file could end up in your Downloads folder, unless you specify a different folder.)

Bank Transactions Default Template

AMS360 provides a default template titled Bank Transactions. If your .csv file matches the following layout, you can use this permanent template. Furthermore, if it matches these columns, you can skip to the "Import" step. If not, see the steps below this section to create a custom template.

Bank Transactions Default Template Columns:

Column	Description	Example
Date	Date each transaction was processed, as stated in the bank transaction's .csv file	01/25/2023 or 1/2/23
Amount	Amount of deposit as a positive number with two decimals. Amount of payment (check) as a negative number with two decimals. Amount will recognize numbers with and without commas for thousand spacers, dollar signs, and negative signs in front	For a deposit row: 2084.00 For a payment row: -11,600.42
Payee / Payor	The entity or person from which you received an amount (Payor), or the entity or person to which you paid an amount (Payee).	Customer as payor, Building Supply Inc. Company as payee, Apex Brokers
Description	Any description, memo, or other text from the bank that describes the deposit or payment.	n/a
Ref #	If the bank provides a Ref# that matches an AMS360 deposit Ref#, insert that number in this column. [You may have provided them with this number.] This will help with auto-matching deposits.	2508112
Check #	The check number of a payment / check made to a Payee. This will help with auto-matching payments / checks.	11144

Create a Custom Bank Transaction Import Template

Your bank-downloaded .csv file may not exactly match the columns listed in the table above. To use the bank account transaction's .csv file without having to reorder your columns, follow the steps noted below to create a custom template that matches the way your bank file is arranged.

1. Follow the steps in the Download a bank account transactions CSV file for Import section above to download a .csv file from your bank.

2. From the Bank Transactions view, select Import File.

3. Select the New Template link to open the Data Schedule Template tool.

   

4. Select the New button.

   

5. Open the Data Schedule drop-down; from that list, choose Bank Transaction.

   

6. In the Template Name box, enter the name you wish to assign to your custom template.

7. In the Description box, enter any description to show users when to select the template (optional.)

8. In the File Sample box, select the Browse link, navigate to your Bank Transaction .csv file, and select it. You will see up to 10 rows of your .csv file displayed in the grid.

9. If the text columns look wrong, try a different Text Qualifier selection.

10. Select the link for Data Format Instructions.txt to open a pop-up that will contain helpful information regarding each data column you can include in your template; it also indicates the format for each and whether they are "required" or "optional" columns.

    

11. Bank Transactions Template Available Columns: Choose a single Amount column OR choose two columns for Deposits and Payments, to match how your bank provides the amounts in the .csv download. Do not use all three.

12. In the grid, on the grayed row where your Column headers are displayed, right-click on the first column to see the menu shown below.

    

13. Select Assign Bank Transaction Column and choose from the list shown the type of data that is in the column. In the image above, this indicates the Data column. This will populate the Date column title in the cell above the grayed row. Do the same for the remainder of the columns. If any columns are not needed, select Ignore Column and it will be skipped during the import process.

14. Here is how your template grid may look after you finish, although it will follow your .csv file column order:

    

15. Select the Save and Close button from the menu bar to save the custom template and return to the Import Bank Transactions form.

1. From the Bank Transactions view, after you have selected the Import File button and can see the Import Bank Transactions form, select the Choose File link, browse to where you saved the .csv file, and select it.

2. Returning to the Import Bank Transactions form, choose your template from the Template drop-down .

3. Enter the row number of the first row that contains data in the box titled Start Import at Row.

4. Select the Import File button to begin file validation. If no errors are found, it will begin to import the data rows.

   Invalid Import Data Detected Form

5. If errors are found, a pop-up message will display the error rows. Select Skip Errors to skip these rows and proceed to import the remaining rows. Otherwise, select Cancel to abort the import.

   

6. A message bar at the top of the view will display the following status message" “An import has started processing. Click Refresh to see the latest status.”

7. Click the Refresh button.

   

8. Once the import is completed, a green bar will show that the status has been updated. It will read: “The process completed successfully.” Additionally, you will see that the imported rows are now posted in the grid.

   

Rows that meet the following conditions will be matched to an AMS360 transaction. These rows are given an "S" in the Status column to indicate they are Suggested Matches created by auto-matching during the import process. If there are no matches, or more than one match, leave the bank transaction unmatched: "U."

For a Deposit bank transaction to match an AMS360 deposit, they both must match on Bank Date and amount of the bank transaction to an AMS360 deposit GL Date and amount. In addition, they must be for the specific bank, unmatched to another bank deposit, uncleared (open) on an unposted bank reconciliation (i.e., not saved on a bank reconciliation as "cleared"), and not on any posted bank reconciliation (i.e., not saved on a posted bank reconciliation as "reconciled").

For a Payment (Cash Disbursement) bank transaction to match an AMS360 cash disbursement, they both must match on (Ref # and amount) or (Check # and amount) or (transaction date and amount), in that priority order. In addition, they must be for the specific bank, unmatched to another bank payment, uncleared (open) on a saved not posted bank reconciliation, not in any posted bank reconciliation, and not a standard check that has not been printed (i.e., possessing no check number).

Bank Date Link Opens Matched or Suggested Matched Transaction

Rows that have a Status of "S" for Suggested Match or "M" for Matched will have the Bank Date displayed as a link that will open to the matched AMS360 transaction. This is how you can review a match prior to confirming it.

Confirm the Match for the Suggested Match Rows

When you select a Suggested Match row (Status is "S") or choose multiple "S" rows, the Confirm Match button is enabled. Suggested Match rows must be confirmed to be correct by selecting the row and then selecting the Confirm Match button. This will change the row from a Status of "S" for Suggested Match to "M" for Matched. When a row becomes a confirmed match (status = "M"), it will show as Cleared in an open Bank Reconciliation Statement.

For any row (matched or unmatched or suggested match) but especially for unmatched rows that are a Status of U for Unmatched, select the Match icon on that row (i.e., the pencil-and-yellow-paper icon), which will open a manual matching form that searches for a matching AMS360 transaction. Select the row that matches the Bank Transaction row that appears above the grid. This enables the Match and Close button. Select the Match and Close button to save the match, thus returning you to the Bank Transactions view. That row will now show an "M" for Matched.

Match Deposit Form:

Match Payment Form:

New Deposit from Match Deposit Form

There is a New Deposit button in the tool bar of the grid on the Match Deposit form. If you do not see the deposit, select this button to create a new deposit and then select receipt(s) or create receipt(s), if needed. You will be returned to the grid that now shows the new deposit.

Unposted Deposits

Unposted deposits will show as disabled in the Match Deposits form—marked with a Status of Unposted—and you can open it by the Bank Date link and post it. This action will return you to the form, where the row is now enabled and may be selected for a match.

From the Bank Transactions view, any Matched ("M") row or Suggested Match ("S") row may be selected or multi-selected, after which you can select the Unmatch button to remove the AMS360 transaction(s) linked to the row(s). NOTE: This action cannot be undone.

From the Bank Transactions view, any row can be selected or multi-selected and then deleted by selecting the Delete button. This action cannott be undone. However, you can re-import the data from your bank's .csv file, if necessary.

From the Bank Transactions view, you can export the rows in the viewable pages to a .csv file. This function excludes rows that are filtered out by View Option selections.

The new Bank Transaction view allows matched transactions to automatically move to the cleared items grid in the bank reconciliation statement, thereby allowing a previously manual process to conserve time through automation.

Statement Dates

Having the correct beginning and ending statement dates, which are chosen in the bank reconciliation statement, are crucial to ensuring all relevant matched transactions automatically appear in the cleared items grid. Only matched transactions with a bank date between these two dates will appear as automatically cleared when the bank statement is built.

It is important to note that the GL balancing date decides which transactions will appear in the bank reconciliation statement.

Example: Looking at the above scenario, if a matched transaction has a GL balancing date of May 20, 2023, and a bank transaction date of May 19, 2023, it will not appear in the bank reconciliation as either an item to clear or as an already cleared matched item, since the GL balancing date is outside of the selected parameters of this statement.

Unmatching or Deleting Transactions

If a transaction has been mistakenly or incorrectly matched, there are two ways to unmatch it.

The first is to unmatch the transaction in the bank transactions view, as defined above.

The second is to manually move the matched transaction from "cleared" to "open" items. When this action is performed, you will receive the message shown below that states the transaction will be unmatched upon performing a "save and close" function. Clicking "OK" will move the item from cleared to open, leaving the item in an unmatched state when the statement is saved and closed.

When deleting a matched transaction from the bank transactions view, the transaction will appear in the open items grid as unmatched once the bank statement is saved, closed, and then reopened.

Posting a Bank Reconciliation Statement

When posting a bank reconciliation statement, the message shown below will appear to alert you that the matched transactions will be permanently deleted from the bank transactions view upon posting.* Clicking "OK" will continue with the post, whereas clicking "Cancel" will stop the post.

Note: * This message will appear every time a bank reconciliation is posted, regardless of matched transactions being present.

If the bank statement needs to be deleted and reposted at a later date, a new bank transactions import will need to be performed to rematch the transactions to posted items in AMS360.

Helpful Tips

The bank transactions import and the bank reconciliation statement can be worked on simultaneously. Items in an unposted bank statement can be unmatched, matched, or deleted while the bank statement is open. To ensure the bank statement is up to date as it is being worked on, either click the refresh button at the top of the statement or save, close and reopen the statement. Performing either of these actions will ensure your statement remains accurate and up to date.

While the bank transaction import and the bank reconciliation statement can be worked on simultaneously, there is a lock that will appear when attempting to open a bank reconciliation statement while a bank transactions import is in progress. This is to ensure all bank transactions have the opportunity to either match or become a suggested match when importing. The message shown below will appear when opening the bank reconciliation statement whenever an import is in progress.