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 **
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!
16 September 2013 at 9:01 pm
Hi Emily, great post, thanks.
We started using Kanban at the start of the year and started using a CFD based on instructions by Hakan Forss (see http://hakanforss.wordpress.com/). However, he also has guides of how to create a control chart and histogram which give some interesting output. Give me a tweet at @bigpinots if you want to discuss further…
17 September 2013 at 7:05 am
Hi David,
thanks for the link, I’ll take a look, I love the data side of kanban.
27 June 2014 at 12:46 pm
I’ve done some changes to this to get it in the new google docs format, this includes trying my hand at translating Hakan Forss excel histogram to work in this context, take a look https://docs.google.com/spreadsheets/d/1vArF4tuzKDLlrs99bd5k2RmEAebA4nb6sY3Y5tqW2WQ/edit?usp=sharing
7 August 2014 at 1:35 pm
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 🙂
7 August 2014 at 2:14 pm
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.
7 August 2014 at 5:08 pm
Hi Tamsin, I have added some description above. Let me know how you get on
8 August 2014 at 2:13 pm
Brilliant! Thanks Emily. I shall take a look now and let you know 🙂
10 December 2014 at 11:33 am
I’ve spent last two days customizing the tool for my workflow, and now it’s good to go! Thank you a lot for sharing it with everybody, it’s amazing!
10 December 2014 at 11:52 am
Great, it would be good to know how you get on with it after using it for a bit.
12 March 2015 at 4:55 pm
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
13 March 2015 at 11:08 am
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
23 April 2015 at 7:39 pm
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!!
11 May 2015 at 10:19 am
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.
16 May 2015 at 9:16 am
Hi Emily,
Thanks for creating, sharing, and maintaining this great tool!
Cheers,
G
7 March 2016 at 6:06 pm
Emily, I get an error trying to get this spreadsheet. Something about a certificate.
7 March 2016 at 6:32 pm
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
27 January 2017 at 1:47 pm
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
27 January 2017 at 2:07 pm
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.
27 January 2017 at 3:41 pm
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!
28 January 2017 at 8:41 am
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.
10 November 2017 at 11:22 am
I have introduced kanban systems to the DevOps teams at Elsevier. We are using both physical boards and reflecting those electronically in JIRA so that we can automatically track the timestamps as it moves through the workflow. We then create an excel extract out of JIRA and enter the numbers into your workbook.
Thanks for making this open source Emily. It’s awesome!
18 November 2017 at 12:00 pm
Great stuff, glad to hear it’s working for you
3 December 2018 at 1:59 pm
Hi Emily,
Tired to use the Kanban Tracker but when I click on the backup stages but it doesn’t do anything and there doesn’t seem to be a script behind it.
Any ideas where I can get the script?
Barry
3 December 2018 at 2:31 pm
Hi Barry,
can you share the link to your version so I can take a look – you can use the contact form here https://emilywebber.co.uk/about-me/
Emily
2 April 2019 at 9:43 pm
AMAZING!!! Thank you so much for sharing! It’s Genius!!!
29 April 2019 at 6:49 pm
Hi Emily, this is a great sheet! I love it. I’m unable to run the backup stages script on the “data over time” sheet, just due to network and access restrictions with my company. Is it possible to get a source view of the script so I can reproduce or perhaps another way to populate those columns?
30 April 2019 at 5:40 am
Hi Lola,
I’m not sure what restrictions you have, but I would expect you to be able to see the scripts in the copy you took of the sheet. Essentially the script takes cells A3:F3 in “data over time” and pastes them in the next available cells in the same row in the table below, something you should be able to easily replicate manually.
Emily