I’m David Chesnut; a programming writer on Team System. You may have seen some of my previous posts on our use of Scrum in UE on Rob Caron’s blog. We have been using Scrum for over a year to manage development of our help topics for Team Foundation. In addition to Scrum we have adopted use of the cumulative flow diagram (CFD) to track our work in progress.
Several folks outside of Microsoft have asked to see our Microsoft Excel spreadsheet where we track our backlog and use the CFD. I decided to post an example here in our blog for those folks who are curious about how we use it.
At the beginning of each sprint, we create a spreadsheet that contains 3 worksheets: Backlog, CFD, and Stats. A sample spreadsheet is attached to this blog entry for reference. This spreadsheet is an actual spreadsheet we used back in September for topics we were writing at the time. The only change from the original is that the people names were changed to fictitious names, but otherwise the data is real.
The Backlog worksheet contains all of the topics that we agree to complete during the sprint. They are a slice of the overall product backlog selected during sprint planning.
The CFD is a stacked area chart that displays what state each topic is in over time. It shows how many topics are in each state for each day of the sprint. In a sense, the sprint goal is to move all work items to the final state. In this spreadsheet, the goal was to move topics to a “Ready for Tech Review” state.
The Stats worksheet contains the source data that generates the CFD. Each day a new column is added containing the counts of work items in each state.
How we use the spreadsheet
Before each daily Scrum meeting, the ScrumMaster updates the Backlog worksheet and CFD. In the daily Scrum, the backlog is used as context for each person reporting their progress, and what they plan to work on that day. The CFD is used as reference for the team to see overall progress.
We also use the spreadsheet to improve our transparency. The spreadsheet is posted on an internal Web site for any team member, external team member, or manager to look at. Anyone can at a glance see the backlog we are working on, what state it is in, and our trends (as shown on the CFD).
Benefits of using the CFD
The CFD visually captures several useful metrics for us. The Y axis is the number of work items (backlog) that we will deliver. For our team as writers, this is typically topics to be written. The top line of the CFD represents work in progress. If the line rises, then work items are being added and unplanned work is accumulating.
Each data series (area) in the CFD represents a state that a work item is in. In the attached spreadsheet sample, we use Blocked, Writing Not Started, Writing, Editing, Revising Per Edit, and Ready for Tech Review. The benefit of the CFD is that you can see how much work is stacked up in each state. If one area of the graph is ballooning, this could be a sign of a bottleneck, blocking issues, or other problem in the workflow.
The states are ordered so that work flows from top to bottom in the CFD. The top state is the initial state, and the bottom state is the final state where a work item is ready for hand-off. The size of the final state on the last day represents the team’s capacity for a sprint. For example, if 100 topics were in the Ready for Tech Review state on the final day of our sprint, our team’s capacity would be 100 topics. This means our team is capable of delivering 100 topics per sprint.
If you are interested in learning more about the benefits of CFDs and how to use them, see David Anderson’s Web site at http://www.agilemanagement.net.
How to use the spreadsheet
I’m sure folks will look at this spreadsheet and see a lot of ways to improve it or modify it for their own teams. Please do so! Here are the basics for how to use the spreadsheet.
- Create the backlog. Decide what units of work apply to your team. Each row should represent a unique work item. They can be features, tasks, topics, or whatever the core thing is that you work on. Update the columns to display the information you want to look at (title, priority, state, and other fields.) If you use different states, update the counters in the upper-right corner to capture correct counts for each possible state for your work items.
- Each day, refresh the backlog data so that it is current with reality and each row represents the proper state of its work item. On the Stats worksheet, copy the values of the counters, not the formulas, into the column for the current day.
- Update the CFD to show the new day:
- Select the CFD chart sheet.
- From the Chart menu, choose Source Data.
- Select the Series tab.
- Update the Category (X) axis labels box so that it includes the new day in the date range.
- Click OK.
How to create your own CFD
I was originally going to include detailed steps on how to create a CFD, but it gets very lengthy. I’ll assume most folks know how to use the Chart wizard to create a new chart in Excel. Here I include a few details on some things that took me many hours to figure out in Microsoft Excel.
Creating a cut-off point
The most difficult part of the CFD was figuring out how to prevent Excel from graphing empty cells and trending the entire chart to zero while in the middle of a sprint. To correct this you must force the X axis to chart all days, and then force the data series to only chart columns with data.
- Right-click the X axis and choose Format Axis.
- On the Scale tab, set the Minimum to the first date in your sprint, and the Maximum to the last date in your sprint. This forces the chart to show all days of the sprint. Click OK.
- From the Chart menu, choose Source Data. On the Series tab, update the Category (X) axis Labels box so that it only graphs days that have data. Click OK.
Changing a data series color
This took me a while to figure out, but basically you right-click the data series area that you want to change and choose Format Data Series. You can change the color on the Patterns tab.
Changing the data series order
If you want to change the order of the data series, right-click any data series area on the graph and choose Format Data Series. Then click the Series Order tab where you can move any data series up or down in the list.
Integrating with Visual Studio Team System
Can you integrate this spreadsheet with Visual Studio Team System? Absolutely! You can use a work item query to retrieve your backlog items for a specific sprint. So all you have to do as ScrumMaster each day is refresh the work item list.
Furthermore, there are two ways you can create the CFD. One option is to bind the CFD chart in Microsoft Excel to the Team Foundation Server data warehouse. Then you simply refresh the CFD each day.
The second option is to create the CFD as a Team Foundation report. This makes the CFD more visible because it would be accessible through the team’s project portal.
Cumulative flow diagrams are a great way to track work in progress and improve visibility of the work. I hope you find this spreadsheet CFD sample useful as a reference for whatever problem you are solving.