Projecting Staff Longevity and Growth

[Ashley_Jackson]

Hello AT Community!  Today I am bringing you my biggest challenge yet!  I have been banging my head over this for two days and I cannot make heads nor tails of it lol - I'm hoping you guys can give me some possible solutions to make this work!  Here's the situation:

An organization has staff members and volunteers.  They want to be able to record and project their staff and volunteer growth.  They track these metrics:

1. # Staff
2. Staff Longevity
3. Staff Replication Rate
4. # Volunteers
5. Volunteer Longevity
6. Volunteer Replication Rate

Let's just focus on volunteers for simplicity!  In Year 1, they start with 0 volunteers.  Staff recruit volunteers and by Year 2, they have 5 volunteers.  Those volunteers will serve an average of 2 years before moving on.  In one year, roughly half of their volunteers will recruit a new volunteer.  So in Year 2, our 5 volunteers will recruit 2.5 more volunteers.  Year 3 starts with 7.5 volunteers and adds 3.75 more.  Year 4 starts with 11.3 volunteers, they add  5.6 new volunteers... but the 5 volunteers who started in Year 2 have served their 2 years, so they're leaving.  So they will start year 5 with 11.9.  Here's a visual chart to help make this clear:

 So, the question is... can this be done in AT when we can't reference "the record above," etc???

I'm excited to see what you guys come up with!  TIA!

[Jack_Manuel]

Hi!

This is a really interesting challenge and I'd like to have a crack at it. Can I ask if you intend to track information for individual staff/volunteers in this same base? At first blush it seems you would need a table of people with their year on and projected year off, linked to a year record on another table for rollup and summarisation.

[Ashley_Jackson]

Ideally it was be tracked in one base and as close to one table as possible but I’m not opposed to adding tables for people, etc. Can’t wait to see what you come up with!

[Jack_Manuel]

Thanks! Will have a bash over the weekend if I get time. 

[Ashley_Jackson]

I was thinking more about your initial direction as I woke up this morning. It seems like that would require tracking individual people and then calculating projections off of them. I’m not entirely opposed to that idea but would it be possible to do purely off of numbers - no specific people???

[Jack_Manuel]

I'm sure it's doable with just numbers, just my initial intuition was to use a record per person. Perhaps a record per intake of people. Like I say, I'll have a go over the weekend if I get the time but I can't promise anything!

[Jack_Manuel · Accepted solution]

I think I was over complicating this by trying to restructure your data, instead I've just answered the question at the end of your first post and replicated the behaviour from your chart in Airtable.

 The key is to add a linked record field, here called "Previous Years", which links to the same table, and add each previous year's record to this field. 

You can then perform a rollup on each of the "New Volunteers" and "# Volunteers Leaving" fields to get the cumulative total for previous linked years.

Lastly, a formula field, "# Volunteers" to add the "New Volunteers" and "# Volunteers Leaving".

It's annoying to have to add all previous years to the linked record field when creating a new year but I believe this is the only way to do it without scripting and automations or restructuring your data completely.

Hope this is helpful. I'd also be keen to dig deeper if you want to optimise it, but would need more info to go on.