Generate a lump sum report to record donations into QuickBooks
Our recommended approach for coordinating between your donor management database (LGL) and your accounting software (QuickBooks, etc.) is to record the specific details of your donations and other fundraising revenue into LGL, but to record only lump sum entries into your accounting software.
If you just need a listing of entries by Gift Category and Fund, then please refer to the first recommended approach in the article Working with Quickbooks and LGL.
If you have more complex requirements to generate your lump sum entries, this article explains how can you can generate a custom report with the lump sum amounts to enter into your accounting software.
Degree of difficulty: medium. It relies on some post-processing outside of LGL. It does not require a high level of technical skill, but does rely on some familiarity with the concatenate function and pivot tables. For more on these and other spreadsheet functions, see our blog post Top 10 favorite Excel tips for nonprofits.
Gifts should be entered into LGL using a combination of Campaign, Fund, and Gift Category, and the way you categorize gifts in LGL should be somewhat similar to how you do so in your accounting software. For example,
In your accounting software you might have a combination like:
- Chart of Accounts Code: Special Events Donations
- Class: Scholarship
And the corresponding entry in LGL might be:
- Gift Category: Event Donations
- Fund: Scholarship
For each unique combination in your accounting software, you'll need a corresponding combination of gift attributes in LGL.
Running the report in LGL
The easiest place to start is in the Fundraising tab, where by default you will see all your gifts for the current fiscal year.
Selecting gift dates
Change the date range to reflect the dates you want to cover in your report. Depending on how frequently you intend to enter data into your accounting software, the best two options are:
- "Last week rel."
- "Last month rel."
In both cases, the report will return gifts for the period relative to the date you run the report.
"Last week rel." is defined in LGL to mean the period Monday through Sunday immediately preceding when you run the report.
"Last month rel." in LGL returns transactions for the month prior to when you run the report.
You should filter your report to include only monetary transactions, which means you want to include the gift types of "Gift" and "Other Income", but exclude the gift types of "Pledge", "Installment", "In Kind", and all of the related gift types ("Soft Credit", "In honor of", "In memory of", and "Matching").
This screenshot shows a fundraising query based on gift date and gift type:
Click the Search button to run the search.
Take your results into the report builder by clicking the Export results button in the upper right corner.
You are taken to the Customize tab in the report builder, where you will define the columns you want in your report.
The columns we recommend for your report are:
- "LGL gift ID" (always handy in case you need to identify an exact gift in LGL)
- "LGL Constituent ID" (again, handy for identifying an exact constituent)
- "Sort name" (name field; you can skip this if you don't want donor names in your spreadsheet)
- "Gift date"
- "Gift type"
- "Gift category"
Click the Next button to proceed through the report builder until you get to the Save step.
In the Save step, set the export as a CSV type (a generic spreadsheet format) and set the Export Generation Schedule so the report runs on a recurring basis, using one of the following:
- Every Monday
Align the frequency of your report with the time frame you selected in your search criteria (i.e., if your report runs weekly, set the gift data range to "Last week rel."; conversely, if your report runs monthly, set the gift date range to "Last month rel.")
After selecting one of the recurring options, you will also see the option to "Create a permanent link to this report." Check that box so that your recurring report will be available to pull into another application; in our case, we'll be using Google Sheets.
Producing the lump sum totals
The report you created has a row for each transaction, and each transaction should include a column for each of the following: Gift Category, Campaign, and Fund.
Now what we want to do is sum the transactions for each unique combination of attributes that corresponds to your accounting categories.
In our example, we will generate a lump sum for each unique combination of Gift Category and Fund (we are not going to use the Campaign attribute). To do this, we first create a new column where we concatenate the values for Gift Category and Fund. The formula for this in Excel or in Google spreadsheets looks like the following:
=&A1&" : "&B1
This formula results in a new value that is the combination of what's in cells A1 and B1. The part in the middle (" : ") is there just to insert a colon between the two values.
Next we want to run a pivot table where we place the concatenated values into the rows and sum the totals in the values area. Pivot tables let you sum totals by unique values, in our case for each unique value for Gift Category: Fund (column J).
After running the pivot table, you will have a summary report that shows a lump sum for each unique combination of Gift Category and Fund, which should correspond to your Chart of Accounts and Class codes in QuickBooks. You can enter these lump sum totals into your accounting software.
Who should be listed as the customer?
Your accounting software likely asks for (if not requires) the name of the customer. When you're entering these as lump sums, you should use a proxy customer name like "See LGL" or something along those lines to indicate that the details are in LGL.
Streamlining the process
This probably feels like a fair amount of work. Fortunately, you can set up quite a bit of automation by using Google Sheets. In a blank Google Sheet, you can put a formula into cell A1 that looks like:
The ID after /rptlink/ should match the permalink provided to you by LGL for the recurring report you set up.
When you enter that into cell A1, the Google Sheet goes out to that location and pulls back all of the data from that report.
Each time your recurring report runs, when you open up your Google Sheet, it will re-grab the data, so it should always have the latest report.
Google Sheets can include multiple tabs, so one could be for the raw data you pull from your LGL report and another tab can be for the pivot table you create.
The pivot table will update automatically as the data sheet updates (weekly or monthly, depending on what you've set up).