HowTo. Как построить Cumulative Flow Diagram (CFD) в Excel

Cumulative Flow Diagram - второй по ценности источник информации о бизнес-процессах (первый - Lead Time Distribution Chart).
Эта диаграмма позволяет объяснить, в какой момент упала производительность, и почему это произошло. Используя ее, можно в динамике видеть последствия своих управленческих решений и быстро оценивать средние значения метрик процесса - средний Lead Time и среднее значение Work In Progress. Для тех, кому интересно нырнуть в глубины анализа этого графика, предлагаю ознакомиться с PDF с инструкцией о том, как интерпретировать эту диаграмму.

А сегодня мы поговорим о том, как строить эту диаграмму в Excel на основе данных о датах перехода между статусами.

Дисклеймер: я исхожу их предположения, что таск-трекер у вас есть, вы его грамотно настроили, ваша команда своевременно перетаскивает задачи их статуса в статус (а не пачкой в конце спринта), и вы точно знаете где у вас точка принятия обязательств и точка отдачи обязательств. Если это не так, то полученная по данной инструкции статистика может быть неверна.

Содержание:

Я предполагаю что у вас есть способ выгрузить нужные данные из вашего таск-трекера в виде таблицы, где в строках - ID задачи, а в заголовках колонок - названия статусов. В ячейках должны содержаться даты перехода задачи в конкретный статус.

Вид таких данных будет примерно такой:

Напомню как строится CFD: по горизонтали откладываются календарные даты, а по вертикали - количество задач в разных статусах.

Поэтому исходные данные нам надо преобразовать следующим образом:

а) найти минимальную и максимальную дату в данных, чтобы задать границы оси X

б) на каждую дату между минимальной и максималной - посчитать для каждого статусу задач (колонка) - сколько задач находилось в этом статусе

Используем формулы МИН(<диапазон_ячеек>) и МАКС(<диапазон_ячеек>) и задаем им в качестве аргументов весь диапазон дат

а) Делаем новый лист

б) Копируем в него заголовки с названиями статусов из таблицы с датами из первого листа.

в) В левой колонке делаем диапазон дат - от минимально, до максимальной

Получается вот такой лист:

Теперь нам над заполнит лист 2 таким образом, чтобы в каждой ячейке было указано, сколько задач на данную дату (строку) было в данном статусе (колонка)

При этом надо учесть, что в исходных данных даты были только тогда, когда задача переходила в другой статус. Это значит, что если задача 1 марта 2023 года перешла в статус A , а 10 марта 2023 года перешла в статус B, то между этими двумя датами у этой задаче был один и тот же статус - статус A.

Именно эту логику нам и надо заложить в формулу подсчета. Для каждой колонки статуса в Листе 2 мы будем использовать следующую формулу:

Эта формула считает, сколько ячеек в области обозначенной как диапазон_ячеек соответствует условию_подсчета.

В нашем случае значения переменных будет таким:

- диапазон_ячеек будет равен диапазону дат из Листа 1, в нужном нам статусе

- условие_подсчета - это равенство дате на Листе 2 в левой колонке

Вот как это выглядит в Excel:

И далее копируем эту формулу на все ячейки Листа 2

В результате, у вас должно получиться вот так:

Выделяем все значения в только что заполненной нами таблице на Листе 2 (вместе с заголовком), кликаем на таб “Вставка”, затем в диаграммах выбираем линейную диаграмму “С областями и накоплением”

Получаем вот такую диаграмму:

Но это еще не финал. Дело в том, что Excel строит эту диаграмму не так, как нам нужно. Обратите внимание - первый статус "Открыт" отображен синей областью внизу графика, а последний статус "Закрыт", отображен на самом верху в виде ярко-красной области. А ДОЛЖНО БЫТЬ НАОБОРОТ!

а) Выделяем график.

б) Жмем на нем правой кнопкой мыши

в) В появившемся меню выбираем пункт "Выбрать данные"

Появится меню графика

Над списком, где перечислены статусы, справа есть стрелки. Эти стрелки определяют порядок расположения областей графика - что будет первым, что вторым, что третьим, и так далее.

Нужно проделать одну и ту же операцию с каждым статусом, начиная с верхнего ("Открыт"):

а) выделяем его в списке

б) тыкаем в стрелку "вниз" надо списком статусов, до тех пор, пока этот статус не окажется сразу под последним по логическому порядку. статусом ("Закрыт")

В итоге все статусы встанут на свои места: первый статус "Открыт" (синий) - станет верхним, остальные по последовательности, расположатся под ним, а в самом низу будет последний по последовательности статус "Закрыт" (ярко-красный)

Пример Excel-файла с построением CFD можно взять по этой ссылке

Лайк, шер приветствуется ;)

Но одной диаграммы CFD обычно мало для понимания того, как устроен рабочий процесс. Чтобы прогнозировать сроки с вероятностью 80-90% нужно воспользоваться Lead Time Distribution Chart, и о том, как ее строить, читайте в этой статье

О том, как с умом использовать Cumulative Flow Diagram, Lead Time Distribution Chart и другие метрики читайте в этих материалах

#канбан #kanban #excel #инструменты #прогнозы #оценказадач #управлениепроектами