Cumulative Flow Diagrams (CFDs) are valuable tools for tracking and forecasting the performance of your processes. In this post I will show you how you can create a Cumulative Flow Diagram in Excel that fetches the data from Team Foundation Server 2010 (TFS 2010).
As been pointed out on the KanbanDev mailing list by Daniel Vacanti the approach of counting items per process stage is a simplified way to construct CFDs. Daniel points in his book Actionable Agile Metrics for Predictability out that this approach only works when the following two conditions are satisfied:
- Work items are not moved backward in your workflow.
- Work items are never completely removed before they are completed.
Keep this in mind if you use this approach.
In this example I ‘m using a modified MSF for Agile Software Development v5.0 template. I have added the following states to the User Story work item workflow:
- Analysis Done
- Development Done
Let’s get started.
1. Open a new Excel worksheet
2. Open the “Team” tab in the Excel ribbon
3. Create a new report by clicking on the “New Report” button on the Excel ribbon
4. Select your “Team Project” and click the “Connect” button
5. Select a Work Item Query to generate your report from. In our case what query you select doesn’t really matter but I select the “Product Backlog” query as this closes to what we will be using in the Cumulative Flow Diagram.
6. Deselect all reports
7. Expand the “Trend Reports” and “Work Item Count” tree nodes. Check the “State” report
8. Click the “Finish” button
9. Select the “1.1 State Trend” sheet.
You will now se the default trend report generated by TFS. The generated report are using weeks for the rows(horizontal axis). I usually use one date for each row. Lets change to using dates for each row.
10. Remove the “Year, Week, Date” from the Axis Fields.
11. Add the “Date” field to the Axis Fields
Now the chart will show all the dates that have values in the data warehouse. I ‘m only interested in the last few dates. Lets filter the date rows.
12. Filter the date rows by selecting “Last week” from the “Date Filters”
13. Now deselect the states that you don’t want to be part of the diagram.
Now we come to the most important part to make the Cumulative Flow Diagram work. Change the order of the state columns to be in the reverse order. That means that the last state of the process should be first and vise versa.
14. For the first state column (in our example this is the Active state column), right click and use the move column and move it’s new position.
Repeat this for every state column and you should end up with something like this:
15. Now make style changes to the diagram and the result may look something like this:
Note: You have to refresh the diagram to show updated data in the data warehouse. The data warehouse updates on a regular basis and the default update interval is 7200 sec.