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.
Pulldown the "Script Menu" and select "Finish Up".
Reply with "OK" to the confirmation message.
Note: When the script completes, a sidebar (see Figure 2) appears from which you can download/import transactions into QuickBooks. The sidebar will be active for a few seconds to complete the following download/import steps below. If you need more time, reply with a "No" to the confirmation popup (see Figure 3) that appears.
Click on "Download the CSV File" link in the sidebar to copy the CSV file to your downloads folder. A browser tab will temporarily appear during the download.
Click on "QuickBooks Import Receipts" in the sidebar.
Click on "Import CSV" (DO NOT OPT TO USE THE "New Layout").
Check the box to "Add all new customers to QuickBooks".
Browse and select the downloaded CSV file, then click "Next".
Review the displayed column mapping, then click "Next".
If the table of sales receipts data reflects errors, resolve them before continuing.
Click on "Save and send". You will receive notification when the data has been imported and email messages have been sent to the donors.Review the sales receipts to be imported and if correct, click on
Click on "Launch the Deposits PDF..." link in the sidebar to view the PDF that details how the receipts should be grouped into bank deposits.
Click on "QuickBooks Add Bank Deposit" link in the sidebar.
Use the information in the deposits PDF (see Figure 4) 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"
Return to the spreadsheet tab in the browser window.
If the import described in steps 2 and 3 above is complete, reply "Yes" to the confirmation popup.
Reply "OK" to the "Processing Complete" popup.
Close the spreadsheet tab.
Figure 1: VancoWork Tab
Figure 2: Finish Up Sidebar
Figure 3: Confirmation Dialog
Figure 4: QuickBooks Deposits PDF
