Cumulative Flow Diagram – How to create one in Excel 2010
Cumulative Flow Diagrams (CFDs) are valuable tools for tracking and forecasting the performance of your processes. In this post I show you how you can create a simple Cumulative Flow Diagram in Microsoft Excel 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.
Let’s get started.
1. Create a new workbook
2. Rename “Sheet1” to “Data”
3. Write “Date” in the first column
4. Write each name of the process states/steps as separate columns in the process order
5. Select all the columns and the row below
6. Open the “Insert tab” in the Excel ribbon
7. Create a table by clicking on the “Table” button on the Excel ribbon
8. Check “My table has headers” and click OK
9. Rename table to “CFDTable”
10. Format the header row to make more useable
11. Add some example data
12. Select the table with all the data
13. Open the “Insert tab” in the Excel ribbon
14. Insert a “Stacked Area” chart from the Excel ribbon
15. Select “Select Data” from the Excel ribbon
16. Reorder all “Legend Entries (Series)” so they are in the reverse order. That means that “Inbox” should be at the bottom and “Deployed” should be at the top
17. Click OK
18. Select the chart and select “Move Chart” in the Excel ribbon
19. Select “New sheet” and name it CFD
20. Click OK
Now you have a cumulative flow diagram! Now you can replace the example data and add your own data. Just add a new row for every tracking period. Remember that the last column is the total number of work in that state. So if you count the number of tickets you have on your kanban board the count of the last state should be the total number of tickets that is in that state, not just the ones that are on the kanban board at this time.
Download example file here: CFD Example.xlsx