End of Year Procedures on-demand webinar (US)

Arlene Berkowitz: We are now ready for our webinar. I will now formally welcome everyone to the first of two installments of DonorPerfect Online Year-End Procedures. My name is Arlene Berkowitz. I am joined here with my colleague in the training Read More

Arlene Berkowitz: We are now ready for our webinar. I will now formally welcome everyone to the first of two installments of DonorPerfect Online Year-End Procedures. My name is Arlene Berkowitz. I am joined here with my colleague in the training department, Miranda Gibson.

Obviously, I think the goal or what most people are here to learn is the Year-End Tax Letter. Our recent change to what we call the user interface or the look and feel of DonorPerfect. Made it easier to set that up. I’ll show you where that’s going to be done. Before we do that, there are one or two things you need to know or do in DonorPerfect. We’ll talk about those.

We’ll then get to what I call, I guess the highlight or the goal of this session, which is Year-End Tax Letters. For those of you who wear a financial hat, if not, then you can pass along the information we’ll talk about in this segment, which is the useful year-end financial reports.

Those are reports that should be run at year-end. They don’t have to be run solely at year-end, but it’s a sample or a suggestion for things that you want to run at the end of the calendar year. Then we’ll talk about some other year-end procedures. Those are best practices.

They would be some clean-up if you have the time for that. We’ll talk about some of those things. If you have any questions regarding this agenda, please feel free to send that to Miranda. Hopefully, this is what you looked at on the agenda when you signed up for this session.

I just wanted to mention that we have another one on January the 6th so that if anyone in your office wanted to attend this live, they can register on January 6th. You should have seen the link for that in the e-tip that was in your inbox right early this morning.

We are going to record this session and it will be published. It will probably be available after the first of the year, but just in case you want to review or someone who can’t attend either session, wants to see it is my goal to have this session recorded. Here we go.

Before we get into what I call the nuts and bolts of this session, let’s understand a few terms and key things in DonorPerfect and related to this webinar. Are we dealing with fiscal or calendar year-end? I hope everybody on the call is definitely nodding their head, “Yes, calendar year-end.” At the end of 2015, that’s going to roll around, in around 10 days or so. We will have our end of the 2015 calendar year for many of you on the call.

If you actually want to click on your grab tab and raise your hand, how many of you have a fiscal year that is concurrent with the calendar year? Meaning how many of you raise your hand, have a fiscal year for your organization? That begins in January as well. If you don’t know, that’s really an okay answer.

I’m seeing almost 50% of you. All right. That’s actually not unusual and I will talk about it in a second, but if you’re not sure, then you can actually get the answer to that question in DonorPerfect where the fiscal year has been set. It usually is set only once and not changed, but it is possible should your accountant or for some other reason, if you choose to change the fiscal year, it can be done.

That’s going to be in settings, which is now the gear at the top right corner of DonorPerfect going into parameters. Then in general parameters, there’ll be a two-digit number, and either it’s 01 for January, it could be a July 1, which also is very popular or common in nonprofits. Would be a 07, it could be October, et cetera. Any month in the year would work, but all of us have in common today that the end of the calendar year is approaching soon.

Why do I make this distinction? Why is it necessary to understand these two terms? For the calendar year-end, that is going to be very critical to your donors. Your donors only have one year-end. Their fiscal year-end happens to coincide with the calendar meaning on December 31st, that is the end of the year for which their donations can qualify for tax deductions. They have hopefully been getting receipts or what you call acknowledgments from you during the year.

However, for some of you, you may only send an end-of-year tax letter, but if you send both, that’s perfectly fine. Just letting you know that it is a possible operation choice to only send end-of-year tax letters that will capture all donations made in one year. On the other hand, the fiscal year relates to your organization’s internal calendar and what your accountants or the accountants for your organization use for closing the books.

There are going to be at least a handful, maybe a dozen reports that you’ll want to run when you close the books. I will make a minor statement here that DonorPerfect books technically do not close. Unlike an accounting software where there is a hard close to the fiscal year, DonorPerfect is what we call an open ledger. You could continue to receive donations into January that are related to the previous year.

I’m not really going to go down that road, but just did want to make that statement. It is going to be important regardless of when your organization’s fiscal year ends, that you’re going to have certain reports. We’ll talk about that towards the end of this session about those reports that you’ll want to have on hand. Now that we’ve understood the term fiscal versus calendar year-end, another term you need to be familiar with is something called recalculation or recalculating.

If you’ve ever seen it before, well now we’re going to discuss it. When you come into the office on or about January 1st. Hopefully, it’s going to be January 4th, just like me. Should you log into DonorPerfect sometime on or after January 1. This magical process takes place overnight between 12:31 probably while we’re sleeping and before we wake up on January 1.

We here at DonorPerfect will calculate all of the gifts that your donors have made in the calendar year of 2015. We are going to summarize that and put them into last calendar year’s totals two calendar year totals, three calendar year totals, et cetera. Prior to that January 1 date, we have buckets of money per donor that are totaled per year. We have this current calendar year’s totals, last calendar year’s totals two calendar years ago, going back six calendar years in total.

Each donor has these calculations. Those are only a handful of the calculated fields per donor that exists in DonorPerfect. Now, just as a piece of information for you, these calculations are once again statistics or totals about the gifts. These calculations live in the main file when building filters. Some of you may have these fields displayed on each donor’s screen, but if they’re not on the screen, they can be used in filters as long as you filter on the main/bio-table.

This is a picture of what happens. All of the numbers are shifted in this calculation or recalculation process so that the buckets are appearing in the proper years. What is in the current calendar year to date, all of a sudden on or after January 1 will go into, it’s actually spilled out and goes into last calendar total. What was last calendar year is spilled out and goes into two calendar years. What was in two goes into three calendar years ago total.

This is what is going on with this recalculation process. Just to repeat, if any of you were wondering, “Do I have to turn on a switch, Arlene? Do I have to click on any button?” No, you do not. We will do this for you on or about early morning of January 1. If you happen to be on this webinar and your fiscal year is a month other than January start, then you will get a popup that says, “Your fiscal year has just changed. It’s time for you to recalculate the gifts.”

Should you see that message in a month other than January, I would recommend that you say yes. You will not harm the system if you do that. If you are taking in any gifts for the previous fiscal years, each donor’s information will be updated. This is the discussion about recalculating. What do you want to do before these tax letters need to get printed? I know that you’re all chomping at the bit and you want to know how to get those letters out.

That’s wonderful. However, one of the things that we’re going to suggest you do, while it’s not a requirement, but this would be a best practice, is to remove duplicate donors. I’m going to put hands down. Now I want to show of hands, how many of you think you have duplicate constituents in your database?

Click on your hands up. If you’ve been running reports, if you’ve been looking up donors on your search screen, you probably have come across– Wow. All right. I’m going to say this is a very informal number. I’m going to say probably 60% to 65% of you or more suspect you have duplicates. One of the other takeaways you’re going to get from this webinar is what to do with those duplicates. Arlene says, and this graphic shows we want to get rid of duplicates. All right. How are you going to do that?

You’re going to do that in DonorPerfect, and you’re actually going to merge the constituents together. How do you merge that? Let’s actually go into DonorPerfect now and show you how that’s done. Now I’m going to be on a generic DonorPerfect. If you see something that isn’t similar to what you have, it’s because I’m on a demonstration database that we use for these webinars.

Before doing duplicate removal, one of the things that you must do is go to utilities and make a backup. Now, we do make backups of your database every night, but should you want to undo the duplicates that you’ve decided to do, the way to undo that is by restoring a backup. This is one situation where we would recommend you make a backup of your DonorPerfect database.

Now, this is not something you need to do every night, but in these situations that are highlighted here, last of which being removing duplicated records through duplicate removal, I am going to create a new backup. It won’t take long, and you can have other users or other staff in DonorPerfect while this is taking place. That’s how quick it can be done. My backup is done.

I can now proceed to the duplicate removal without any worry. Under the wrench, which is your utilities, we also have, in addition to backup and restore the duplicate removal. Now when duplicate removal comes up, it’s going to scan the database and look at pairs at a time of constituents who could possibly be duplicates. What’s going to happen is it’s first going to come up with what we call the tightest or the strictest definition of duplicates.

Where the last name is the same for the first 10 characters, where the first name is the same for eight characters. Address and zip code are the same. This is what I call almost an exact duplicate. All you need to do is to click on show report to show you who it suspects are the duplicates. There’s actually two ways of doing it, and I will illustrate in a second. Notice that I have actually three different records for Kelly and I have one pair of records for Justin.

In this case, notice that it is a group of three constituents for Kelly. The way duplicate removal works is it will deal with one pair at a time. You’ll first address one pair, and then that other Kelly record will have to be compared to that pair that remains. Let me go back a step. What happens with duplicate removal? Not only am I removing the constituent who was added incorrectly, but should there be gifts or should there be information on other info, should there be pledges, all of that gets combined into one surviving record.

That’s what you have in order to help you to use the duplicate removal. All right. Here we see the strictest definition of duplicates. What I do want to suggest to you, while you can do this one round, it’s probably a good idea to do several versions or rounds of this. If I were to, let’s say, clean this up, I would then relax the requirement regarding first name. For instance, this will address nicknames.

If I had the name of– Trying to think of somebody. Let’s say Robert, and let’s say I have another record with Rob and it’s the same donor, but we go by a nickname, most nicknames are going to be three letters. Obviously, there are issues where Patricia and you have Pat in there. You’re going to take care of most of the situations. There are some names. For instance, if I had Robert and I had another record in there for Bob, that’s not going to meet this test, but let’s just deal with the majority would possibly have a nickname if I choose to compare it by three characters.

I now show the report. That didn’t give me anything else. In your database, it might. After you’ve cleaned up first names of only three characters matching, let’s remove or relax the requirement of first name. What am I saying here? I’m saying, “DonorPerfect, I’d like you to compare my database where the last name is the same, the street address is the same, and the zip code.” That’s how you’re going to take care of William and Bill.

That’s also how you’re going to take care of William and the spouse is Mary in a separate record, going back to the best practice of combining household information. Notice I do have, in this case, eight constituents. Here’s our Robert and Bob classic example. They both are on 360 Markel Street. Now, while there could be an issue of senior and junior, if that were the case, I would mark them not as duplicates.

In my example, let’s say that I passed this report around to other people in my office and they know that there are no adult children living in that house, then I can either use the simple or I can use the advanced. Now, for purposes of this webinar, just to move things along, I’m going to use the advanced method. I do recommend that this is preferable over simple because you’ll see many more fields of information.

The sample will only show you about a half dozen fields of information. When I click on advanced, I now see Bob Lukins and Robert Lukins, and I see many more fields of information so that I can say that I want certain pieces of information from one record, but keep something from another. Let’s say that the surviving information or what’s going to stay in DonorPerfect is this record 634.

The one gift from this record will be added to the one gift from this record and I get a total of two gifts totaling $750. Donor full name, I would like this but if for instance, I wanted donor full name to be this option, notice that I can override any of the choices here. Very helpful for you to pick and choose what you want to be in the remaining or surviving record. I look at this to see if there are any other discrepancies and I don’t see anything so I’m now going to just continue with the combine.

What that will do is it will actually delete the one record and leave me with one Markel and then I can come back to the list and Mr. Markel is in fact removed off the list. I would go through this depending on how long this is, it would be a good idea to consolidate. The reason why we’re suggesting this now is because I don’t want to send two letters one to Bob Markel, and another one to Robert Markel, for the end of your tax letter. Ideally, I want to consolidate all of the gifts onto one tax letter for the Markel individual.

That’s why duplicate removal is recommended prior to year-end, it’s also a good tool or practice to run prior to any mailing. All right, let’s get back to our presentation. Now, what I guess everybody came for is your Year-End Statement. There are IRS regulations you may want to check with your accountant but you may be sending a year-end tax statement to all of your constituents. If that’s the case, then we’ll show you how it’s done.

A couple of what I call ingredients, what do you need? You’re going to need a letter, you’re going to need data from DonorPerfect, those are the two primary ingredients for this tax letter. How do we come up with it? You can get the premade template from the knowledge base, I will show you where it actually comes from. This letter actually uses a tool, you may not use it for any other time during the year but it uses something called the gift list.

This gift list is what is going to help you put all of the gifts together on one sheet of paper because you don’t want to send five or six different letters to the sandstones. You want one letter that says, “Below is a list of all your contributions made in the past year.” Where that’s going to come from? You can do it from mailings and mail merge, I actually like to go into reports listings, choose the last option there which is export to file, and specifically you’re going to have it on main bio-data.

All right, everything going okay, Miranda, just want to make sure if there is any question if you have something you want to share with me? All right, I think everything’s running smoothly so far. Okay, great, thumbs up from Miranda. Wonderful. We’ve got a letter and we need the data from DonorPerfect. All right. Now let’s get into a little more detail about how this process occurs.

First, have an idea of what you want to put into that letter, from either mailings, mailmerge, or reports listings export to file. You have a check box and I will be demonstrating this, you’ll have a checkbox for include gift list. To the right of that include gift list is a gear or a wheel. This was just changed in the most recent enhancement in DonorPerfect, that gear or wheel allows you to, number one, determine if the information or that list is going to appear as either individual fields or consolidated fields that is.

I do suggest, you’ll see it bolded here, I suggest and our internal at software, our best practice is to choose the individual fields option on the radio button. Then last but not least, you can have up to five columns or fields of data in that table and you’re going to choose the fields. Generally, those fields are going to be information from the gift screen, date of gift, definitely amount of gift. You may choose general ledger, you may choose solicitation, you may choose campaign, you may choose reference.

Any of those but keep in mind up to five. Now you’re going to need that starter letter. Where are you going to get it? You’re going to go to the mail merge template from the gear which is the top right corner of DonorPerfect, the settings, select from the settings menu, mail merge templates. While you may have some templates that are there in mail merge templates that you use for your acknowledgments, you’ll actually look at the bottom right for the caption regarding looking for pre-made templates, look for the word pre-made that’ll stand out and you’ll click on that and you’ll download the starter template.

All right. We’re halfway there. You’ll then modify that starter template, put your own verbiage, put your tax ID, put any logo you want or possibly even copy the fields onto an existing letter that you have that might work better for this purpose. Then you’ll upload that final template. I’ll show you how it’s done. Now, how do I get the data? We have to build a filter and where do we do that?

In reports and listings, you’ll select export to file but this time, you’ll select main bio. Now, here I’m suggesting a filter of date of gift is between January 1, 2015, and December 31, 2015, that’s the date of the gift. Now, this will be critical one for those of you who may be watching this or attending this webinar in January, that’s where the date of the gift you might change to December 31, even though you’re physically putting it in January.

That’s the exception to the rule when the date of gift might be the day you received it, for tax purposes, you might want to change date of gift to 12/31. Now I’m suggesting that also part of your filters should be and the amount of the gift is greater than zero. Essentially, I’m looking for any monetary gifts given in the calendar year of 2015. Now, this would be a time if you have questions you can send them to Miranda, you might have other scenarios.

For instance, you might have gifts in kind that you’ve been recently recording in DonorPerfect. You might have donations, let’s say for tickets for a Gala, and you have the total amount of the check but you also have been told to record in the fair market value, the non-tax deductible amount, that is wonderful to do that. Miranda will give you a little guidance but essentially DonorPerfect support, our team is well equipped to help you with how to produce this filter and how to get these letters out with information, should you have in-kind gifts, or should you be using the fair market value in DonorPerfect.

For our purposes today, I am excluding those two other scenarios. Now to make this all work you must make sure to check the include gift list. By checking the include gift list that will produce a table of one or more gifts per donor. Last but not least, you’ll click export to file you’ll get a CSV file that can be merged with the Word template. All right, you want to see how it’s done, here we go. You probably need the word document. Where are we going to go? To settings, which is in the top right, go to mail merge templates. Now here I have other templates that I’ve been using, whether it’s for acknowledgments, whether it’s for an appeal. Those templates are here.

What I need to do now notice the word here is pre-made. Click here unless you happen to have the template from last year. If not, then click on looking for pre-made templates, which will take me to the knowledge base. When I scroll down, I’ll see many letters. If this is the first time you’re going into this particular article, this is where you’ll find what we call the pre-made or the starter templates that have the DonorPerfect merge fields in them.

For instance, for your acknowledgment, you’ll use letter thank you for an appeal letter general. For the end-of-year tax letter, we’re going to use one of these two. Now I have multiple and I have single gift lists. I do recommend that you download and that you use this one, which is called multiple gift list fields. I’m going to open it up. What happens with this letter? Here are the DonorPerfect merge fields.

They look a little familiar. If you’ve used any of these other templates, dear first name, last name, you could do that. What if you don’t have– What if you are on a more informal basis with your constituents? Then maybe you want salutation. What I’ve done, and I’ll bring up the letter that I’ve created in a second to show you how I’ve changed this into the word or the merge field called salutation.

Now, this sample letter also uses all five gift list fields. If you’re not going to use five, then you’ll need to move things, or delete a column. You also might need to change what is the column heading. Maybe you’re not going to use GL code in your letter. You may use campaign, you might use solicitation.

Here, last but not least, we see LYCYTD. That is the field that we’ll have. Remember in the beginning of this presentation, I talked about calendar year total and last calendar year total. The field as it stands now is pulling last calendar year total per donor. This will be what you’re going to use if you’re producing the letters in January. Assuming that you are going to get many donations on the 30th, 31st, maybe on even on the fourth and fifth, you’re still going to be entering them with a date of December 31.

Then in January, you will use this field last year’s calendar year total. However, for today’s purpose, just to show you that it works, I’m opening up a template that I have changed.

I actually am not using the gift list five, I’m only using four. The field for solicitation, I’m calling appeal. One other thing I want to highlight is the body of the letter. If you use the letter that we created, make sure you change it to the year 2015.

Now here I have the merge field calendar year. This means this current calendar year. I’m only illustrating this because we happen to be still in 2015. If you are watching this video and you’re doing this in 2016, make sure that the merge field in your template is this LY_calendar year. All right. Let me just get the right one up. Hang on a second.

This is my letter. If you want to have the total at the bottom, that’s fine. You do not have to. Notice that the letterhead here has been changed to look like an organization. If you want to change the margins or the header and you’re using stationary paper, that’s perfectly fine. This is just a sample. The most important thing that you need to do is to use these merge fields that came from the pre-made letter that I downloaded.

All right. Any questions out there, feel free to send them to Miranda. If it is more specific to your way of keeping donations in DonorPerfect, then Miranda will guide you to contact support. All right. That’s the letter. I have a letter that has been modified and suits everybody’s needs. I’m going to close out of Word. Now, what do I do?

Once that letter has been finished at some point, feel free if there’s a couple of days or you have to wait till January 4 to get that letter finished because you’re waiting for somebody’s signature that’s going to be electronically captured as a jpeg and it inserted into your letter. All right. Perfectly fine. What do you want to do?

You want to upload the finalized letter, upload new, I’m going to browse my computer to find that letter, which can be anywhere on your hard drive or anywhere on your server. Here are my letters. I want the letter here actually that has current calendar year just for purposes of illustration. I do have another template here for last calendar year. If I were to do this in 2016.

I’m going to open that. All right. Just click the upload button and now that letter is uploaded. Great. I could have put it into the end-of-year folder, but for now, I’m okay with it being in the default templates. All right. I’ve got one piece of that puzzle complete, which is the template, the Word document that’s going to house that information. I next need to produce the information.

I’m going to go to reports and listings. I’m going to choose export to file, leave it on main biodata. I’m going to choose a filter. I’m going to build a new filter where on the gift screen, the gift date is between 01/01/2015 and 12/31/2015. Now, because I’m once again excluding in-kind gifts from today’s illustration, I’m going to add more criteria to say that the gift amount is greater than zero and continue.

I’m going to exclude pledges and link gifts. I’m not going to put soft credits or notifications on that list because those gifts are not tax deductible. I could save the filter if I wanted giving it a name and sharing it across all users, but I’m not going to do that for today. I’m going to click done. I have my filter. If you recall, one of the most important things to get this letter out is going to be to check the box, include gift list in mail merge.

I now need to look at my gear to see what fields are going to be in that gift list. Feel free to change the order. I am suggesting here that a field that might have longer information, such as solicitation code should probably be at the end on the right, but completely up to you. This is something you might want to play with. Look at the different orientations and decide which position you want things in.

Notice that my gift list is exported as individual fields. I mentioned that a few times. Why am I saying that? Having this information as individual fields, which really means separate columns in that document, gives me a little more flexibility, a little more independence of the format and specifically the font size or font type. I prefer, and our best practice here at DonorPerfect Support will tell you to export it as individual fields. I have my gift date, I have the amount of the gift, the rest is up to you.

I have the reference field, which hopefully has my check number, and I might do the solicitation code just to show you all these fields from the gift screener here. Maybe the campaign is something you also want or want instead. The length of the fields here. Make sure that you don’t have a text field and have too few characters. Just make sure you have enough in the lengthier. If it’s a field that is a coded field such as solicit code or campaign, it’s probably a good idea to say yes to display the description because your donors might not know what the code means.

Then save those options. Last but not least, the export template that will actually put the information into an Excel spreadsheet. To be honest, it can be anything here. It can be the default mail merge. It can be something that’s used for mailings. I actually created one called End of Year which I’m going to edit and show you. If you added a new template, make sure that the type is main bio. Sorting by last name. I call the template End of Year.

Yes, it has the merge fields that you need to send a mailing out, but I also have these two fields related to calendar year totals. For any of you trying to do this or you’re going to send the letter out on 12/31 and you do not get any further donations on 12/31, then you can use the field calendar year to date. However, most non-profits will be getting a very large number of checks dated 12/31 that you physically won’t get into your office until January.

Therefore, I recommend you have this field called Last Year’s Calendar Year Giving. That is a full year of giving for the previous calendar year. This is what you’re going to use if you’re producing these letters in 2016. Something else that I did here for an export template. While it came up as code and description, I actually changed it to display the code because I don’t really have any coded fields here. For these fields, for instance, for my state, I don’t need Pennsylvania spelled out. I don’t need California spelled out. I just need the state abbreviation, which we know happens to be the code.

Now, in case any of you were wondering, “Arlene, you said you wanted the description of the solicitation field.” That’s absolutely correct, but that is not one of the fields here in my export template. That field is part of the feature called gift list. What this is asking about is how do I want these fields in my Excel spreadsheet. The only coded field happens to be the state field. I’m suggesting that you create an end-of-year main bio template.

I’m going to save and close. I’ve got all the ingredients I need to export to file. Now, while I could go through Instant Merge, I’m going to take you a little longer and slower path because we’re doing this for the very first time. I want to deliberately show you the data file. My donor ID, all of my name and address information. Now, this is what I’m talking about. Everybody see my gift list one, my gift list two, gift list three, gift list four.

Just in case you’re wondering, “Arlene, it only looks like one gift per donor.” Yes, it does, but what happens in this gift list feature? Let’s see if A&W Root Beer is going to cooperate. If you notice I’m in the formula bar up here. On the right, you have an up and a down arrow. Notice 02/17. I’m going to scroll down. Wow. That changes, 03/05, et cetera. Those two gifts belong to A&W. Let’s go into, I’m sure Mr. Sandstone who you’ll all recognize.

Mr. Roger Sandstone. Mr. Roger and his wife, Mary. I see January 9. There’s got to be more than that one gift from Roger. Notice how the date is changing over here? Roger is our best donor. His tax letter is probably going to run onto two pages. That’s the date field or gift date field, has all of those instances. Same can be said for the amount of gift. Notice how it’s changing?

This was done. It looks like it’s only one row per donor, but each of these fields have multiple rows within them. Don’t touch this. Just know that all the information is here. Here is the current calendar year total. It’s actually the sum of all the donations. Here is the last calendar year total. Now, while I’m in 2015, this last calendar year total is not what I want to use, but if you’re producing the letters in 2016, you will want last calendar year total per donor, which is 2015 calendar year total.

I’m actually going to save this file. I’m going to call this donmergeoy. I’m going to save it somewhere and I’m going to then show you how Instant Merge can be used. I’m going to save it on top of a previous file. I’m going to say yes, keep it in that CSV format. All right. Close out of it. While I did, in fact, download the file, you do not need to. You can go right to Instant Merge.

I should see on– Here we go. Letter End of Year Tax Merge, Multiple Gift Fields. This is the letter that I want. Now, while the data is here, let’s say I saved it somewhere. I can then upload a file. It wants to know where to get that file. I’m getting it from here. I’m going to click Open, and now I’m going to click Merge. I could have actually left it on the option that was use data from DonorPerfect.

However, I’m just showing you that should you put the file somewhere else, maybe you made a slight change to it. If you happen to delete a row, that’s fine. I can then upload a file.

Be careful not to delete any of the columns, but upload the file and then still use it in Instant Merge. Now, click merge in the bottom right. What I will get is one document down in the left here or on a Mac. You’ll have it in your downloads at the top.

The name of the file will have if you can see this, _merged.doc at the very end of the file name. I’m going to open this. Wow, wow, wow. Let me actually change the view. Probably need to expand this. There we go. A little bit better. We’ve got, let me make it– Mr. Smith has two gifts. Here’s the total. Make this even bigger for you Mr. Smith. Here’s Jack Jones. Now, in this case, no check numbers. Here’s the appeal which is the solicitation code Justin Ackland. Now this is in a word file so should I want to change the spacing should I want to personalize it for Adams McKinley because we have a very strong relationship with that company I can feel free to change this word document and insert something. This is really just about personalizing it but just want to show you this is all possible.

Isn’t that great? Every single gift is listed here. Thank you. Here’s my letter. Is it going to Arlene? I probably will pull this off so it’s not even printed. Don Draper. Wow. Here you go. That’s how you do your end of year tax letter. The only thing you need to do is just make sure that all your data entry has been completed. Get this letter finalized so that you’re not fixing 2014 into 2015. The nice thing is that with word I could do a find and replace. That’s always an option but hopefully you’ll catch it in the beginning. That’s your end of year letter.

I’m done. We’re not done with this webinar. In the next remaining 30 minutes the other part of this webinar is going to go over some financial reports. We’re also going to look at some maintenance or cleanup things that you can do. Now let’s go to the year end reports discussion. Here are some of the recommended reports.

I don’t think there’s anything that is required. The one on this list that I would make sure that you run is if you do have pledges and your accountant comes to you in March and wants to know what the outstanding pledges were as of the end of this, let’s say calendar and if it’s the same as your fiscal year, at any point that you need your what’s called accounts receivable or balances outstanding, you’re going to make sure that you run your pledge balances due report. Now you can run it on 12/31 or you may get a better picture if you run it on January four but you’ll want that one report as close to year end as possible.

These other reports can be run in December, they can be run in January. There’s really not much of a difference. I do want to make a point that this one report, pledge balances due is critical to run at the end of the month or as close to the beginning of the next month is possible. Here are some of the recommended reports. You may have your own favorites but some of the ones that you’ll want to see, donor recognition, gift listing, income analysis with gift splits, statistical tabulation and Comprehensive Donor Revenue Analysis report. I’m not going to tell you what each one of them do because it’s better to show you what they do.

Let’s start out with donor recognition report. Let me go put our hands down. How many of you have heard of or know that you have the need to produce something in an annual report or in a newsletter or possibly even placed on a wall or on a plaque something called a donor recognition list or a donor wall? Raise your hand if you have in the past or you know you have a need to produce that information for a donor wall.

Maybe it’s not going on a wall but somewhere printed. Great. Wow. We’ve got about 50% of you who need to do it. Maybe the term isn’t something you’re familiar with but when I show you the report you then might say oh yes, exactly, we do need that. How do we get that? We can get that very easily by going to Reports, Financial. Under column two I’m going to select a report called Donor Recognition. Now there really isn’t too much to this report. I would recommend that you pull either last year’s annual report or the newsletter whatever was done last year and find what we call splits or categories. One of the things you’re going to do is put the date range. 1/1 through I’ll just change it to 12/31/2015.

Once again this report probably could be run or should be run in 2016 after you’re done catching up with all of the 12/31 dated checks. After that’s done, great, the data entry has been in donor perfect. What are you going to do? You’re going to put these giving levels together. Now we do recommend that the lowest be here and the highest be here. These are purely examples. The dollar splits can be your own meaning your organizations, the names or captions can be your own but there’s probably some culture. If you’re in a Humane Society, there may be something related to pets. Maybe there’s something about either medals or jewels or president’s, secretary, treasurer, et cetera whatever those captions are, you can use them here.

Notice low going up and up the ladder. Notice also that here is the rounded number on the left, here is the number ending generally speaking in a 999. Now if that 25 is too low maybe I’m going to change it to 99,999.00. You could probably run some other reports to see the highest giving in the calendar year or in 2016 last calendar year because you don’t want to make sure to leave anybody off the list. Make sure you don’t cut this too low.

Include gift amounts in totals. You definitely want to do that the first time around or second et cetera. When it comes to the final report, you will uncheck include gift amounts and totals because you don’t want to publish the report telling how much someone gave specifically in the year. It can be implied or inferred but you’re not going to put the exact amount. Display option line, probably a good idea if you have households where the spouses or partners gave together. Without any selection filter, like I say I’m going to keep this as simple as possible. I then send the report to the screen. Wow Miranda congratulations. I see that you’re in our top group. Miranda and her fiancé Jason have given a very nice amount. Notice here I’m at 10,000 to 99,999.

Looks like I’m safe. I don’t think that there’s anybody but you never know. I would hate to ignore someone who happened to give 500,000. Speaking of that, what if someone inherited a large amount of money and gave to your organization but requested that they be listed anonymously? Then you would want to make sure that either gift or that donor is listed as anonymous. If you have a question about that support can help you. There’s also a knowledge based article. You could look that up about how to track anonymous donations. Here we see the information. This is beautiful everybody’s in the proper groups. Beautiful, beautiful, beautiful. Now let’s say it’s showtime and you want to use this information. What are you going to do?

You’re not going to include the gift amounts and totals but you probably would want to export this report to Excel. It’s going to have the data. It may not have the same headings but let’s actually export it to Excel so that if you needed to get this information to a graphical designer or do something else with it, I can then manipulate it or change the font in Excel or open it up in any other application, I presume. You could move your headings over. Right now it’s centered. You want to move it to the left, you want to change the font. Whatever you want to do, I can do easily in excel. Isn’t that beautiful? Imagine How long did it take to do this? I did a little bit of homework to set up these groups but once those groups are set up, it took a second or two. Now, something else that you may need to work with support is to the extent that you have gifts in here, maybe they are membership gifts.

Maybe your event tickets should not get included in these groupings. Sure. That’s all possible. I’m just trying to simplify this and include every single gift from your constituents. Please go over any individual cases that you have with support and they can work with you on selection filters or other ways of producing the data. That’s our donor recognition report. Great tool for year end. Our next report should you want to have for internal purposes, a gift listing. There also is a variation of it with soft credits.

For those of you who have gifts from United Way, should you track the gifts in United Way’s record and then make or create soft credits to the donors? This is one way of showing that. Here in fact, I see ABC company has $10 in soft credits. Keep in mind, in most situations, I won’t say 100% of the time, most situations though, soft credits is not something that you would put on a tax letter. This is possible to use, you would want to possibly use a selection filter and look for a particular donor or on the sidebar put in the donor ID.

Something else that our year end materials suggest is an annual statement. This is possible if you wanted to produce this information for one constituent, I would suggest either a filter or using the sidebar of entering that donor ID. In this case, 267 would be my filter or my sidebar filter. That’s your annual statement. Something else for your accountants perhaps or maybe a director, income analysis with gift splits. Did you ever want to know how are you doing this year versus last year?

This is a great report to do that. Now I can probably do it or would want to do it by general ledger code and I would have the ending date of 12/31/2015. Now, here I have more than one building fund. Ignore that, assuming that we had nice clean data just like you have. There’s a lot of information here so you’d want to print it out but I have the number of gifts in the current month. The most important thing to look at here, I’ll scroll back up so you can see the columns, would be year to date.

For the current year or the year ending that date here is the dollar amount. Here is the dollar amount last year, meaning 12/31/2014 percentage change. Then you also have statistics on the average gift size in the current year and last year. I have so many variations here on building fund, I’m actually going to run again this report, income analysis with gift splits by something that’s I’ll say cleaner called the donor type.

In the top left corner, generally speaking of your entry screens, you have a donor type field where we would say that a constituent is a company or it’s a foundation or it’s a hospital, individual or organization. This can tell me how we did this year over last. Now this however, doesn’t have a code. This means that I have constituents where that field is blank. We’ll get to that issue shortly but this would be helpful. For instance, if I have a major gifts officer for corporate donors, this would show me that we have a 52.6% decline in corporate giving.

I can click on anything here to drill down and look at the specific gifts. Great report. Once again, income analysis with gift splits where it looks at year over year performance. If I just want all the information for the year in one number, statistical tabulation, let’s say instead of donor type, I’m going to look at solicitation code. How did we do per solicitation code this year? The count of donors, the count of gifts, the average gift made and total per solicitation code. Anything that has a hyperlink here, I can drill down to look at the list of all of the donors and the gifts.

I can go even further to drill down on the gift transaction. Back and back to get me to the report back one more time. That is the statistical tabulation, put in your beginning date, your ending date and any field that is what we call drop-down or single coded. Two more reports to show you. One being I call this the report. The comprehensive donor revenue analysis is an eye-opening report. Why? It’s going to show you how your organization is doing at attracting new donors, at retaining donors at let’s say, how well you are doing in keeping them or how fast you are losing donors.

That’s called attrition. Comprehensive donor revenue analysis. That only took a couple seconds if that. This is amazing, the amount of information that is presented in this report. I could spend an hour on this but I do want to share with you and you can find this report explained to the knowledge base. If you hover over the first row underneath this bolded title it gives you a definition, count of all the donors for the year, number of donors who gave this year and last year, count of donors who gave their first gift this year, et cetera.

Phenomenal report. It says for this year, ending 12/31/2015, same day, a year ago, same day, the year before that. Here is the dollar amount and the percentage change. Feel free if you want to look at 2014 versus 2013, put the report in Excel and you can create the formulas and then copy those formulas down. Amazing report. Comprehensive Donor Revenue analysis. Last but not least, I mentioned the pledge balances due. That’s one report that I tend to pick from the category one of pledge reports, balances due.

Notice I cannot put a date range here. That’s why the report needs to be run on 12/31 or as close to the first day that you come back in the office because I cannot change the date. Here’s the report that has in this column, three pieces of information, the amount pledged, the amount paid or received and the balance outstanding. Likewise, two columns, three columns, et cetera. Feel free to use the sidebar. If I wanted to remove my recurring gifts from here, I would use the field called total and I would want to say where the total is $1 or more. Okay, these are all of your pledges that are outstanding in the database.

If you only want to see something for the capital campaign, either use a selection filter or use the sidebar. You might need to manage the fields to get a particular field to appear here on the sidebar so that, for instance, if you wanted campaign to appear here, you could do that by using managed fields. All right, those are some very valuable reports. Okay, last but not least, let’s go over some year end procedures. Those of you attending this webinar here in December, have the luxury perhaps of a few days to catch a breath before January comes. I don’t know, maybe if this is very, very busy, this is something that you’ll want to snooze on until the springtime or some other time when it’s not so hectic. Being busy is a good thing, obviously.

Okay, so we have something called lapsed or inactive donor maintenance, code maintenance, and then something else where you might have some unnecessary or duplicate custom reports out there. Maybe you’ve got many versions of save filters that are out there that need to be removed, okay? All possible to be cleaned up. Whenever your slow time of year is, you can do some of these things. Let’s first talk about lapsed or inactive donor maintenance.

The first thing you need to do is have a discussion. If there’s anybody else in your office in the development department who can shed some light into what is a lapsed or inactive donor. We’re suggesting that it’s a donor where the last contribution date is less than let’s say January 1st, 2007. That’s completely up to you. If you want to say it’s a five year, it’s a seven year, it’s a 10 year period, completely up to you. Do you want to hold on to donors who have not given anything in 20 years, perhaps? Most likely not. What do you want to do? Well, probably not a bad idea to send a targeted solicitation either by snail mail or by email if you have their email address and using constant contact just to see if we can revive them, wake them up one last time and get them reengaged.

If you get a very low return rate, or maybe you don’t have the luxury of time of doing that and you just want to clean house, what are you going to do? Well, you’re going to globally update two fields. One of them is going to be the do not send mail field, and you’re going to fill it in with a why for yes, which translates into or means a checkbox in the Do Not Mail field. Along with that, we suggest that you create a code of inactive in the field called No Mail Reason

You’re going to fill in the donors who meet whatever filter you decide on, you’re going to fill in the no mail reason field with the value or specifically the code that you’ll create for inactive. Now you can do that. At a minimum you probably say, listen, we’re just not going to send mail to them. There’s no point. They’re obviously not interested, as a last resort. This is something you should proceed with. Caution is to delete them from your database. Number one, it’s really a two part scenario. One is if I’ve got constituents and I would actually use a different filter where the creation date is less than December 1st, 2010. I’m throwing that out, okay? The number of gifts equal zero.

That means people who I have not gotten a gift from, they’ve been in my database for five plus years, I’m not getting anything from them whatsoever. Let’s possibly thin out the forest. You could delete them. That’s what we call non-donors, where the date created is less than a certain date and the number of gifts equal zero. Or the lapse donors they gave in the past, maybe they didn’t give much of a gift and they are going to be deleted.

That gets back to this scenario. The last contribution date is less than 1231 2007. You might say end last contribution amount less than $100. You might want to actually do a more personalized appeal. You may call or you may send a specific individualized email. If a donor gave a large gift, maybe a thousand dollars gift, but they haven’t done anything in seven years, maybe they don’t get the same solicitation that everybody else would. Those are a couple things. Then last but not least, they can be deleted in somewhere in Donor Perfect, under Utilities Global update, under the delete feature, you would check a filter, but I’m not going to go any further.

I’m going to recommend if not urge you to do that with the help of support, also making sure to make a backup before you globally update. Now, if any of you were concerned, and maybe I’m going to head off some questions here, if any of you want to know, well, can I export their information before I delete it? Oh, most definitely. From reports and listings, you would use the filter of last contribution date, less than a certain date. Then going to export to file. Probably export out the default template from Main Bio, and I would also export out all gifts from 1/1.

I’m going to go back in time to 1900 and export out the default financial template. These might be large Excel spreadsheets, but better safe than sorry if somebody says what was the information or what was the address? Or should you want to re-import them? Another discussion. Just letting that it is possible to what’s called archive, which is really just creating CSV files of the gift pledge and the main biodata of constituents or donors before you actually delete them. Assuming I’m keeping Miranda very busy. All right. Code maintenance.

I don’t have a slide, but let me actually go there. Under the settings, code maintenance, this is a place where you want to go. Specifically the field to start would be solicitation code. It’s probably not a bad idea to look at all of your fields, but solicitation code, click go. I’m going to actually sort these to show all the ones that have a green check. I come here and I look at anything that has a green check that might be for something that happened in 2014, and hopefully if we’ve got a relatively clean database, that won’t be the case.

We do have a super clean database as far as the solicitation code goes, but in your office, if you had annual appeal for 2014 and 2013, if they have a green check, simply come here and make the green check into a red circle. What that will do is eliminate that code or option showing in the dropdown when you go to enter a gift or pledge. However, you can always run reports on in active codes regardless of the field. I can always run reports for data that has an inactive code, so no worries there.

Okay, and last but not least, about the– I’ll say save filters that might be redundant or unnecessary. Under settings, you have filters. I can actually look folder by folder. If one filter was named a little different, but it actually does the same thing. Either delete it with the trash can, maybe edit the name. If this is not just Pennsylvania, but if it were Pennsylvania and New Jersey, then I want to fix the name. In your spare time, feel free to do that. Probably in your default, the junk drawer. Wow. I’ve got four versions of what appeared to be the same exact filter.

Good things or bad things happen to us. If I delete this, now this happens because I’ve got multiple staff working in the same database. You probably do as well, but this is a good time to lock down and clean up the address, clean up filters. I simply checked all three. Click on the button that says delete selected filters. Excellent. I’m left with one. Isn’t that wonderful? All right. This requires some time, but it’s really not too onerous a task at just going through here and looking to see. If there’s no reason to use a filter for the annual appeal of 2013, then delete that. All right. That’s what that was about.

[silence]

All right. Code maintenance. Another tool that’s helpful is, if you’re not sure what’s being used, so let’s say you’ve got gazillions of flags, I think I can do it in the flags field. If I have lots of options in the donor type, a very helpful report to use is actually found in listings. It’s called the counter report. Let me go over there and show you what kind of report. Now, this is something that happens to be in listings. It’s more of an analytical tool. It’s not really going to tell you who are the donors, who have something so that you can mail it to them. The counter-report, I tell it what screen has the particular field, and let’s go to donor type.

[silence]

All right. I’m looking for my donor-type field just in case somebody changed it around on me, no. Well, maybe somebody changed the name here. No. Here we go. Donor type right in front of my eyes. This allows me to analyze any, I think, single-valued field and see how often a code was used. Send it to the screen. Wow. What’s this telling me? If I were looking at the chats, I would be waiting for an answer.

This tells me, “Ow, Arlene, we’ve got a little bit of a problem.”

If I have someone in my financial office who wants to run a report for revenue by donor type, what percentage of my money came from donors, from individuals? What percentage came from companies? What percentage came from foundations? I do not want to see a field or do not want to see the donor type field specifically where the type is blank. Ouch. I can either click on the AB company and fix it right here on the spot. Let’s say it is in fact A company and save it. Or if that list or number was pretty long, I probably would print it out. Now if you’re wondering, well, it was fixed, why is it on here? Let me refresh the screen and it came off. Print out the report and pass it around and just write down what it should be.

Then you can look them up by their donor ID number. That’s a helpful way to clean up data. All right. We talked about filters. Then, so now with about a minute or two spare. Now, what are we going to remember? Number one, that the fiscal year is set in parameters. You do not need to touch it, but should you ever have a situation where you need to change it or you simply want to check it, it is found in the settings, in parameters, general parameters.

The year-end task that we looked at today, the year-end tax letters, you may have donors who have a particular filter, or some value on their main screen for the donors who want that, or you’re just going to send it to all donors who have made a gift in 2015. However, you choose to do that internally is perfectly fine. We had some financial reports that we recommend that you run. If the time right now is relatively quiet, I understand on the 30th, 31st, it’s going to be busy, et cetera, but if you have some time for some database cleanup or at any other time in the year, if any of those things that I mentioned you want to come back to in the springtime or some other lull maybe in the summer, feel free to use those database cleanup tools that I suggested.

All of you got this morning in December an e-Tip on some year-end procedures and tips and the checklist, et cetera. Either feel free to look at that e-Tip, if you didn’t get it. Then in the knowledge base, there’ll be articles on it. Look up the term year-end or end of year, and you’ll be taken to the knowledge base article with lots of pertinent information. All right. I want to thank you everyone for your time today. I hope that you came away with some valuable tips, some knowledge, some awareness, some words to use as you communicate with support, look for a follow-up email that will have links to the knowledge-base article.

Should anything that I went over with you today appeal to you and you need more assistance, please reach out to support. I would suggest phone or email. Chat is probably going to direct you to calling us for some specific assistance. Thank you so much. I hope that this has been a very successful year of fundraising for all of you. I wish you a very happy and joyous and safe holiday season. Thank you. It was a pleasure working with you for today. Take care. Once again, this is Arlene signing off. Thank you so much, Miranda.