Tl;dr: Looking for a more elegant way to manage staff funding.
I work in academia and manage a team of people who are primarily funded through various grants. For example, on a 4-person team for the 2024 calendar year, the breakdown might be:
- Meg: 50% effort on Grant A & 50% effort on Grant B, all year
- Jo: 100% effort on Grant C for January - March, then 75% effort on Grant D and 25% effort on Grant A for April - December
- Beth: 90% effort on Grant B and 10% effort on Grant C for January - October, then TBD how we’re funding her for November - December
- Amy: 100% effort on Grant D January - March, 100% effort on Grant A for April - June, 100% effort on Grant B July - Sept, and 100% effort on Grant C for Oct - Dec.
Part of my job is to ensure that always we have sufficient funding to cover everyone’s salaries. So to do that, I also capture:
- Everyone’s individual salary (so that I can calculate cost per funding source) for specific time periods (e.g., need to be able to account for salary increases each year or mid-year promotions/changes)
- Each grant’s account number and contact person
- When I last confirmed effort for each person/source
On an ongoing basis, I need the following level of detail/reporting:
- For each person, by month, how are they funded?
- For each person, how are they funded across the whole year? (I.e., if Amy is on 4 grants at 100% effort but only for 3 months at a time, then she is effectively 25% effort for each of them at the year level.)
- For each grant, which people is it funding (both by month and year), and for how much time?
- What does my funding stream look like at a high level? When is funding running out and for whom?
- Given each person’s salary, how much money is on which funding source for a given period?
I am currently managing all of this via Excel and while it works, it’s getting a bit tedious, and I feel like there ought to be a more elegant solution for doing this. While I can get retrospective/current state data out of our HR/finance systems, I also need my own system to a.) cross-check/validate and b.) forecast. Any ideas or recommendations?