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.

  1. 2011-11-16 at 20:13

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 360 other followers

%d bloggers like this: