The purpose of the Bank Activity screen is to make it simple to match your Bank Statement to Transactions that have already been entered in to R365. Bank Activity also allows an easy creation of new Deposits and Withdrawals based on data from your Bank. Using the Bank Activity feature will make the Bank Reconciliation process significantly faster. Each transaction that is 'Matched' on Bank Activity will be checked off as 'Reconciled' on your Bank Rec.
Once your Bank Accounts are set up (click here for more information on setting up Bank Accounts), then the Bank Activity process consists of 3 steps:
- Download activity from your Bank (.qfx or .qbo file formats only)
- Upload the file on the Bank Activity screen (this will attempt to Auto-match the Bank Transaction with its corresponding R365 Transaction, if it has previously been entered)
- Manually Match / Create / Exclude / Delete entries that do not Auto-match
These steps will be described in detail below, but first, the bank activity screen consists of 4 tabs and the terminology of those tabs is important so those will be described here first:
- Unmatched - this tab shows all the remaining bank records that did not auto-match to existing transactions or match any bank rules you have created.
- Rule Applied - this tab shows all bank records that matched the logic of bank rules you have created. The system tries to match records to bank rules during initial upload of bank activity as well as each time a new rule is created or a rule is modified.
- Matched/Excluded - this tab shows all bank records that have been matched or excluded. Matching means a record downloaded from your bank has a corresponding transaction in the system for the same amount and same bank account. Records are matched and moved to this tab during initial upload of bank activity (the system applies a series of auto-matching logic as described at the bottom of this training article). They can also be matched on the Unmatched tab manually by the User and created as new transactions from the 'Unmatched' tab or the 'Rule Applied' tab. Creating a new transaction on those tabs will also automatically match that record to the transaction and move it to the matched tab. On the Unmatched tab, records can also be Excluded which will move them to this tab. Excluding is described in more detail later in this training. If something is on this tab that should not be matched or excluded then press the button on that row that says Unmatch or Include respectively.
- Bank Rule - this tab shows all bank rules you have previously created. Think of a bank rule as a transaction you want to create the same as a transaction you've created on bank activity in the past. For example, you can set a rule so any bank record uploaded where the name is "Sprint" should create a transaction for vendor "Sprint Wireless" and post to the "Telephone Expense" account. These rules are on the Bank Rule tab and can be modified and deleted there. This tab is described in more detail further down in the training.
Step 1 - Navigate to Bank Activity and Select the Bank Account
1) Open the Bank Activity Screen by hovering over the 'Banking' menu and selecting 'Get Bank Activity'
2) Select your desired bank account in the Bank Accounts selector
3) Check or Uncheck the 'Match Undeposited Funds' checkbox based on whether you want the system to match these or not.
- If Checked - The auto-matching logic will first try to match Transactions in the system for this Bank Account and if it doesn't find a match it will then try to match this record to transactions in the system that were posted to the Undeposited Funds account. If it does find a match in Undeposited Funds, it will change the Account from 'Undeposited Funds' to the Bank Account and then perform the Auto-match. It will not match to Undeposited Funds in a closed period.
- If Unchecked - Bank Activity will not match to Undeposited Funds transactions
Step 2 - Import Bank Activity from your Bank
Bank Activity can be imported into R365 using two separate methods. Method 1, via the R365 Direct Bank Connection, allows you to seamlessly download Bank Activity directly from your bank and upload into R365. Method 2 allows you to manually export your Bank Activity from your bank and then upload it into the Bank Activity Screen. Please refer to the following articles for more information on how to use each method:
Step 3 - Automatch
Once the transactions have been imported, Bank Activity will perform the Auto-matching
- The auto-matching logic will attempt to match each activity record with a Transaction in the system (auto-matching logic is outlined at the end of this article).
- Records that are successfully Auto-matched will be moved to the 'Matched/Excluded' tab
- For records that did not Auto-match an existing transaction, one of two things will occur:
- The Auto-match process will attempt to match the Activity against the conditions set on previously created Bank Rules (located on the 'Bank Rules' tab). If the Activity meets the conditions, it will be moved to the 'Rule Applied' tab.
- If the Activity does not Auto-match and does not meet any Bank Rule conditions, it will remain on the 'Unmatched' tab to await processing
Undo File Upload
If a Bank Activity file was manually uploaded to the wrong Bank Account (not applicable to Direct Bank Connections) or otherwise incorrectly uploaded, click 'Undo File Upload' to open the Undo File Upload prompt.
The prompt will display the most recently uploaded Bank Activity file and ask if the file should be removed. Click 'Yes' to remove the upload, otherwise click 'Choose a different one' to review and select a different Upload file.
Step 4 - Manually Match / Create / Exclude / or Delete Activity that didn't Auto-match
You will spend the majority of your time in Bank Activity on the 'Unmatched' tab processing Activity that was unable to be Auto-matched.
If there is a possible match in the system but the match wasn't close enough that the system could Auto-match, it will indicate there's a potential match with an 'M' on the row in the 9th column (highlighted in red in image below). Reasons for possible matches include:
- There are multiple Transactions in the system for the same amount
- There are multiple Bank Activity records imported with the same amount and only one R365 Transaction for that amount in the system
To see the possible matches, click the arrow on the far left of the row (highlighted in orange in image below) to expand the row and list the potential matches for that record (highlighted in green in image below). Click the Match button on the far right of the row that is the correct match and this record will be matched and moved to the 'Matched/Excluded' tab. Collapse the row by clicking the same arrow (highlighted in orange below).
It's possible that you imported some records from your Bank that you didn't intend to or that you've already reconciled manually. You can remove these Activity records from the unmatched tab by selecting each line then in the 'Select Command' selector, choosing 'Exclude Selected' (red arrow in image below). This will move those rows to the 'Matched/Excluded' tab. If you need to move an excluded record back to the 'Unmatched' tab from the 'Matched/Excluded' tab, simply click the 'Include' button on the 'Matched/Excluded' tab.
If you are confident that the record will not need to be Included or Matched again, you can perform the same process as described in the 'Excluding' step above, but instead, select 'Delete Selected' to manually delete the Bank Activity records from the 'Unmatched' tab
You can create Transactions in one of two ways:
1) 'Simple' Transactions that only involve:
- One GL Account
- One Location
2) Regular Transactions with multiple Accounts and Locations
'Simple' Transaction Steps:
- Populate the Vendor on the Activity row - this field is optional and only applies to Withdrawals (not Deposits).
- If you select a Vendor that has a Default Expense Account, the Account column will populate automatically.
- Set or change the Account on the Activity row - this is required and must be populated on both Withdrawals and Deposits before they can be created.
- Set or change the Location - this will default to the Location on the Bank Account and is a required field.
- Check the 'Add Rule' checkbox if you want a Bank Rule created.
- By default 'Add Rule' is not checked which means a new Bank Rule will not be created unless you check that box. Checking the box to add the rule will make it so that any other Activity records with this same name will be recommended to hit this same Vendor/Account/Location and be moved to the 'Rule Applied' tab.
- Leaving the 'Add Rule' box unchecked means that when this transaction is added, no Bank Rule will be created. Refer to the Bank Rules section below for more information.
- Add the Transaction:
- Click the 'Add' button on the Activity record row to add the single transaction immediately. If 'Add Rule' is checked, this will also create the Bank Rule.
- To add several Transactions at once, complete the required fields, then select each Activity record row. Click on 'Select Command' and select 'Add Selected'. Each Transaction will be added and any rows with 'Add Rule' checked will also create new Bank Rules.
Regular Transaction Steps:
For Transactions that require multiple GL Accounts or Locations, instead of populating the Vendor / Account / Location values on the Activity row, click the 'Withdrawal' or 'Deposit' button. A Withdrawal or Deposit form will appear in a popup where you can create the Bank Transaction with as much detail as needed. When you have entered all of the details, click 'Create Withdrawal' or 'Create Deposit' in the upper left corner of the popup to create the Transaction and move the Activity row to the 'Matched/Excluded' tab.
Clearing Unmatched Tab
Once you've finished creating and matching all the desired Transactions in the 'Unmatched' tab, if any Activity records remain, you can move them to the 'Matched / Excluded' tab by clicking the 'Remove All Unmatched' button near the top of the Bank Activity window. All remaining Bank Activity records will then be moved to the 'Matched/Excluded' tab.
Rule Applied Tab
If you have Bank Rules setup and you've uploaded transactions that match those rules, the system will recommend the rules be applied to those records. It will show those records on the 'Rule Applied' tab which looks and functions like the 'Unmatched' tab. The benefit of this tab is it shows you records matched your existing Bank Rules and it shows what Vendor / Account / Location will be assigned if you choose to add those Transactions.
Review the records on this tab, make changes to the Vendor / Account / Location fields on any row that you'd like to change and then just as on the 'Unmatched' tab, you can either add one at a time with the 'Add' button on each row, or add in bulk. Adding in bulk is usually best and the quickest way to do it is to click the Select all check box at the top of the far left column (circled in orange in image below) to select all rows and then click Select Command drop-down in the upper right above the grid and select 'Add Selected' from the drop-down (red arrow in image). This will create those Transactions and move each selected Activity row to the 'Matched/Exlcluded' tab.
This tab is where you manage your Bank Rules. Bank Rules are used so the system can attempt to assign a Vendor / Account / Location to the Activity records you import based on set conditions. Bank Rules can only be created by finding an example transaction that you want a rule to apply to going forward. On this 'Bank Rule' tab, you are able to edit that rule, along with its Conditions, which are extremely important to ensuring that the specific transactions are matched correctly.
- Note: Rules will not apply to transactions on the first creation, and if you want them to apply, you will want to undo your import and re-import the bank activity.
1) Name - The name of the bank rule. This can be a manually named to help you know this rule. By default, the rule will have the same name as the Name on the Bank record used to create this rule, but it can be modified on the Bank Rule tab
- Note: When creating a new bank rule, make sure to enter a unique name so it will be successfully added
2) Type - The type of transaction that will get created using this rule (withdrawal or deposit) and will default to the same type as the record used to create the rule
3) Bank Account - This defines which bank accounts this rule applies to. You can either select the word All and it will apply to all bank accounts or select 1 specific bank account. If you want the same rule to apply to multiple bank accounts but not to all, then you will need to create that rule for each bank account you wish to apply it to
4) Condition - This selection will determine how strict of a match the Bank Rule must make to meet the rule conditions. You can choose one of the following conditions:
5) Value - The value that the Bank Rule will try to match to the Name field on the Activity records. You can simplify the value so instead of an exact name (i.e., 'Sprint #05918') or it can be a more generic name for matching purposes (i.e., 'Sprint'). With the generic value, the condition can be set to 'Name Contains' and then during the Auto-match process, any Activity record that's name contains the word 'sprint' will be moved to the 'Rule Applied' tab for further review
6) 2nd Condition - This allows you to be more specific in your filtering on Bank Rules that are applied during the Auto-match. The same Condition options are available here
7) 2nd Value - The matching value for the second condition. This will typically be more specific as the first Condition was more generic
8) Vendor - The Vendor that will be automatically added to Bank Withdrawals for records that match this rule
9) Account - The GL Account that will be automatically added to Withdrawals or Deposits for records that match this rule
10) Location - The Location that will be automatically added to Withdrawals or Deposits for records that match this rule
Bank rules get created by having the 'Add Rule' checkbox checked on an Activity row where you populate the Vendor / Account / Location fields and click 'Add' (or bulk add). You can make changes to existing Bank Rules and then click 'Save changes' to save any updates. To delete a rule click the trashcan icon on the far right of the row.
Auto-matching Logical Process Flow:
It is important to note that when matched, it will match transactions indefinitely going back, not forward. The matching process goes through the following sequence to find matches and potential matches:
- Accounts match, Amounts match, and with a Date Range of +2 days – Mark as Match
- Same Check Number and with a Date Range of +2 days – Mark as Match
- Dates match – Mark as Match
- Amounts match and unique (only 1) and with a Date Range of -4 days – Mark as Match
- Amounts match and unique (only 1) and with a Date Range of -7 days – Mark as Match
- Locations match and with a Date Range of +2 days – Match
- Amounts match with a Date Range of +2 days – Match
- Amounts match multiple records – Mark as a potential Match (capital 'M' on 'Unmatched' tab)
- Repeat from step 1 for all Undeposited Funds Account entries after the most recent Closed Date on the Legal Entity
Issues Caused by Duplicate Bank Rules
At times, the system will stop working properly if there are duplicate Bank Expenses listed in the Bank Activity screen. Simply delete the duplicate entries by clicking the trash can iconat the end of each duplicate entry row and click 'Save Changes' just above the grid. This should enable you to rematch the Unmatched bank transactions.