Skip to main content

5110 Bank Acct - M&T Checking

Controller's Office

Purpose

Track the main M&T Bank checking account used for remote deposits, AP checks, ACH and wire transfers

Manager

Karen LaPoint

Reconciliation

Preparation Steps
Create a current month folder in S:\departments\Bus&Fin\Bank Reconciliation\M&T\FY 2021
Copy the AP Log from S:\departments\Bus&Fin\Kelly\AP\Check Run\Accounts Payable Check Log.  On your copy, delete the prior year tabs.
Request the Cashier Log from Jeffrey Hamilton.
Get the current bank statement and outstanding checks report (available ~8th of the month) from S:\departments\Bus&Fin\M&T\OPERATING ACCOUNT STATEMENTS (ask Betsy if they aren't downloaded yet)
Get the current Sweep statement from S:\departments\Bus&Fin\M&T\Sweep Statements (ask Betsy if it isn't downloaded yet)
Pull up the file S:\departments\Bus&Fin\Bank Reconciliation\M&T\FY 2021\FY21 Outstanding Item Tracking

* Copy the prior month 3 columns into the next 3 columns
* Change the date and color of the top cell for the current columns
* Wipe out the entries in row 3 for the current month
* Remove the color from row 4 for the current month
* Wipe out the entries in just the Bank and GL current month columns for rows 7-39
Use the query CU_GL_ACCOUNT_INQUIRY to download the transactions in Account 5110
Use the query CU_GL_ACCOUNT_INQUIRY_BALANCE to get the current balance in account 5110
Reconcile cleared checks in PeopleSoft
Betsy will cleared the cashed checks in PS > 5110 Clear M&T Cashed Checks
Betsy will provide you with a download of the outstanding checks in PS, the last check number shown in PS, and any checks that cleared Sweep that were not in PS
Convert the Bank Statement to Excel
Pull up the pdf version of the current bank statement
File > Export To > Spreadsheet > Microsoft Excel Workbook 
Name this tab Statement; copy this tab within the workbook and name the new tab Details
On the Statement tab, enter a formula to add deposits - checks & debits - service charges to get the value of the transactions for the month
On the Details tab, highlight all columns and hit Merge & Center, widen all columns so you know which ones you need
Delete all the page headers to get down to just line item details (there may be some text boxes that also need deleted)
Some of the line item details carry over to a second line, copy the text into the upper line and delete the lines with no values 
Delete columns that have no relevant data
Using a new column called Amount, enter a formula that adds deposits column and subtracts the withdrawals column.  Copy and paste special this column as values to get rid of the formulas.
Highlight the new column and, if the total of the transactions for the month equals the value your calculated above, you can delete the deposits and withdrawal columns.
You should now have 3 columns on the Details tab:  Date, Transaction Description, Amount
Save the file in the current folder and name it Bank Reconciliation Backup MM YYYY 

Putting It All Together

In the file Bank Reconciliation Backup MM YYYY, on the Details tab, insert/create columns in this order:

image2021-3-15_16-13-33.png

For all the lines from the bank statement, enter M&T in the Source column Using the query CU_GL_ACCOUNT_INQUIRY of the transactions in Account 5110, copy all the data into the relevant columns on the Details tab and enter GL as the Source for all these lines

Create a pivot on a new tab with the following setup - call the tab Bank Rec; subtotal by Group

image2021-3-15_16-17-53.png

SAMPLE

image2021-3-15_16-19-22.png

Create another pivot table on a new tab with the following setup - call the tab Cash Flow; subtotal on both CF In/Out and CF Subtotal

 

image2021-3-15_16-21-42.png

  SAMPLE

image2021-3-15_16-22-57.png

Start Matching Transactions on the Details tab:

  • I find it easier to match if I change the font or color for the M&T transactions than the one used for the GL transactions

  • Filter the database

    Filter On Description using: Enter this in the fields
    ZBA

     

    image2021-3-15_16-32-16.png

    image2021-3-15_16-35-39.png

     

     

    Where there is a match between M&T and GL:

    image2021-3-15_16-36-28.png

    ELM 

     

    Where there is a match between M&T and GL:

    image2021-3-15_16-38-21.png

     
    Nelnet

    Where there is a match between M&T and GL:

     

    image2021-3-15_16-39-42.png
    Delta

    Where there is a match between M&T and GL:

    image2021-3-15_16-40-42.png

     
    Phil

    Where there is a match between M&T and GL - just enter Gifts for now, you will classify the Gift type later

    image2021-3-15_16-42-6.png

     
    Alum

    Where there is a match between M&T and GL - just enter Gifts for now, you will classify the Gift type later

    image2021-3-15_16-43-48.png

     
    Ship

    Where there is a match between M&T and GL:

     

     

    image2021-3-15_16-45-59.png


    image2021-3-15_16-49-34.png

    Where there is a match between M&T and GL - there are lots of matching amounts; watch the dates for making a match:

     

    image2021-3-15_16-51-36.png

  • On the Details tab, remove any filters, sort the entire database by Journal ID, then filter by Group = (Blanks)

  • Filter on Journal ID using AP, enter as follows:

image2021-3-15_16-58-40.png

    • AP Journals  (these are voided checks)
    • APC Journal - just enter AP in the Group column for now

  • Filter on Journal ID using X, enter AP in the Group column for now

  • Remove all filters, sort the entire database by Amount

  • Filter the Group = (Blanks) and the Journal ID is not SF

  • Match as many line items as possible using last month's file as a reference on how to complete the columns

  • Remove all filters, sort the entire database by Date, Journal ID, Ref

  • Filter the Group = (Blanks)

Reconcile Outstanding Checks
Pull up the M&T Outstanding Check file for the month.  It should be in S:\departments\Bus&Fin\M&T\OPERATING ACCOUNT STATEMENTS saved as a .TXT file.  Otherwise, ask Betsy to download it for you as a .TXT file.
The 2nd page provides a summary of the Previously Outstanding+New Issues......-Paid Checks=New Outstanding.  I screenshot this summary and put it on the Bank Rec tab.  You may need it later for troubleshooting.
In the next section of this report, look in the Exception Condition column for any Force Posted Item and screenshot this onto the Bank Rec tab too.
Close the .TXT file and open the PS Outstanding Check file provided to you by Betsy.  You are now going to covert the .TXT file to Excel and merge it into this file for reconciliation.

* Choose File > Open and navigate to the .TXT file (change the file type to All Files) to select it and hit Open
* The Text Import Wizard will come up.  In Step 1 it should be Fixed width.  Next>
* On Step 2, pull the scroll down to the middle.  Put divider lines around each field.  Next>
* Depending on the column that is highlighted, choose what type of format should be used during the import or choose to skip the column.  Finish
* It looks like a bunch of crap, just trust me...
* Search for HOOF - delete all rows prior to this one  (This is the first outstanding check - let's hope she never cashes it)
* Highlight the Check Number column and search for O/S - delete this row and all rows after this one



Common Differences Resolution




Transactions recorded in Account 5110 M&T that should have been recorded as Community Bank transactions or vice versa (this happens when the Cashier selects a tender type aligned with the incorrect bank account) Verify the 5093 Community bank reconciliation is also out of balance by this transaction; journal the transaction to the appropriate bank account
Back to top