Hi! I am working with a nonprofit to get their workspace set up. Make sure you're seated for this one! ๐
The nonprofit wants to see grant funds contributing to individual budget line items broken down by month.
Say they have a grant from the Gates Foundation for $120,000 that must be used between Jan 1, 2023 and Dec 31, 2023. $80,000 of the grant is designated for Staff Member A's salary. $40,000 is designated for Staff Member B's salary. They have a second grant from the Scott Foundation for $6,000 that must be used between March 1, 2023 and May 31, 2023.
The Executive Director wants to see how these grants break out over the months that they are active. For example, that in January, $6,666.67 ($80,000/12 month grant duration) from the Gates grant is applied to Staff Member A's salary. But in March 2023, when the Scott Foundation grant becomes active, $1,000 from the Scott grant and $6,666.67 from the Gates grant are applied to Staff Member A's salary for a total of $7,666.67 in March 2023.
One more thing -- the nonprofit wants to be able to reassign these monthly chunks of funding to different line items as needed while preserving the original allocation data.
Here's what I see as critical pieces of data:
Funding Start and End Date* -- to indicate the number of months and *which* months each grant applies to.
Budget Line Item -- obviously!
Allocation Amount -- also obvious!
*Note that I have already tried calculating the grant duration in months and need more information in order to solve this use case -- e.g. WHICH months the funding applies to.
Here is the sample base with the information I need to create and ideally automate.
I would love to create a button in the "Full Grant Allocation" sheet that expands the allocation into multiple records: one for each month the grant funds with the allocation amount divided by the number of months. I looked at some outside automation options like Make and Zapier and am still stuck on how to do this. Also this forum post was helpful but didn't get me all the way there.
Any help with this is so so appreciated! Thank you.
