RE and BBMS Report Combo

Neya Fitzgerald

Hi Everyone - 

Has anyone been successful in running a report in RE than coincides with BBMS disbursement reports? I continue to take training in RE and ask this question to the Trainers and either I am told there are multiple reports that need to be ran in RE to give me what it is I am looking for or they simply do not know. It could be because each organization needs are different and I need to give more detail or I need to physically show them what I am doing to get the result I need. So, ex. I printed Nov 1-7 disbusrement report in BBMS I could not go into RE and obtain a "batch" because the transactions/appeals are different. I actually have to go through each constituent on the disbursement report from BBMS to identify the appeal, how many transactions, calculate the gross-fees-net all in excel in order to have the report information I need for our accountant. Can anyone help me simplify this process? Or have run across this same issue and have any helpful tips that can help me. I will say it taken me an extremely long time to pull all of this information for the past 2 quarters but I was 85% successful in my calculations and my excel spreadsheet is user friendly for others in our organization but I would like to simplify if possible to be more efficient with this process. Thank you all in advance for your feedback!!!

 

April Snow

I was myself just wondering about this as our accounting team asked me to think about ways to make it easier for them to do the reconciliation since BBMS does not do nightly deposits. 

Neya Fitzgerald

April Snow:

I was myself just wondering about this as our accounting team asked me to think about ways to make it easier for them to do the reconciliation since BBMS does not do nightly deposits. 

Hi April - 

This has truly been nerve wrecking trying to figure out. Maybe this discussion will help RE / BBMS see some of the challenges we have as users and either help assist us or tweak both systems. I know BBMS dumps the info into RE and when we commit all transactions we have to select the "appeal" once the process is completed I feel that RE should then talk to BBMS to add the "Source" which is blank on all of my disbursement reports. This way for account purposes each gift is justified and documented easily and efficiently. I invite anyone else with this same thought or challenge to jump into discuss or see how we can help each other. :-) Thanks again April. If I find out anything outside of our discussion I will keep you posted. 

Christine Adams

I've attached a sample of something I do each month.  It is by batch and then by the feed into Banner.  I pull out the detail from BBMS based on the credit card deposit date and it's broken down by date.  I enter the fees on each card for each of the batches/dates and reconcile the number.  Then send the summary/detail and the attached document to Finance each month.  This is what they use to reconcile to the bank statement.


Chris Adams

Neya Fitzgerald

Good morning Chris thank you for responding to my post. I will review what you've attached and see if that's something that will help both myself and our accountant. Our accountant once all appeals the number of transactions the growth fee and net amounts listed weekly. I will review this with her when she's back in the office next Wednesday and see if this is something that will work thank you so much I truly appreciate your information and feedback.

Elaine Tucker

Neya Fitzgerald:

Hi Everyone - 

Has anyone been successful in running a report in RE than coincides with BBMS disbursement reports? I continue to take training in RE and ask this question to the Trainers and either I am told there are multiple reports that need to be ran in RE to give me what it is I am looking for or they simply do not know. It could be because each organization needs are different and I need to give more detail or I need to physically show them what I am doing to get the result I need. So, ex. I printed Nov 1-7 disbusrement report in BBMS I could not go into RE and obtain a "batch" because the transactions/appeals are different. I actually have to go through each constituent on the disbursement report from BBMS to identify the appeal, how many transactions, calculate the gross-fees-net all in excel in order to have the report information I need for our accountant. Can anyone help me simplify this process? Or have run across this same issue and have any helpful tips that can help me. I will say it taken me an extremely long time to pull all of this information for the past 2 quarters but I was 85% successful in my calculations and my excel spreadsheet is user friendly for others in our organization but I would like to simplify if possible to be more efficient with this process. Thank you all in advance for your feedback!!!

 

We have RE/FE/NEtcomm and BBMS.  When I get notification that our weekly disbursment is ready, I print a summary of the disbursment report, and if we have any refunds, I print the detail page on that from BBMS.  I then have a crystal report that I run to check to make sure that all the credit card transactions for that week balance and "line up" to what is shown on the BBMS report - transaction batches, card not present etc.  Most of the time it balances perfectly - every once in a while it may be off, but that is usually a timing issue between BBMC UCT time and "real" time so if I am out of balance, I'll look at the last day of the disbursement and see if there is anything in the RE side that is extra that is not in the BBMS side.


I then post to FE from RE using the summary cash option.  Once the JE batch has been added to FE I go in and change all the bank desposit dates to the actual disbursement date. I also update the reference to read Summary Batch # 1111 NET $ 14,568.44.  That way when we are reconciling the bank all the cc charges for that week show up together. 


If there are any refunds that have been issued that week, I also post those to GE in DETAIL, and also change the bank refund date to the actual disbursement date and also note in that reference field Batch # 2222 NET $14,568.44 so that accounting knows that refund is part of that NET deposit.


Sounds like a lot of work, but it actually goes really fast.


Hope this helps.

 

Chris Fedor

Neya Fitzgerald:

Hi Everyone - 

Has anyone been successful in running a report in RE than coincides with BBMS disbursement reports? I continue to take training in RE and ask this question to the Trainers and either I am told there are multiple reports that need to be ran in RE to give me what it is I am looking for or they simply do not know. It could be because each organization needs are different and I need to give more detail or I need to physically show them what I am doing to get the result I need. So, ex. I printed Nov 1-7 disbusrement report in BBMS I could not go into RE and obtain a "batch" because the transactions/appeals are different. I actually have to go through each constituent on the disbursement report from BBMS to identify the appeal, how many transactions, calculate the gross-fees-net all in excel in order to have the report information I need for our accountant. Can anyone help me simplify this process? Or have run across this same issue and have any helpful tips that can help me. I will say it taken me an extremely long time to pull all of this information for the past 2 quarters but I was 85% successful in my calculations and my excel spreadsheet is user friendly for others in our organization but I would like to simplify if possible to be more efficient with this process. Thank you all in advance for your feedback!!!

 

I don't have a good answer for you, but I'm glad to know I'm not alone in this struggle. I also have to go through each constituent on the disbursment report from BBMS to match up with how it came in. We have our online gifts which go directly into RE, but we also use TeamRaiser and the Mobile Pay and those don't go directly into RE so I have to manage those a little differently but all are mixed together on my BBMS reports. I feel like month end is a lot more challenging now that we are using BB because of  how separate each part of the process is.

Neya Fitzgerald

Good Morning Chris,

No, I have not and continue to work tediously thorough each disbursement report which I am currently doing. So I export the detailed report in BBMS and go through each constituent to determine their appeal. I gave up trying to figure it out and as you did I took class, asked questioneven reached out to bbms the end result is their systems do not coincide which is crazy because their under the same umbrella. So, I have been doing this since Jan 2018 and its the only thing that works when our accountant is reconciling. What I will do different next month each week I will have to complete a report instead of spending hours jumping from RE, BBMS and Exported document. Hope this helps.

Jill Freidmutter

We charge credit cards through Raiser's Edge (RE), Online Express (OLX) and Luminate Online (and formerly through Sphere). I created a custom report that:

- subtotals gifts by credit card type (to reconcile with the summary page of the BBMS disbursement report)

- subtotals gifts by Appeal and Fund (for Finance to know how to book the gifts)

- lists each gift with relevant info for comparing to BBMS detail report, if there is a discrepancy.


Similar info could be created by exporting the gifts from RE and using Excel pivot tables.


When our RE report doesn't reconcile with the BBMS disbursement report, the most common reasons appear to be:

- I manually added a credit card gift in RE and forgot to add the gift attributes that tell the query that it's a credit card gift

- BBMS "suspect transactions", where the gift date (in RE) and resolution date (in BBMS) appear in different reports

- Gifts that are given on the cusp of the report dates and were recorded as different dates to RE and BBMS


I'm curious about what others have found. 

Megan Thoms 2

Jill Freidmutter:

We charge credit cards through Raiser's Edge (RE), Online Express (OLX) and Luminate Online (and formerly through Sphere). I created a custom report that:

- subtotals gifts by credit card type (to reconcile with the summary page of the BBMS disbursement report)

- subtotals gifts by Appeal and Fund (for Finance to know how to book the gifts)

- lists each gift with relevant info for comparing to BBMS detail report, if there is a discrepancy.


Similar info could be created by exporting the gifts from RE and using Excel pivot tables.


When our RE report doesn't reconcile with the BBMS disbursement report, the most common reasons appear to be:

- I manually added a credit card gift in RE and forgot to add the gift attributes that tell the query that it's a credit card gift

- BBMS "suspect transactions", where the gift date (in RE) and resolution date (in BBMS) appear in different reports

- Gifts that are given on the cusp of the report dates and were recorded as different dates to RE and BBMS


I'm curious about what others have found. 

I totally agree with you on the final reason why the reports are different '- Gifts that are given on the cusp of the report dates and were recorded as different dates to RE and BBMS.' We have found that because we are located in Colorado and the BBMS reports are based on EST, lots and lots of gifts have different dates to the BBMS report. It is a NIGHTMARE!!!

Mark McConnell

Neya Fitzgerald:

Hi Everyone - 

Has anyone been successful in running a report in RE than coincides with BBMS disbursement reports? I continue to take training in RE and ask this question to the Trainers and either I am told there are multiple reports that need to be ran in RE to give me what it is I am looking for or they simply do not know. It could be because each organization needs are different and I need to give more detail or I need to physically show them what I am doing to get the result I need. So, ex. I printed Nov 1-7 disbusrement report in BBMS I could not go into RE and obtain a "batch" because the transactions/appeals are different. I actually have to go through each constituent on the disbursement report from BBMS to identify the appeal, how many transactions, calculate the gross-fees-net all in excel in order to have the report information I need for our accountant. Can anyone help me simplify this process? Or have run across this same issue and have any helpful tips that can help me. I will say it taken me an extremely long time to pull all of this information for the past 2 quarters but I was 85% successful in my calculations and my excel spreadsheet is user friendly for others in our organization but I would like to simplify if possible to be more efficient with this process. Thank you all in advance for your feedback!!!

 

Hi Nigel,

What we do is use the NL Post Date field as the date that each gift as hit the bank. Because of this we can then run a report based on each NL Post Date for each month.

The only problem we do have is that we have to wait until the cash as hit the bank before we can update the Post Date.


Regards

Mark

Katharine Jurkovic

Gifts that are given on the cusp of the report dates and were recorded as different dates to RE and BBMS


I'm curious about what others have found.

I totally agree with you on the final reason why the reports are different '- Gifts that are given on the cusp of the report dates and were recorded as different dates to RE and BBMS.' We have found that because we are located in Colorado and the BBMS reports are based on EST, lots and lots of gifts have different dates to the BBMS report. It is a NIGHTMARE!!!


We are new to BBMS/Online Express for donations and have also found the same reporting issues with the gifts received at the end of the month. These gifts are processed through Online Express and go into RE with one date (e.g. Jan. 27) and are disbursed to the bank the following month (e.g. Feb. 3). This throws off the month end reconcilliation reports with our Business Office. They have suggested that I go in and manually change the gift date on these transactions in RE to match the disbursement date. Does this make sense? It doens't seem like best practice to change gift dates in RE. Any suggestions would be appreciated. Currently we use Gift Detail and Summary Reports (based on Gift Dates) to balance with the Business Office.

JoAnn Strommen

I feel your pain on getting gift dates to reconcile. Here there's three dates in the mix causing confusion: online gift date the date card charged, date used as gift date and GL post date in RE (can be several days later) and the date it hits the bank.


I would agree that it is not best practice to change the gift date to match disbursement date but I know it is done. I think it's confusing to donors who look at their giving history, gift date in a receipt, annual giving statement etc. when the date does not match their credit card statement or the date they know they made the gift. It can also mess up giving totals for the year when you start figuring donor categories/giving societies looking at specific date range.


It seems to be something business office may just have to grumble about. Do all checks clear on the same date?

Judy Spigarelli

Katharine Jurkovic‍, I would not suggest changing the gift date to match the disbursement date. The gift date reflects the day the donor chose to give. As a donor-centric database, I do my best to represent the donor. If the business office has different needs, that is an internal process and not a donor-facing process. Just my thoughts

Katharine Jurkovic

Thanks Judy. I absolutely agree. I believe the best way to handle the two dates is to enter gifts with their correct gift date and add the BBMS disbursement date as the GL post date (thanks Mark). That way I can run the Gift Detail and Summary report for the Business Office using the GL Post Date which aligns with their bank deposits.

Pam Aylor

Neya and others, our bookkeeper uses the Gift Date (Online Express data for online, date check is received for checks) that we enter into RE for accounting purposes - even though the money is not received for several days (OLX). For month-end reconciliation she includes all gifts received for the month, but those where we haven't received the money yet are put into an 'Accounts Receivable' status in her books.


I don't know if this will help your Accounting Departments, but it might be something worth asking about. This does keep both RE and our Accounting in sync.

Melissa Chin

@Neya Fitzgerald Thanks for initiating this discussion!

I'm a new hire at my organization, and found this discussion helpful. As well, I'm grateful to our colleague who suggested a specific Reconciliation training. Good one!

I'm sharing some instructions I created to help me use vLookup to bring over Fund ID (from an RE export spreadsheet) into a BBMS monthly credit card report (also an Excel export spreadsheet.) This will save me time and help with accuracy the next time I perform this monthly function.

ONBOARDING training notes - Time Saving Excel Formatting and Formulas.pdf

All my best, Melissa Chin

Melissa Chin

@Melissa Chin Here is an UPDATED version of my notes. (I spotted a few typos, pardon me.)

ONBOARDING training notes - Time Saving Excel Formatting and Formulas.pdf