Here's one for the formula wizards here.
I've had a red hot go, but cannot figure it out.
We have projects that go from a week to almost a year.
We'd like a way to calculate out an approximate "value" for each month in time.
Right now, we are not doing complex budgeting in smartsuite, but we will look at this in the future. For now, we just have a single "project value" for each project
We've just started inputting project data into our database - you can see how the projects fan out over time on this timeline -
I really don't know how to approach this in smartsuite. I can get it working in coda, and think i have a way in airtable.
Essentially, each project needs to figure out how many months the project covers. One can do this fine grained (% of months) but for now, it is completely fine for a broarder approach - if a project is in a month, an equal percentage of the budget gets assigned to that month.
So if a project is all in 1 month, 100% of the budget is assigned to that month.
If a project straddles two months, 50% goes to each month it is over. 3 months, 33.3% each etc.
I have been able to get a reasonable amount working.
I can identify the start and end month, and convert that to months since the beginning of 2023 (as thats where data will start, and we need to go into next year. Thus Jan 2024 is month 13)
And from there I calculate the number of months the project straddles, and it calculates the $/month.
I can make an automation that links a project to the different months in a cashflow app whenever a date range changes.
However, it overwrites any previously linked projects (so it wont work). In addition, it won't delete its old months first. To do that, it would need to look for itself in the cashflow app, remove the links, and add new links.
So - its almost there, but the link to projects only ever allows one project per month to be linked via the automation - and I also have no way of cleaning up previous entries (if date ranges completely change etc). See below - where the automation runs and works great - but just for the first project I trigger. From then on its problematic (overwriting the links if new ones need to be added for that month)
Does anyone think I'm on the right track ? Can anyone suggest an automation approach?
In other systems like coda - or even old school filemaker pro, I'd just make a column an auto link to projects with a filter (month number is between the start and end months in the project). Super simple. But right now, I do not believe Smartsuite has anyway to bring in an array of project links like that - am I correct? (Apologies if I'm using the incorrect terminologies... I'm SLOWLY coming around to calling each table an app....ha!
In small businesses, cash flow is super important - and thefore being able to have some idea of what you are actually pulling in each month - even if payment is 2 to 3 months away - is great for planning.
Ok - super interested to hear how folks might approach this in smartsuite - even if completely differently from where i've currently got to.
Brendan.