I have been working hard at finalizing the 2003-2004 financial statements for our Habitat affiliate. I am the treasurer now and I need to file the 2003-2004 Form 990. The problem is that the final financial reports coming out of QuickBooks were not to my liking. The previous treasurers/accounting firms appear to have used a combination of Peachtree, Excel, and other data to prepare the statements. I have moved the data into Quickbooks last year. I wanted all of the data I was going to report to the IRS to be in QuickBooks. The combination of several treasurers and the lack of original cost data to support some of our transactions made the going slow. Don’t get me wrong! We have lots of paper trails for cash donations and our payments to construction vendors. We were missing the cost of donated securities and donated land. I also found the mortgage information on the foreclosed property we sold missing. Finally the mortgage and escrow data conflicted with our mortgage tracking system.
Probably the most irksome problem was reconciling our mortgage and escrow tracking system, Habitrax, with our accounting data. The first part of my solution was to create a spreadsheet to track deposits and split out each homeowner deposit into principal, escrow, and late payment. We generally have three to four bank deposits a month so I summarize the data based on bank deposit at the bottom of the page. This made it nice at reconciling the checking account but I still had problems reconciling QuickBooks and Habitrax. My most recent change to the spreadsheet was to add reconcilition data to the bottom of the spreadsheet. I use a Habitrax report to subtract out the deposits received last month but deposited this month and add in the deposits we have recorded as received but have not deposited. In a perfect world the resulting number will match the value on the Habitrax report. In my world I have another row for missing receipts, deposits, errors, and Habitrax only transactions(e.g. principal to escrow transactions). This allowed me to reconcile our accounting data to Habitrax data back to 2003. No one ever attempted to reconcile the two systems till I asked the question. Since reconciling these two systems by hand is practically impossible, errors have not been caught.
Obviously the two systems come up with different amounts. The difference is significant but not large. So the question is who is right. To reconcile the two systems I needed better information on the accounting side. The big change on the accounting side was to move away from the summarized transactions back to individual transactions per homeowner. To achieve this I entered an invoice for each homeowner’s mortgage. Each month I enter in a sales receipt for the individual escrows and late payments. Then I use their payment to pay the sales receipts with the rest going to pay the mortage balance. Now I had individual account balances for the mortgage and escrow for each homeowner and could reconcile individually.
To make this all come together I had to add adjusting journal entries to get the right historical balance. I added a column to the existing worksheets showing the receipt date and created a new worksheet that built import transactions in IIF file format. Since my source data was organized as one month per worksheet, I was going to create a batch of IIF transactions by month. I used data validation and the VLOOKUP function to minimze my data entry errors. Each IIF transaction requires three rows for each row of source data. So I used the OFFSET function so that I could copy the three rows and only move down one row in the source data. To extend the worksheet all I had to do was copy and paste. To shorten the spreadsheet all I had to do was delete the extra rows. After I had reviewed the sheet and made any adjustments, I saved the spreadsheet file. Then I saved the spreadsheet in Tab-delimited format. Since Tab-delimited format only saves the active worksheet this was perfect for my use. It created a Tab-dilimited format file that matches up exactly with the IIF format required by QuickBooks import function. I created separate files for each month. I entered a year and a half of data this way(75 transactions/month). Wow! I am finally ready to review the reports and file the 990.