Kanban tracking tool charts

A Tool for tracking Kanban projects (that you can cut out and keep)

Reading Time: 3 minutes

I have been looking around for the right digital tool that will support my physical project wall. I want something that allows me easily to back up information for reference, but more importantly outputs the useful metrics that are important for continuous improvement.

A lot of people I know use Trello, which is not bad as a tool that creates a visual representation of the cards, but it doesn’t give me the data I want to be able to plan or improve the team’s flow. I also had a quick look at KanbanTool, which does offer cycle time and cumulative flow diagrams, but again is more of a replacement to a wall rather than a tool to support one and it’s not the prettiest of designs.

My solution was to create my own tool in Google Docs, this is a first pass at something that will do what I need and I’ve started testing it out with my own project already. I’ll change it as I need to as I go.

I’m sharing it here for anyone to take a look under this Creative Commons Attribution-ShareAlike licence (full terms of usage are here, by creating a copy of the speadsheet you are agreeing to these). If you want to use it, you can create your own copy and edit to make it suitable for your needs. You may will need some knowledge of formulas and scripts (ask me if you need some pointers). I’d love to know if you find it helpful and do actually use it for your own project(s).

** disclaimer: this was the original blog post that went with the tool, the tool has changed a bit since then **

You can find it here

How to use it:

Add your work items in the tab called “Work items”

When a card on your physical wall moves into a new column, update the sheet to reflect the date by adding it into the relevant column. (This automatically updates the stage column and the count of cards in each column at the top)

Every time you want to record the data in the current state, go to the “data over time” sheet and press the “backup stages” button (you will want to schedule the script for this to automatically backup if you are using it regularly, I haven’t done this here as the sheet is not in use). This will build your data and feed your graphs over time. Doing this will update the cumulative flow diagram, the WIP / delivery rate chart, the lead time data / curves, the forecasting tool and the process control chart.

To hide done stories from your view on the “Work items” sheet to make it easy to read, use the menu item “kanban scripts” and “hide done items”

Once you have some data, to see how adding stories will change your forecast, change the numbers in the blue boxes on the “forecasting tool”

Adding new dates also updates the Lead Time sheets, which will record how long things take from being committed to, which gives us averages to plan with and charts to review to see how process improvements help (see the lead time charts)

There is also a “blockers” sheet, so you can have a single view of what the current issues are and what has happened in the past.

Updates

To keep this post neat, there is a change log here

Big Thanks to….

Hakan Foss http://hakanforss.wordpress.com/2012/10/31/getting-started-with-collecting-kanban-process-metrics/ for the maths behind the confidence graphs

Dan Brown (aka @kanbandan) for pairing with me on the forecast tool

If you have any questions, please ask

Happy Kanbanning!

22 thoughts on “A Tool for tracking Kanban projects (that you can cut out and keep)

  1. Hi Emily, Your tracking tool has been shared with me … it’s great! I am planning to use it for my team’s reporting and wanted to know how easy it is to add additional columns and would I need to update any of the formulae? Thanks 🙂

    1. Hi Tamsin, yes if you add columns you will need to update the formulas and the scripts to account for the new columns. Let me have a look over it and write some pointers on that, which may happen tomorrow.

  2. Hi Emily

    Great tool, this is what the Kanban community needs, not a replacement of the wall but a complement.

    I´m wondering if you have documented more pointers related to column changes? That will really be useful !!

    Thanks!!!
    Manuel

    1. Hi Manuel,
      there is a fair bit of description within the document itself and some detail in this post. Further than that it will require some knowledge of formulae to be able to update to meet your needs.
      Let me know how you get on
      Emily

  3. Hi Emily! This is a great kanban tool!
    I have a question,
    How would you do if, for instance, in “review” state, a developer is asked to modify some things? which dates should this dev change? I would clear the “review” date, and update the “in progress” date to that day.. then update all graphics. Is this approach correct?

    regards!

    Thanks!!

    1. Hi Nicolas,
      it all depends on how you want to regard this in your own workflow and how you are tracking the work done. The common view (which I have held during projects) is that you don’t move cards backwards, in which case you would need to decide what fixes you would accept under the review column and when that is a bigger change and needs a new card.

    1. Hi Tom,
      I need a bit more information from you to understand what the problem is.
      When you say “get this spreadsheet” what exactly are you trying to do? Can you walk through it step by step and what errors do you get.
      Thanks

  4. Hi Emily,

    I have already made a copy of your spreadsheet and it is brilliant.
    The only question I have for you is about the Projected Dates column. Apparently it is not updated automatically, and if I have to fill it up manually (honestly) I don’t know which day to put there. As I’m leading an Operations kanban board, we don’t have a projected finish date.
    Could you give me your opinion on this?
    Thanks

    1. Hi Eugenia, If you are referring to the data over time sheet, the projected dates column shouldn’t need updating, it’s there to help understand what number of stories might be delivered on that day in the future and when it might end based on current data. It then populates the graph on the forecasting tool sheet. The column title might be misleading and should probably just be “date”. This column has a formula in it in the original spreadsheet to calculate weekdays.
      This whole sheet shouldn’t need anything to be added manually as long as the script is set up to run every week day.

      1. Hi Emily, thanks for your quick answer.
        In my case, the value in cell G7 stays fixed as “19 May 14” that is the value you have in your template. That’s why I asked you if I needed to update it manually.
        The rest of the values on that column have a formula as you explained above.
        If my project started on the 1st of Jan, which should be the value on G7?

        Thanks!

        1. Ah yes that’s right, actually you can update G7 to contain “=A7”. Then as long as you are recording data every weekday then the dates in column G should match the dates in column A.

Leave a Reply

Your email address will not be published. Required fields are marked *