Automating BBMS Disbursement Reconciliation to GL

Valdez Roman

Hi everyone, I’m looking for advice on streamlining our gift posting process. Currently, I’m manually updating each gift record by cross-referencing the BBMS Disbursement Transaction Report. It’s incredibly time-consuming to do this gift-by-gift.

Does anyone have a workflow or tool to automate the posting of these gifts to the General Ledger? I am looking to move away from manual lookups and reconciliation. Any tips on built-in integrations or third-party tools would be greatly appreciated!

Dennis Ladnier

It's been a long time since I looked at BBMS data however in the past I was able to create a process which reconciled against posted transactions. I can see a process also working to post gifts however the initial challenge is to assure you are matching to the current RE constituent - assuming your BBMS data is not tied to another system which ties back to RE (Ex. BBMS>LO>RE).

Btw, also used the bbms data daily to look for those donors which tried and were eventually unsuccessful in making a donation thus allowed us to reach out to them to provide assistance.

Carlene Johnson

Here’s the workflow that’s been working really well for my clients, especially now that Transaction ID is available in RE Web View query:

I start by building a gift query in RE and exporting Constituent ID, Name, Gift ID, Gift Date, Amount, Pay Method, Post Date, and Transaction ID to Excel. That gives me a clean list of all gifts expected in the disbursement cycle.

Separately, in BBMS, I pull a LIST (not the Disbursement Transaction Report) that includes Name, Date, Amount, Gross, Fee, Net, Disbursement Date, and Transaction ID, and export that to Excel as well.

Once both files are in Excel, I use VLOOKUP or XLOOKUP on the Transaction ID to match the two lists. Because Transaction ID is now exposed in RE Web View, the match is very reliable and makes it easy to confirm that every gift in RE ties out to the BBMS disbursement. (Note that Transaction ID is not available in DBV query.)

One thing that keeps the process clean: I set the GL Post Date in RE to match the BBMS Disbursement Date. That way, when I pull gifts for a specific disbursement, I’m always looking at the correct set of transactions on both sides.

The only items that don’t always line up perfectly are direct debits, since they can take up to three days to settle. Those sometimes fall into a different disbursement window than the credit card gifts, but the Transaction ID match still makes them easy to identify.

Roberta A. Gilbert

https://community.blackbaud.com/discussion/85405/automating-bbms-disbursement-reconciliation-to-gl

I do the same thing; I pull in the BBMS gifts from RE and compare the RE Validation report each day to the BBMS report in total but not gift by gift, since the person who does the actual gift entry into RE has already verified each individual gift. As long as the GL post dates are correct, then all of the gifts in one BBMS report should get posted in the same batch.

If the totals don't match, it usually means the GL Post date in one or more gifts is incorrect in RE. At that point I go back to the person who entered them and show them that there is a discrepancy in the report totals for BBMS on that date. Then it's up to them to find and correct the error in RE. Usually the 2nd time is a charm.

Roberta A. Gilbert

One other thing occurred to me, @Valdez Roman . Do you have FE and RE integrated so that you can pull gifts directly from RE to FE? I was assuming you did, but perhaps not. Integrating the two databases allows you to open RE and post directly to the GL in FE, without having to even touch individual gift records. It's basically 2 steps: Step 1 produces a Validation report from RE which you can view and compare to what you expected to see from BBMS or any other gift processor. If they match, then Step 2 actually posts the gifts in RE, and copies them to the designated revenue account(s) in an FE JE, which you can then post manually or edit (e.g. I might add processing fees, if any) before posting.