Tracking Spending with GAUs in NPSP #NonprofitHelp

Welcome to the first in what I hope will be a long-running series, #NonprofitHelp at the Trailblazer online community! If you’re new to the online Trailblazer community, check out our Resource post “Welcome to the Trailblazer Community” to get your bearings. In this series, we take a question from the #NonprofitHelp topic and provide our take on how you could solve for the use case. Let’s dive in!

The Use Case

Today’s use case is a great question about tracking expenditures in Salesforce. Let’s meet Miriam Volchenboum and take a look:

Miriam advises, educates and collaborates with organizations to develop effective data collection and reporting structures and processes. She’s a taskmaster and data steward and currently consults as the Salesforce admin and technical project manager for the Portland Refugee Support Group (PRSG). PRSG offers support services to refugees, by empowering them, through education, psycho-social support and life skills training, to become independent and effective members of society.  Three cheers for Miriam and her important work!

So, here Miriam is looking for an easy way to track grant-based spending with her General Accounting Units. At Navigators, we get this request from our clients fairly frequently, so I think it’s worthy of some deeper treatment. 

A quick caveat first. Like a lot of things in Salesforce, there isn’t so much one “correct” answer as there are lots of possible ways to handle this problem. At enterprise scale, the right solution for tracking expenditures usually means an integration with the ledger, using the Outbound Funds Module, and/or possibly also use of the Accounting Subledger add-on. That said, there can be a lot of good reasons to not go this far. A full ERP or ledger integration is no small task, and if this is your only use case, it’s almost certainly overkill. Outbound funds module is also a comparatively large solution that can be overkill. Every situation is different so there could be other reasons to choose an integration to solve this case in addition to just the problem before us, so I’m going to largely skip over this option. 

How can we provide this functionality with just what we have on hand in the Nonprofit Success Pack (NPSP)? Read on for my suggested solution.

The Solution

The General Accounting Unit or GAU conveniently rolls up Allocations made from Opportunities. This is how we track that a particular gift is “booked” in the ledger to a specific line of accounting. GAUs need not always equate to a specific line in your accounting system, however; many organizations create simpler categories like “Restricted” and “Unrestricted” with their GAUs if they don’t need that level of complexity. However you organize them, we need to create another way to roll up expenditures. Allocations represent inbound funds and are a junction object between the Opportunity and the GAU, so let’s mimic that for expenditures. 

Step 1: Expenditure Custom Object

Start with a custom object that represents your expenditure. Again, nothing in Salesforce is universal, but I strongly recommend against using Opportunities for this. It’s not what they were built for and it can be a huge pain excluding them from reporting and other automations and integrations. Especially if the scope of the use case is fairly light, I believe a custom object is called for. 

I’ve called mine Expenditure. We will also need at minimum a currency field to track the amount of the expenditure, and a Master/Detail lookup to the GAU. When I created the lookup, I also allowed Salesforce to add the Related List for Expenditures on the GAU layout. 

Here’s my result – pretty simple, right?

Step 2: Rollup Fields on the GAU

Now let’s head over to the GAU object and add a field to summarize the Expenditures. Because we choose a Master/Detail relationship from our Expenditure object to the GAU, this is pretty easy with a standard Rollup Summary Field. I’m calling mine “Total Expenditures.”  Use this field to SUM the Amount field of all Expenditure records to the parent GAU, like this:

I’m also going to create a new section on my GAU’s page layout for Expenditures to make this all a bit neater and easier to read. You may want to be thoughtful about which page layout should feature this information, but for this example, I’m just dropping it on my one and only layout near the top. 

Step 3: Doing the Math

So far… we have a pretty darn workable solution! Now, if we create an expenditure record and link it to a GAU, we can see that expenditure total on our GAU record. 

In my scenario, my GAU “Program Funds” has already had $100,000 in grant funds allocated to it using the NPSP Allocation feature. We can see that amount on the record, and we could subtract our total expenditures in our heads (or in reporting, I guess)… but who wants to do THAT? Let’s add a formula field!

For our Formula field, you’ll need to select the Currency Type. I’m calling mine “Balance.” The formula syntax is pretty simple:

 npsp__Total_Allocations__c –  Total_Expenditures__c 

Now I drop that on the page layout, and I can easily see what the remaining balance is for this GAU and the program funds it represents:

Taking it Further

Now that we’ve achieved “minimum viable product,” or we’ve solved the basic requirement, how could we take this a big further? 

The first and easiest step is introducing more complexity on the Expenditure object, and even adding additional roll ups based on that complexity. Expenditures probably have dates associated with them, so adding a Date Field would make it possible to filter rollups for only a specific period of time. Picklists for “types” of expenditures can perform the same way, and also make reporting richer. 

You may also want to take things even further, and introduce a “Stage” or “Status” picklist for your Expenditure, and split out your totals based on if the expense it represents is just “Pending” or if it’s “Approved” or “Spent.”

Speaking of approvals, once you’ve gone as far as having a Status for your Expenditure, you could also create an Approval Process to allow users to submit expenditure records to other users for review and approval.

Considerations

Let’s talk about Considerations. These are cautions or potential risks to how a set of configuration functions that we need to hold in mind while building and maintaining it. Every configuration has considerations – the trick of good development is being aware of and managing them like all risks to the successful use of your org. 

First, to get a rollup summary on the GAU, we used a Master/Detail lookup. Remember that this relationship is much closer than a standard Lookup, so in addition to enabling that easy lookup it also extends user access based on access to the parent record. If users have access to the Expenditures table and access to the GAU, then they’ll see that expenditure record… and you may not want that. You may need to get creative with your overall security configuration, or consider a standard lookup with a tool like DLRS (aka “Delores”) or Flow to populate the data. 

Second, and a bit “squishier” to manage, most organizations don’t use their GAUs in such a precise way. Our configuration assumes a specific bucket of funds that is being spent, and once spent is gone. GAUs, strictly speaking, don’t really track balances – the ledger does. Rolling up both Allocations and Expenditures by dates or periods like quarters can help with this, but it’s important to realize that the best balance information is going to be in the Ledger. This configuration can be a great aid, but your accounting team will know best what funds are actually available. An alternative solution can be using Campaigns, tracking gifts by Campaign, and then tracking your expenditures as a child record of the Campaign. 

Resources

Ready to Dive in? Here are some Trailhead modules & Documentation to help you get started:

• Nonprofit Success Pack (NPSP) Documentation
• Allocations Overview
• Configure GAU Allocations and Payments (Trailhead)
• Data Modeling (Trailhead)

Have you ever configured for this use case? Let us hear about it! We’re @crmnavigators on Twitter.