Home > Kanban > Cumulative Flow Diagram – How to create one in Excel 2010

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.

CFDDone

Let’s get started.

1. Create a new workbook

2. Rename “Sheet1” to “Data”

NewWorkbook

3. Write “Date” in the first column

4. Write each name of the process states/steps as separate columns in the process order

AddColumns

5. Select all the columns and the row below

6. Open the “Insert tab” in the Excel ribbon

SelectColumnsAndSwitchToInsertTab

7. Create a table by clicking on the “Table” button on the Excel ribbon

8. Check “My table has headers” and click OK

CreateTable

9. Rename table to “CFDTable”

10. Format the header row to make more useable

RenameTableAndFormat

11. Add some example data

12. Select the table with all the data

13. Open the “Insert tab” in the Excel ribbon

SelectTheTableWithData

14. Insert a “Stacked Area” chart from the Excel ribbon

StackedAreaDiagramInserted

15. Select “Select Data” from the Excel ribbon

SelectDataSource

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

SelectDataSourceLegentEntriesReorderd 

17. Click OK

18. Select the chart and select “Move Chart” in the Excel ribbon

19. Select “New sheet” and name it CFD

MoveChart

20. Click OK

CFDDone

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

About these ads
  1. Mats Alm
    2011-06-17 at 11:08

    Very helpful, thanks!

  2. 2011-06-20 at 10:39

    Thanks, helpful.

    Sudipta.

  3. 2011-07-19 at 17:04

    Hey Håkan – cool that you’ve came around to post this. Had miss it but we surely use it now.

    Thanks

  4. Frank Vega
    2011-08-14 at 15:39

    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

    • 2011-08-14 at 22:18

      Thank you Frank!
      I’m glad you found it useful and good luck with the Book Study Group.

      /Håkan Forss

  5. Rashmi
    2011-11-13 at 07:02

    realy nice post – very useful.
    How do you show progress of user strories – separate view – Functional V/s Non Functional?

    • 2011-11-14 at 13:16

      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

  6. Patrik Möberg
    2012-02-06 at 16:35

    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

    • 2012-02-07 at 00:03

      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

  7. Jose Neto
    2012-04-09 at 06:37

    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.

    • 2012-04-09 at 09:49

      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

  8. 2012-05-02 at 12:14

    Thanks. I needed to set one of these up in a hurry and I’d forgotten how I did it before.

  9. 2012-06-05 at 16:06

    Very nice Hakan. Thanks a lot.

    • 2012-06-08 at 12:09

      You are welcome and thank you for allowing me to listen in on your Toyota Kata sessions with Bill

      /Håkan

  10. Ann
    2013-02-01 at 14:19

    Very helpful, thank you

  11. 2013-05-19 at 22:53

    Mr Hakan, Thank you for the detailed explanation.

  12. 2013-06-14 at 06:44

    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!

  13. Mark
    2014-01-08 at 22:20

    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

    • 2014-01-09 at 03:58

      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.

  14. Saif
    2014-11-11 at 21:43

    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?

    • 2014-11-12 at 00:41

      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.

      • Saif
        2014-11-12 at 21:10

        Thanks Hakan.

  15. 2015-01-13 at 07:19

    Thanks for the tutorial. I’m a mac user and the mac version of Microsoft Excel can select cells and create formulas by dragging.

  16. Saif
    2015-04-07 at 20:05

    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)?

  1. 2011-11-16 at 20:13
  2. 2014-09-26 at 15:42
  3. 2015-03-03 at 15:52
  4. 2015-05-21 at 20:28

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,513 other followers

%d bloggers like this: