End of Year: Sending a Tax Letter

Hello and welcome to our pre-recorded webinar about our US, End of Year tax letter preparation procedures. This is specifically for our US clients. If you are Canadian, please refer to the separate recording with those separate procedures. And if you are in the US, but looking for the steps Read More

Hello and welcome to our pre-recorded webinar about our US, End of Year tax letter preparation procedures. This is specifically for our US clients. If you are Canadian, please refer to the separate recording with those separate procedures. And if you are in the US, but looking for the steps for an end of year email instead, there is a separate recording for that very similar but slightly different procedures. Today’s process is going to require that you have Microsoft Word installed to edit the letter, and I’ll be going over all of that today. Hello. My name is Sean batter. I’m a training specialist here at DonorPerfect, often working one on one with clients. Learn more about DonorPerfect. I do get to do these fun recordings every now and then where this is a very helpful topic. Ever recording on everybody is essentially going to be going through a very similar process, and it doesn’t really change from one year to the next. That’s my first caveat about this recording that I’ll be mentioning again is I’m recording this in October of 2024 so as I go through this demonstration, I’ll be doing it for 2023 last year. No matter what year in the future you’re watching this recording, you’ll always be looking at last year’s gifts to be included in this end of year letter. And before we even look at the letter. It is important to understand the basics of data entry, beyond just the gift amount. If you have events with a sponsorship where maybe it’s only partially tax deductible, if you’re taking in kind donations of goods and services, there are proper ways that we should be entering these and this is so important, because those amounts that we’re doing during the data entry process then need to be reflected accurately in our year end calculated fields. There are fields called calculated fields. They do a little bit of math for us. And for the end of your letter, what we’re going to need to have for that letter is one field that has a calculated total of what last year’s receipt amounts were. What can they claim accurately to the IRS for those donations? Now you may or may not have these. I’ll show you how you can find out if you do, if you don’t, the support department can assist you in their creation. From there, we can look at the mail merge templates. Again. It is just a it’s a Microsoft Word document. You are going to need to have Microsoft Word on your computer, even if it’s Microsoft Word, 1998 it’ll work. And then couple couple good ideas to consider before actually merging it create a backup of your database, something that occurs when you are maybe doing data cleanup, but if you’ve never created a backup of your database before, right before the end of your tax letters. Good time, a good time to do it, because this could also coincide with merging of duplicate records. Both of these things not going to be covered today. We do have separate material on both of these if all right now let’s, let’s dive right on in. Let’s dive right on in. I have my database open over here. This database is familiar to you, but when we look within a constituent profile, since all the fields are customizable, you can make your own sections. The view that we’re going to be seeing is maybe just a little bit different from what you’re used to seeing, but I’m going to go to Quick Search, and I have a constituent in mind that gave last year that we could use as an example. And since we’re talking about letters today, I picked a good example, because Anthony and Charlene here have an address, so I could, I could send them a letter if it was just an email address. That’s a slightly different set of steps. Really. What I’m focusing on right now is what was entered on the gift screen. Again, I’m recording this in October of 2024 so this end of year letter is going to be including gifts from last year 2023 and I think all these fields are important for data entry. All. Drop down menus. But let’s look at one of these, just regular donations from 2023 and highlight the currency fields, gift amount. This is where the amount of the transaction goes. In this case, it is just a donation. The gift amount is how much the person was charged how much they can claim. Fair market value is another currency field, but not applicable for a regular donation. Fair market value is where we would record the monetary value that they get in return, or perhaps the value of goods or services donated. It’s not going to be needed for a regular cash, credit, bank account, wire transfer, whatever it may be. And we have our third currency field here receipt amounts, which is showing accurately the receipt amount of $375 that’s what they can claim on their taxes, and it’s one in the same as the gift amount in this case. But some of you may be taking different donations besides monetary donations, say, if you are taking in kind donations, say you are a shelter, maybe an animal shelter. That’s what I use in a lot of my examples. You might take in kind donations. These would be things that aren’t exactly money, so they won’t have a gift amount, because there is no cash exchanging hands or coming out of any bank accounts, there is just a fair market value describing what the value of this good or service is. Maybe it’s physical items donated to that animal shelter. It could be labor too. Perhaps they’re an electrician or an accountant giving you their valuable services. We could record that as a donation as well the receipt amount, though, still going to show the value of what their donation was. Suddenly, this receipt amount field becomes very valuable, and for any of you that are recording with fair market value, and we’ll look at another example here, in a moment, we need to make sure that this receipt amount field is in there and calculating for you accurately. Let’s look at one more example, say, from an event. Let’s find us an event transaction here in 2023 this is a good one. So last year there was an event, and if I edit this, we can see that they bought some tickets and the gift amount, the amount of that transaction, was $165 but for this golf tournament, they got a little something in return. They got t shirts. They got a expensive meal that was valued at $75 which then gives us a receipt amount of $90 we have to subtract that 75 from the gift amount. And this is happening automatically, or at least it should be you.

Now, with all of that said, this is, this is our first step, making sure that we are entering in the amounts correctly, that receipt amount field, then when fair market value is in play, should be giving us the correct amount that they can claim on their taxes, still within the same example constituent profile, I want to point out a couple fields that I have in my giving and engagement profile section. And again, since all of our databases are customizable, your fields might be in different places, but everybody has a field called last year, Cy, TD, and what this field is doing is it’s adding up all of the gift amounts from last calendar year to date. So if you are an organization that is not using fair market value as part of your data entry. You just get donations. You don’t take in kind donations. You don’t have events where they’re getting a monetary value out of it. If that’s the case this field, then for you would be a representation of what they can claim on their taxes. For everybody else where fair market value is at play, this field is a bit inaccurate. It’s actually going to need to be the sum of not gift amount, but instead of receipt amounts. And while everybody has a last year cytd, it. Is possible that you might not have what I have here, last cytd receipt total. This might be called something slightly different. It might have a slightly different naming convention than what I have here, but in function, what it’s doing is it’s adding up the receipt amounts, what they can claim on their taxes, not the gift amounts, because we had that in kind donation. There was the ticket purchase. Anthony and spouse here cannot go to the IRS and say, This is what they donated to that organization. It’s actually this amount down here, and they’ll have to make sure that you have these calculations. These are our calculated fields, and they can be found in settings the gear icon at the top right. And then from there go to calculated fields. I’m going to hop in there in just a moment and show you that there are four of these calculated fields that are making all the magic happen. Three of them are controlling receipt amount on the gift screen, and then another one is adding up all of those receipt amounts onto the main screen, where fair market value is a part of your data entry for recording donations amongst your constituents. This is going to be a necessity. Many of you may have this, but just in case you don’t, this is a this is an instance where the support department will create these for you. If they’re not in your database already, they will help you out. I’ll show you where we can find them. And just by example, at least in this instance, I know that this system has it. I have that field here for adding up the receipt amounts and on the gift screen, uh, receipt amount is doing exactly what I wanted to do be a reflection of the donation amount, or, uh, perhaps a subtraction of a fair market value from a gift amount. So I’ve already seen it working. But that is because when I go to settings and calculated fields, there is a mechanism. We call it a calculation going on behind the scenes that drive those numbers. And once I go here, there might be many. There are different calculations. The ones we care about are receipts. I’m going to do a search for the word receipt, and I can see those four calculations. There’s a column that says field to update three of them should be RC, PT, underscore, amount. And then there should be a fourth one in here that’s for the cumulative total in my system. I have it called last cytd receipt total yours might be something slightly different. It might even have a slightly different field name, but it has to be in here, and if you get to this point, you can’t find those calculations, or you scroll through the list, look to see if it maybe has a slightly different name. Pause this video, reach out to the support department and request these calculated fields be created for you for that year end tax letter. And I’m actually going to leave this open here, uh, because, uh, this, this field last cytd receipt total, that field that we saw here, and for me, I have it down here at the bottom. I want to use this field in the Word document, the letter that I’m going to use for the end of year tax letter, again this end of year tax letter is a Word document. There is a sample that you can download as a starting point. Uh, once that document is downloaded, we have two options for editing it. It’s going to be a generic, uh, message for a non existent, non profit. You’ll have to either edit around the merge fields. Everything with these little greater than less than symbols is a merge field. It will fill in with the constituents information. You could leave those in the Word document and remove the paragraphs, change your message, put in your name and your branding. Option number two is to take the merge field and just copy and paste, not to over complicate it. It is a Word document, and just the nature of it being a Word document, adjusting the margins and what have you, is probably going to be the most challenging part of this. But if you’re familiar with word this will be fairly straight forward. Forward, and I’m going to leave my example constituent open over here. It always boils down to data entry at some point, so I’ll keep him open as an example and our calculated fields, like I said, this total last year, last Cytd receipt total. I’m going to need to put that in this letter. So I’m going to leave my Calculated Fields open as well. I’m going to go to settings, and I’m going to go to mail merge templates, settings, the gear icon and the blue ribbon at the top right, and then mail merge templates, this is where all of our Word documents are stored. And if you’ve never been in here, there still might be some sample templates that are in here for you. This database that I’m using, we’ve had it for like, eight years, so we have many Word documents that are in here already. Just going to ignore the existing ones and go right down to the bottom right where we have a link that says, looking for pre made templates, we got you covered in the knowledge base. And this one I’m definitely going to open in a new tab, otherwise it redirects us out of DonorPerfect. Scroll down and let’s find our letter, we have many different word documents for different purposes, but if you scroll down in the description, it’s the only one with bolded text in it, recommended for end of year letters. End of Year letter with multiple gift list fields, that’s what we’ll need. I’ll click on the name of this file, and it’ll give me a little preview of what it’s going to look like. And if you stop moving your mouse around for a moment, the option to download disappears. Just move your mouse around again, and there’s my download. I will click on it, and now it’s downloaded onto my computer. From here, I can open it up and we can get to work customizing this. Now, for the sake of example, I’m not going to spend the time and effort that I know all of you will to get your branding and your message in there. I’m just going to focus on the functional merge fields and aspects of this.

Let’s zoom in here. We have today’s date. We have our address block. Word of warning about the address block, it is going to take up six rows once it merges, it’ll shrink itself down to three rows or possibly four if we have a spouse with a different last name, but it’ll always work, just leave it as is. We have our salutation in there, how we are addressing the constituent, and then we have Microsoft words, closest approximation to a to a list, to a grid, to a chart. We have gift list, 1234, and five, and each of these gift lists are going to end up being a list, but the fields that go into these lists are of our choice, date and Gift Amount. Certainly we would want, and it’s a minor detail to the constituent. They might not have a distinction between Gift Amount and receipt amount, but I do. I do because there’s fair market value in here. I’m just going to call it receipt amount instead, and at the very least, we should have date and whatever the amount is. Additionally to that, we could include up to three other fields, so five total, always. And in our example, we also have general ledger code, the type of gift and the check reference number, which I think is all well and good. The one change I might make, though, is GL code. Let’s call that fund. GL Code is a little too inside industry terminology. Fund would be a little bit more recognizable. And then we have a total because a Word document doesn’t have a way to add up our receipt amount column, unfortunately, it just doesn’t have that ability. So we need a separate field that is adding up the amounts from the previous year, and that’s what we have in here. We have a calculated field, l, y, underscore, C, y, t, d. What that is is that’s this field here last. Year. Cytd, our sample template has this field in it, but this field is only okay to use if you’re if you don’t have fair market value involved in any of your gifts from the prior year. So if you, if that happens to be you, and you are good to go, if you, if you are not using as a nonprofit fair market value and just the gift amount that they can claim rock on last year, cytd is ready to go for you, for everybody else, we’re going to need to replace that field with with a different one, with a different one. Going back over to settings and calculated fields, I still have that calculation open last C, y, t, d, receive total again. If you don’t have this, ask our friends in the support department, they’ll create this for you, although to create the calculations for you. And what I’m going to do is I’m going to copy this field’s name. This field name is the true name of that calculated field. And for my Word document, if I want to replace this one, I’ll need to know that field name. So here’s what I’ll do. I will right click on it, and I am going to say, edit field where it has field name in this pop up. This is the field name of the other calculated field that’s adding up gift amounts, I will replace it with my calculated field that is adding up receipt amounts. And I’ll say, okay, and then I’ll right click and select Update Field. And there we have it again. Not a great looking letter. I know yours will look better than this one from here. I’m going to save it. Let’s put it somewhere where I can find it easily.

And that is our 2023 year end tax letter. I’ll call it again. I’m doing this in October of 2024 so I’m performing this for 2023 whatever year you’re doing it, it’ll be the same steps. Okay? And now I have a Word document. I can go back into settings and a mail merge templates, and at the bottom right, I have an option to upload new from here I will browse my computer and let me find my Word document. Here we go, 2023, year end tax.

And then we can hit Upload, perfect. So now the word document is in here. It’s uploaded. It is ready to use, and now we can merge it, and the merge for the year end tax letter is a little different. If you’ve ever merged anything out of DonorPerfect, this one is merged from a report. There’s a couple different places we could do this, but I would point you towards this report called export to file. Oh, this is our Swiss Army knife of reports. It can pull information out of anywhere, but it can also merge letters for us. So we’re going to go to this very specific report in the Report Center. From there, we’re going to need a selection filter anytime in DonorPerfect, where you have to segment your results. In this case, I want to segment by last year’s donors. A selection filter is going to be needed, and we’ll make sure it excludes the non gifts as well. We’re going to want to include no mail names. There is a check box that you could check off called do not send mail. Exclude them from then excludes them from solicitations. We want to include those people in this we’re not soliciting anybody. We’re just fulfilling an obligation, giving them cumulative total for what they donated last year. And then, because you get to decide what’s going to go into the gift lists, those five fields, those five columns, can be whatever fields you want them to be, at the very least, date of gift and, you know, receipt amount or gift amount in there. But there is an option on here where you actually select what field it’s going to be. Then we’ll have to consider something called an export template, if you have ever done a micro. Soft Mail Merge outside of DonorPerfect, then you know that you can take an Excel file full of names and addresses and information, and you can take a Word document, merge them together to get your mailing list of merge letters. That’s exactly what DonorPerfect is doing on the back end. We’re very aware of the Microsoft Word portion of it, but there is an Excel file that happens here. We don’t necessarily see it, but the fields that are going to be used for this mail merge do need to be considered from there. We’ll select our Word document, merge, double check our results and call it a day.

Let’s head back into our database now, where, as I said earlier, is best practice to at least once a year, create a backup of your database. This would also be something that you should be doing before merging duplicates, importing information, performing global updates or merging duplicates. I’m going to create a backup of my database now, and I’ll say, okay, and now I have my backup. Ideally, you’ll also be going through utilities and merge duplicates. We do have additional information on this process if you’re not familiar, but it’s not the subject of today, but would be a best practice each constituent we want, getting just one letter or one email, and merging duplicate profiles together will help with that, but not the focus of today. Let’s move on to reports and Report Center. From here, I will go to the listing folder on the left hand side, which is where we will find our export to file report. This is a very versatile report. You can do many different things, but for the export type, we want to leave it on Main bio data for me, Selection Filter says no. Filter selected if I had been running reports say, before I did this, a selection filter might have followed me over. If you’re not seeing the option to apply, there’s probably a filter there a segment of the database, there’ll be a blue X next to it that you can click to remove it, so that you’ll get the option to apply a new one. And then in the pop up that we see, we’ll have all of our selection filters different segments of your database. And here I’m going to add a new filter. Now, whenever we’re building these filters, I think of them as instructions for results. And for these instructions, we could use any field that has information in it, any field. And there’s over 750 fields for this purpose, for the end of your tax letter, I only care about two I care about whether or not the constituent has an address that’s going to be one instruction address is not blank, and then my other instruction is going to be a date range, date of gift. I want that date of gift to be sometime last year. Don’t let these filters intimidate you. Always works left to right. Four steps. Step one, I’ll pick where my field of interest lives. Now it’s between date of gift and an address. I could do this in any order. Let’s start off with gift pledge. And then in box two, I’ll select date of gift. And in box three, I’ll select between I want it to be between the beginning and the end of last year. And then in box five, I’ll click on add more criteria, and it takes what I selected and puts it into SQL code at the bottom. And then I can repeat the process again for my second instruction, my second step, my second criteria, which is going to be address is not blank. Step one, pick where my field lives, and then I can select address in box, two likely it’s not going to be showing up. These are my favorite fields. I used this Manage favorite Selection Filter option to customize this list of fields. Most likely you’ll have to switch from favorite fields to all fields. And then it’ll show you all the fields on the main and bio screen that we could use for our selection filter, for our instructions, for our rules, for the output. I don’t care about all these. I just want to find address. I want it to not be blank. Very last option in box three is not blank. And then continue. Those are my two criteria. They have an address and they have a gift in this date range.

Call this our 2023, year end tax filter. I will share it across all users with this check box. And then I’m also going to check off the box to exclude pledges and linked gifts. So exclude the non gifts. We don’t want the pledge. A pledge is just a placeholder for the gifts. The gifts are still going to be included here. And then I’ll say done, and then I’m going to check off include no mail name. And again, the reason I’m doing that is because I don’t think this profile has it, but there is a field called do not send mail again, you were you have this field. It might be located somewhere else. If, if they expressed disinterest in being a part of solicitations, we could check that off. We could also check that off. If somebody was deceased, we should still be sending the family a tax letter, even if that was the case. But regardless of what their mailing preference is, we want to include them. We want to include those no mail names. And then we can skip over these other check boxes and go right to include GIF list in mail merge. GIF list that is specifically referencing our five columns, our five gift lists, 1234, and five. And now mine, I have date of gift receipt, amount, fund, type of gift and check reference number. So what I’m going to do is I’m going to click on the gear icon, and here I have five drop downs for my five fields. Gift list one is gift date. Gift list two is receipt amount. Gift list three general ledger, code GL, underscore code. Gift list four is type of gift, also known as gift underscore, type. Gift list five is our check reference number and for our drop downs of general ledger or fund and type of gift, we have display description set up. So these are the five fields in my five gift lists. I have verified that, and then I can click on Save. And here’s my instant merge button. But there’s one last thing that I have to consider. I have to consider what fields are included in this Word document. As I said earlier, that this is performing a Microsoft Office Mail Merge on the back end, here’s our Word document. I have to come up with a list of fields that are on this Word document. We call it an export template, and I’ll show you how to make one. Here’s our drop down for export template at the bottom could be from a previous year. Maybe you had a another user create one for you already for me, I’m going to click on add new template at the top left, I’m going to give it a name. This is our end of year template. Actually shorten it to EOY, and then we have our selected fields on the right hand side, all of the fields that are going to be in the mail merge, and when we create a new one, it does start us off with most of the required ones, name, address, email and phone numbers in here are not going to be used, but it doesn’t hurt to have it the the one field that is Missing, though, from this list is going to be our calculated fields. Whether it’s the receipt amount, total or last calendar year to date, we’re going to need to include those fields as well. Imperative for this whichever one. Using we’re going to need to have that field included, besides name and address and all that good stuff. And on the left hand side, we have all available fields. Let us find them. There is a search box. And here we go, last year Cytd. That would be for the folks that aren’t using fair market value, which isn’t me. I’m going to be using last Cytd receipt total check off the field, right blue arrow to add it, and there we go. There’s my selected fields. So when I do the instant merge with the Word document, it will know that these fields are to be included, besides name and address, and then save and close. And then might be a good idea to preview beforehand. Spot check, make sure that your list looks appropriate. A preview will show you the results before you actually create them. But let’s say I’m going to click on instant merge. Here is my 2023, year end tax letter. I will select it and then merge.

Now it taking a few moments, is to be expected. This is going to be one of those things that DonorPerfect might take a minute or so to do. Think there’s a few 100 on this list, but if you have several 1000 or 10s of 1000s, know that this might take 510 minutes. It’s doing a lot of work on the back end, but it’s finally done. I can go to my downloads, and I can open it up. And again, I just advise a little bit of patience. It might be thinking about it for a little bit. The bigger the results, the longer this is going to take. And it even takes a little bit for the document itself to load. So this is one document with however many pages for last year’s constituents, and if you can see it at the lower left the page count, we can see it’s loading page one of 200 of 300 give it a moment. It takes a little bit to open itself up.

Okay. And here we have it. We have our address block, we have our salutation, we have our amounts, and they’re lining up perfectly with our total and our one example. Let’s find our example from earlier. Let me find Anthony here. Here we go, Anthony and Charlene. Charlene and Anthony have different last names, one of the instances where we’re going to have four different rows in the address block instead of three. But it did shrink itself down. If you remember that address block. It looks big. It’s six rows. It has to stay as is. It will work, but it takes up more room in our template, and we can see that it’s adding up correctly. It’s using the correct amount, because I have the correct calculated field and my my data entry is on track. Now we are also seeing that it would probably be a good idea for me to go back to the template and expand these columns a little bit. It will they will run into the next row if it’s a very long string of characters. I probably should have considered this when I was grading the template, but that’s okay. We can always fix it after the fact and then spot check before, before we print this out now, as long as you have those Calculated Fields, these should be adding up appropriately, but we’re all human. We make data entry mistakes. Sometimes it happens. Something else to consider here too is that my selection filter only has address is not blank, and that will be true for everybody in the results. They will have an address, but what if they were missing a city or a state or a zip code, they could still slip through? So I do take a few moments to spot check these before you print them off and. Send them out to your constituents, and that is the year end tax letter procedure. It has remained essentially the same for many years, and it will stay that way. We do have a different recording for us end of your year emails, which I would encourage you to check out. Lot of it will be the same as far as data entry and calculated fields, but the emails you don’t need to have Microsoft Word for all that’s done directly within doing a perfect so thank you for watching. I’ve been Sean Potero, you have been a great audience. Thank you. Take care.