Another Planned Gift "Double" Counting Problem with Insight Designer

Options
Tom Klimchak
Tom Klimchak ✭
Tenth Anniversary Facilitator 2 Name Dropper Photogenic
in Blackbaud Raiser's Edge NXT®: General Discussion
We've always tried to use the Raiser's Edge Planned Giving module the way it is "supposed" to be used, but we feel as though we're constantly punished for doing that.  We just found another double counting in the Dashboard/Insight Designer reports.

Scenario:  We booked a Planned Gift in January of 2017.  The donor sadly passes away and we received an Estate settlement in December of 2019.  

Data Entry:  That would be a Planned Gift record, 1/15/17.  Then we put in a Gift / One Time Gift record on 12/15/19... and we properly code it as being Realized Revenue for the 2017 Planned Gift.  So far, so good...


Now I go to build some Insights for a cool new Dashboard.  The first insight is all our COMMITTED revenue between two dates, and the next insight is for all RECEIVED revenue between two dates.  


They're simple to do, but that 12/15/19 One Time Gift is counted in BOTH insights.  It's a One Time Gift, so it counts as Received (cash in) AND it counts as Committed.  


But... that's ridiculous.  In reality it's a "payment" on a pledge (planned gift) and it really should not be count as new committed money.  So now my dashboard for ONE gift looks like this:

e9b447eadc7581f03d2ad18a38c06df9-huge-do

  

Any ideas?  There isn't yet a way to build Insights with the ability to filter out planned gift realized revenue.  
Tagged:

Comments

  • David Hecht 2
    David Hecht 2 ✭
    Sixth Anniversary Facilitator 1 Name Dropper Photogenic
    I am also struggling with this question.  I created a similar dashboard and was disappointed that when realized revenue is counted, the planned gift is no longer counted in Committed revenue.  I understand that this prevents double counting.  However, this poses a concern as we report fundraising based on Committed revenue.  When we realize revenue, our prior year number changes for Committed revenue.  I have developed reporting in the database view to be able to track the number, but would like to be able to have the dashboard in NXT.  Any thoughts here would be great.
  • Jill Deming
    Jill Deming ✭
    Ninth Anniversary Facilitator 1 Photogenic
    We're working through something similar at the moment, but not with a planned gift. I have not delved into it too deeply yet, but I noticed on the Committed and Revenue tiles that you can filter it further by excluding one-time gifts (using gift type, detailed as a filter option) from one of them. Have you tried that? I am concerned about some dashboards I am building for our Board.

     
  • Tom Klimchak
    Tom Klimchak ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic

    Soo… I haven't seen any progress on this in the last 18+ months, but it's still an issue for us: Committed Giving in RE “Insights” will count a gift that is linked/realized against a previously entered planned gift/bequest.

    There doesn't seem to be a way to exclude gifts that are linked to planned gifts in NXT.

    And.. YES, I am aware there is a Giving Analysis / Configure Committed Revenue setting that reads:

    Planned gifts

    If you include both one-time gifts and planned gifts, committed revenue subtracts any realized revenue from the planned gift amount to prevent double-counting.

    I think that actually ONLY works if both the planned gift AND the realized gift are included in the same time period (or reporting records)…

    The example again:

    In FY20 Robert Hernandez gives us a $150,000 planned gift (bequest) as Committed money. Yay! $150k committed!

    In FY22 Robert passes away and we receive a GIFT of $150,000 from his estate. We enter it as a CASH gift and it's linked to his FY20 planned gift. In our FY22 reports we AGAIN count that $150k as committed (but, really, it's “payment” on a previous commitment/bequest).

    This sort of thing undermines the credibility of our NXT dashboards/reports AND it forces us to keep going back to database view for any sort of accurate accounting.

  • MaryAnn Coyle
    MaryAnn Coyle ✭
    Fifth Anniversary Facilitator 1 Name Dropper 2024 Product Update Briefings

    We are relatively new to RE NXT and had few planned gifts in the first year. Now that our activity has increased and we have planned gifts and cash distributions crossing fiscal years we have encountered this problem not just in web view but also in database view reports. Has anyone yet found a way to suppress cash payments from planned gifts when the payouts stretch beyond the original fiscal year?

  • Tom Klimchak
    Tom Klimchak ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic

    MaryAnn,

    You can almost always exclude gifts that pay off Planned Gifts in database view by using a GIFT query that selects all gifts where “Linked to Planned Gift = No”.

    So for most of our financial reports (and exports) that count up Committed money we include Gift Query filter that specifically shows all gifts not linked to a planned gift. You can also use that when building summary fields in constituent queries.

    It's a pain to remember to do it (and it DOES slow down your queries a little bit) but it does work in database view. We still don't have a real clean-cut way to do it all the time in RE NXT.

  • MaryAnn Coyle
    MaryAnn Coyle ✭
    Fifth Anniversary Facilitator 1 Name Dropper 2024 Product Update Briefings
    Thanks for the tip on "Linked to Planned Gift."


    We are also exploring using a subtype for Realized Revenue as subtype is available in Insight Designer. Any experience with that approach?
  • Tom Klimchak
    Tom Klimchak ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic

    Unfortunately we already use Gift Subtype for other things so that's not actually an option for us in this case…. But in theory, yeah, it could work.

    I haven't looked at this in depth for a while, but I notice that we can filter on “Package ID” which might be something we could use as a filter just like the Gift Subtype could be used… Hmm, maybe we'll try this out with my gift processor today… thank you! ?

  • MaryAnn Coyle
    MaryAnn Coyle ✭
    Fifth Anniversary Facilitator 1 Name Dropper 2024 Product Update Briefings
    Package ID is promising. Looking at filters for web view canned dashboards, we will explore a dedicated Appeal ID as well.
  • Larry Wheeler
    Larry Wheeler ✭
    Eighth Anniversary Facilitator 2 Name Dropper

    I have gone back to “hard copy” to solve this problem. I know it's contrary to the technology we're paying to use (and doesn't answer the question), but my administration is not interested in known issues with the software. Results is all they want to see and they are not concerned with how Blackbaud thinks they should view those results. We have a couple of donors paying their planned gifts off early, or wanting to reduce the total amount owed once they are deceased. All money was to be counted when the money was given, not when the pledge/planned gift was made.

    I began keeping a snapshot worksheet of giving totals when I began in this office. We enter in received amounts on the planned gifts (thus reducing it) so double counting doesn't occur. We refer to the snapshot worksheet if we need to converse about how much was actually committed in a past year. Of course, the worksheet can only be incorporated into any type of output, manually.

    I am going to have a look at the package id idea, as well. If nothing else, it may fix nxt insights/dashboards?

  • Tom Klimchak
    Tom Klimchak ✭
    Tenth Anniversary Facilitator 2 Name Dropper Photogenic

    Hi.. I just wanted to follow up with this. We did NOT use Package Record ID (that seems to be some sort of an internal ID) but we DID use Package Short Description.

    We have a “Cash” gift that was a Realized Payment to a Planned Gift. We gave that single gift an Appeal Package of “PG Pay” and then we changed our “Committed” insight to filter on every Package EXCEPT that single PG Pay. We did this by keeping a checkmark on every existing package but removing the checkmark from the PG Pay code… And then we waited a bit for everything to update in NXT…

    And it worked! The insight added up all the gifts for the year without that ONE gift.

    However…. When doing this the filter thing it removes the checkmark from the default “All” … which makes me wonder what will happen when we add more packages down the road… will they automatically be checked or UNCHECKED in that filter? Will have to experiment another day…

  • Carlene Johnson
    Carlene Johnson ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    @Tom Klimchak it was only after I started a new discussion thread on the same topic today that I then discovered this thread. Wondering if you have any additional information since this was last discussed on this thread?

    I'm wrestling with the same issue and am hoping there has been some progress since 2020-2021!

  • Allison Phillips 2
    Allison Phillips 2 ✭
    Fifth Anniversary Name Dropper Photogenic First Reply

    @Tom Klimchak:
    We've always tried to use the Raiser's Edge Planned Giving module the way it is "supposed" to be used, but we feel as though we're constantly punished for doing that. We just found another double counting in the Dashboard/Insight Designer reports.

    Scenario: We booked a Planned Gift in January of 2017. The donor sadly passes away and we received an Estate settlement in December of 2019.

    Data Entry: That would be a Planned Gift record, 1/15/17. Then we put in a Gift / One Time Gift record on 12/15/19... and we properly code it as being Realized Revenue for the 2017 Planned Gift. So far, so good...

    Now I go to build some Insights for a cool new Dashboard. The first insight is all our COMMITTED revenue between two dates, and the next insight is for all RECEIVED revenue between two dates.

    They're simple to do, but that 12/15/19 One Time Gift is counted in BOTH insights. It's a One Time Gift, so it counts as Received (cash in) AND it counts as Committed.

    But... that's ridiculous. In reality it's a "payment" on a pledge (planned gift) and it really should not be count as new committed money. So now my dashboard for ONE gift looks like this:

    e9b447eadc7581f03d2ad18a38c06df9-huge-do



    Any ideas? There isn't yet a way to build Insights with the ability to filter out planned gift realized revenue.

    @Tom Klimchak
    We too are just having this problem and my team took a 4 hour working session to figure out what is going on. You have done everything correctly as we did in entering the planned gift and the realized reveue. What we discovered is that if you go back to the date of the original commitment. The planned gift will be listed but the amount is zeroed out in the totals on your comitted amounts.

    It is incredibly confusing and is counterintuitive to what we expect the system to do when counting for campaigns. Rather, it is designed and performing exactly as an accountant would expect it to for revocable planned gifts.

    We would expect that the one-time gifts of realized revenue (payments) would be excluded and be acting in the same manner as pledges. In fact, these amounts are zeroing out the originally planned gift commitment in the date range that the planned gift was recorded. This is extremely problematic when you are looking at a year to year comparison.

    This needs to be fixed for Campaign counting Standards going forward and for the system to function similarly to pledges and pledge payments.

  • Allison Phillips 2
    Allison Phillips 2 ✭
    Fifth Anniversary Name Dropper Photogenic First Reply


    @Carlene Johnson I have just posted what my team found in a reply in the post above. In using @Tom Klimchak's example if you were to look at the committed amount in January 2017 you would still see the planned gift in the gift list but the calculated amount would be $25,000 less (the amount of the realized gifts from 2019).

    Again this is super problematic when counting for campaign numbers according to CASE guidelines. I am hoping to use this thread in the idea bank for this to be fixed.

    Our workaround is to build a dashboard outside of the system using PowerBI to create it. Super frustrating though for the insights to not act as we would expect.

  • Allison Phillips 2
    Allison Phillips 2 ✭
    Fifth Anniversary Name Dropper Photogenic First Reply

    @Carlene Johnson @Tom Klimchak Here is the idea I have added to the IDEA Bank RENXT-I-6952. Please vote.

Categories