How to Find Descrepencies in Undeposited Funds


Step 1

  • Double-click on the Undeposited Funds account in the General Ledger.
  • Filter to the month you are researching. 
  • Export to Excel



Step 2

  • Open the saved spreadsheet saved on your computer. 
  • Use the Text to Columns function under the Data tab to separate the Source Information column.
  • Highlight the Source Information column
  • Click Text to Columns
  • Select Delimited > Next
  • Check Other. Type in :  (colon) > Next 


 

  • Click Finish


  • The transaction source/deposit number will be listed in the Source Information column. The journal number will be listed in the column to the right of Source Information.
  • The important data is the deposit number, now listed in the Source Information column. 



Step 3

PropertyBoss recommends working with a bookkeeper or accountant during this step. PropertyBoss provides the following as a suggestion only and cannot offer accounting services or advice. 

  • Insert rows between all the groups so a total can be calculated for each group:
    • Rows with deposit numbers are one group (these are the deposits themselves)
    • Rows with no deposit number but a journal number starting with a T are one group (these are usually reversed and reversing eft transactions that need to be totaled)
    • Rows with deposit numbers and journal numbers (these are the payment transactions that need to be totaled)
    • If there are manual journal entries, they should be grouped together 
  • Note: It also helps to sort each group by deposit number and total amount for the deposits for the month you are working in. 


Identifying Discrepencies 

  • The reversed group should net zero. if it does not, this could be part (or all) of the discrepancy.
  • The total deposits for the month less the total of last month’s Undeposited Funds should equal the amount of payment transactions.
  • The manual journals (if any) should net zero.