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.
Update
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
Done!
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
Hakan Forss's Blog 
Very helpful, thanks!
Thanks, helpful.
Sudipta.
Hey Håkan – cool that you’ve came around to post this. Had miss it but we surely use it now.
Thanks
Hi Håkan,
Great work capturing this! During our Agile Denver Kanban SIG mtg on 8/11, we discussed CFDs and referred to your post. I’m looking forward to reviewing your site a bit more for other nuggets.
Take care,
Frank
Thank you Frank!
I’m glad you found it useful and good luck with the Book Study Group.
/Håkan Forss
realy nice post – very useful.
How do you show progress of user strories – separate view – Functional V/s Non Functional?
CFDs are mainly for analyzing the flow in the process not tracking individual user stories. One solution you can use a pivot table as your data source. For every measuring point (usually once a day) you add all user stories to the pivot table and what state it is in. This will create lots of more data but you will be able to track individual user stories.
/ Håkan
Great help, thanks. I have an issue with my graph, though. The graph includes the zero value after the latest messaure point. How do i avoid including the zero points so i can present a graph every day without making the curve ending up at zero the day after?
BR
Patrik
Hi Patrik,
Make sure the last row of the data table contains values and you shall be okay. If you want the dates on the x-axis to extend beyond the dates in the data you can change the x-axis scale from auto to a fixed date. If this does not work let me know and I can take a look at your Excel document.
/Håkan
This chart is just plain wrong! Don’t use it!!! You cannot use stacked area chart. To see how to create a correct CFD chart see: http://leadinganswers.typepad.com/leading_answers/2006/09/creating_and_in.html. If you use the chart in this page your analysis will be wrong as well as the average lead time calculus.
Hi Jose,
Thank you for your comment. To my knowledge the only difference between an area diagram and stacked area diagram is how the plotted data is set up.
In the references blog post by Mike Griffiths, great post by the way, he has set up his data to be the total amount of work in a process state and all steps down stream.
In my example the data represent just the amount of work in a specific process state. It will only be the last process state that should grow over time, or you have a problem in you process
I downloaded Mike Griffiths spread sheet and made a stacked area diagram on the same data after I have modified the data to only show the actual amount of work in each process state. There is no difference in the charts. See here: http://dl.dropbox.com/u/19243273/Area_vs_Stacked_Area_cfd_example.xls
/Håkan Forss
Thanks. I needed to set one of these up in a hurry and I’d forgotten how I did it before.
Very nice Hakan. Thanks a lot.
You are welcome and thank you for allowing me to listen in on your Toyota Kata sessions with Bill
/Håkan
Very helpful, thank you
Mr Hakan, Thank you for the detailed explanation.
Great intro, thanks.
Though in Excel 2011 its a little more work to rearrange the order of the series – I found I had to update each of the series’ label and data forumlas. The UI doesn’t allow for the simple rearranging as in 2010!
Thanks – quick question I hope: I created a CFD per these instructions but I have now found over time, my completed items is overshadowing all other queues. How does one limit or reduce the count of completed items over time in a consistent, logical fashion? Perhaps I expose a
Hi Mark,
Not sure I understand your question and your comment looks to have been cut short for some reason. Is your problem that as the completed items is growing it becomes harder and harder to see the changes in the other parts of the diagram? The simplest way to address this would be to limit the time you are showing on the diagram. How mush of the historical data do you need to show to be able to manage your work today? You should of cause keep all the historical data but you can limit how much get show on the diagram. The easiest way to do this is to adjust the min and max values of the y and x axis of the diagram.
Hakan
I have used an excel to track the CFD’s in past. But I am also using a Kanban board to draw the CFD diagram. We have 2 kanban boards. One with swim lanes and index cards where we are moving the work items and another dedicated for a CFD. Its just starting so currently I am doing daily progress on the X axis and work items on Y. I plan to change the X axis to weekly numbers after couple weeks because I will run out of space on X-axis. I still feel like keeping the counts right on physical board specially the WIP count will be challenging. The reason I say that is as you see the WIP items move to Complete, human tendency is to drop the the WIP number accordingly not realizing its a Cumulative diagram and it can only grow up or stay constant. Also keeping a track of all the index cards on the board once they start moving to done will be tough. Any thoughts? Anybody tried to draw the CFD on a physical board?
Hi Saif,
I’m all for drawing the CFD by hand. When you actually draw the numbers by hand your brain can reflect on what the new numbers really mean. Pulling a report out of Jira, LeanKit or any other electronic tool you very easily miss out on the opportunity to really reflect on the change. See Change blindness
Can you track the last xx days on using the daily scale. Then add a second chart that will have a longer time horizon?
I have used an envelope or a box for collecting older cards that are done. When you have XX finished cards you have a celebration of all the great things you have done. You then place them in the envelope or box for safe keeping.
Good luck with your CFDs.
Thanks Hakan.
Thanks for the tutorial. I’m a mac user and the mac version of Microsoft Excel can select cells and create formulas by dragging.
Hakan,
This is Saif again. As mentioned before I have used the physical board to draw both a daily CFD that spans for two weeks and starts all over again at the end of the two. And a weekly CFD that has been growing. My questions are:
1) Do you include weekends and holidays in CFD and Cycle time calculation or only business days?
2)Cycle time for each item will be different (with in a range). For estimation / discussions / reporting purpose, which one cycle time do you use or average it out? If you use an average, then what work items do you consider (all, items in last x period, last x number of items)?
useful thanks