Capacity and Resource Planning

[Kat_Hunsinger]

Hello Airtable community!

I was wondering about the best ways of implementing capacity planning and project planning to a base.

I’m looking at trying to track level of effort by month per project.
Example:
– Jane Doe works 80% on Project 1 and 20% on Project 2 for July 2022
– Bob Smith works 40% on Project 1 and 60% on Project 2 for July 2022
– Jane Doe works 90% on Project 1 and 10% on Project 3 for August 2022
etc

An employee can be assigned to multiple projects, and a project can have have multiple employees working on it.

Tables I’m currently looking at:
Team - contains data on team members
Projects - contains data on projects occurring throughout the year(s), can be overlapping
Allocations - contains data which details level of effort for a team member for a project for a month

I’ve seen a couple questions like this in the community but none have been answered, would appreciate any guidance!

[TheTimeSavingCo]

What you’ve got in mind looks right.

If I were you I would probably write a script and just get it to calculate everything and create records etc on its own. If you needed it automated and you’re on a Pro plan you could run it via an automation, if not you could click a button to get it to run every time you needed the data

Without a script, the only way I can think of accomplishing what you’re trying to do would be with a bunch of conditional rollups, but this means you would have to create a conditional rollup field per variable, e.g. one field per employee, possibly one field per month etc. So to get the total number of hours worked by Bob, for example, you’d create a rollup field in your Team table per team member, rolling up on the Allocations linked field with the formula of sum(values), and the conditional being Team Member = Bob, you know what I mean?

Hopefully I’ve missed something and there’s a simple way to handle this that I’ve missed that someone else can suggest