Columns that change each month

I am trying to set up a base that lets me plan out my purchasing for the next year. I have something similar set up in Google Sheets, but was hoping for more views and control by moving it to Airtable.

In my base I have a recipe book table (BoM) that says, for example, for a cake I need 50 flour, 20 sugar, 5 strawberries. For cookies I need 20 flour, 10 sugar, 30 chocolate chips. The structure of that table is:
(Concat A and - and B) | Final product | Ingredient | Quantity
Cake - Flour | Cake | Flour | 50
etc.

I have table that is a schedule of things I am planning on making (Build Schedule). The structure of that table is (currently)
Month Year | Cake | Cookies
September 2021 | 30 | 28
October 2021 | 50 | 80
etc.

Want to create a table that will plan out what ingredients I need to have by each month. For example:
Ingredient | Qty on hand | Multiple | Needed his month | To buy this month | Amount remaining after this month | Needed next month | etc.
Flour | 0 | 200 | 2500 | 2600 | 100 | 2200 etc

And then next month it automatically changes the calculation so that “This Month” is October instead of September, etc.

Is this even possible to do with Airtable or should I be looking for a different tool? Like I said, I have this working in a spreadsheet, but I would love to have customizable views I can share with different people, and data that’s less fragile when I decide I need to work in a new features (such as scheduled deliveries)

Thanks in advance!

Hi @Eric_Edwards ,

this is possible as actually you are looking for resource planning system. I guess that in your processes you have to include several information more, like the amount of safe stock, and the correct date to order it based on time of delivery, while also financial numbers could be included in more efficient cash flow calculations.
But all those are some suggestions!
You can develop what you want but it is a whole project development that, if need some help it would be a pleasure to help :slight_smile:

Thanks

Yeah, most of the ERP systems we have looked at fall down in the time-dimension area. They do a good job of showing what we have today, or maybe even what we have on order, but not “As of next March, Y orders will have come in and X goods will have been consumed, meaning we need Z more”. There are a few extra facets like safety stock (in our current system that’s a column that subtracts from current inventory) but they aren’t too complex to work out, it’s just this starting system that’s problematic.

Like I said, I have it constructed in Google Sheets, but it’s hard to make a system there that can be spread to a team of people with multiple views based on what they need to see.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.