Hi guys,
So I am not new to Airtable, but I certainly do not consider myself an expert. This is the first complex project I have worked on, and I am hoping to get the community’s help in building it.
This is a business model template with a cash flow analysis.
The goal is to enter a set of assumptions in the scenario table, and then Airtable builds a business model with these assumptions with a cash flow analysis.
The area I have the most trouble with is referencing certain records that fit dynamic criteria.
In this specific case here the money from sales generated in month x is collected in month x+4
Each record in the sales table is for a month with a unique month ID.
I want to have every month show the sales made and the sales collected automatically based on this formula Sales Collected for Month n = Sales Made for Month n-4
I tried doing it with a rollup function, but I wasn’t able to do it without manually linking each record independently.
So instead I used the following solution
Created a linked record field that links back to the sales table {Sales}
Used a formula to calculate the month being collected, x-4 and used a similar concatenate formula as the ID to generate the collection month ID {Collection Month ID}
I then used an automation to update the linked record field with the {Collection Month ID}
And then I used a look-up field to show the amount collected
The automation is triggered when I check the update box in the update field.
My question is the following: Considering how easy it is to do this in excel, is there an easier way to do this? Is there a way to do it without using running an automation?
This is something that will come up quite often in building this model where vendors will be paid in x+2 months and where sales will be collected x+4 months
I feel there could be a way to achieve this with a single roll-up function
I have attached a copy of the base below
