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