Query to find projected value of gifts within a set period of time

Options
Roxanne Vose
Roxanne Vose ✭
Ninth Anniversary Photogenic First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion
Hello,


Does anyone know if it's possible to write a query which will show the projected value of gifts within a set time period? The time period in question is 01/03/2017 - 28/02/2022, therefore some gifts will already have been received, and some are yet to arrive.


Any advice gratefully received!


Thank you,


Roxanne Vose (Magdalene College, Cambridge, UK)
Tagged:

Comments

  • Ryan Hyde
    Ryan Hyde ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    Help clarify something before I proceed: are you talking about the projected pledged cash received within that time period? Or are you just trying to create a mathematical projection future gifts based on past performance?
  • Roxanne Vose
    Roxanne Vose ✭
    Ninth Anniversary Photogenic First Reply
    Thank you for your reply. We would like to know how much cash we will have received during the time period 01/03/17 to 28/02/22, which should include pledged cash received as well as non-pledged cash received (e.g. regular gifts as well as single gifts). Past performance is not relevant in this instance. Hope that makes sense, please ask if you need further clarification.
  • Ryan Hyde
    Ryan Hyde ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    Well, you can't query on future events unless they're planned future events, such as pledge installments. So that means that what you want to build is a projection based on past performance because that's the only data you actually have. 


    This is pretty difficult to do, and you can only do it to within a certain degree of certainty. And RE has no built in projection models, so you'll need to use a third party software package to get this to work, unless of course you know enough about statistics to build a model directly in Excel or R or something like that.


    The easiest software package to use is Tableau. There's a free version that you can monkey around with and get information, though you won't be able to print anything or save it in a format that other people can open up and see it in, but it would be available to look at from your personal desktop. I'm not going to go into how to create this type of projection here as this isn't a Tableau forum, and Tableau has its own set of forums that are really very good. Just google around for it.


    However, to be really really clear about this - no matter how much data you feed into Tableau or any other stats package, projecting out to 2022 is just not reasonable. Anything past a year is purely guesswork as there are just too many potential variables. I know you see this sort of projection in business models and that sort of thing, but even then it's just guesswork (sometimes educated guesswork, sometimes not). So before you go learning a new software package or trying to learn how to build models yourself somewhere, stop to consider that this may not be a great use of your time.

    As for future pledge payments, if you have a payment schedule programmed into your pledges, you can pull an expected revenue report out of RE. 


    Reports > Pledge and Recuring Gift Reports > Cash Flow Report
  • Steven Cinquegrana 2
    Steven Cinquegrana 2 ✭
    Ninth Anniversary Facilitator 3 Name Dropper Photogenic
    Hi Roxanne,


    If you have direct access to your back-end data (that is, you're not Blackbaud hosted), you could likely do this in SQL by making assumptions about continutity of gift value and frequency, etc.


    Cheers,

    Steve Cinquegrana | CEO and Principal Developer | Protégé Solutions

     
  • Roxanne Vose
    Roxanne Vose ✭
    Ninth Anniversary Photogenic First Reply
    Thank you Steve Cinquegrana; unfortunately we are Blackbaud hosted.

    Ryan Hyde, thank you also for your comment. I've had a look at the report you suggested but it doesn't seem able to include instalments made to pledges which were set up before the Campaign started on 01/01/2017, e.g. a pledge with a monthly payment schedule might have been set up on 01/01/2017 and therefore instalments received monthly from January, but we only want to include instalments received from March. The report you suggested doesn't seem to be able to include such instalments as these. Do you know of a way around this please?


    Thank you in advance!
  • Ryan Hyde
    Ryan Hyde ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    Roxanne, do you have an installment schedule set up for all of these pledges? To verify, find one of the pledges, and find the Schedule button on the left side of the general tab. If you have future installments planned, then the Frequency should be something other than "Single Installment", and you should see all installment dates on the right hand side. If you haven't set up installments, you can do so from this screen, unless the pledge is marked 'posted' in which case you'll have to adjust the gift to create an installment schedule.


    Once you've verified that, go back to the report I indicated earlier and click on the Report Periods tab. You can define whatever future periods you want (month by month may make the most sense here, or maybe quarter by quarter) and it'll do the calculation of expected installment amounts based on the Schedule data for each pledge. Of course you may also need to use the Filters tab to look only at a certain campaign or appeal or fund if that makes sense in your particular scenario.
  • Roxanne Vose
    Roxanne Vose ✭
    Ninth Anniversary Photogenic First Reply
    Many thanks Ryan. I think we're slowly getting closer to getting the information we need, but could you please tell me whether it's possible for Gross Gift Amounts to be displayed (the original gift amount plus Gift Aid)? It looks to me as if this isn't possible, but I really hope I'm wrong!

  • Ryan Hyde
    Ryan Hyde ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    I'm sorry, I'm not sure what you mean by "gift aid." Could you define that for me?
  • Roxanne Vose
    Roxanne Vose ✭
    Ninth Anniversary Photogenic First Reply
    Gift Aid is a scheme which enables registered charities to reclaim tax on a donation made by a UK taxpayer, effectively increasing the amount of the donation. I assume you're in the US and so perhaps your version of RE does not include the various Gift Aid / Gross Gift Amount calculations?
  • Ryan Hyde
    Ryan Hyde ✭
    Tenth Anniversary Facilitator 3 Name Dropper Photogenic
    Aha, yes, that explains my confusion. No such thing exists in the US version of RE. I would suggest jumping on chat support to see if they can help you suss out how exactly to pull that complete number.
  • Christine Cooke 2
    Christine Cooke 2 ✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    Roxanne Vose:

    Hello,


    Does anyone know if it's possible to write a query which will show the projected value of gifts within a set time period? The time period in question is 01/03/2017 - 28/02/2022, therefore some gifts will already have been received, and some are yet to arrive.


    Any advice gratefully received!


    Thank you,


    Roxanne Vose (Magdalene College, Cambridge, UK)

    This is to see what the lifespan of your pledges are I assume?


    There is a Cash Flow report than can break it down by whatever time periods you want.  Pledges and Recurring Gift Reports --> Cash Flow Report


    easy peasy

Categories