When working with our Target Analytics predictive modeling clients during consulting sessions, we often help them create action plans with their results with the use of an idealized potential gift table. We do so by utilizing the bottom end of estimated annual capacity ranges identified from their project (i.e., Target Gift Ranges or Principal Giving Segmentation Ranges). I thought I would make the Excel spreadsheet I use for this more applicable across a variety of dollar ranges and usages so that any nonprofit could use it with their own capacity ratings. Attached to this blog is a gift capacity planning table.
To utilize this gift capacity table, you simply need to enter in the number of prospects that you have at each dollar rating under column B of the spreadsheet, and maybe start with using it as a guide for the bottom level of your capacity range. One could also create additional worksheets in this Excel spreadsheet for a table that calculates the middle of the range and a top end of the range too. The idea is that you can get an idealized look at the overall potential of prospects who are rated $1,000 in your prospect pool. As I mentioned we use a similar tool in calculating potential for prospects who are rated using our predictive modeling scores, so if you have the Target Analytics Target Gift Ranges (TGR) 6-12 or Principal Giving Tiers 1-4, you can apply accordingly. Here is a screenshot of the spreadsheet:
To explain this worksheet further, here is a listing of the columns and what they are:
What you may find is that you are using this chart with actual verified and confirmed ratings from your prospect research team for more realistic potential and forecasting purposes. With this scenario you may want to use tighter yield ratios such as 1:3 or even 1:2, especially at the higher dollars range amounts, such as at the $100,000 annual amount (rows 2-6 above in the spreadsheet), given that historically through your descriptive data analysis, this is what your major gift team experiences. Also you may be including only your verified prospects who have a long-standing relationship with a loyal giving history to your organization. Another factor in using tighter yield ratios might be that they may already be assigned to gift officers. You could create one worksheet for assigned prospects and another worksheet for unassigned/newly identified prospects, whereas the already assigned and/or qualified worksheet has those tighter yield ratios of 1:3 and the unassigned and to be assigned/qualified group has yield ratios of 1:5 to 1:8. I do highly recommend doing a historical analysis that measures gift ask to gift realization to help you identify your yield hit rates, keeping in mind most likely at the smaller gift amounts of $1,000 for example, you may have a higher yield ratio such as 1:10 or 1:15, and at the higher dollar amounts you have tighter yield hit rates. This will help your organization better understand your yield ratios from the past, but keep in mind that those yield ratios could have recently improved or be less successful, depending upon factors either way such as whether or not you are in a campaign, better prospect pipeline management, gift officer turnover, unflattering press, etc.
How our consulting team uses this table, and you could also use it this way as well, is at the planning stages once you initially have ratings on prospects, as mentioned above with modeling capacity ranges. You could also apply this table for calculated Major Giving Capacity ranges from a WealthPoint screening too.
I hope you find this tool helpful in your planning and forecasting efforts. This leads me to asking our Target Analytics Community members to share their needs or past usages with a table like this... so please offer up your ideas here.
To utilize this gift capacity table, you simply need to enter in the number of prospects that you have at each dollar rating under column B of the spreadsheet, and maybe start with using it as a guide for the bottom level of your capacity range. One could also create additional worksheets in this Excel spreadsheet for a table that calculates the middle of the range and a top end of the range too. The idea is that you can get an idealized look at the overall potential of prospects who are rated $1,000 in your prospect pool. As I mentioned we use a similar tool in calculating potential for prospects who are rated using our predictive modeling scores, so if you have the Target Analytics Target Gift Ranges (TGR) 6-12 or Principal Giving Tiers 1-4, you can apply accordingly. Here is a screenshot of the spreadsheet:
To explain this worksheet further, here is a listing of the columns and what they are:
- Column A – The dollar value for the rating, and this could simply be altered so that you can customize to dollar ratings utilized by your organization
- Column B – The number of prospects in that range, which can be edited as they change over a period of time of your choosing
- Column C – This cell takes the Capacity rating in column A and multiplies it by column B to get the total dollar value of the number of prospects by that gift capacity amount
- Columns D through G – These cells take the total potential dollar amount in column C and applies a formula for whatever level of yield your organization would receive for gifts at the specified level
- For example if you have 10 prospects at the $1M level in row 2, column C would be $10M; then column D would multiply the $10,000,000 in column C times 10% or .1 to equal $1,000,000; i.e., this cell’s calculation is =SUM(C2*0.1)
- Row 13, columns D through G – Sums the total dollars for that column within each yield ratio, also known as hit rate
- For example cell G13 has a calculation of =SUM(G6:G12)
- Row 14, columns D through G – Sums the above row multiplied times 5 to help in projecting potential for a 5-year gift pledge such as a capital campaign
- For example cell G14 has a calculation of =SUM(G13*5)
What you may find is that you are using this chart with actual verified and confirmed ratings from your prospect research team for more realistic potential and forecasting purposes. With this scenario you may want to use tighter yield ratios such as 1:3 or even 1:2, especially at the higher dollars range amounts, such as at the $100,000 annual amount (rows 2-6 above in the spreadsheet), given that historically through your descriptive data analysis, this is what your major gift team experiences. Also you may be including only your verified prospects who have a long-standing relationship with a loyal giving history to your organization. Another factor in using tighter yield ratios might be that they may already be assigned to gift officers. You could create one worksheet for assigned prospects and another worksheet for unassigned/newly identified prospects, whereas the already assigned and/or qualified worksheet has those tighter yield ratios of 1:3 and the unassigned and to be assigned/qualified group has yield ratios of 1:5 to 1:8. I do highly recommend doing a historical analysis that measures gift ask to gift realization to help you identify your yield hit rates, keeping in mind most likely at the smaller gift amounts of $1,000 for example, you may have a higher yield ratio such as 1:10 or 1:15, and at the higher dollar amounts you have tighter yield hit rates. This will help your organization better understand your yield ratios from the past, but keep in mind that those yield ratios could have recently improved or be less successful, depending upon factors either way such as whether or not you are in a campaign, better prospect pipeline management, gift officer turnover, unflattering press, etc.
How our consulting team uses this table, and you could also use it this way as well, is at the planning stages once you initially have ratings on prospects, as mentioned above with modeling capacity ranges. You could also apply this table for calculated Major Giving Capacity ranges from a WealthPoint screening too.
I hope you find this tool helpful in your planning and forecasting efforts. This leads me to asking our Target Analytics Community members to share their needs or past usages with a table like this... so please offer up your ideas here.
News
Organizational Best Practices Blog
08/02/2017 10:55am EDT
Leave a Comment