Excel offers a set of functions to build from scratch your Kanban metrics based on your historical data. To help practitioners who want a quick and easy solution to try them out, I’ve documented a series of steps I've used to arrange my data and generate my Kanban metrics. To follow along, you can download this sample Excel spreadsheet.
Arrange your data
Before we start using Excel functions, we first need to prepare your data. More precisely, we need to create a spreadsheet with the following columns:
| Title | Description |
|---|---|
| ID | The column of your product backlog item |
| Link (optional) | A URL of the product backlog item in your tracking software (if you have one) |
| Title | The summary or title or name of the product backlog item |
After these first 3 columns, you then add the columns of your workflow. Suppose we have the following workflow:
Then you would have the following columns in the spreadsheet:
| ID | Title | Backlog | To Do | Development | Testing | Deployment | Done |
|---|---|---|---|---|---|---|---|
| 1 | Some name |
As for the values in these columns, you put the date at which your PBI entered each column, leaving it blank for columns it didn’t go into or hasn’t gone into yet.
I then add the following columns:
| Name | Description |
|---|---|
| State | To know which PBIs are done or in progress. |
| Type | So you can have the opportunity to filter on a specific PBI type. |
| Effort | If your Scrum team is pointing your stories, you can add the estimation value here. In another post, I will explain how to compare the effort versus the actual cycle time. |
| Cycle time | We will calculate the cycle time in this column. For work in progress, this column will be blank. |
| Age | For work in progress, we want to see their current age so you can track them. It will be blank for completed PBIs. |
Extracting your data
As you now know how to arrange your data, it is time to extract it. Most Agile project management electronic tools provide an export functionality. In case this export isn't working out for you, check out the GitHub of PaceMkr for extraction tools based on your Agile electronic tool.
Generating your metrics
Throughput
To calculate the weekly throughput of your data, I use a second worksheet where I will list the weeks of the project in a column and the number of completed PBIs in another. In the sample Excel file, it is called Dashboard.
To count the number of PBIs completed per week, I use the COUNTIFS function. In the example spreadsheet referenced in this article, I count the number of completed items based on the date in the Done column.
Under the average throughput orange cell, I calculate the average throughput of the Scrum team.
In this cell, notice how I exclude the 2 weeks during the Holidays. As the team was on vacation for 2 weeks, I've excluded these weeks from my calculation. Please keep in mind weeks you want to exclude throughput. For example, you might have an offsite where team building activities are the main focus of the week. As no work is done on PBIs druing such a week, it can be recommended that you remove it.
Service Level Expectation (SLE)
We then generate the percentiles based on the data that we have in the data worksheet.
To do so, I use the PERCENTILE.EXC function. You basically select the cycle time column and provide the percentile as the second parameter. In the following screen shot from the example Excel file, I show the 50th, 75th, 85th and 90th percentile of my data.
Conclusion
I believe generating Kanban metrics in an Excel Spreadsheet is a first step for anyone who is looking at understanding and adopting these metrics in their Scrum teams. From my own experience, extracting the data from your current tool is the hardest part. Thankfully, Pacemkr has a Github account where you can find tools to do this.
As you discover and value how these metrics can benefit your teams, you can then start looking at more professional tools for a more entreprise adoption.