Contribution Cash Flow Forecasting - a gift-by-gift approach?

Hi there!

I work on the Philanthropy team at Boston Lyric Opera and am the primary individual responsible for designing and implementing reports for contributed revenue and contribution cash flow. I think this post may straddle the line between reporting/analytics and finance, so please let me know if I should post in the Finance forum instead.

My company has tasked me with creating a 12-month cash flow projection that communicates the contribution cash (i.e. received $) we expect to receive each month over the next 12 months. This report needs to be refreshed on a monthly basis, always looking 12 months ahead. Below I pasted an image and description of what a final deliverable might look like.

Here is where this gets tricky: instead of creating broad analytical estimates based on past giving trends, I am being asked to account for the timing and amounts of individual gifts from donors and institutional funders. All of the anticipated amounts and timings should roll up into the 12-month cash flow projection.

I see this breaking down into a few different areas of increasing difficulty:

1. Current fund activity: getting a detailed report of cash received in the current month, broken out by fund (i.e. If I'm building a report for Aug23 - July24 cash flow during August 2023, I need to have the current month's current activity numbers). This is easily done with the fund activity report or simple widgets in Analytics.
   
   
2. Existing pledges: the Yearly Cash Planning report will show the amounts scheduled to be received over the next 12 months for existing pledges. This should also be easy, as long as the payments are scheduled at the times we expect those dollars to arrive.
3. ***Donors not currently pledged: this seems most difficult to me from a technical standpoint - tracking when and how much individual donors will give. Is there a way in Tessitura to log the anticipated month and $ amount we will receive if the donor doesn't already have an existing pledge? My organization uses Plans rigorously to track moves management related to contributed revenue, so I suggested using the "By" date field in Plans to estimate when cash will be received. However, this doesn't easily allow us to track the expected timing of multiple cash inflows from the same donor who does not have a pledge (e.g. some of our donors reliably make more than 1 substantial gift during a given fiscal year).

The third category is what jumps out as most difficult to achieve in Tessitura. Perhaps the best (if unideal) solution may be to track these anticipated cash flows in a shadow spreadsheet (again, NOT ideal), creating a line for each cash inflow we expect, based on cultivation strategies and past giving amounts and timing.

Do any Tessitura member organizations create cash flow reports like what I'm describing? If so, how are you handling these different pieces, whether inside or outside of Tessitura.

Any thoughts are appreciated! Bonus points if there's an elegant way to do this in Tessitura that also leverages Analytics.

Thanks!

Sean

Image description: a data table where the columns represent individual months (June 2023 - June 2024), the rows represent contribution Fund categories, and the values in each cell represent the anticipated cash flow for a given fund and a given month. The bottom row represents the total cash flow across all listed funds for the given month.