There was an error in this gadget

Friday, May 18, 2012

A simple project management spreadsheet in Google Docs

I love Google Spreadsheets and I won't apologize because it's the single best tool for project management in the universe.  It lacks some of Excel's functionality and is somewhat harder to use, but the ability it provides groups to simultaneously edit a single data source is transcendent. That's why I use it for simple project management.

I reference the simple project management spreadsheet shown below in Shipping Greatness and discuss how to use it in some detail.   You can access and copy the spreadsheet here.  What I don't talk about is how I made it.

See, the trick with a simple project management spreadsheet is to make it automatic enough to be useful but not too automatic that it breaks or takes lots of management time.  You can see that each task in the Task Breakdown has an assignee (by globally unique email address), a target version, and time remaining.    If you expand out columns B..D you can see some of the magic...

The first bit of magic is to compute a symbolic name - "email"+"|"+"version".  This enables you to do the lookups in the "Task Allocation" through the SumIf operation.  That's a simple way to get you totals on an individual basis and identify the long pole.

The next trick is to ensure that code complete doesn't end on a Monday.  You can make your estimates of linear development days turn into linear weeks by ROUNDUP("maximum developer days"/5*7).  Apply your fudge factor here (divide by 0.6).  Use the MAX not the SUM of the team's developer days, since code complete is defined by the long pole, not the total work.

Now that you have a code complete date that might end on a sunday, make it end on a monday with a WEEKDAY function.  One such function looks like this: 


What about Test Complete? Well, testing starts either after Code Complete or after the previous Test Complete - so use a MAX function again, and apply similar weekday logic.

Finally - if you have a launch day schedule (e.g. a Patch Tuesday) you need to get a little more tricky. I'm sure there's a better way to do this, but I just created an ugly IF statement, like so:


Notice that this spreadsheet doesn't have any hard dependency checking. That would add LOTS of complexity and you can manage big dependencies in notes, in my experience.

Now that you know how this spreadsheet works, feel free to copy it and let me know if you have improvements. Look for tips on how to use it in the book!