A few months ago I blogged here about using simple "T-shirt" sizes of Small (25%), Medium (50%) and so on to track resource allocations across projects at a high level. ProjHugger reader Andreas asked for the details of how to do this, and that is the subject of this week's post.
Here's how I described this approach originally:
Let's say I have several unique projects and a fairly stable set of work resources (i.e. people). Most of the resources will need to work on more than one project. For my needs, I'll take some pretty broad first cuts, described next.
First, I'll just work in in broad percent allocations of a resource per project. I'll use T-shirt sizings:
- S = 25% allocation
- M = 50% allocation
- L = 75% allocation
- XL = 100% allocation
Any given resource, for any given time period, might have a mix of T-shirt sized assignments per project that sum to 100%. For example, a resource might be assigned to Project A at 75% and Project B at 25%.
Next, I'll limit the time period for which a T-shirt sized allocation applies to just calendar months.
What I end up with is a pretty high-level view of the resource's working time per project, per month. If I've made my low-touch assignments correctly, I should end up with each resource's allocations summing to 100% per month.
I originally referred to this approach as low-touch resource management. Upon further reflection I decided that 's a terrible description--it has some bad HR connotations I don't want to dwell on. Well, let's instead call it cross-project resource allocation tracking with T-shirt sizings.
It might be possible to do this type of resource allocation modeling in Project, but probably not practical. Instead, I'll keep my T-shirt sizings in Excel and just keep whatever level of task details I need in Project. Here's what such an Excel workbook could look like:
Tip Click the screenshot image for a larger view.
In this workbook, resource assignments are indeed limited to the T-shirt size percentages per project, per month. I use sorting and filtering in Excel to see the data in a couple of important ways.
The first filtering I'll use is per project. Here for example I've filtered the Project column to show just a single project, called "Travel Guide." I can easily see per month each resource's T-shirt size percentages.
What I'm mostly interested in seeing here is the sum of visible cells (row 5). The values here are derived from a set of formulas that sums up the percentage values per column. This tells me the overall resource investment I'm making on this project per month. Here's the formula that does this calculation, in this case in cell C5:
If you've never used the SUBTOTAL worksheet function, the key thing to note is that the first parameter (with the value "9") calls the SUM function. Here's the Excel Help topic that describes the function and its parameters:
The second useful filtering I can apply to the workbook is per resource. This time I show all rows, then filter to show just a specific resource name in the People column (column B). In this case, I've filtered the list to show just those values for Dan Jump:
For this example let's assume Dan Jump works full-time. If I've made my assignments per project correctly, Dan's per-month totals should equal 1, or 100%. The same formula produces this value.
I've shown just a couple of simple examples here. I can show more than one value when filtering, for example, and if I felt ambitious I could add subtotals to my Excel list. This quick look-up approach to the Excel workbook generally meets my needs, though so in this case simpler is better.
This approach is without a doubt highly simplistic. But if I needed more complex resource assignment details, I'd move the action into Project. When I just need a quick-and-dirty high level view, this approach works fine.
Thanks for the question Andreas, I hope this answers it.