Gift Detail and Summary Report re: Write Offs

Options
Hi Everyone-


When you all are running your Gift Detail and Summary reports to report on total commitments, what is your strategy to ensure you are not counting current fiscal year pledges that have been written off?


It recently came to my attention that in the Gift Detail and Summary Report, if a constituent pledges $100 this fiscal year and that pledge is written off the following day, this transaction now has two Gift Types, Pledge and Write Off. We know this is true because if you run the Gift Detail and Summary report with only two columns, Pledges and Write offs, you will see a dollar value in both columns.


Here's my problem. Typically when I report commitments, I include all gift types with the exception of pledge payments and write offs. The problem is, I can't easily tell which of those Pledges are write offs and therefore should not be included in my commitments. One clarifying note, I realize if I check the box to report only on the Pledge Balance I will not see dollar totals on the written off Pledges in the Pledge column, but for the purpose of reporting all commitments I need to see the entire pledge amount, not just the remaining balance.


To complicate things more, let's imagine I put an additional column in my report called Write Offs so that I can see the Pledge and the Write Off on the same line. The issue here is that if the Pledge was made in a previous fiscal year, yet the Write Off happened in the current fiscal year, I will see the Write Off, but in this case I should ignore the Write Off because the commitment was in a previous fiscal year and thus isn't included in this fiscal year's commitments total.


So, the question again is, When you all are running your Gift Detail and Summary reports, what is your strategy to ensure you are not counting current fiscal year pledges that have been written off and that additionally, you are ignoring Write Offs where the Pledge was made in a previous fiscal year (again, this is for calculating total commitments for this fiscal year)?


Thanks in advance-

Aaron
Tagged:

Comments

  • If you need to be that specific, I would suggest utilizing the reports under Pledge and Recurring Gift Reports >> Written Off Report is very handy, give you the amount pledge and the amount written off.


    There is also the Cash Flow Report if you want to see what is committed to coming.


    The Pledge Status Report shows you the Full Pledge Amount, the payment schedule, Write Off if there is one.  You can set the window of time to look for Pledges, then you can subract out those that you feel you do not need to count this FY.


    Lastly, the Cash Flow report will give you a picture of what is committed to coming in.  You can break down by year, quarter, month, whatever


    Hope this helps
  • It's a good question.  Whenever we have run reports or queries or exports where there are write offs it always seems to take a fair amount of manual editing.  Generally our unpaid pledges for 2015 are written off in 2016.  Yes, it can make a reporting mess. And donors will pull with original pledge amount for many listings instead of what their gift was after write off.  It is sort of frustrating to us.


    Will be watching for the solution if another user has found it.
  • Thanks for your input Christine as those are good suggestions. I haven't used those reports because I'm not having a hard time finding the write offs. I can see them in the Gift Detail and Summary report. It's the research part of finding which ones to back out of my commitment totals and which to ignore that I was hoping to somehow automate. JoAnn seems to face a similar challenge. And I hear you JoAnn on the Write Offs happening the following FY making reporting a mess. We wrote off our pledges on July 14 of this year and I won't be doing that again, lesson learned. I've already spoken with our gift processing manager and our plan is to write everything off on June 30 and recreate pledges if gifts for any of those write offs come in the first two weeks of the next fiscal year. We're still working on how to sell that story to the directors/VPs.
  • instead of writing a new pledge why not do an adjustment and carry it forward?  That way you will not lose the original entry date, which is on the books with finance? 


    What if you wrote a query looking for JUST the gift type of Write Off and then pulled that through gift detail and summary? 

    Or use the query to do an export that would list the original pledge date/amount/fund and and then the same with the write off info.
  • In theory (logically) lots of things should work with write offs. 


    Christine, what would you change in your gift adjustment?  The fund? Sorry, I'm not seeing what an adjustment would do to solve reporting issues of pledge from previous year.


    To me there are unresolved issues with write-offs in RE.  You would think that you could query (constituent or gift) on write offs to a specific fund.  Not so that I've found...the only way to get that info in a query is an output from Reports > Write offs.  
  • We view write-offs as an "expense" which wouldn't change the "commitment" total.
  • I am not sure if this resolves your specific problem, but I recently found something that relieved much of my frustration when looking back in time at totals raised for a particular fund or campaign using the Gift Detail and Summary report.  I recently discovered that using all of the Cash types, all of the Stock types, and Recurring Gift Pay-Cash in one column then using the gift type Pledge in another column while choosing the 'Use gift balance instead of gift amount option' along with the 'Calculate gift balance based on date range, campaign, fund, and appeal' option on that Pledge column, I can see the amount paid towards the pledge and the balance of the pledge at any particular time during the year.  For example, we have a constituent who made a $300 pledge in November, paid $100 of it in December, then had us write off the $200 balance in January.  If I choose an end date of November 30 on tab one of my parameters for the report, I see the whole pledge amount ($300) in the Pledge column and in the Total column.  If I choose December 31 as the date, I see the amount paid toward the pledge ($100) in the Cash In column, the Pledge balance ($200) in the Pledge column, and $300 in the Total column.  If I choose today's date, I see $100 in the Cash In column, $0.00 in the Pledge column, and $100 in the Total column.


    Aaron Rothberg
    :

    Thanks for your input Christine as those are good suggestions. I haven't used those reports because I'm not having a hard time finding the write offs. I can see them in the Gift Detail and Summary report. It's the research part of finding which ones to back out of my commitment totals and which to ignore that I was hoping to somehow automate. JoAnn seems to face a similar challenge. And I hear you JoAnn on the Write Offs happening the following FY making reporting a mess. We wrote off our pledges on July 14 of this year and I won't be doing that again, lesson learned. I've already spoken with our gift processing manager and our plan is to write everything off on June 30 and recreate pledges if gifts for any of those write offs come in the first two weeks of the next fiscal year. We're still working on how to sell that story to the directors/VPs.

     

  • I don't remember all of the reasoning, but one of my first major frustrations with RE was write-offs.  I decided, with buy-in from my boss, to avoid the write-off function and use Gift Adjustments instead.  This allows a Query to show the Original Pledge Amt and the Adjusted Amt both (I could never find an option other than the Gift Record itself that shows the Write-Off Amt).  Every such gift gets a Subtype of "Write-Off", a GiftNote that fully explains the circumstances (including approvals from Finance/CFO and VP Development), and on the Adjustment, the Reason is "less $150 (from $200)" with a short description or "see GiftNote".


    However, we do not use RE canned reports, so if we were to start using them, we'd need to do some careful testing.  Instead, we export our data (from Export or from Query...and I know Query is not supposed to be used for exporting, but these Queries have been thoroughly tested) and run custom reporting outside of RE.


    I wish RE would create an actual Gift Record for a write-off, that could be opened, edited, exported, etc. just like any other gift, except you could include or exclude the record in data outputs (reports, queries, exports).  I suppose that's an option...use Gift Type: Other (which becomes Pay-Other) with Subtype: Write-Off and enter it as a payment.  Again, would need to do some testing with canned reports (and edit some code in my custom reports!).
  • Bleh, write-offs.


    I don't know that I would use the gift type of other for write-offs. I have sometimes found other uses for the other type (specifically charity campaigns, but that's another post).  But mostly I would worry that the write-off could look like a gift.  It would be nice if you could use the legend to make that row red or gray or something, but if you are using other for any other gifts they would also appear red.


    Personally, I think perhaps at lease seeing the write-offs without having to go through the original pledge record.  I also think that if the write-offs would export like a negative number it would help when reconciling with finance.
  • with the Gift Detail and Summary Report you can also choose to have the Write Off amount as a column that would be included, choose that Gift Type to go into one of the columns. 

    I find that utilizing the 4 columns available this wait is most straightforward and tells the story the clearest:

    Column 1 are gift types of: header is cash/stock - Cash, Stock/Property, Stock/Property(sold), recurring gift, other

    Column 2 are gift types of: header is full pledge - Pledge and MG Pledge AND

                               the :"use gift balance instead of gift amount" is UNchecked

    Column 3 are gift types of:  header is payments - Pay-cash, MG Pay-cash, Recurring Gift pay-cash, Pay-stock/property,

                             MG Pay-stock/property, pay-Stock/property(sold), MG Pay-stock/property(sold), Pay-Other, MG Pay-Other

    Column 4 are gift types of:  hear is write off - Write off and MG Write Off


    That will give you the 4 categories most everyone is looking at.
  • We have similar issues with write-offs.  We use an attribute on the gift of Pledge Date on the pledge and pay cash (payments) so we can pull in the pledges within the period we want to see the initial pledge. Then pull in the pledge balance and calculate accordingly. 

    It is frustrating that write offs are not linked to the original pledge.


    Hoping for improvements!!


    Jane

Categories