# How to Use AAMfP

## Spreadsheet composition
The whole spreasheet, in LibreOffice Calc format, contains these sheets:

* [Notes](#Notes)
* [WorkItems](#WorkItems)
* [CFD](#CFD)
* [Lead Time](#Lead%20Time)
* [Little's Law](#Little's Law)
* [Metrics](#Metrics)
* [Distribution](#Distribution)
* [Data](#Data)

and it can be modified and used following the [Attribution-ShareAlike
4.0 International (CC BY-SA
4.0)](https://creativecommons.org/licenses/by-sa/4.0/) license and
criteria.

For daily normal use, [WorkItems](#WorkItems) should be the only sheet
you need to work on, while all other sheets are needed to read, report
and visualize metrics. There are no hidden sheets nor password-protected
cells. The whole spreadsheet is completely available for you, following
the license terms.

Please note that [WorkItems](#WorkItems) sheet already contains some
data. Those are sample data to show you how to use and write them. Feel
free to remove (or orverwrite) them.

## Notes
The [Notes](#Notes) sheet is just a sort of cover page for the whole
spreadsheet with licensing note and a minimal set of usage instructions,
usually the same you see in the
[Usage](https://bitbucket.org/marcobresciani/aamfp/src/tip/README.md?fileviewer=file-view-default#Usage)
section of
[README.md](https://bitbucket.org/marcobresciani/aamfp/src/tip/README.md)
file associated to it, in this site. It also contains the version number
of the whole spreadsheet, for reference.

## WorkItems
For daily usage purposes, [WorkItems](#WorkItems) is the only sheet you
need to work on and modify, by adding your own work items tracking.

The whole [WorkItems](#WorkItems) sheet is compatible with [Actionable
Agile™ Analytics](https://www.actionableagile.com/) tool (see [Format
for Data File](https://www.actionableagile.com/format-data-file/)):
simply export [WorkItems](#WorkItems) sheet as CSV (select comma
(**,**) as cell separator and quotes (**"**) for fields) and then import
it in the tool.

As described in the [Format for Data
File](https://www.actionableagile.com/format-data-file/) page, The first
row contains the column names for each of the data fields.

* The first column is an item **ID** field.
* The second column is optional, and it's the **Link** to the item
  location in your online Kanban/Agile/PM tool, if any.
* The third column is optional (but I strongly suggest to use it), and
  it's the item **Name** (or title or description) field.

The next set of contiguous columns are the sequential steps of the
workflow. Currently, due to the basics of Personal Kanban concepts, they
have been set to **Backlog** (optional, as described above, but I
suggest to use it), **Next**, **In-progress** and **Done**. The contents
of these columns are the date the item entered each workflow step. For
standardization purposes, the dates are displayed in ISO8601 date format
(year, four digits; `-`; month, two digits; `-`; day, two digits:
`yyyy-mm-dd`) but can be entered in your local format (e.g.:
`dd/mm/yyyy` or whatever), as configured in LibreOffice Calc.

These above are the main mandatory columns for metrics. There are also
optional columns that may help identifying work items and their
information:

* **CoS** is the Class of Service of the work item and it should help
  differentiate items based on their Cost of Delay. Each class of
  service has a specific execution policy that the team agrees to
  follow. Those following are the basic standard classes of service
  defined in [Kanban](https://leankanban.com/shop/kanban-book/) book and
  predefined as available in this sheet. If you need other classes of
  service, feel free to modify the sheet according to the license above.
  Traditional Classes of Service can be described as:

    * *Expedite*, has an unacceptable cost of delay and therefore
      requires immediate attention. It can be pulled into development,
      even in violation of current WIP limits (even if it shouldn't
      be…). Typically, there can be only one expedite item in the system
      at a time, and teams may set a policy to swarm on that item to
      make sure it moves through the system rapidly.
    * *Fixed date*, describes work items that must be delivered on or
      before a specific date. Typically, the cost of delay of these
      items is nonlinear and is highly sensitive to small changes in the
      delivery date; these must be actively managed to mitigate the
      schedule risk. Therefore, these items are pulled into development
      when necessary to be finished on time. Some items may require
      additional analysis to refine the expected lead time. Some need to
      be reclassified as expedite if the team falls behind.
    * *Standard*, represents the baseline class of service, applicable
      to work items that are neither expedited nor fixed date. Most
      backlog items should fall into this category. The cost of delay is
      linear for standard items, meaning that value cannot be achieved
      until delivery occurs, but there's no fixed-date requirement.
    * *Intangible* is used for work that goes through the system that
      doesn't directly deliver value to the customer. This is work that
      needs to be done - but for which it is hard to show a return on
      investment. It is a good idea to have some Intangible work going
      through the system. It is better to set this aside when an
      Expedite comes through then work with an associated due date or
      cycle time expectation.

* **WIT** is the type of the work item (Work Item Type) and it should
  help categorize items based on your ideas, needs and concepts. Each
  work item type is specific for the person or team or group that define
  it and it has a specific meaning. Some examples can be: "work",
  "home", "children", "taxes" or whatever category or main argument you
  may want to use to aggregate items information and types. There are no
  pre-defined WITs in this sheet.

* **Deadline** is usually used for Fixed Date CoS only and it represents
  the date before which the item shall be completed. The column value is
  anyway optional, even for Fixed Date CoS. The content is automatically
  formatted to ISO8601 standard as described above and conditionally
  formatted with the following rules:
   * today, red background cell;
   * this week, orange background cell;
   * this month, yellow background cell;
   * next month, green background cell;
   * expired completed work item, red text.

* **Blocked Days** represents the total number of days when the work
  item was blocked due to external dependencies or events or any reason
  for which the item itself was not available to work on. In order to be
  properly evaluated, it shall be computed as the difference between two
  dates, plus one. That is: if a work item has been blocked today only,
  the Blocked Days column shall count 1 while if the work item has been
  blocked yesterday and today, the column shall count 2, etc. It is
  obviously possible to add multiple blockers, even through formulas,
  by simply adding the intervals and rembering to add 1 for each:
  `=date(2019,8,15)-date(2019,8,12)+1+today(2019,3,5)-date(2019,3,3)+1`
  that represents a blocker from 2019-08-12 to 2019-08-15 (4 days) and
  another blocker from 2019-03-03 to 2019-03-05 (3 days), for a total of
  7 blocked days.

* **Deleted** is the column that contains the date when a work item has
  been considered discarded (while in Backlog) or the work is aborted
  (while in Next or In-progress) and no more bringing value to the flow.
  
  *While bug [#6](https://bitbucket.org/marcobresciani/aamfp/issues/6/)
  is open*, when a work item has to be discarded or is aborted, you have
  to write such cancellation date in all remaining columns cells and
  also write the same date in the **Deleted** column. With this, the
  work item will be properly identified as deleted.

* The **Labels** column may contain a set of strings and text
  identifiers (labels) that can be used to further filter, categorize or
  tag the work items in order to better identify and collect their
  metrics. As shown in the example data of [Actionable Agile™
  Analytics](https://www.actionableagile.com/) tool, a single label can
  be written as simply as `A` while possible multiple labels can be
  represented as `[A|D|Y]`. The tool will take care of using them for
  filtering purposes.

All those columns above are properly managed by the [Actionable Agile™
Analytics](https://www.actionableagile.com/) tool as attributes that can
be filtered and grouped. Filtering by **CoS** or **WIT** or other
columns is not yet supported in this spreadsheet.

> In order to have correct metrics, chart, and data *you* have to keep
the [WorkItems](#WorkItems) sheet rows in the correct order.
Specifically, select the whole [WorkItems](#WorkItems) sheet table and
apply the following sorting criteria (all crescent order): **Done**,
**In-progress**, **Next**, **Deadline**, **Backlog**, **ID**. Once the
column are ordered in this way, you can scroll all other sheets where
you can find the metrics and charts.

## CFD
The [CFD](#CFD) dashboard sheet has the name tab highlighted in green
colour to show that it does not contain nor compute data but represents
them in charts. It contains some major metrics charts such as:
* *Cumulative Flow Diagram* (CFD) that shows:
  * a gray line showing the cumulative total number of work items that
  were created (**Backlog**);
  * a red line showing the cumulative total number of work items that
  has been commited (moved to **Next** column);
  * a yellow line showing the cumulative total number of work items that
  has been worked on (moved to **In-progress** column);
  * a green line showing the cumulative total number of work items that
  has been completed or cancelled (moved to **Done** column).

  The horizontal axis is a timeline and each work item is marked on the
  date of status update. The vertical axis represents the number of
  work items for such status on the reference date. This chart is a
  representation of the ongoing status of the service flow.

  This chart contains data rows till spreadsheet row number 3651, that
  is enough to run the board for ten years. 
  
  For a detailed description of the behaviours and issues you can find
  with a CFD, take a look at this relevant and interesting article by
  Pawel Brodzinski on his blog: [Cumulative Flow
  Diagram](http://brodzinski.com/2013/07/cumulative-flow-diagram.html).
* The *Daily Run Chart*, is the run chart for the daily values of
  **Backlog** (gray, number of work items waiting), **DR** or Delivery
  Rate (light green, number of daily completed work items),
  **Throughput** (green, number of daily done (completed+discarded) work
  items) and **WIP** (orange, number of ongoing work items).

  This chart contains data rows till spreadsheet row number 3651, that
  is enough to run the board for ten years. 
* The *Backlog Age Distribution* is the distribution chart representing
  the age of all work items that has been created, regardless they are
  still in *Backlog*, they are in progress or already completed or
  discarded. This resemble the distribution of your Customer Lead Time. 
* The *CoS Ratio* chart shows the Classes of Services ratio with respect
  to the total number of work items. It uses the four standard Classes
  of Services, and the colours are those defined as examples in
  [Kanban](https://leankanban.com/shop/kanban-book/) book. 
* The *Flow Efficiency* is the run chart for Flow Efficiency with its
  moving average from the **FE** and **mFE** columns in
  [Metrics](#Metrics) sheet.

  This chart contains data rows till spreadsheet row number 3651, that
  is enough to run the board for ten years. 

## Lead Time
The [Lead Time](#Lead%20Time) dashboard sheet has the name tab highlighted
in green colour to show that it does not contain nor compute data but
represents them in charts. It contains some charts related to Lead Time
metric such as:
* the *Lead Times Scatterplot* chart of completed and ongoing
work items, starting from the earliest to the current date. The scatter
plot shows:
  * a blue dot for each "Done" work item with the value of its
    corresponding **LT** (Lead%20Time);
  * a red upward pointing triangle for each work item still in progress,
    with the value of its current **Age** (number of days since it
    entered **Next** state);
  * a red dotted line representing the moving average of the **95th
    percentile** of the **LT** values;
  * an orange dotted line representing the moving average of the **85th
    percentile** of the **LT** values;
  * a yellow dotted line representing the moving average of the **70th
    percentile** of the **LT** values;
  * a green dotted line representing the moving average of the **50th
    percentile** of the **LT** values;
  * a blue dotted line representing the moving average of the **LT**
    values themselves (**mLT**).

  The horizontal axis is a timeline and each work item is marked on the
  completion (**Done**) date. The vertical axis represents the number of
  days: lead time of completed work items goes from the committment
  point (when the work item enters the **Next** column) to the **Done**
  status (either completion or discard); age of ongoing work items goes
  from the committment point.

  This chart is a representation of how long (**LT**) it takes to get
  things done for individual work items on your Kanban board and the
  trends the **LT** has.

  This chart contains data rows till spreadsheet row number 3651, that
  is enough to run the board for ten years. 

  Possible uses for scatterplots are:
  * visualize clearly the lead times of the work items, possibly
    filtering them according to CoS or WIT;
  * compare different WITs lead times;
  * forecast the lead time of future work items.

  For a detailed description of the behaviours and ways to read a lead
  time scatterplot, take a look at this article by
  [Kanbanize](https://kanbanize.com/): [Using Scatter Plot to Measure
  and Forecast Cycle
  Time](https://kanbanize.com/kanban-resources/kanban-analytics/cycle-time-scatter-plot/)
  with further details on [Meet the Cycle Time Scatterplot from Premium
  Analytics](https://kanbanize.com/blog/premium-cycle-time-scatterplot/).
* The *Lead Times Distribution* is the chart that represents the
  bin-constrained frequencies of Lead Times for all done work items. It
  plots the frequency of lead times, split in a set of 42 bins from the
  lowest to the highest with an blue dotted line overlay of the
  estimated Weibull distribution for such frequencies. The values that
  are involved are **F(LT)** and **Weibull(LT)**;
* the *Lead Times Detail* charts Lead Times split according to time
  spent in each status.

  For example: if a completed work item has spent 2 days in Backlog, 3
  days in Next and 12 days in In-progress, then the bar for such
  work item will be 17 days long, split in three differently coloured
  sections: a gray part, 2 days long, for Backlog; a red part, 3
  days long, for Next and a yellow part, 12 days long, for In-progress.
  Keeping the same colour-conventions as in the *Cumulative Flow
  Diagram*, this chart shows:
  * a gray vertical bar showing the total number of days a completed
    work item has spent in the **Backlog** column;
  * a red vertical bart showing the total number of days a completed
    work item has spent in the **Next** column;
  * a yellow vertical bar showing the total number of days a completed
    work item has spent in the **In-progress** column.
 
  These are actual (calendar) days: if a work item has been moved from
  **Backlog** to **Done** in the same day, all column will be 0 (zero),
  but the corresponding Lead Time for such work item will be 1 (one)
  day.

  Since this is a bar chart, it cannot be spread to include 3650 days
  like, say, the *Lead Times Scatterplot* chart because it does not
  automatically scale to skip empty value. So, while categories include
  the whole ten years set, the values of Backlog, Next and In-progress
  columns have been limited to 45 rows. Feel free to increase it, as
  soon as you have more than 45 activities.
  
* The *Approximate Average Lead Time*, shows the **mLT** value, together
  with the trend of the approximate average lead time (**AALT**), as
  shown by the CFD chart. The approximate average lead time is described
  in chapter 9 of the [Actionable Agile Metrics for
  Predictability](https://leanpub.com/actionableagilemetrics) book (as
  Approximate Average Cycle Time) and a practical example is show in
  [Actionable Agile Metrics for Predictability by Daniel S. Vacanti,
  Re-read Saturday Week 10: Flow
  Debt](https://tcagley.wordpress.com/2017/12/23/actionable-agile-metrics-for-predictability-by-daniel-s-vacanti-re-read-saturday-week-10-flow-debt/#more-9151)
  while reviewing and commenting the book itself.

  This chart contains data rows till spreadsheet row number 3651, that
  is enough to run the board for ten years. 

## Little's Law
The [Little's Law](#Little's%20Law) dashboard sheet has the name tab
highlighted in green colour to show that it does not contain nor compute
data but represents them in charts.

All the charts in this dashboard sheet, contain data rows till
spreadsheet row number 3651, that is enough to run the board for ten
years. 

The dashboard contains some charts related to Little's Law and other
useful metrics such as:
* The *Little's Law*, is the *LT = WIP / Thr* chart that represents the
  three terms of Little's Law. Running average of WIP (**mWIP**,
  orange), computed on actual data; running average of Throughput
  (**mThr**, green), computed on actual data; running average of Lead
  Time (**mLT**, blue), computed as ratio between those two values
  above;
* *I/O speed*, is the chart that represents the running average speed of
  incoming (**mArrival**) and outgoing (**mThr**) work items, in terms
  of average number of work items per day. There is also the running
  average of delivery rate (**mDR**) considering only completed
  work items, without discarded/deleted ones;
* *Blockers*, is the chart that represents the running average
  **Likelihood** (probability) that a blocker might happen during a
  work item life and the **Impact** blockers may have in a work item,
  that is the average number of blockers (day) that may happen during a
  work item lifetime;
* *Little's Law (7 days)*, is the *LT = WIP / Thr* chart that represents
  the three terms of Little's Law. Running average of WIP (**mWIP(7)**,
  orange), computed only on the latest seven days of actual data;
  running average of Throughput (**mThr(7)**, green), computed only on
  the latest seven days of actual data; running average of Lead Time
  (**mLT(7)**, blue), computed as ratio between those two values.
* *WIP Run Chart*, is the chart that shows the ongoing daily **WIP**
  values together with its moving average (**mWIP**) and the weekly
  moving average (**mWIP(7)**), computed only on the latest seven days
  of actual data).
* *Effort Run Chart*, simply represent the inverse of the daily **WIP**
  (`1/WIP`) and, for this reason, shows, each day, the **Daily Effort**
  that is the time theoretically spent, such day, for each ongoing
  work item.

  For example: if the **WIP** value is 1, you theoretically spend 100%
  of your time on such work item. If you have 2 ongoing work items
  (`WIP = 2`) means that you should spend half of the day for each work
  item, this means the effort for each work item is 50%. With 5 work
  items, your **Daily Effort** for each work item is 20%, and so on.

Since the same data are sometimes shown in different charts, in order to
improve readability, the data always (try to) use the same colouring
throughout the charts in this sheet. So, for example, **mWIP** is always
an orange line, both in *Little's Law* and in *WIP Run Chart*, etc.

## Metrics
The [Metrics](#Metrics) sheet contains a set of metrics computed from
the data above. Mainly, together with a few duplication of information
(work item **ID**, **Name**, and **Done** date) for bettere reference
and readability, for each row there is a work item that has these
metrics and columns.

To easily remember the content length, the formulas in this sheet are
spread till row number 3651, that is enough to manage 3650 activities. 
Feel free to increase the formulas coverage by dragging them to cover
more rows, when needed.

* **Backlog**, **Next** and **In-progress** columns that contain the
  number of days spent by the work item in each column. If the work item
  enters and exists a column on the same day, the count will show 0.
* the **95%**, **85%**, **70%**, **50%** columns that represent the
  moving percentile of the completed work items Lead Times. You can find
  a description of percentile in
  [Percentile](https://en.wikipedia.org/wiki/Percentile) page on
  Wikipedia. For more details, see [Actionable Agile Metrics for
  Predictability](https://leanpub.com/actionableagilemetrics) book.
* **LT** computes the Lead Time for each completed work item;
* **mLT** contains the moving average of all the Lead Times of the
  completed work items. Please note that this is the plain running
  average, done point-by-point: a proper calculation should probably
  might consider the average of average, since for a single days there
  could be multiple completed work items so the day average is the mLT
  of all work items completed in such day, and so on;
* **Age** contains, for all not-completed work items, the number of days
  since the work item is in progress (since in entered in **Next**);
* **Blocked Days** contains the total number of days the work item has
  been blocked;
* **Impact** is computed (as described in [Kanban Maturity
  Model](https://www.kanbanmaturitymodel.com/) book), for a completed
  work item, by summing the total of blocked days of all previously
  **Done** work items (current included), divided by the number of
  work items. It represents the average number of blocker days per
  work item;
* **Likelihood** is computed (as described in [Kanban Maturity
  Model](https://www.kanbanmaturitymodel.com/) book), for a completed
  work item, by counting the total number of blockers of all previously
  **Done** work items (current included), divided by the number of
  work items. It represents the average probability of having a blocker
  per work item;
* **Computed Effort**, for a completed work item, considers, for each
  day of the progress life of the work item, the number of parallel work
  items ongoing in the system (that is, the daily WIP). e.g. While a
  single work item counts as 1 day of effort per day, if there are two
  parallel work items (WIP = 2), the actual effort, per work item, is
  1/2 (or half) day. So, summing up all these daily computed efforts,
  for the whole work item lifetime, this column contains the total
  number of days theoretically worked on the work items, blockers
  included;
* **Risk** is computed (as described in [Kanban Maturity
  Model](https://www.kanbanmaturitymodel.com/) book), for a completed
  work item, by multipling the Impact and the Likelihood. It represents
  the average number of blocker days that may appear in work items;
* **FE** (or Flow Efficiency) is computed, for each completed work item,
  as the total number of blocker days over the lead time. e.g. If a
  work item has been completed in 13 days but it has been blocked for 10
  days, its FE is 23,08%;
* **mFE** contains the moving average of all the Flow Efficiencies of
  the completed work items.

## Distribution
The [Distribution](#Distribution) sheet contains some set of data used
for previously described chart. Specifically, it contains the frequency
distributions and percentile value for **LT** and **Backlog**, as shown
in [CFD](#CFD) and [Lead Time](#Lead%20Time) dashboards.

* The **F(LT)** column contains the frequency related to lead times, the
  number of completed work items that had a lead time less than or equal
  to the *A* column bin value. This table column is also plotted as
  histogram (*Lead Times Distribution* bar chart) showing the current
  distribution of lead time frequencies;

* The **Weibull** columns contains the computed values of the
  approximate Weibull distribution that mostly resemble the current
  distribution of lead time/backlog values described by the
  **F(LT)**/**F(Backlog)** columns. These table columns are also plotted
  as line overlapped to their specific distribution bar chart. The
  Weibull distributions are computed from the values of **Shape (k)**
  and **Scale (λ)** that have been approximated from the
  **Percentile(LT)**/**Percentile(Backlog)** values.

* The **F(Backlog)** column contains the frequency related to age of
  elements in backlog, regardless they are still in backlog (so their
  age will increase) or they've already been moved to other columns.
  This is the number of work items that have/had a backlog age less than
  or equal to the *E* column bin value. This table column is also
  plotted as histogram (*Backlog Age Distribution* bar chart) showing
  the current distribution of backlog age frequencies;

**Shape (k)** is computed from the 35p and 75p (percentile) values in
the **Percentile(LT)** column while **Scale (λ)** is computed from the
35p value and the **Shape**. I'd reference to Alexei Zheglov nice blog
post [How to Match to Weibull Distribution in
Excel](https://connected-knowledge.com/2013/08/01/how-to-match-to-weibull-distribution-in-excel/):
even though my calculations came from a different source (that I can't
actually remember, but still searching to give it a proper reference),
the results are similar to those written by Alexei in such post. 

* **Percentile(LT)** and **Percentile(Backlog)** columns represents the
current percentile values for Lead Time and Backlog age, the latter
being the number of days a work item remains in the Backlog (since it
has been created) before being took in charge and worked on;

## Data
The *Data* sheet is the daily representation of the whole board, with
reference metrics needed for each day (**Date** column). Together with the
daily number of work items for each column (**Backlog**, **Next**,
**In-progress**, **Done**), the sheet contains:

* the daily (cumulated) number of **Deleted** work items;
* the daily *Delivery Rate* (**DR**), the number of completed work items
  for the day, that is the number of work items moved to *Done* column
  and not deleted/discarded;
* the moving average delivery rate since the initial period (**mDR**);
* the daily **WIP**, the number of currently ongoing work items
  (**Next** plus **In-progress**);
* the moving average WIP since the initial period (**mWIP**);
* the moving average WIP of the latest seven days (**mWIP(7)**);
* the average lead time (**mLT**) computed as **mWIP** / **mThr**;
* the average lead time (**mLT(7)**) computed as **mWIP(7)** /
   **mThr(7)**;
* the actual **Daily Effort** per work item, as the multiplicative
  inverse of **WIP** (1 / **WIP**);
* the daily **mArrival** and **mThr** rates for work items as described
  in [Actionable Agile Metrics for
  Predictability](https://leanpub.com/actionableagilemetrics) book (as
  "Incoming" and "Outgoing");
* the set of cumulative daily data of work items for each column
  (**Backlog**, **Next**, **In-progress**, **Done**);
* the Approximate Average Lead Time (**AALT**), computed as described in
  [Actionable Agile Metrics for
  Predictability](https://leanpub.com/actionableagilemetrics) book.

This chart's rows and formulas are spread till row number 3651, that is
enough to run the board for ten years. 

----

Copyright © 2017, 2018, 2019 Marco Bresciani.

[![Creative Commons 
License](https://i.creativecommons.org/l/by-sa/4.0/88x31.png)](http://creativecommons.org/licenses/by-sa/4.0/)
AAMfP: a simple (Personal) Kanban metrics spreadsheet by [Marco
Bresciani](https://bitbucket.org/marcobresciani/aamfp) is licensed under
a [Creative Commons Attribution-ShareAlike 4.0 International
License](http://creativecommons.org/licenses/by-sa/4.0/).

![Free Cultural
Works](https://freedomdefined.org/upload/6/63/CCBYSA_black.png)
This is a freely licensed work, as explained in the [Definition of Free
Cultural Works](https://freedomdefined.org/Licenses).
