Как считать текучесть персонала в excel (кейс на примере конкретной компании)

Рекомендую посмотреть данные видео:

1. Как считать текучесть персонала Excel Power Pivot
2. Дашборд отчет динамика найма увольнений Excel Power Pivot
3. Как считать среднесписочную численность Headcount Excel Power Pivot

Пост родился вследствие опроса в нашем телеграм канале Какие показатели текучести кадров считают в компаниях? (по ссылке результаты опроса). Я показал, что из себя представляет средний срок жизни - см. Кейс: метрики текучести персонала на примере одной компании, но самой востребованным показателем текучести персонала, который бы хотели изучить коллеги - как считать % текучести персонала в excel

* присоединяйтесь к нашему телеграм каналу @hranalitycs.
Поэтому я и решил сделал кейс по расчету текучести персонала в excel на примере данных конкретной компании.
Также рекомендую Вам этот пост Как считать текучесть персонала в R/Rstudio (кейс на примере конкретной компании)

Видео

Крайне рекомендую посмотреть видео расчета текучести в excel 

Вводная

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

1. Дата приема работника;
2. Дата увольнения работника (если он работает на момент отчета, то поле остается пустым).

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

Формула расчета 

Я принял за основу форму, где текучесть определяется по формуле:
число уволенных работников за отчетный период / среднесписочная численность работников за отчетный период.
В нашем случае данные за несколько месяцев 2018 года, поэтому текучесть персонала в excel мы будем считать помесячно. Уверен, что вы легко сможете применить эту формулу уже по отношению к году.

Среднесписочная численность работников "за отчетный месяц исчисляется путем суммирования численности работников списочного состава за каждый календарный день отчетного месяца, т.е. с 1 по 30 или 31 число (для февраля - по 28 или 29 число), включая праздничные (нерабочие) и выходные дни, и деления полученной суммы на число календарных дней отчетного месяца.
Численность работников списочного состава за выходной или праздничный (нерабочий) день принимается равной списочной численности работников за предшествующий рабочий день. При наличии двух или более выходных или праздничных (нерабочих) дней подряд численность работников списочного состава за каждый из этих дней принимается равной численности работников списочного состава за рабочий день, предшествовавший выходным или праздничным (нерабочим) дням." Консультант плюс

Расчет 

Таким образом (открываем файл excel), если мы хотим посчитать текучесть персонала на январь 2018 год, нас нужно посчитать количество работников на каждый день января  и количество уволенных за месяц в компании. В нашем файле я создаю даты каждого месяца (см. колонки D, H, L, P - каждая колонка соответствует дням месяцев январь, февраль, март, апрель 2018 года.).
Сразу оговорюсь, что я обещал только предоставить корректные формулы расчета текучести персонала в excel, но не обещал красоты и секси дизайна, в последнем я совершенно не силен.
Давайте посмотрим на примере одного месяца - января.

1. Колонка D соответствует дням января - от 01 января 2018 года до 31 января. 
2. Напротив каждого дня нам нужно показать количество работников в статусе "работает" - этому соответствует колонка E;
3. И мы также считаем количество уволенных по каждому дню - это колонка F.

Среднесписочная и количество уволенных

Самое хитрое, как мы считаем колонки E и F. Среднесписочную я считаю по формуле  (поставьте курсор в ячейку E2):

• =СЧЁТЕСЛИ($A$2:$A$7556,"<"&D2)-СЧЁТЕСЛИ($B$2:$B$7556,"<"&D2)
• , где 
• СЧЁТЕСЛИ($A$2:$A$7556,"<"&D2) - показывает количество принятых на указанную дату 
• $A$2:$A$7556 - это наша переменная "Дата поступления", 
• D2 в нашем случае обозначает 01.01.2018
• формула в целом считает всех принятых в компанию ДО 01.01.2018
• СЧЁТЕСЛИ($B$2:$B$7556,"<"&D2) - показывает количество всех уволенных на указанную дату;
• $B$2:$B$7556 - переменная "дата увольнения"
• D2 - как выше уже заметили - 01.01.2018

Таким образом, среднесписочную мы получаем как разность между всеми принятыми до указанной даты и всеми уволенными до указанной даты. Если Вы поставите курсор на ячейку E3, то увидите, что среднесписочная считается уже на 02 января 2018 года.

Колонка F считает количество уволенных на указанную дату (курсор на ячейку на F2):

• =СЧЁТЕСЛИ($B$2:$B$7556,"="&D2)
• $B$2:$B$7556 - переменная "дата увольнения"
• D2 - 01.01.2018

Т.е. excel считает всех уволенных на 01.01.2018.
Давайте коротко пройдемся по данным. Если бросить взгляд на колонку E, то заметно, что уже 04 января среднесписочная меняется. И поскольку она выросла, а увольнений мы не наблюдаем, значит 03 января должны были быть приняты два работника. Чтобы проверить правильность расчетов, мы можем в колонке A (Дата поступления) поставить фильтр на 03.01.2018 и увидеть, что да, в этот день были приняты двое работников. Рекомендую Вам делать подобные проверки данных, чтобы

• убедиться, что ваши формулы работают корректно
• данные в системе учета вводятся корректно 

Без подобной проверки есть сомнения, что текучесть персонала excel будет посчитана верно.

Текучесть

Ну и чтобы посчитать % текучести персонала по месяцу в excel, мы

1. ячейка E33 - получаем среднее значение списочного состава по месяцу
2. ячейка F33 - количество уволенных за январь.
3. G33 - доля уволенных

Ну в общем это все. Как перевести полученную долю текучести - 0.0106446658345205 -  в % не поясняю - это просто.

Сравнение текучести персонала по месяцам

Не могу удержаться, чтобы не вернуть полезный компонент от расчета текучести персонала в excel. Надеюсь, что полезный.
Мне кажется, что одна из основных задач выглядит следующим образом:
Кейс
Наша текучесть по месяцам выглядит так:

1. Январь -1, 06 % (ячейка G33);
2. Февраль - 1, 04 % (K33);
3. Март - 1, 45 % (O33);
4. Апрель - 1, 22 % (S33).

Возникает вопрос, является ли показатель марта случайным или не случайным отклонением по отношению к зиме? Или по другому: начинают ли в марте влиять какие-то факторы / драйверы / причины, которые повышают % текучести персонала (например, фактор сезонности / выплата головой премии, после которой увольняются и т.п..).
Или же повышение % текучести в марте носит случайный характер, и, следовательно, за этим не стоят никакие факторы / драйверы / причины текучести персонала, и нам не надо выбивать специальный бюджет по снижению мартовской текучести.

Доверительные интервалы 

Поскольку я обещал только показать, как считать текучесть персонала в excel, то не буду глубоко погружаться в суть нижеследующих вычислений, пусть это будет приманкой для дальнейшего изучения hr-аналитики.
Формулы расчета доверительных интервалов показаны на оранжевой области листа.
Суть вычислений такова: мы можем вычислить для каждого месячного показателя текучести персонала границы случайного разброса. Т.е. какие значения текучести персонала по отношению к январю будут случайными.
Для января (я решил взять январь - так текучесть даже чуть чуть выше, чем февраль) этот диапазон будет

1. % текучести 0.010644666
2. Нижняя граница ошибки 0.008232271
3. Верхняя ошибка 0.013057061

Т.е. разброс январской текучести укладывается в границы от 0, 8 % до 1,3 % при фактической 1, 06%. А мартовское фактические значение текучести - 1, 4 %, т.е. фактическое мартовское значение выше, чем верхняя граница для января. И значит, мартовское отклонение текучести персонала не является случайным, за этим стоит какой то фактор / драйвер / причина (обратите внимание, я даже отрасли компании не знаю).
Для марта эти показатели такие:

1. Нижняя граница ошибки 0.0116929
2. % текучести 0.014500271
3. Верхняя ошибка 0.017307642

Т.е. разброс январской текучести укладывается в границы от 1, 1% до 1,7 % при фактической 1, 45%. Нижняя граница марта 1, 17 % , а фактическое значение января 1, 06 %. И снова не пересекаются. Т.е. отклонение января от марта тоже не случайное.
На рисунке справа показано пересечение доверительных интервалов. Стрелочки фигурной скобки не попадают в границы скобки другого месяца.

Хи квадрат

Еще один способ сравнения текучести по месяцам. Изучить Хи квадрат можно:

1. Самостоятельно по книге Е. Сидоренко Математические методы в психологии - в интернете ее легко можно скачать.
2. В моих постах Как считать Хи квадрат в excel и Как в excel быстро считать ожидаемые частоты для вычисления Хи квадрат, но в этом случае уже надо понимать логику применения критерия - зачем и куда его тыкать. 
3. Мой семинар Аналитика для HR - ближайший будет 18-19 октября в Москве.
4. Мини-он-лайн курс Принципы создания HR-дашбордов в excel - без срочный

Я лишь сообщу, что Хи квадрат для нашего случая равен 0, 044 - что различия между текучестью в январе и марте не случайны, но настоящие аналитики при p-value 0, 044 скорее всего просто не будут замарачиваться.

Другие способы

Два вышеприведенных способа не являются единственными способами диагностики текучести персонала. Могу также порекомендовать почитать

1. Контрольная карта Шухарта в управлении hr бизнес процессами (на основе текучести персонала) - инструмент карты Шухарта, чтобы понимать отклонение;
2. Что такое workforce planning - более глубокий метод на основе временных рядов - в нашем случае мы должны март сравнивать не с январем, а с мартом прошлого года - выявлять сезонные тенденции.

Не все так просто с расчетом текучести персонала в excel?)