Balancing Work By Discipline Instead of Individuals

As part of our effort to become a highly-effective agile team, we’ve been working on switching from an individual to a team focus. During sprint planning, we want to know that we haven’t taken on too much work for each discipline. And on a daily basis, we’d like to see if we’re running behind on a discipline. For example, if we’re running behind on test, are there things developers can do to help?

The Iteration Backlog workbook that is included in the MSF for Agile Software Development v5.0 process template is a really nice workbook. As implemented, it allows you to see how much work is assigned to each individual so you can tell if they have the right amount of work.

However, not all agile teams like to assign work up-front to individuals—it’s fairly common to have group ownership of work within a discipline. As a result, I decided to modify the Iteration Backlog workbook to report on discipline rather than individuals. The changes are quite involved, so I’m only going to provide a high-level overview of the changes I made.

Overview of Changes

If you want to make this type of changes, you’ll want to read my blog post: Customizing the Iteration Backlog Workbook.

Here is a brief overview of the changes I made in order to have the capacity worksheet use discipline rather than team member. First, I needed to add a separate table that maps team members into disciplines:

Add a new Team Members table to the Settings worksheet:

You can actually add the same person more than once if they’re sharing their time between disciplines. The team member names here can be anything you want—they’re not tied to a field in the query results. The Discipline column, however, is tied to values from the Activity field. You do this by turning on data validation (in the Design tab of the ribbon) and setting the Source field to =Disciplines, which is a named range on the HiddenCapacityData worksheet.

The Remaining Capacity cells have the following formula:

 =IF(AND(ISNUMBER(IterationStart),ISNUMBER(IterationEnd),ISBLANK([@[Team Member]])=FALSE),
    [@[Hours/Day]]*NETWORKDAYS(TODAY(),IterationEnd, Holidays[Date])
        - SUMIFS(PlannedInterruptions[Remaining Hours],PlannedInterruptions[Team Member],[@[Team Member]]),
    ""
)

Modify the Planned Interruptions table so the drop-down list of team members comes from the new Team Members table instead of the Assigned To field of the query results. This change required modifications to the VBA code

The finished Capacity worksheet looks something like this (I’m using dummy data):

VBA Changes

In the sections below I’m going to provide some of the details of changes I made to the VBA code. You’ll need some understanding of working with VBA in Excel in order to be successful following these examples. Because of the large number of changes I had to make to the workbook, I’m not going to provide you with all the details, but rather enough so with some effort on your part, you can get this to work, or perhaps make other types of changes.

VBA Changes for the Interruptions Worksheet

The interruptions worksheet needs to pull a list of users from the new Team Members table. Here are the changes you’ll need to make to the VBA.

First, add a new variable definition, m_disciplines, near the top of the file:

 Private m_users() As String
Private m_disciplines() As String

Then at the end of the code, modify the UpdateLists sub, and add a new sub:

 Private Sub UpdateLists()
    UpdateList FIELD_ACTIVITY, NAME_DISCIPLINE_LIST, m_disciplines, False
    UpdateUserList
End Sub

Private Sub UpdateUserList()
    Dim r As Range
    Dim c As Range
    Dim n As name
    Dim list As ListObject
    Dim columnIndex As Integer
    Dim vals As New Collection
    
    '
    ' Build a collection with only the unique values
    '
    Set list = GetListByName(Settings, LIST_TEAM_MEMBERS)
    
    columnIndex = list.ListColumns(Settings.Range(NAME_TEAM_MEMBER_CAPTION).value).index
    If columnIndex = 0 Then Exit Sub
    
    Set r = list.ListColumns(columnIndex).DataBodyRange
    
    For Each c In r.Cells
        On Error Resume Next
        vals.Add c.value, c.value
        On Error GoTo 0
    Next

    '
    ' Sort the values we got back
    '
    Dim sortedVals() As String
    SortCollection vals, sortedVals
    
    UpdateListRange NAME_USER_LIST, sortedVals, m_users
End Sub

VBA Changes for Capacity Worksheet

The changes in the code behind the Capacity worksheet involve changing formulas, and also using a list of disciplines instead of people.

At the top of the file, change m_users to m_disciplines:

 Private m_disciplines() As String

In the code around line 92 (in the Sub called UpdateFormulas), change the code so it looks like this:

 formula = "=IF(ISBLANK(" + _
          LIST_INDIVIDUAL_CAPACITY + "[[#This Row],[" + FIELD_TEAM_MEMBER + "]]),""""," + _
          "SUMIFS(" + _
          tableName + "[" + FIELD_REMAINING_WORK + "], " + _
          tableName + "[" + FIELD_ITERATION_PATH + "], " + NAME_CURRENT_ITERATION + " & ""*"", " + _
          tableName + "[" + FIELD_AREA_PATH + "], " + NAME_AREA_PATH + " & ""*"", " + _
          tableName + "[" + FIELD_ACTIVITY + "], " + "[" + FIELD_TEAM_MEMBER + "]," + _
          tableName + "[" + FIELD_WORK_ITEM_TYPE + "], """ + WORK_ITEM_TYPE + """" + _
          "))"

This changes the formula to group by the Activity field instead of the AssignedTo field.

Finally, change the UpdateLists sub at the end of the file:

 Public Sub UpdateLists()
    UpdateList FIELD_ACTIVITY, NAME_DISCIPLINE_LIST, m_disciplines, False
End Sub

This will retrieve the unique set of activity values from the query results and update a named range in the hidden CapacityData worksheet. You’ll also need to update the named range in that worksheet, as well as update the set of named ranges in the GlobalConstants module.

Using the Discipline Iteration Backlog

Using this workbook is very much like using the version shipped with the process template, with some important differences.

Enter Team Members

First, you’ll need to add your team members to the Team Members table on the Settings worksheet. It doesn’t matter what you type in for each team member. You should, however, ensure that you use unique names for team members (if you don’t, Planned Interruptions won’t behave correctly).

If a team member is available to work in more than one activity, such as both development and testing, add one line for each combination of team member and activity. That way their time can be shared between activities.

Select Disciplines

In the Discipline Capacity table, make sure you have one row for each activity that you’re tracking. The rows in this table control what you see in the graphs on this page.

That’s really all there is to using the Discipline version of the Iteration Backlog workbook.