Generate a lump sum report to record donations into QuickBooks

NOTE: Little Green Light also offers a direct integration between Little Green Light and QuickBooks Online (see help article).

If you are not a good candidate for using the integration with QBO, please continue below for an option of:

  • Recording details of transactions in your Little Green Light account
  • Recording lump sum entries in your QuickBooks or other accounting software account

If you just need a listing of entries by Gift Category and Fund, 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 you can generate a custom report with the lump sum amounts to enter into your accounting software.

Degree of difficulty: Medium. The process 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.

Background 

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. 

Gift types

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", "Peer Credit", 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 of the page.

Columns 

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"
  • "Amount"
  • "Campaign"
  • "Fund"

Click the Next button to proceed through the report builder until you get to the Save step.

Save export 

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
  • Monthly

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 "Create a permanent link to this report?" option. 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 a CSV file or in Google Sheets looks like the following:

=&A1&" : "&B1

NOTE: If you decide to create a scheduled report to set up a repeat export of your lump sum totals, be sure you are exporting the file in CSV format rather than XLSX—Google Sheets can't read XLSX files through the import function.

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:

=importdata("https://prefix.littlegreenlight.com/rptlink/805b71cb-6e13-4bfb-b915-ba71b721542e")

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).

Related Articles