Recently I was tasked with finding a resource allocation tool for our team of developers. We needed a way to quickly glance and see that So-and-so has x hours available in October, but is overbooked in November. Sounded easy enough, but I searched high and low and wasn't able to find one that I was super happy with. My goal was to find a tool that met the following criteria:
Simple, easy to update, easy to share with the team
Allowed us to allocate one person to two roles within the same project (for example: sometimes a Project Manger does QA as well)
Ability to assign different allocation percentages to each person (for example: a contractor may spend 25% of a normal full-time person, while our in-house developers are working 100% of a 40-hr week)
Satisfied my OCD (you'd be surprised how many resource allocation tools don't account for holidays or days off)
Most of the tools that I researched were either too simple or overly complex. And all the solutions in between that might have worked cost too much to justify trying (or were just really really ugly). So, naturally, I set out to make my own resource allocation tool from scratch in a google sheet. Just kidding, it wasn't natural at all, but that's besides the point. I'm a scrum-freak, so I've started here with the basics. I plan to use the tool for a few weeks and dive back in to iterate and make additional improvements. Here's what I focused on to start:
Start Date: enter the day you want to start allocating hours, and the document automatically calculates the following days in the 3-month timeline.
Holidays: enter all holidays in the following 3 months to ensure those hours are not considered when calculating future time available.
Team Members: add the name of all the team members you'd like to allocate resources for.
Allocation %: full time workers get 100% of their hours allocated. Part-time or contracted workers can receive a smaller percentage of a normal 40-hr work week allocated.
Project and Role: this isn't used in any calculations at this time, but here you can add all projects that person is working on and their role. We make sure to add everyone to the 20% Time project and set aside 20% of their available hours to personal projects.
Project %: this is the percentage of that person's allocation that should ideally be dedicated to each project. For example, we want a team member to dedicate 20% of their time to this project. This percentage is used to calculate Ideal hours each month.
Hours per Project: of course, you gotta enter in how many hours per day is spent in any one place. This tool will be green as long as it's 8hrs or below, and turns red if more than 8hrs are given to any one project in a day.
Ideal vs. Actual Scheduled: ideal hours scheduled is calculated based off the team members percent allocated to each project. Actual is calculated based on how many hours you've scheduled for each person, per project per month. These numbers are displayed side by side so you can easily see if you're scheduling too much.
Total Available: this is the total number of hours available to allocate each month, assuming an 8hr day and subtracting all weekends and holidays.
Free Time: my favorite. This number is calculated by the number of hours available to be scheduled for each person([8.]Total Available x [4.]Allocation %) minus [7.]the number of hours that have been scheduled. This section was really the entire point of creating the document, because now we can immediately know if we have the bandwidth to take on another project at any given time, and all it takes is a quick glance at this section of the document.
So far I have a few other ideas up my sleeve for the next iteration, but first I want to put it to use and see how it works in the real world. If you or anybody you know could benefit from this google sheet, duplicate it and play around with it. Meanwhile, we'll be testing it out at Standard Code to see how it holds up.
