.

Сделать репост в соц сети!

четверг, 31 мая 2018 г.

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








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



Пост родился вследствие опроса в нашем телеграм канале Какие показатели текучести кадров считают в компаниях? (по ссылке результаты опроса). Я показал, что из себя представляет средний срок жизни - см. Кейс: метрики текучести персонала на примере одной компании, но самой востребованным показателем текучести персонала, который бы хотели изучить коллеги - как считать % текучести персонала в excel
Как считать текучесть персонала в 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-аналитики.
Формулы расчета доверительных интервалов показаны на оранжевой области листа.
Суть вычислений такова: мы можем вычислить для каждого месячного показателя текучести персонала границы случайного разброса. Т.е. какие значения текучести персонала по отношению к январю будут случайными.
Как считать текучесть персонала в excel (кейс на примере конкретной компании)Для января (я решил взять январь - так текучесть даже чуть чуть выше, чем февраль) этот диапазон будет
  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?)










12 комментариев:

  1. А запланированные увольнения учли? Если в марте у части сотрудников закончился срочный ТД? Их увольнение не должно влиять на показатель текучести.
    Спасибо за формулу расчёта Особенно как самостоятельно посчитать среднесписочную. Но обращайте внимание на правила учёта В текучесть не входят запланированные увольнения (к примеру срочные ТД) смерть сотрудников и т.п.
    Никита.

    ОтветитьУдалить
    Ответы
    1. конечно, я об этом думал, но эта проблема решается легко: мы добавляем третью колонку с причинами увольнений.
      И у нас формула меняется в СЧЕТЕСЛИ на СЧЕТЕСЛИМН - мы добавляем еще одно условие - причина увольнения.

      таким же образом, мы, кстати, считаем добровольную / не добровольную текучесть - это тоже важно.

      И (!) если у нас возникнет потребность посчитать текучесть по разным филиалам / позициям персонала, то мы добавляем соответствующие колонки / переменные, задаем новые условия и считаем по ним

      Удалить
    2. Всё верно. Я предлагаю добавить эти уточнения в статью, чтобы у менее искушенных коллег сразу складывалось правильное понимание термина и они делали поправки на эти данные.

      Удалить
    3. я думаю, что коллегам достаточно прочитать комменты)
      ну либо пришлите данные с новыми вводными

      Удалить
  2. Если копать глубже в ньюансы расчета то появляется еще больше ангументов к "счетеслимн"
    1. Декретницы - по-хорошему не должны попадать в среднесписочную, хотя от компании к компании варьируется взгляд на это
    2. "Потеряшки" - распространенная ситуация на производстве когда сотрудник просто не приходит на работу. Таких тоже не совсем корректно считать в среднюю численность в мае, если в январе они перестали ходить на работу.
    3. Перевод через увольнение - сотрудник переходит со внешнего совместительства на внутреннюю полную ставку и приходится его увольнять, хотя по факту человек работает и не уволился. Либо человек уволился в одной стране присутствия и принялся в другой в эту же компанию, т.н. техническое увольнение.

    И получается что простых дат увольнения и приема не хватает и если использовать только их то результаты могут сильно исказиться :)

    ОтветитьУдалить
    Ответы
    1. Рамис, добавьте третью колонку
      по факту могу сказать, что в компании чаще просто вообще нет правил подсчета среднесписочной и по конкретному человеку принимают конкретное решение, и тогда отсев происходит по полю ФИО - распространенная практика?

      Удалить
    2. а по пункту 3. Перевод через увольнение
      в чем проблема? Он уволен, но тут же принят и снова попадает в среднеспиосочную
      мы же не модель оттока строим, где надо его стаж учитывать

      Удалить
  3. Проблема не в среднесписочной, а в том что такой сотрудник попадет в список уволенных, хотя по-хорошему не должен.
    Про правила подсчета среднесписочной да, скорее соглашусь, иногда практикуется пофамильное исключение из списка уволенных.
    Ну и правила подсчета все равно вырабатываются из специфики бизнеса. Если в компании нет проблем с потеряшками, то нет и необходимости учитывать дату с которой сотрудник перестал ходить на работу.

    В целом у меня еще комментарий - на мой взгляд намного удобнее считать среднесписочную по каждому сотруднику в формате от 0 до 1 для каждого конкретного месяца. Т.е. был активен весь месяц - 1, принялся или уволился в середине месяца - 0.5, если был в декрете/потерялся/еще не принялся/ГПХ - 0. Так расчет получается структурированее и легче для обработки и можно учесть больше вышеописанных ньюансов.

    ОтветитьУдалить
    Ответы
    1. Рамис, вот это я вообще не понял
      "Проблема не в среднесписочной, а в том что такой сотрудник попадет в список уволенных, хотя по-хорошему не должен."
      а в чем здесь проблема? ну попал он в список уволенных и что? С т.з. построения среднесписочной и текучести персонала нас все равно. Если у вас другая задача встает, то ее можно решать другими способами, у меня такой задачи не встает.


      Вот этот комментарий я вообще не понял
      "В целом у меня еще комментарий - на мой взгляд намного удобнее считать среднесписочную по каждому сотруднику в формате от 0 до 1 для каждого конкретного месяца. Т.е. был активен весь месяц - 1, принялся или уволился в середине месяца - 0.5, если был в декрете/потерялся/еще не принялся/ГПХ - 0. Так расчет получается структурированее и легче для обработки и можно учесть больше вышеописанных ньюансов."

      мы считаем среднесписочную (должны считать как указано в Консультант Плюс) как среднее всех дней по месяцу, как вы будете здесь учитывать 1, 05, 0 - я не понимаю.

      Удалить
    2. вы можете стать автором блога, покажите свои собственные расчеты - нивапрос. Вам все равно еще задание делать после семинара

      Удалить
  4. Здравствуйте.
    В развитие темы хочу описать вариант расчета текучести в Power Pivot (надстройка для Excel 2010,2013, встроена в Excel Professional или Standalone) или Power BI. Удобство заключается в том, что вы имеете возможность использовать необходимые измерения (признаки, в разрезе которых строится расчет) на лету, изменяя детализацию и построенные графики.
    Будет ли интересно описание сложных формул языка DAX (ниже пример) для решения подобной проблемы?
    Среднесписочная численность за скользящий год :=
    CALCULATE (
    [Среднесписочная численность];
    FILTER (
    ALL ( 'Календарь' );
    'Календарь'[Номер дня]
    > MAX ( 'Календарь'[Номер дня] ) - 365
    && 'Календарь'[Номер дня] <= MAX ( 'Календарь'[Номер дня] )
    )
    )

    ОтветитьУдалить