Posting to Your Accounting System
General Ledger Integration on Salesforce, Part 7
Hello! My name is Alexandra. I am a Solution Engineer for Salesforce.org. Welcome to my blog series about GL integration!
In Part 6, we walked through the first part of the gift cycle, from entering a donation, to allocating it to a fund, and finally translating it into an accounting ledger entry with debits and credits.
THE GIFT CYCLE, PART 2
At this point, George has entered Diane’s donation into NPSP. We know Accounting Subledger automatically created ledger entries for the transaction. What happens now?
The next step is to collect all ledger entries into a “posting file.” Then, you will export the posting file from the CRM and into your accounting system where the entries will be reconciled and ultimately posted to the general ledger. Let’s walk through how to prepare a posting file and send it over to your GL.
Preparing a Posting File
By now we know that the general ledger needs account totals, and account totals are made up of debits and credits. Salesforce Accounting Subledger uses Ledger Entries to record debits and credits for incoming gift revenue. So what’s a posting file and how does it fit into the GL integration process? Think of a “posting file” as the vehicle that takes your Ledger Entries from Salesforce and brings them over and into your accounting system. In NPSP, you will create your posting file by using Report Builder.
Designing Your Posting File
The best place to start when designing your posting file is by reviewing your key financial reports. Make a list of all the data points necessary to produce a report. Then trace those data points to their source fields in your journal entries. This is the data your accounting system needs to get from your CRM in order to reflect donation revenue in the GL.
Under FASB rules, most nonprofits will need to produce a Statement of Activity and Statement of Financial Position. That means for every transaction, you’ll probably want your posting file to include at a minimum: Transaction Date, Debit Amount, Credit Amount, and GL Code. (Do these concepts sound unfamiliar? You can review the basics of what makes up a general ledger in the previous Anatomy of a General Ledger article.)
Here’s an example of Learning Empowerment’s posting file. LE uses one of the common accounting systems on the market:
Your ledger report should be unique to your organization’s business needs. So include any additional details or dimensions that your nonprofit wants to run financial reports on. For example, if you want to run income statements by fund, you should include the Fund or GAU Allocation. If you need to report back to a project funder on a regular basis, include Project Code and possibly Donor Name.
Creating Your Ledger Entry Report
Now you have a list of the data fields you need to include in your ledger report. Great start! So where does this data that we need come from?
Your records in Salesforce! The Ledger Entry, which Accounting Subledger (Growth edition) automatically creates when you enter a Donation (or Opportunity), contains the core data fields that will need to be posted to your accounting system. This includes: Transaction Date, Credit Amount, Debit Amount, and GL Code. For additional details you want to pull in, you will find those data fields on the related transaction records like Donation, Payment, and GAU Allocation.
Now that you know which fields to pull into your report and which objects they come from, you can use the native drag-and-drop Report Builder in Salesforce to create your posting file. Accounting Subledger actually gets you most of the way there because it includes out-of-the-box Ledger Entry report templates for 18 of the most common nonprofit accounting systems.
Posting to the General Ledger
Take a moment to pat yourself on the back, because the hardest part is behind you! Now that you have the transactions entered into your CRM, the accounting details are recorded, and everything is ready to go in your Ledger Entry report (or “posting file”), it’s all downhill from here. Let’s walk through the final steps that Anna and team at Learning Empowerment (LE) take to post these donations to the general ledger.
Note that there are a number of ways to “integrate” Salesforce and your accounting system in order to get revenue subledger data created in your CRM into your general ledger. I discuss all the options, including automated and API options, in the subsequent How to Integrate Your CRM & GL Systems article. Since Learning Empowerment, like many nonprofits, prefers the control of a manual process, I will walk through the very common import/export method here.
Exporting a Posting File
Any CRM should have an “export to CSV” feature, and Salesforce is no different. To export your posting file, open up your Ledger Entry Report. From the caret menu in the upper right hand corner, select ‘Export.’Select to export the report in ‘Details Only’ mode, and choose your preferred format (which will be dictated by the import tool you are using). Then, hit the ‘Export’ button. Salesforce will generate a file you can download and save to your computer. If you need to do any final data preparation, you can do so directly in the spreadsheet. But you shouldn’t need to do much, since Accounting Subledger did the heavy lifting of translating the language of donations to the language of accounting for you.
Now, you’re ready to import the file into your financial management system! Most accounting softwares will have a data import tool built in or be compatible with external import tools you can use. The data import tool should guide you through a data field mapping process to match the fields you pull from Salesforce to their corresponding fields in the accounting system (because often the names of the fields will be slightly different). Most data import tools also allow you to create import mapping templates so that you only have to set up the field mappings once and then it’s a quick few clicks every time thereafter you need to import your posting file.
In this way, the Salesforce Accounting Subledger acts like a true gift revenue subledger, no different from your other subledgers like Accounts Payable, Fixed Assets, etc. You record a donation transaction, the system generates the accounting for the transaction, and then you post the transactions to your general ledger.
How Often Do You Post?
Many organizations post on a nightly basis. Also common is a weekly posting cadence. For others, once per month will suffice. Generally speaking, the higher your volume of transactions the more frequently you will want to post to your GL. That way, you are processing and reconciling transactions in manageable batches rather than getting overwhelmed by too many at once. Another important consideration is that the more often you post, the more up-to-date your financial reporting will be. Therefore, your business leaders are making decisions on more reliable information.
Yet these considerations should be balanced by what is typical for your organization. For example, if you typically see many changes to your transactions in the days after they are received, it might make better sense to post on a weekly basis so that your GL is not too cluttered with adjustments. How often you export transactions and post them to your accounting system will depend, ultimately, on your business needs.
Reviewing Transactions Before Posting to GL
Once you export your Ledger Entry report from Salesforce, the CRM’s job is done. Accounting Subledger prepares your gift revenue transactions to be imported into your accounting system and ultimately posted to your general ledger. Many accounting managers will want to inject an additional step of reviewing the transactions before posting to the GL for added scrutiny. A good practice is to build a review and approval process in Salesforce so that all transactions are reviewed in your donation subledger before they are exported. You might want to explore process automation features in Salesforce such as Process Builder or Flow to build this for your business.
Marking Transactions as Posted
As a final step in your GL integration process, you need to mark the status of transactions that have been posted to your General Ledger. This is important for keeping your records organized and your subledger-to-GL reconciliation process easier.
A common way to track this is through the Posting Status field on the Ledger Entry. This field allows you to track the Ledger Entry as it progresses through the stages of CRM-to-GL integration. Typical values for the Posting Status field might include, “Entered,” “Reviewed,” “Approved,” “Sent,” “Posted,” and “Denied.” For example, when a Ledger Entry is created the status is marked as “Entered.” Once your posting file is sent to the accounting system, the status is updated to “Sent.” Finally, once your accountants have posted the transactions to the GL you update the status to “Posted.” Accounting Subledger will suggest some stages out-of-the-box. However, you have the flexibility to determine your own status values so you can align this field to your organization’s unique process. It’s easy to add or remove values to the Posting Status field; reach out to your Salesforce Administrator to request help with this.
Certainly you can update the status of each Ledger Entry on an individual basis, but why not use the power of the Salesforce platform to do this more efficiently? One tool you can use to make light work of the task is the Mass Edit function in List View. Create a List View of your Ledger Entries, and filter by Posting Status to capture all ledgers that are in the “Sent” status but not yet “Posted.” Use the Mass Edit button to update the status of each Ledger Entry to “Posted.”
If you would benefit from a less manual approach, you can design a more automated process for marking transactions as posted through native Salesforce automation tools like Process Builder or Flow. For example, design a flow where you push a button and the Posting Status on all qualifying Ledger Entries is automatically updated to “Posted.” Another automated approach is through a Path that aligns your gift process and accounting steps. When your Donation/Opportunity advances to a new stage your Posting Status will automatically update as well.
Finally, if you have an API integration between Salesforce and your financial management system you can write an automatic Posting Status field update into the integration. For example, when a Ledger Entry is sent over to the ERP it marks the Posting Status as “Sent.” Then when an action is taken in your ERP to post the transaction to the GL, the integration writes back to Salesforce to update the status of the Ledger Entry to “Posted.”
Ultimately, you have the flexibility to design a process for marking transactions as posted that matches your organization’s internal business process. It’s a good idea to bring together stakeholders from Development, Finance, and IT to discuss what your process should look like so that everyone’s needs are considered.
Security & Auditability
A quick note on security and auditability. If you are an accountant, this is really important to you! If you are using a different CRM system that does not have a native subledger feature built in, you will need to do the translating manually. That often entails exporting a list of gift transactions from your CRM into a spreadsheet where you can manipulate the spreadsheet data to get the transactions ready for posting. That method not only means more time consuming and duplicative manual work, but it also means your financial processes are less secure.
First, by taking a part of the process offline, you break the audit trail that should follow the entire lifecycle of a transaction. There is no way to prove who did what when you’re working in Excel spreadsheets. Alternatively, if you are using Salesforce as your true subledger then your audit trail is automatic. Every time a record is created or edited, the system records who took the action and time stamps it. This is what your auditors want to see!
Second, when you are working in spreadsheets there is no way to enforce access and permission controls to ensure that only authorized people are touching your financial records. Spreadsheets do not protect against bad actors. Salesforce, however, bakes security and access controls right in. You can enforce system access such that only users with the appropriate permissions can see, create, or change your financial data.
What’s Next?
Whew! We have really come a long way, and the bulk of the GL integration work is done. In the next article, I’ll review what to do if you need to make any changes and the steps you’ll take to close an accounting period.
