Use the MSF Agile workbooks in the MS Scrum template

The MSF Agile 5.0 Process Template in Team Foundation Server 2010 comes with a great set of Excel workbooks. These workbooks allow you maintain your Product and Iteration Backlog with Excel. The workbooks contain a set of macros that are very useful, because they transform information from the workbook in a visual appealing way.

You can read all about these workbooks on MSDN

The Product Planning Workbook shows information about the different sprints, to allow a good indication of how many story point will fit within an iteration

The Iteration Backlog Workbook shows the capacity of your team and allows you to fill in Interruptions and Holidays.

The MS Scrum Template 1.0 does not have these workbooks. Main reason is probably that is is not really Scrum to assign tasks and sprints beforehand.

Although I like the Scrum way of working, I also like to see some indication of capacity and sprint planning. I refactored the Agile Workbooks so that they work for the Scrum Template as well.

In my Devdays 2011 talk about Adopting Scrum I showed how to use these sheets. I will attach the sheets to this post as well.

The workbooks do not have a TFS connection yet. Once you make a connection, it is stored inside the excel sheet and cannot be easily changed. Save the files somewhere on your WSS portal (or locally) as a template and copy every time you want to make a new planning.

In order to use the sheets you need to follow some steps.

Product Planning

In your MS Scrum Team Project, create a Team Query that shows you all the Product Backlog Items. Save this query as for example “Product Planning”

For example:

Make sure the query contains the following columns

Of course it is wise to sort the query by “Backlog Priority”

Now follow these steps

• Open (a copy) of your Product Planning Workbook and connect the Team Query.
• Place your cursor in cell A1 of the first worksheet.
• Switch to the Team ribbon and click New List.
• Select your Team Project and click Connect.
• From the New List dialog, select the “Product Planning” query from your team project .

• Your sheet is now filled with data
• Save the workbook

In the Iterations Sheet a graph will fill, as soon as you assign Product Backlog Items to a Sprint and assign effort to the items. The table will only show the sprints that are filled in the Product Backlog sheet.

Iteration Backlog

In your MS Scrum Team Project, create a Team Query that shows you all the Sprint Backlog Items. Save this query as for example “Sprint Planning”

For example:

Make sure the query contains the following columns

Now follow the same steps as in the Product Planning Sheet. Open the Iteration Backlog sheet and connect the Sprint Planning Query.

Make sure you fill in a sprint, start and end date in the Settings Tab. As soon as you assign Items to the selected sprint and put Remaining Work in the items, the graphs start to fill up.

Now you can start planning !

Download the Excel Sheets