How to visualize the (IMHO) most important metric of the Kanban method

In my humble opinion the Lead Time Distribution is the most important metric in the context of the Kanban method.

Your mileage may vary, but that would be the topic for another post.

A brief mailing list discussion about ways to get this in Excel triggered me to make a list of some of the approaches I use to get to that metric. (Only some of them with Excel)

Content:

• Google Spreadsheet approach
• Hand-rolled Excel
  • Percentiles for given durations
  • Dates for given percentiles
• More versatile hand-rolled approach w/o Excel
• Closing thoughts

Plot and percentiles in Google Spread-Sheet

I would feel remiss not to mention the works of Emily Webber: https://emilywebber.co.uk/a-tool-for-tracking-kanban-projects-that-you-can-cut-out-and-keep/

This gives most of one could want for the evaluation of a physical board.

Unfortunately, it isn't Excel yet, but "only" google-docs. Exporting to Excel "kind of" works, but the most important formatting is rather distorted, so I wouldn't exactly recommend that route to get to Excel.

Maybe it is right for you, maybe not. Give it a shot.

Percentiles per given durations in Excel

I used Excel quite often to generate percentages (not yet percentiles), usually inversely by pointing out the percentile that a given duration falls into.

This leads to heat-maps like the one on the right (from my talk at the 2018 LKCE ) but you don't necessarily get the exact numbers for the percentiles – and it is achieved by a concatenation of manual bin sorting, Summing up the results for each bin and comparing that to the maximum number of occurrences for this type of work.

You can find a simplified example (for only on type of work) in [this Excel-Sheet.](http://www.consulting-guild.de/files/ExampleHeatMap-GeneratedData.xlsx) The raw data is in the first tab, the bin definition in the second and the evaluation in the third. All tabs are named accordingly.

Durations for given percentiles in Excel

Excel already has a built in function called PERCENTILE which yields the mathematically correct percentile over an array of numbers (e.g. lead-times)

For the Emily Webber Data this looks like this: (In the German Version of Excel they translated the names of the functions and hence PERCENTILE is called QUANTIL)

The more flexible approach (without Excel)

Still, sometimes this is not quite what you need and the whole structure of the "chains of Excel spreadsheets" which tends to arise from the Excel-based approach tends to become too brittle (or too solid) after the first two or three improvements-loops.

Nowadays I recommend evaluating Jupyter Notebooks for this kind of scenario. It seems to require programmings skills, since the statistics approach is based on (amongst others) Python, but actually it is quite approachable and most people who are comfortable with complex Excel sheets tend to fall in love with it quickly.

My colleague Martin for example is using this approach for a very flexible evaluation of data collected from physical boards.

It can look like this, but can be easily extended if you need more complex statistics or shapes.

Closing thoughts

IMHO – provided the work-items are coarse grained and the time you want to inspect is not too long – oftentimes it pays to ditch electronic tools in favor of more mechanical approaches as outlined in the talk I mentioned above. (Unfortunately in German)

And for more elaborate mathematical elaborations while still working in Excel I find Troy Magennis Excel-Sheets an extremely helpful ressource.

till next time
  Michael Mahlberg