Internal Account Bank Reconciliation

Bank reconciliation is the process of matching the balances in Focus’ records for a cash account to the corresponding information on the bank statement. The goal of this process is to determine the differences between the two, and to book changes to the accounting records as appropriate. The information on the bank statement is the bank's record of all transactions impacting the entity's bank account during the past month.

You can customize several of the settings for Bank Reconciliation via Setup > Settings > Miscellaneous tab > Bank Reconciliation.

Profile permissions must be established before utilizing the Bank Reconciliation screen via Setup > Profiles. Select the Permission Type: Menu, the Role: Admin, and the applicable Profiles from the pull-down. Click Submit. In the Budgeting/General Ledger section, set the applicable Internal Account Bank Reconciliation permissions.

Creating a New Reconciliation

1. From the Budgeting/General Ledger menu, click Internal Account Bank Reconciliation.

2. From the Internal Account Bank Reconciliation screen, click the Upload tab to upload a new bank file and create a bank reconciliation.

3. Select the correct fiscal year from the FY pull-down. The pull-down defaults to the current fiscal year.

From the Upload screen, the system must be configured to process the formatted bank document. Once the fields have been setup, Focus will pull in the same settings for each subsequent file that is uploaded for the designated bank.

4. If a partial reconciliation has been started, select the reconciliation from the Edit Existing Reconciliation pull-down to add another entry to the original.

5. Enter the Start Date and End Date of the applicable bank statement.

6. Select the applicable Bank from the corresponding pull-down that will be reconciled. If reconciling multiple accounts, create a new entry line for each account and include the Bank column in the uploaded statement.

If necessary, click the delete button (red minus sign) to remove a selected Bank.

7. You are required to enter the Beginning Balance from the statement if this is the first upload for the selected bank account. Otherwise, the Beginning Balance will populate automatically according to the ending balance recorded in the previous reconciliation. If necessary, edit this field to reflect the true beginning balance on the current bank statement.

8. Enter an Ending Balance if the bank statement will not be uploaded, and the reconciliation is being completed manually.

9. If this is the first time uploading a statement for the selected bank account, enter the titles of the File Header Columns exactly as they appear on the bank statement.

File headers are subject to case, space, and punctuation; if they are not entered precisely, the reconciliation will not run.

10. Select the File Type of the downloaded bank statement from the pull-down; options include CSV: comma-separated values are separated using commas as a delimiter or Tab Delimited: tab-separated values are separated using tabs as a delimiter.

11. Upload the bank statement file in the Files section. As indicated in the Files section, drag files directly into the box to attach documents or click the Select button to browse for files. There is also a Scan File button next to the Select button for direct scanning. Click the Scan File button; this will prompt you to download Dynamsoft. This is a one-time download that allows you to attach files directly from your scanner. In doing so, you will bypass saving the file to your computer first.

12. Click Upload.

Formatting the Bank Statement

In order for the system to read and process the transactions from the bank statement against the transactions recorded in Focus, the bank file itself must be configured properly.

The statement should be a CSV file or a TSV (tab delimited), and must contain the following six columns, at minimum:

  • Date: Transaction date
  • Check Number: The number printed on the check
  • Description: Brief explanation of the transaction type
  • Debits: Dollar amount of the debit transaction (i.e. check)
  • Credits: Dollar amount of the credit transaction (i.e. deposit)
  • Bank Code: Required only when reconciling multiple banks at once

The first row of the file must be designated for the column headers each time a statement is uploaded. Take note of the exact spelling, capitalization, and spacing used for each column header as it will be keyed into Focus in the same way it appears on the file.

Existing Reconciliations

1. From the Budgeting/General Ledger menu, click Internal Account Bank Reconciliation.

2. Click the Existing tab for all existing reconciliations.

3. Select the correct fiscal year from the FY pull-down. The pull-down defaults to the current fiscal year.

Existing Reconciliations displays the User responsible for the reconciliation, the Bank Account, the Period Start Date, Period End Date, the Date Created, and the Date Finalized.

4. Click the View button to review all reconciliation data via the Current Reconciliation tab, which includes the following tabs: Balances, Reconciled Checks, Outstanding Checks, Voided Checks, Check Errors, Reconciled Credits, Reconciled Debits, and Summary.

5. Before the reconciliation is finalized, you can click the Delete button to delete the bank reconciliation.

6. If you have permission, click View All Users to view all bank reconciliations created by all other users as well.

a. Upon viewing all users, to revert back to only your reconciliations, click Hide All Users.

Balances

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Balances tab.

2. View the Type, Beginning Balance, Credits, Debits, and Ending Balance reflected on the bank statement as compared to what is recorded in Focus.

The Difference row will show a zero balance when both accounts have been fully reconciled. If any column displays a balance other than zero, review the appropriate tab and reconcile the transactions listed.

3. Review the Outstanding Check Balance, the Outstanding Debit Balance, and the Reconciled Balance.

4. As indicated in the Files section, drag files directly into the box to attach documents or click the Select button to browse for files. There is also a Scan File button next to the Select button for direct scanning. Click the Scan File button; this will prompt you to download Dynamsoft. This is a one-time download that allows you to attach files directly from your scanner. In doing so, you will bypass saving the file to your computer first.

a. To review document attached, click the document link. You also have the option to remove attached files by clicking the delete button (red minus sign).

5. To add a comment, type the comment in the provided text box. Then, click Post.

If you are trying to reconcile multiple bank accounts, all account information will be listed in the Combined Balances section, then each bank account will be listed with corresponding data.

6. Once the Bank and System amounts balance, click Finalize Reconciliation to record the reconciliation as complete in the Focus ERP system. Once finalized, the record can be reviewed from the Existing tab, but cannot be altered.

With the appropriate permission, you can click the Unfinalize button to revert finalized reconciliations back to unreconciled.

The Unfinalize button only displays when the permission Unfinalize Reconciliations is enabled via Setup > Profiles.

Reconciled Checks

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Reconciled Checks tab.

The Reconciled Checks tab lists all checks that have been either automatically or manually reconciled between the ERP system and the bank statement. Checks will automatically be reconciled and listed here if the system is able to find an exact match on the Check Date, Check #, and Amount.

The last row of the Reconciled Checks displays the Total for the Amount column.

The Total displayed will change if Filters are applied. See Additional Features for more information.

2. Click the Check # link to open the corresponding check via Purchasing/Accounts Payable > Internal Account Checks.

Outstanding Checks

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Outstanding Checks tab.

Any checks that have been cut out of Focus and that do not match with any record from the uploaded bank file will be listed here. Outstanding checks will continue to rollover and accumulate until a match is found on a subsequent month’s bank statement or until the check is cleared manually.

2. To manually clear a check, select the Clear checkbox and click the Save button.

3. A pop-up window displays prompting you to enter the cleared date of the selected check. Once entered, click the OK button. You can also click Cancel to close the window and return to the Outstanding Checks tab.

The check will be removed from the list and the Amount Total will be adjusted accordingly.

The last row also displays the Total of the Ledger Amount column.

The Total of the Amount column and the Ledger Amount column displayed will change if Filters are applied. See Additional Features for more information.

4. Click the Check # link to open the corresponding check via Purchasing/Accounts Payable > Internal Account Checks.

Voided Checks

Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Voided Checks tab.

The Voided Checks tab displays a report of all voided checks during the reconciliation period for the banks included in the reconciliation vis a vis the following columns: the Check Date, Bank name, Check #, the Amount, the Ledger Amount, the Vendor Name, and the Vendor Number.

The last row also displays the Total of the Amount and Ledger Amount columns.

The Total of the Amount column and the Ledger Amount column displayed will change if Filters are applied. See Additional Features for more information.

Check Errors

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Check Errors tab.

The Check Errors tab displays a list of checks that were not reconciled with the uploaded bank statement.

The cause of each check error is explained in the Description column.

The system will identify the check record with one of 3 error descriptions:

  • The check exists in the statement but not in the system.
  • The check is duplicated in the statement.
  • The check amounts are not equal.

2. To clear the check, click the Reconcile button.

The check exists in the statement but not in the system:

a. In the pop-up window, select the correct radio button depending on how you'd like to resolve the error.

b. Select Enter the correct check number, if the check number was entered incorrectly and enter the Check number in the text box. Then, click OK. You can also click Cancel to close the pop-up window and return to the Check Errors tab.

c. Select Resolve as bank error if the check error was an error on the statement but not in the system and enter an Explanation in the text box. Then, click OK. You can also click Cancel to close the pop-up window and return to the Check Errors tab.

d. Select Don't link to a check if the error cannot be linked to a check or noted as a bank error. You can manually resolve the error by selecting Don't link to a check, but it is not recommended. Then, click OK. You can also click Cancel to close the pop-up window and return to the Check Errors tab.

The check is duplicated in the statement:

a. If the same check was entered twice, leave the Check number text box blank.

b. Click OK. You can also click Cancel to close the pop-up window and return to the Check Errors tab.

The check amounts are not equal:

a. If the check amounts are not equal, enter the correct Check number in the text box.

b. Click OK. You can also click Cancel to close the pop-up window and return to the Check Errors tab. Note: Once reconciled, accept the system amount as well as the bank amount.

3. Click View Reconciled to view all checks that have been reconciled.

a. From this screen, you can review all checks that have been reconciled, and Unreconcile the checks, if necessary.

b. To go back to all check errors that have not been reconciled, click View Unreconciled.

The last row also displays the Amount column.

The Total of the Amount column displayed will change if Filters are applied. See Additional Features for more information.

Reconciled Credits

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Reconciled Credits tab.

The Reconciled Credits and Unreconciled Credits tabs are used to work through balancing all receipt/deposit entries. The Reconciled Credits tab displays credits that have been reconciled via the Unreconciled Credits tab.

The Reconciled Credits report displays the Date, Bank, Source # (dependent upon the Source Type such as Deposit), Source Type, Amount, Ledger Amount, Description, and Notes.

2. Select the Unreconcile check box and click Save to unreconcile the applicable credits. Upon clicking save the credits will be moved back to the Unreconciled Credits tab.

3. Enter any additional Notes in the provided text field.

4. Hover over reconciled credits highlighted in red for additional information: This outstanding credit was manually cleared and will not be included in the system or bank balances.

5. Click the Source # link to open the corresponding Source Type, such as Manual Journal, Deposit, etc. Notice manual reconciliations do not have a Source #.

The last row of the Reconciled Credits reports displays the Total for the Amount and the Ledger Amount.

The Total of the Amount column displayed will change if Filters are applied. See Additional Features for more information.

Unreconciled Credits

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Unreconciled Credits tab.

The Reconciled Credits and Unreconciled Credits tabs are used to work through balancing all receipt/deposit entries. The Unreconciled Credits tab displays all credits contained in the bank file.

The Unreconciled Credits report displays the Date, the Bank, the Amount, and the Description (lists the bank file transaction title).

2. Enter any applicable Notes for each unreconciled credit.

3. From the Reconcile column, match credits to records in the system by selecting the record from the pull-down. Records that are an exact match on date and amount will be selected automatically; those that do not match must be selected or a receipt must be generated.

4. Select the Manual check box to manually clear a credit without linking it to a system record. Manually clearing a record will not impact system balances. This option can be used for a transaction that occurred on the bank statement that will not be reflected in the system. Note: The Manual check box will override a selected credit from the Reconcile column if both are selected.

a. Upon clicking Save, a pop-up window displays where you must enter the clear date.

b. Click OK to submit the date or click Cancel to close the pop-up window and return to the Unreconciled Credits tab.

5. Click Create Receipt to generate a receipt record in the system to link it to the bank entry, if one has not already been created.

a. In the pop-up window, select the Bank from the corresponding pull-down, if reconciling multiple banks.

b. Enter the Description in the provided text box.

c. If applicable, select the Product from the pull-down to reference a product from the product list.

d. Click Create to generate the receipt, which is recorded via Accounts Receivable > Internal Account Receipts.

6. Click the Save button apply changes. Clicking save moves all reconciled and manual credits to the Reconciled Credits tab.

The last row of the Unreconciled Credits report displays the Total for the Amount column.

The Total of the Amount column displayed will change if Filters are applied. See Additional Features for more information.

Outstanding Credits and Outstanding Debits

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Outstanding Credits tab or the Outstanding Debits tab.

The outstanding credits/debits tabs include all transactions in the system from the current and previous periods that have not been reconciled. As entries from the unreconciled tabs are reconciled, they will be removed from the outstanding tabs.

If receipts are not deposited in the month for which they are dated, they will continue to display as outstanding in future reconciliations; this also applies to deposits, which will remain as outstanding credit until cleared.

Receipts and Deposits remain as outstanding credits on the Outstanding Credits tab if voided in a future month.

The Outstanding Credits report displays the Date, the Bank, the Source # (dependent upon the Source Type such as Deposit), the Source Type, the Amount, the Ledger Amount, the Receipt Type, the Description (lists the bank file transaction title), and Notes made.

The Outstanding Debits report displays the Date, the Bank, the Source # (dependent upon the Source Type such as Check), the Source Type, the Amount, the Ledger Amount, the Description (lists the bank file transaction title), and Notes made.

2. Select the Clear check box to manually clear the credit/debit transaction when the bank entry record does not match the system record.

3. Enter any applicable Notes in the provided text field and click Save.

4. Upon selecting the Clear check box and clicking Save, a pop-up window displays. Enter a cleared date for the transaction, the last day of the reconciliation period will be defaulted but can be modified if needed. Click OK to manually reconcile the outstanding record and move it to the applicable Reconciled tab. You can also click Cancel to close the pop-up window and return to the applicable Outstanding tab.

5. Click the Source # link to open the corresponding Source Type, such as Manual Journal, Deposit, Checks. Notice manual reconciliations do not have a Source #.

The last row of the Outstanding Credits and Outstanding Debits reports displays the Total for the Amount and the Ledger Amount.

The Total of the Amount column displayed will change if Filters are applied. See Additional Features for more information.

Unreconciled Debits

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Unreconciled Debits tab.

The Debits tabs are used to work through balancing all debit entries. The Unreconciled Debits tab displays all debits contained in the bank file.

The Unreconciled Debits report displays the Date, Bank, Amount, Description (lists the bank file transaction title), Notes, and additional selections.

2. Enter any applicable Notes for each unreconciled debit.

3. From the Reconcile column, match debits to records in the system by selecting the record from the pull-down. Records that are an exact match on date and amount will be selected automatically; those that do not match must be selected or a manual journal must be created.

4. Select the Manual check box to manually clear a debit without linking it to a system record. Manually clearing a record will not impact system balances. This option can be used for a transaction that occurred on the bank statement that will not be reflected in the system. Note: The Manual check box will override a selected debit from the Reconcile column if both are selected.

a. Upon clicking Save, a pop-up window displays where you must enter the clear date. Click OK to submit the date or click Cancel to close the pop-up window and return to the Unreconciled Debits tab.

5. Click Create Manual Journal to generate a journal record in the system to tie to the bank entry, , if one has not already been created.

a. In the pop-up window, enter the manual journal Draft Title in the provided text field.

b. Select a Template from the pull-down, if applicable.

c. Click OK to create the manual journal be matched to the bank record and reconciled. Click Cancel to close the pop-up window and return to the Unreconciled Debits tab.

Once created, the manual journal displays via Budgeting/General Ledger > Internal Account Manual Journals.

6. Click the Save button apply changes. Clicking save moves all reconciled and manual debits to the Reconciled Debits tab.

The last row of the Unreconciled Debits report displays the Total for the Amount column.

The Total of the Amount column displayed will change if Filters are applied. See Additional Features for more information.

Reconciled Debits

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Reconciled Debits tab.

The Reconciled Debits tab and the Unreconciled Debits tab is used to work through balancing all debit entries.

The Reconciled Debits report displays the Date, Bank, Source # (dependent upon the Source Type such as Check), Source Type, Amount, Ledger Amount, Description, and Notes.

2. Select the Unreconcile check box and click Save to unreconcile the applicable debits. Upon clicking save the debits will be moved back to the Unreconciled Debits tab.

3. Enter any additional Notes in the provided text field.

4. Hover over reconciled debits highlighted in red for additional information: This outstanding debit was manually cleared and will not be included in the system or bank balances.

5. Click the Source # link to open the corresponding Source Type, such as Check or Manual Journal.

The last row of the Reconciled Debits reports displays the Total for the Amount and the Ledger Amount.

The Total of the Amount and Ledger Amount columns displayed will change if Filters are applied. See Additional Features for more information.

Summary

1. Upon uploading a bank statement and starting a bank reconciliation or clicking View from the Current Reconciliations tab, the Current Reconciliation tab displays; from here, click the Summary tab.

The Summary tab provides a breakdown of the Bank Reconciliation, compares the bank totals to the system totals, and displays the difference (if any).

The Adjusted Bank Balance is the sum of Cleared Checks, Cleared Debits, and Cleared Deposits/Credits in the reconciliation.

The Adjusted Ledger Balance is the sum of the transactions in the system from the applicable period.

The reconciliation must be in balance between the ledger and bank; i.e. the Difference must equal 0.00 in order to be finalized.

If the Bank Reconciliation setting (Enable adjustment entry on reconciliation summary tab) for allowing adjustments is enabled via  Setup > Settings > Miscellaneous tab > Bank Reconciliation, the Adjustments section is displayed for entering manual adjustments.

2. Enter a Title for the difference in the provided text box using the first blank row in the table.

3. Select a Bank (if reconciling multiple banks) from the pull-down.

4. Enter the Amount in the provided text box.

5. Select the Bank or Ledger check box to indicate whether the adjustment will reflect the Bank or Ledger balance.

6. Press the Enter key to save the line of data. Once saved, the balances in the summary will update automatically. The line turns blue and the delete button (red minus sign) displays.

At the bottom of the summary will be a report of the transactions included in the reconciliation. This includes Cleared Checks, Deposits and Credits, Outstanding Transactions and Voided Checks.

7. Click Print to print the summary report, which will enable applicable users to sign the report as needed via Reconciled By and Approved By.

a. Clicking Print displays in a print preview screen. From this screen you have several options including, Rotate clockwise (circular arrow), Download as a PDF (down arrow), Print (printer icon), Fit to page (small inner arrows), Zoom in (plus sign), and Zoom out (minus sign).

b. Click the white X to close the pop-up window.

Manual Reconciliations

Manual Reconciliations allow users to manually enter bank information, such as the start and end dates as well as the balances. Once all information has been entered, users can then review each record in the system and manually clear them without matching them to data recorded in the system.

In order to create manual bank reconciliations, you must the profile permission to access the Manual tab via Setup > Profiles.

1. From the Budgeting/General Ledger menu, click Internal Account Bank Reconciliation.

2. From the Internal Account Bank Reconciliation screen, click the Manual tab.

Completing Bank Reconciliations manually via the Manual tab is not recommended.

3. Select the correct fiscal year from the FY pull-down. The pull-down defaults to the current fiscal year.

4. Enter the Statement Information starting with the bank statement Start Date and End Date.

5. Select the applicable Bank from the corresponding pull-down that will be reconciled. If reconciling multiple accounts, create a new entry line for each account.

If necessary, click the delete button (red minus sign) to remove a selected Bank.

6. You are required to enter the Beginning Balance from the statement if this is the first reconciliation for the selected bank account. Otherwise, the Beginning Balance will populate automatically according to the ending balance recorded in the previous reconciliation. If necessary, edit this field to reflect the true beginning balance on the current bank statement.

7. Enter an Ending Balance if the bank statement will not be uploaded, and the reconciliation is being completed manually.

8. Click Submit to begin the reconciliation.

Bank Connections

In order to set up bank connections, the setting, Enable connecting of banks to show other banks' outstanding checks, must be enabled via Setup > Settings > Miscellaneous tab > Bank Reconciliation.

1. From the Budgeting/General Ledger menu, click Internal Account Bank Reconciliation.

2. From the Bank Reconciliation screen, click the Bank Connections tab to connect a secondary bank to a primary bank so outstanding checks in the secondary bank's reconciliation also display in the primary bank's reconciliation.

3. Select the correct fiscal year from the FY pull-down. The pull-down defaults to the current fiscal year.

Internal Account Bank Reconciliation

4. To link a secondary bank to a primary bank, first select the Primary Bank from the pull-down.

5. Select the secondary bank from the Linked Bank pull-down.

6. Once both selections have been made, press the Enter key to save the line of data. You'll know the data has saved when the line turns blue and the delete button (red minus sign) displays.

7. Link as many banks as needed using the blank top row for new information.

8. You can also edit bank connections already made by clicking the pull-downs and changing the selection made. To remove the bank connection, click the delete button (red minus sign).

Additional Features

If there are multiple pages of data, click the Prev and Next buttons to sift through pages. You can also enter a number in the Page text box to jump to a page.

Click the Excel icon in the Export section to export the table of data to an Excel spreadsheet, which can then be saved to your computer.

Click the Printer icon to print the table of data.

Click the Filters button to filter data and apply filter rules.

a. To add more than one filter to a column, click on the green plus sign.

b. To delete an added filter, click on the red minus sign.

c. Select the gray arrow for additional filtering rules.

For more information on how to use the Filters feature, see the Filters document.

You can also sort data by clicking on any of the headers. Click once for ascending results; click twice for descending results.

Use the Filter text box located in select pull-down to quickly find a selection. Begin typing the name or number of the data in question to pull it to the beginning of the list.