Cumulative Flow Diagrams with Google Spreadsheets

Jan 12 2010: UPDATE – a new version of the spreadsheet with bugfixes is here.

Let’s face it: Most project stakeholders care more about time to market than preposterous “agile” metrics such as velocity and burndown charts. Teams that only use these tools to report progress completely conceal information about how quickly a team can deliver new features. This is because velocity and burndown charts ignore the time to market aspect and provide zero quantitative information about how to improve it.

A Cumulative Flow Diagram (CFD) is a visual tool that communicates a team’s ability to deliver working software in a timely manner, showing a detailed picture of the entire process. Its primary purpose is to improve the current process, and not to predict the future (although it can be used for that too).

If you know Scrum, think of a CFD as a burndown chart that goes beyond showing when work items (user stories, MMFs or even tasks) are moved to the last column on the story board. A CFD shows when work items are moved in all the columns of your story board, making it painfully visible where there are bottlenecks. -And more importantly – the devastating effect unfinished work in progress has on a team’s ability to deliver working functionality fast.

The knowledge you get from a CFD can be used to improve the process so the team can deliver software faster. It lets you make informed decisions about what to fix instead of the dim-witted “add more people” that happens on many projects.

In this article I will show you how to interpret a CFD and how you can create one with very little effort. As a bonus you’ll get some additional charts that may also be useful to you. You’ll be using Google Spreadsheets for all of this. And best of all – you can use this tool regardless of what kind of process you’re using today, whether it’s iterative, incremental, waterfall, cowboy or whac-a-mole. A CFD doesn’t change your process, it just provides you with disgnostics about it.

Are you ready? Let’s start with a little queueing theory.

Lead time

The term lead time (LT) is just a fancy way to say time to market. It’s essentially the time that elapses (on the calendar) from something is ordered until it is received. In software development, a feature is ordered when someone (like the product owner) asks someone else (the team) to implement it. The same feature is received when it is deployed to the production system and ready to be used by end users.

Many teams don’t measure lead time, and as a result, don’t know how to reduce it. A CFD shows you what your lead time is and also gives you hints about what you can do to shorten it. Let’s look at a sample CFD:

The first thing you need to know about a CFD is how to read the lead time. At any point in time (the X axis), the lead time is the horizontal distance between the top left area and the bottom right one.

For example, in week 18 the lead time was 13 weeks. Just find the Y point on the Specify line (82 items), draw a horizontal line forward in time until you hit the Deployed line (week 31). The length of this line is the lead time. This means that in week 18 the average time to market for a single feature was 13 weeks.

Note that starting from week 33 we don’t yet know what the lead time is – since the Deployed line hasn’t yet reached the same level.

Work in progress

The Y axis on a CFD is the number of work items (typically user stories) at any given station. (Estimates are not interesting on a CFD, we just count how many work items we have). The CFD is a stacked area chart where each area represents a station in the development process. Some people like to think of it as a story board tilted on the side and dragged out over time.

Nov 3 edit : I wasn’t comparing the right interval.

During the 13 weeks between week 18 and week 31 the team deployed 69 new features. This is an average throughput rate of 5.3 features per week (69 features/13 weeks). The inverse of throughput is called cycle time, and that was 0.19 weeks per feature – or approximately 1 day per feature. Despite this the average lead time for 1 feature is 13 weeks. How come the lead time is so much longer than the cycle time?

By week 18 we had deployed 24 features to our production system. Not bad! On average we delivered 2 features per week between week 6 and week 12, which means we had a velocity (using Scrum terms) of 2 features/week. This does not mean that it takes 1/2 week to deliver a single feature. The lead time is 13 weeks even if a single feature pops out every half week. How come?

There is another factor that comes into play here, and that’s work in progress, commonly called WIP. The relationship between lead time, WIP and arrival rate can be explained with Little’s law. Translated to our terminology this can be expressed as follows:

WIP = Arrival Rate * Lead Time

Let’s assume that we want to keep the arrival rate constant, i.e. how many items are moved from Backlog to Specify per week. How can we reduce the lead time so that we get a shorter time to market? It should be obvious from Little’s law (and from the CFD) that reducing WIP will also reduce the lead time.

On the CFD, the WIP at any given moment is the height between the top left area (Backlog) and the bottom right one (Deployed). WIP can only effectively be reduced if the team agrees to never work on more than a certain limited number of items at any given time. Kanban, CONWIP and Scrum-ban are similar techniques to achieve this. I won’t go into detail about these techniques in this article. Instead, I’ll give you a very simple tool that you can use to create a CFD that you can use to communicte visually where in the process you have too much WIP, and how it (negatively) impacts lead time.

After using this for a couple of weeks I’m sure your team will be discussing how to reduce WIP and maybe head over to Limited WIP Society to learn more.

Google Spreadsheets

Once a week (or at a shorter or longer interval if you want) you’ll count the number of items in each station (including the backlog station and the deployed station – i.e. items that are completely done). Then you’ll enter a new row in the spreadsheet with these values. That’s it. The charts that are embedded in the spreadsheet will update automatically, and you can easily export them if you want. If it was harder than this you wouldn’t do it. Boring tasks should be simple.

In order to make the wonders of CFD easily available for as many as possible I have created a template CFD Google Spreadsheet document that you can copy and use in your own project. The document consists of 6 sheets. I’ll explain what they are.

Input sheet

This is where you enter numbers at these regular intervals.

The first column in this sheet is your timeline. It’s entirely up to you how granular you want to make this. I went with a week. Some teams may want to use days, other 2 weeks. If you follow some kind of iterative development process I recommend you chose an interval that is 20-25% of your iteration length.

After the timeline column there is one column for each station in your value stream, workflow, story wall or whatever you call it. For technical reasons with Google Spreadsheets, the columns must be listed from right to left, having the earliest column to the right and the last one to the left.

The remaining columns are calculated and used for the charts. Don’t edit these. There are also some hidden columns for intermediate results. Don’t be scared about the formulas – they are rather simple.

CFD sheet

The 2nd sheet in the document is your CFD. I already explained what this is.

WIP sheet

The 3rd sheet shows WIP over time. In the example we start with little WIP, then it increases. Then the team decides to lower the WIP (maybe they read this article), and we see that it goes down over time. This directly affects the lead time.

Lead Time sheet

The 4th sheet shows lead time (time to market) over time. Notice how the team has reduced the lead time from 20 weeks(!) to 5 weeks just by reducing WIP.

WIP and Lead Time sheet

The 5th sheet shows WIP and Lead time together. It is interesting to observe how the shapes of the WIP chart and the Lead Time chart are similar.

WIP and Lead Time don’t have the same units, which explains why they don’t have the same height, but you can easily see that they are somewhat proportional in shape. Use it to convince the sceptics that juggling fewer balls means quicker delivery.

Throughput sheet

The 6th sheet shows throughput over time. This is roughly the same as velocity when all the work items have the same size. (Velocity accounts for varying size, throughput does not).

Throughput is the rate at which work items leave our process. In our example it can be measured in deployed work items per week. In our example you can see that it increases until week 20 and then decreases again. An increasing throughput can mean several things, for example that the work items have become smaller, or that the team is working more efficiently. It’s not the most interesting metric.

Creating your own copy

You can create a copy of the Google Spreadsheet with File->Make a copy… This will copy all the data, formulas and charts to your own spreadsheet that you can protect and share with whomever you want.

I’m sure your project has a different workflow than mine, so you want to add, remove or rename some of the columns. If you do this, just make sure that the formula in the WIP column adds up all the columns between the first and last station. This should be adjusted automatically, but check it to be sure. I also recommend you double-check the datasets for the charts when you modify things.

Adding/removing rows

When you start up with a fresh copy of this sheet you may not have historical data to fill in – only the status quo. If your chart looks very wide without data, just delete some rows and update the range of all of the charts. (On each chart sheet you can Edit Chart).

Likewise, when you add rows, you have to manually adjust the range in the charts. You could let the charts’ ranges go from the start until eternity so you don’t have to do this, but this would result in very narrow charts with nothing on the right.

Conclusion

Teams that have achieved a rapid, smooth flow don’t really need CFDs. This is because the only way to achieve rapid smooth flow is by actively limiting WIP. And a CFD with small WIP limits looks really boring – the whole WIP section is just a narrow, straight band. However, if your project is struggling with long time to market (or if you don’t even know what your time to market is), it is a wonderful tool to visualise where the process needs to improve. Put the CFD up on a monitor in your project room for everyone to see – maybe with a little explanation about how to interpret it. WIP is the vertical and time to market is the horizontal. The latter can only be shortened by reducing the former.

I also hope you will consider dropping your traditional velocity and burndown charts altogether. A CFD contains all of the same information – plus more. The interesting things to observe about a project isn’t the last column on your board, but all of the columns before it. This is where you can optimise the flow, and that’s what you should observe.

Good luck!

Credits

Thanks to Olav Maassen for help with lead time calculation in Google Spreadsheets and pointing out some wrong conclusions in the first draft.

Thanks to Kristoffer Dyrkorn for pointing out some unclear formulations and encouraging me to focus on why people should consider using CFDs.

And finally to everybody on the Kanban Development Group for great discussions in general.