How to subtract write off pledge amount from donor category report

Naila Asif 4

Hi, I need to deduct the wirte off pledge amounts from donor category report, e.g. if the original pledge was for $200,000, donor has paid $50,000 but later $150,000 has been written off. The donor category report still shows $200,000, I need to see $50,000 for this pledge amount and not the original amount. Is there any automated way to do that?

Thanks,

Naila

Joe Moretti 3

@Naila Asif I am pretty certain, you will not be able to do that with this report or some others. That is somewhat the issue with writting off pledges or part of them, the report do not calucalate that. Unless someone else knows something I don't. When you look at the record of someone who has part of a pledge written off, the amount in the record still has the full pledge amount, you will only see that write off in the installment/payments of the gift record.

Christine Robertson 4

@Naila Asif Are you using the check box for pledge balance rather than pledge amount? If you do that plus the payments, I think that would work for what you're describing.

Naila Asif 4

@Christine Robertson, No I know about this option, I am just pulling the commitments plus one time gifts, not including any pledge payments. The problem is that it gives the original pledge amount and not takes care of the write off, so donor is showing at a certain category but actually he belongs to a lower category. I have asked BB customer services and there is no solution, unless I run the write off report, them manually subtract the write off amount and then move the donor to correct category. It's a lot of manual work. What's the benefit of this report for my organization?

Naila Asif 4

@Joe Moretti, thank you and this is what I am now certain. I am thinking of creating a constituent query and then export the total committed amount based on gift types and get the write off pledge amount in a separate column, analyze the data in excel and then categorize it. You think that will work?

Christine Robertson 4

@Naila Asif If you were to include the option I referenced plus the payments, this function would show the full pledge amount for any paid pledge as well as any open pledge. Any amount that has been written off will not be reflected in the report.

Naila Asif 4

@Christine Robertson, My donor category report does not include pay-cash gift type, when I try this option that you mentioned with gift type pay-case, I do get the amount but for the commitment report for a fiscal year, I do not want to include the payments made for pledges in previous fiscal years

Joe Moretti 3

@Naila Asif That should work if you have a column of the full pledge (Pledge type) and another column of the Write-off and then subtract the two.

Alex Wong

@Christine Robertson I think the issue with Naila request has to do with timing of the various gift type. It sounds to me like Naila wants the original pledge amount be counted in the year the pledge was made, but LESS the amount of write off.

Imagine: Pledge $100K (2022), paid $20K (2023), write off $30K (2024), balance $50K

So what I don't know is, the report calculation of pledge balance, will it take into consideration of pledge balance of the pledge within the time interval selected.

@Naila Asif

Not sure if I understood you correctly above, but in the example, do you want the donor showing he gave $70K ($100K original pledge - $30K write off) in 2022 and nothing in 2023 and 2024?

If so, Donor category report in RE database canned report is not going to help you.

Use a donor query, and use Summary of Giving, then select the gift date (the year you doing donor category) and gift type that is consider committed revenue (i.e. Cash, Pledge, Stock, etc, do not select pay-*** or write off). Summary of Giving will automatically remove write off from the pledged amount as part of it's calculation. Then in excel, you will have to do donor category using excel formula or pivot

Naila Asif 4

@Alex Wong, exactly, I want the donor showing that he committed $70K ($100K original pledge - $30K write off) in 2022 and nothing in 2023 and 2024?

I will try to export the donor query as per your suggestion and see if it works. I will let you know.

Naila Asif 4

@Alex Wong, it did work but I have to do a lot of manual work, see below what I got for one fiscal year.

Pledge committed 2020-21	Write off from 2020-21	Write off from 2021-22	Write off from 2022-23	Write off from 2023-24	Total committed for 2020-21
$26,200.00	$0.00	$0.00	$0.00	$0.00	$26,200.00
$15,000.00	$0.00	$0.00	$15,000.00	$0.00	$15,000.00
$2,400.00	$0.00	$0.00	$0.00	$0.00	$2,950.00
$450.00	$0.00	$0.00	$0.00	$0.00	$450.00
$2,000.00	$0.00	$0.00	$0.00	$0.00	$2,000.00
$3,000.00	$0.00	$0.00	$0.00	$0.00	$3,000.00
$200,000.00	$0.00	$150,000.00	$0.00	$0.00	$200,000.00
$650,000.00	$0.00	$0.00	$0.00	$0.00	$650,250.00
$12,000.00	$0.00	$0.00	$0.00	$0.00	$19,000.00
$600.00	$0.00	$0.00	$0.00	$0.00	$600.00

After I filtered the pledges, I found 2 donors whom pledges were written off after 2020-21, I subtracted the written off amount from total committed column and got the correct total.

Thanks for your help!

Naila

Alex Wong

@Naila Asif
did you have a different report here that you are trying to do?

Reading your OP, it only said you want to do a donor category report but with write off removed. (going back to my example, you want to categorize the donor as $70K giver in 2022 and $0 in 2023 and 2024). If that's the case, there should be no reason why you need a “full table” of how much write off per year like you posted.

Are you trying to do a detail pledge report instead?

Naila Asif 4

@Alex Wong, it is complicated in my organization as there could be multiple pledges on one record, so I did it the write off for every year to find out if only 2020-21 pledge is written off. When I exported one column for write off, I was getting some write offs for pledges previous or later than 2020-21. That was the reason for exporting separate columns. Does it make sense?

Alex Wong

@Naila Asif
For the purpose of what you trying to do, Donor Category, it shouldn't matter. Again, really depends on what kind of result you trying to get.

If you are trying to get result of “overall” number of donor and amount given by category:

2022 Donor Category	# of Donor	Amount by Donor
Under $1K	1000	$100,000
$1K+ Up to $10K	100	$130,000
$10K+ Up to $100K	10	$150,000
$100K+ Up to $1M	5	$200,000
$1M+	1	$1,500,000

You don't need “write off” amount per year, you just need how much is “consider” donated per those year by each donor.

if John Smith gave $100K in 2022 and have $30K written off, it does not matter if he has another pledge in 2021 $100K and have $50K written off. He is consider given $70K in 2022 and consider given $50K in 2021, meaning he is in the $10K+ Up to $100K donor category level.

Using the Donor Query > Summary of Giving you will get John Smith $50K in 2021 and $70K in 2022 column for you to do excel formula or pivot table on to get the donor category.

Alex Wong

and in output just need Constituent ID and the Total Amount of Gifts_1 from the Query Fields section, you can do that for each year you are doing donor category.

or if you want 1 query file .. you can do multiple of Total Amount of Gifts one for each year and use the OR operator in Criteria tab, then output each year's giving with the Constituent ID, and then work in excel that way.

Naila Asif 4

@Alex Wong, When you say donor query to get summary giving information, you mean query or export? I created a constituent query for donors who have any gift from 2018-2023 and then used this query in an export to get cumulative total for gift type (cash, recurring pay-cash, pledge and stock/property) for each fiscal year along with the write off amounts in each fiscal. I then subtracted the write off amount (checked if write off is done the same fiscal or later) from the cumulative total and then put them in donor category report fro each fiscal. I also have to take in consideration of soft credited pledges and cash payments if donation was from a foundation or organization but s/c to a donor who is coming in a lower category with the hard gifts but needs to move up due to s/c gifts.

CnBio_Name	2018-19	Write off 2018-19	2019-20	Write off 2019-20	2020-21	Write off 2020-21	2021-22	Write off 2021-22	2022-23	Write off 2022-23	2023-24	Write off 2023-24
Donor A	$0.00	$0.00	$0.00	$0.00	$200,000.00	$0.00	$0.00	$150,000.00	$0.00	$0.00	$0.00	$0.00
Foundation A s/c to Donor A	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$200,000.00	$0.00	$0.00	$0.00

see here, where donor A has a pledge in 2020-21 for $200k, $150 was written off in 21-22, so he is coming in donor category of $50k for fiscal 2020-21. For fiscal 2022-23 there is a pledge from a foundation s/c to him so I put Donor A in $200k category for 2022-23.

Please let me know if I am on on the right path.

Thanks,

Naila

Naila Asif 4

@Alex Wong, I just see this comment, so you are using a constituent query. I will try to do it this way and see what I get.

Naila Asif 4

@Alex Wong, thank you so much for your help, I am getting the correct amounts in the query and it is deducting the write off amounts. I will manage to identify the soft credit issue and then create the pivot for categories in each fiscal year.

Constituent ID	Key Indicator	Name	2018-19	2019-20	2020-21	2021-22	2022-23	2023-24
17514	Individual	Donor A	$0.00	$0.00	$50,000.00	$0.00	$0.00	$0.00

Thanks again for your help and patience for my questions.