To properly book Vanco donations into QuickBooks , member donations have to be reconciled to the actual deposits that were made by Vanco to the Simmons checking account.
Data downloaded from Vanco and exported from QuickBooks provide the basis for mapping Vanco members to QuickBooks donors and mapping Vanco Funds to QuickBooks Products/Items. Prior to processing a batch of Vanco donations, you need to:
Download transactions from Vanco and Simmons bank by following the instructions that appear on the "Instructions" tab of the Simmons and Vanco Downloads Google sheet.
On "Data" tab of the QBExports for Counters Google sheet, follow the instructions to retrieve the data for the "All Products" and "All Donors" tabs.
The logic on the "VancoWork" and "SimmonsMatch" tabs of the GS Vanco Xacts QBonline Google sheet attempts to match the downloaded Vanco donations to the deposits downloaded from Simmons bank.
Launch the VancoWork tab depicted in Figure 1 and proceed as follows:
Set the Start Date and End Date. Overlapping dates with a batch that was previously imported will cause some donations to be double posted.
Pulldown the "Script Menu" and "Reset Override Columns".
Donations matched to deposits will be identified by green checkmarks. The matched status between Vanco and Simmons Bank appears in the upper right hand corner of the sheet.
While unmatched donations remain, use the the "Force Dep Grp#" column to assign a common group number to donations. When the sum of the grouped donations matches a bank deposit, a green checkmark appears beside the deposit group.
There may be a few issues that require attention. In Figure 1 for example, a transaction is flagged as having failed. The church incurs a charge for each failed transaction so the donors should be contacted and/or the transactions need to be cancelled or put on hold.
Error situations are highlighted with red text - these must be resolved before continuing. In Figure 1, the text "0 unmatched Vanco deposits" appears, but if there were unmatched deposits the text would appear in red. Certain columns should be reviewed for each batch:
Inspect the "QuickBooks Name" column. New Vanco members who are not already mapped to a QuickBooks donor will be added to QuickBooks by prefixing "New_" to their Vanco member name. If you know that a new Vanco member is already a QuickBooks donor, you should map the Vanco member to a QuickBooks donor.
Inspect the "Donation Category" column and if any donation is flagged with "*Invalid Ctgry*", you may have to map the Vanco Fund to a donation category and/or map a donation category to a QuickBooks Item. Alternatively, you could just provide an "Override Category". The latter is especially suitable if you know that this is a one-time donor and that donation profile will not reoccur.
When you have resolved all issues:
Pulldown the "Script Menu", select "Finish Up" and respond to the confirmation message. This will export a batch of receipts in Comma Separated (CSV) format and 2 PDFs - one reflecting the VancoWork tab data and the other showing the donations grouped as deposits.
If a browser tab doesn't open to Google drive, launch the Vanco Import App Google Drive folder. (Note: When convenient, the exported data should be moved to a suitably organized folder archive hierarchy such as Vanco Donations Archive.)
Right-click>download the comma separated (CSV) receipts batch file.
Launch QuickBooks Batch Transactions for Sales Receipts (Donations), then click "Import CSV". DO NOT USE THE "New Layout" OF THE BATCH TRANSACTIONS IMPORT.
Make sure you check the checkbox to add any unknown donors as new donors.
Browse/select the CSV file from your downloads folder, then click "Next".
The "Import Sales Receipts" mapping appears. Click "Next". A table of the transactions will be displayed.
In the bottom right corner, select "Save and send". This will upload the receipts and send a confirmation email to the donors.
Launch Create a Deposit in QuickBooks.
Use the information in the deposits PDF (see Figure 2) as a basis for creating QuickBooks deposits, repeating the following for each deposit:
Select donations that comprise a bank deposit
Enter the deposit date associated with the bank deposit
Verify that the deposit amount matches the deposits PDF entry
Click "Save and new"
Figure 1: VancoWork Tab
Figure 2: QuickBooks Deposits PDF
