My Journey to Creating a Gift Table

Dear Diary,

When I began as a prospect researcher I knew that certain information gleaned about a prospect was important. I knew that past and current philanthropic giving could be extremely strategic in cultivation. I also knew that asking the right and sometimes challenging questions to a prospect about impactful giving could take successful twists and turns when trying to qualify. Today, I can say that learning how to forecast the number of prospects and which specific prospects to engage in a fiscal year is information I wish I had learned years ago. It took me a while to get to this experience, and here I am sharing my tale.

It all began on one January afternoon, a brisk time in the lovely city that I call home. My day felt normal and speedy. I handled requests and drank my warm black tea – a usual winter day with snow forecasted for the following days. Suddenly, a brand-new request was upon me, “Please provide your fundraisers with a gift table.” I was in shock; I had never created a gift table nor did I know what it meant.  Quickly, I learned that a gift table was a strategic modeling tool meant to be adaptable and agile, solely dependent on answering the ways to reach the fundraising goal for the upcoming year. It took me a few months to master how to create and use a gift table, not because it was the most difficult concept, but because I can be slightly obstinate (sometimes) and my initial attempt was a failure.

This is what I learned and how I did it.

First, a gift table is a strategic method of calculating the number of donors needed at various dollar levels to achieve a fundraising goal. It is an agile and adaptable forecasting tool that helps with planning out the fiscal year for fundraisers and prospect researchers.

Let’s take a look at the columns needed in a gift table, and please note that these columns are not mandatory nor do they need to be in a particular order, however, you will find that Gift Amount, Gift Range, and Prospects Needed are the most commonly used. Here are the columns I used in learning the process: Gift Amounts (the giving levels); Number of Gifts Needed (the number of gifts that are needed to reach the fundraising goal); Prospects Needed (the number of prospects needed that are rated within the capacity level); Dollars Needed (total dollar amounts needed at each gift amount); Number of Gifts Secured (the number of current gifts secured at the specific giving levels); Gifts Secured (the dollar size of current gifts secured at the specific giving levels); Number of Open Asks (the number of current gifts that have been asked); the Amount of Open Asks (the dollar size of each open ask at the specific giving level), and the Gap (calculated by how many gifts are needed against the number of open asks). Additional columns that you may want to include, but I did not for this tale are the number and dollar size of planned asks (asks that are planned for the fiscal year).

To fulfill this process, please note that this method requires an excel sheet, basic excel equations, and reports from your database that will help you plug-in key numbers.

For this lesson, our fundraising goal for the year is $10 million. The fundraisers have to raise $10 million, and we are going to help them using this planning model. First, let’s put $10 million at the bottom of the Dollars Needed column just far enough that we remember the goal. Next, fill out the Gift Amounts, assuming gift levels are from $50,000 to $10 million.

Next, let’s go to the information we already have and can plug-in. We can use our Number of Open Asks and Amount of Open Asks within each Gift Amount level. Based on a report pulled from your CRM on current open asks made so far, you can plug-in this information, suggesting that your fundraisers have already made asks in these specific gift levels with the expectation that they will close and bring us closer to the fundraising goal.

Next, let’s look at the Number of Gifts Secured and Gifts Secured that have already come in this year towards our $10 million goal.

Already, we can see that we have made some progress towards our goal, but we still have some ways to go, and we need additional asks made this year. This also means that new prospects will have to be qualified and asked as soon as possible.

We will apply a basic math equation into Excel that will allow it to populate in Dollars Needed. So, in Dollars Needed apply the equation SUM=B2*A2 (this assumes Gift Amount multiplied by Number of Gifts Needed goes into Dollars Needed to reach the total). Right now, all you are doing is only adding the equation into the column.

Then, go to Number of Gifts Needed and start adding the potential number of gifts that could be needed based on what is already secured and the asks that have been made. For example, one $5 million gift would take us very far, and two additional $1 million gifts would be great seeing as we have already made three $1 million asks, and have secured one.

Now, all that is left is to figure out the Number of Prospects Needed and the Gap. To figure out the Number of Prospects Needed, we need to start thinking of the Number of Gifts Needed and the Number of Gifts Secured, while utilizing a 4 to 1 ratio. Several ratios float around in our industry, and I have learned to use the standard that implies that it takes 4 prospects to get to 1 gift.

We take the assumption that if the Number of Gifts Secured is less than the Number of Gifts Needed then we simply multiply by 4, and that if there is a number (any number) in the Number of Gifts Secured column then we must subtract the Number of Gifts Secured from the Number of Gifts Needed, and then multiply by 4.

Meaning, we have secured one $1 million gift, and I believe that we need two gifts (at the $1 million level) to get to goal, so (2-1=1)*4=4 prospects needed to help us reach goal. This equation would read as =IF (E2<B2, (B2-E2) *4,0) – without the logic it’s understood as (B2-E2)*4) It is simply the Number of Gifts Needed minus the Number of Gifts already Secured times (multiplied by) 4.

Finally, the Gap is the simple equation of the Number of Gifts Needed minus the Number of Open Asks.

What does all of this mean? Well, it means that the fundraiser needs 92 prospects to help them reach their goal. The Fundraiser also needs to make sure that they are working to close their open asks that could help them meet a third of their goal. The prospect researcher has to identify these 92 prospects and make sure they are mission aligned, and have research and contact information. The Gap is telling us how many gifts we need or are in deficit of, for example, at the $50,000 level we need 10 gifts but we have 3 asks out that equate to $150,000, therefore, we now need 7 gifts if those asks close quickly.

To really master this process, I practiced creating gift tables several times, sometimes just for “fun”. I wanted to be skilled in this. I hope sharing my journey will help you when it’s your turn.

Until next time, November 15th!

Art Credit: LeNia Stitt