So I’m feeling generous today and giving away free stuff. Before I broke off to start my own company I was a project, program and portfolio manager at different times. So I know the challenges you face when looking at a sea of data, enterprise systems that don’t let you ask simple questions, and a slew of other pieces to the puzzle.
Here are a couple simple Excel tools you can take, use and adapt however you like.
1. Capacity Planning with Repetitive Tasks.
If you find yourself with staff who perform high volumes of routine tasks, you may find this worksheet useful. The top portion of the worksheet lets you figure out your actual total capacity for the department you’re looking at.
Below, that capacity is broken into three “buckets”. The first, “support capacity” lets you enter approximations of how long each task takes on average, and how many times those tasks are performed each week.
The next, “administrative capacity” lets you enter things like meetings, seeking clarification, or other work that is necessary to keep business moving. Again, you can enter guesses on duration, and how many times those events happen a week.
Those two buckets are added up as time you can’t touch. Whatever is left, is time that’s available for variable work (like projects).
When you add these three buckets together, you can see how you stand against total overall capacity. Maybe you need to cut down on the number of meetings, or reduce the number of routine tasks you do in a week to make room for a project. Or maybe you can’t realistically do the project without finding more people.
2. Capacity Planning with Multiple Projects
Okay, let’s say you have a bunch of projects on the go, your portfolio management system is too clunky (or you don’t have one), and you need to do some “what-if” analysis.
You have one pool of Business Analysts, Developers and Testers and up to five projects. How do they all fit together? To get all these projects done, how many resources do you really need? What happens to your other projects if one of your projects is late by a week? What happens if you lose someone?
Enter the durations of your projects, and the number of each type of resource. Use the slider buttons to move your projects back and forth through time. When you do that, the graph at the top will change. If any of the lines in the graph go over the capacity line, you have a problem.
You have a limited ability to change the assumptions that go into this worksheet on the second tab (it is quick and dirty after all).
Feel free to take either of these worksheets to use for your own purposes. There’s no locks on them so you can do whatever you like.
If you like them, but they’re not quite for you, I’d be thrilled to make you something that will do exactly what you need. 😉
