Banner Bank Reconciliation

1
Banner Bank Reconciliation

• Presented by
• Ben Jones
• Manager Grants and Contracts
• Middle Tennessee State University
• Assisted by
• Li Liu
• Accountant I
• Middle Tennessee State University

2
Overview

• The Evolution of the Bank Reconciliation
• The Transition from FRS to Banner
• Queries, Tools, and Reconciliation
• Issues, Problems, and Concerns?

3
The Evolution

• Bank Enhancements and Technological Advancements
• Process Changes
• Financial System Transition

4
The Old Way FRS Reporting

• FBD011 Daily Bank Activity Report
• FBD017 Daily Cash Receipts Listing
• FBD018 Daily Cash Disbursements Listing
• FBM019 Cash Report by Bank
• VBM001 Check Reconciliation Report
• VBM094 Check Listing (Outstanding)

5
Transitioning to Banner
6
SQL Queries for Balancing

• Daily Cash Ending Balance Query
• Use Activity Date
• Run Query in ISQL Plus
• Print Output or Copy Balance into Excel
• Verify Against Daily Cash Transactions Query

• Daily Cash Transactions Query
• Use Activity Date
• Run Query in ISQL Plus
• Copy Output into Excel
• Verify Against Daily Cash Ending Balance Query
  Results

7
Daily Reconciliation Demonstration

• Run Daily Cash Ending Balance Query
• Run Daily Cash Transactions Query
• Copy output into Excel.
• Add column to distinguish transactions.
• Subtotal transactions.
• Copy subtotaled transactions into balancing
  spreadsheet.
• Verify transactions against ending balance.

8
Cross Referencing in Excel

• Copy data from online banking web site into
  Excel.
• Scan daily cashier reports and disbursement
  documentation.
• Input data from cashier daily reports and copy
  disbursement details into reconciliation
  spreadsheet.
• Cross reference receipts and disbursements with
  bank amounts in Excel.

9
Outstanding Check Reconciliation

• Receive canceled check files from the bank daily.
• IT staff should use AppWorx or another scheduling
  software to run daily processing of FARBREC in
  Final Mode.
• Reconcile with FARBREC daily.
• Use FAABREC to manually drop checks not received
  from bank and encoding errors

10
Banking Tools

• Online Banking
• Check and deposit images
• Balance reporting (same day and previous days)
• Stop payments
• Online Credit Card Reporting
• Research credit card detail.
• Discover discrepancies and problems.
• ACH Verification
• Verify totals through automated telephone call.
• Bank calls (Mismatched totals, Missing Files)

11
Audit Enhancements

• Paper Documents
• Find the files.
• Copy the documents.
• Organize the papers.
• Place in a box.
• Deliver to auditors.

• Electronic Documents
• Find the files.
• Build a folder on a shared network.
• Save the files.
• Give access to auditors.
• E-mail the location of electronic files to
  auditors.

12
Issues, Problems, and Concerns?

• Scanning Issues
• Unknown Deposits, Wire Transfers, etc.
• Banner Student Transactions
• Fraudulent Checks, ACH Withdrawals
• Your Thoughts and Issues?

13
References and Contacts

• Ben Jones
• bwjones_at_mtsu.edu
• Li Liu
• lliu_at_mtsu.edu
• Mingwei Xue
• (Graduate Assistant)
• mx2b_at_mtsu.edu