Hello, newbie to Airtable. I am trying to build a CRM tool that also forecasts future period recurring revenues.
We have projects that often span 2-3 years. I would like to allocate the future revenue (equal monthly amounts is fine) across 12/31/XX fiscal year time periods.
My inputs are:
Start date
End date
Monthly revenue
I’d like my outputs to be:
- Prior Period Revenue (any revenue prior to this fiscal year)
- Current Fiscal Year (e.g. 12/31/2020) Revenue
- Next FY Revenue
- Backlog Revenue (anything beyond next fiscal year)
Let’s assume a hypothetical project as follows:
Start date: July 1, 2019
End Date: March 31, 2022
Monthly revenue: $10,000
I would expect the following results:
- Prior Period Revenue: $60,000
- Current FY Revenue: $120,000
- Next FY Revenue: $120,000
- Backlog Revenue (anything beyond next fiscal year): $30,000
I have been able to build this in excel but would really like to manage this process in Airtable. But I am stumped with Airtable date formulae. I think it boils down to this: how do I allocate months in a date range to different fiscal years?
I appreciate any thoughts.
