$ lost from one year to another

Jesse -


I think this might work:

• For your FY 2014 gifts in that range - create gift export to show number of gifts and total amount.
• For your FY 2015 gifts in that range - create gift export to show number of gifts and total amount

Then just compare the results from your two exports

• You'll see the difference in total number of gifts in that range from FY 2014 and FY 2015
• And the difference in total amount of giving for that gift range in both as well.

If you actually want to see the constituents who are making these gifts and the change in both years, that's more involved.

 

Jesse Calagna:

I would like to calculate how much $ our organization has lost from FY14 - FY15 in specific denominations (i.e. $25-49.99, $50-99.99, etc).  When i created a gift query for FY14 using this criteria and then a another gift query for FY15 using the same criteria and ran it through the FY14 query I got $0, which is impossible.  Am I missing something?  Is there a much easier way to go about doing this? 

In my last position as Annual Fund Officer, I created a rather complex process for measuring this, not only at year-end, but tracking the variance as "late" gifts and "early" gifts. This shed light on whether we we artificially ahead of PY (a large gift in 2 months earlier than usual, for instance) or not. I also tracked SYBUNT in this model (for the three previous years).


The basic process was this.


I created a query of all Annual Fund gifts (this was for my purposes to remove endowment giving, etc) for the CY and the 4 PYs with Campaign is one of AF2016, AF 2015, AF 2014, AF 2013, AF 2012.


I created an Export where I exported total gifts for CY, PY, SY1, SY2, SY3 (if you truly only want to look at CY v. PY, then the Query and the Export only needs CY and PY). Each colmn needed to have the appropriate dates and filters to get the correct gifts flowing into them.


I opened the export in Excel and built Pivot Tables with the appropriate filters to meet the groups I was looking for -- donors who already gave YTD both PY and CY ("Same"), Donors who have given YTD CY and gave PY, but later in the fiscal year ("Early") , Donors who gave YTP PY but haven't given in the CY ("late"), New Donors in CY, and CY donors who were a SYBUNT1, 2, or 3. I was able to see this in table form for each constituency as well (Trustees, Alumni, Parents, etc)


All of this is a lot more complex than I think you are asking for, but it helped us know who to make sure we were reaching out to (the "late"). It is also a lot of work to set up the first tiem, but after that, it is a lot easier (you can paste new exports into the old Excel files to avoid rebuilding the Pivot tables from scrath (you still ahve to refresh the data to get the new gifts to be calculated).


For the "lost dollars" I manually added a column calculating CY column minus PY column. Sorting that column then makes it easy to count the number of bigger gifts, smaller gifts, and same gifts. At the Cleveland Institute of Music during my tenure there, each was roughly a third, but thankfully the increases always topped the decreases (and the decreases often were due to death (which can also be controlled for if you export "Deceased" and include it in the Pivot Table filters).


I admit what I did is a lot more work than needed if no one is going to do more of a deep dive into the information (actually following up with people who end up in differnt groups) and FOLLOW UP. But as a nerd, it was fun to create it. It took me several iterations and months to get it to where it was turn-key becasue I was learnign things as I went along. The FEP does all of this SO MUCH more quickly, but since everythign is tied to constituent ID, it is anonymous and not really easily actionable at the micro-level,


Hope this makes some sense to someone.


John