Автоматический подсчет показателей бизнес-юнитов07.02.2022 12:01

Привет!

В нашей компании есть несколько юнитов, которые занимаются разными направлениями. В конце 2020 года мы в KTS впервые составили их бизнес-планы. Раньше мы, конечно же, следили за деньгами. Но за всеми сразу через нашу CRMку. Мы даже написали интеграции с банком, чтобы автоматически получать информацию о новых платежах. Но со временем количество и объем юнитов росли, поэтому нужно было понимать их показатели по отдельности. К тому же бизнес-планы помогают принимать решения в процессе работы.

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

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

План статьи:

Предусловия

Представьте: у вас в компании есть 5 юнитов. В каждом из них разные доходы, расходы и количество человек.

Основную сложность при расчете показателей юнитов представляют общие затраты: офис, HR-служба, закупки и прочее. Обычно они закладываются в затраты каждого юнита в некоторой пропорции.

Вторая проблема — расходы, которые мы назвали непроизводственными. Например, система наставничества: у каждого сотрудника есть старший наставник, и они встречаются раз в 2 недели. Сотрудник и наставник могут быть из разных юнитов. На встрече оба они тратят время и непонятно, к какому юниту относятся эти расходы. Бывают и другие активности, которые тоже оплачивает компания — например, написание статей на Хабр. Все эти расходы надо поделить между юнитами и сделать так, чтобы руководители не считали их дополнительной нагрузкой на свой юнит, за которую они платят «из своего кармана».

Основная проблема, с которой мы столкнулись в 2020 году — в каждом юните расходы считали по-разному, и учесть действительно все очень сложно. Каждый руководитель использовал свою систему расчета эффективности проектов и мог ошибаться.

Так у нас сформировался список задач, которые нужно учесть в новой системе расчета:

Учет всех расходов компании
Ошибки при подсчете расходов разных юнитов разными людьми
Справедливое распределение общих расходов между юнитами
Отсутствие демотивации руководителей от непроизводственных затрат сотрудников юнита

Схема расчета

Первое и самое важное: все расходы компании должны делиться между юнитами. Иначе юниты могут показывать прибыль, а компания в целом — нет.

Для начала эти расходы нужно считать. В целом это просто — мы можем выгружать из CRM все расходы и категоризировать их. Например:

содержание офиса: аренда, Интернет, уборка, печеньки — X ₽
закупки: мониторы, ноутбуки — Y ₽
бэкофис: курьеры, документооборот — Z ₽

В итоге получается сумма, которую тратит вся компания на поддержание существования юнитов. Казалось бы, теперь просто надо поделить эти расходы между юнитами, а зарплаты своим сотрудникам (ФОТ) они оплатят сами.

Но не все так просто. Если руководители юнитов будут «оплачивать» непроизводственные расходы — менторинг, статьи, школа — у них может возникнуть справедливый вопрос: «Зачем мне тратить бюджет своего юнита на деятельность, которая не приносит пользы конкретно юниту, да еще и тратит рабочее время сотрудников?»

Хотя руководители в KTS понимают смысл всей этой деятельности, мы все равно хотели сделать систему более прозрачной и справедливой. К тому же затраты могут быть велики. Во время проведения бесплатной школы бывали случаи, когда сильные сотрудники проводили лекции, на подготовку которых уходило до 30% рабочего времени. Ни один руководитель такому не обрадуется.

Поэтому мы решили, что непроизводственные расходы тоже должны делиться между юнитами. Например, если сотрудник из юнита Х потратил 30% времени на школу, то 30% его зарплаты должны быть разделены между всеми юнитами, их не должен оплачивать только юнит X.

Такая схема сглаживает проблему демотивации руководителей. Ведь даже если сотрудник потратил время не на свой юнит, руководитель оплачивает только часть этих расходов. На практике, если руководители понимают, какую реальную ценность несут все эти дополнительные мероприятия, при такой схеме разделения расходов они еще охотнее стараются уделять им внимание.

Итого у нас получилось 2 части расходов компании, помимо расходов юнитов:

общие расходы: постоянные на офис, покупки, маркетинг, бэкофис, HR
непроизводственные расходы ФОТ: проведение школы, написание статей, менторинг, митапы

Остается справедливо поделить их. Сразу скажу, что наша схема несовершенна, но делить мы решили просто по числу сотрудников. Например, если у вас 2 юнита, и в одном 30 человек, а в другом 70, то первый будет платить 30% общих расходов, а второй 70%. Это справедливо, если у юнитов схожая направленность деятельности и они сбалансированы по составу.

У этой схемы есть недостаток: юнит, взявший в состав условного джуна, сразу начинает платить не только его зарплату, но и общие расходы за этого человека. А размер общих расходов на человека может быть соизмерим с его зарплатой. Получается, что брать джунов психологически «больнее», потому что дополнительные непроизводственные расходы будут такие же, как если взять сеньора. При этом пользы от сеньора, очевидно, будет больше.

Автоматизируем расчет

В 2021 году мы считали общие расходы вручную. Это было так: я сводил лист с общими расходами за каждый месяц. Для этого из нашего таск-трекера YouTrack сначала я выгружал отчет по затраченному времени за месяц на общие расходы, а затем такой же отчет на непроизводственные расходы.

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

Затем берем все общие расходы и добавляем к полученной сумме денег за непроизводственные расходы.

Получается сумма, которая затем делится на количество сотрудников в юнитах.

Это число я передавал руководителям юнитов для учета в их бизнес-планах. То есть это как некоторый «налог» на содержание сотрудника в юните.

Руководители умножают этот налог на количество сотрудников у себя в юните и получают сумму, которую они должны оплатить в счет общих затрат. Получается, что если руководители при этом не допускают ошибок и учитывают все свои затраты корректно, то все затраты компании распределены — наша цель выполнена.

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

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

Поэтому я решил не внедрять этот шаг в нашу CRM, а собрать прототип в Google-таблицах.

ФОТ сотрудников юнита

Шаг 1. Первое, что нужно посчитать — сколько каждый юнит должен заплатить из ФОТ.

Для этого в YouTrack можно выгрузить отчет с группировкой по проектам:

Этот отчет служит основой для всего последующего расчета. Его можно скачать в CSV.

Так как для каждого сотрудника в отчете есть проекты и время — остается поделить время конкретного проекта на общее время, чтобы получить %. А еще нужно привязать проект к юниту, ведь у одного юнита может быть много разных проектов.

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

Шаг 2. Теперь я переношу данные отчета в Google-таблицу:

Шаг 3. Теперь нужно связать проект с юнитом, потому что у одного юнита может быть несколько проектов. Я сделал отдельный лист с данными, в котором указал соответствие:

Шаг 4. В исходный лист я добавил столбец, в который подтягиваю данные из вспомогательной таблицы:

=IF (ISBLANK (B3);»; QUERY ('Данные'! A: B; «SELECT A WHERE B='»& B3 &»'»;0))

Столбец А — юнит, B — проект в моей вспомогательной таблице.

Получается, что если в графе «Проект» не пусто, то выбираются данные из столбца «Юнит» вспомогательной таблицы:

Шаг 5. Теперь нужно поделить «время сотрудника на отдельный проект» на «общее время сотрудника за период». Общее время записано в столбце C, но при условии, что в столбце B пусто. Поэтому я ввел новый столбец, который для каждой строки будет искать соответствующее общее время с помощью формулы:

=QUERY (A: C; «SELECT C where A='» & A2 & »' limit 1»;0)

Шаг 6. Я ищу первое появление (limit 1) строки, записанной в A (ФИО сотрудника), и запрашиваю столбец C (общее время). Так как первое появление в отчете — это всегда общее время, то в итоге для каждой строки я получаю общее время в новом столбце (E):

Шаг 7. Ну, а дальше дело за малым: делим время в проекте © на общее время (E) и получаем долю его ЗП в проекте. Кстати, ЗП подтягивается из внешней таблицы по ФИО сотрудника.

Шаг 8. Дальше простым group by можно получить ФОТ для каждого юнита:

=QUERY (A2: H; «SELECT D, SUM (H) GROUP BY D»;0)

Столбец D — юнит, H — ЗП на проект.

Эту сумму я сверяю с реальным ФОТ в каждом месяце. Таким образом исключены ошибки, что какие-то сотрудники не учтены.

Автоопределение юнита сотрудника

Описанной схемы в целом достаточно.

Ведь теперь я знаю непроизводственные расходы — хотя и остается добавить постоянные оплаты — и могу разделить их между юнитами, как раньше: передать затраты руководителям, а они внесут их в свои расходы. Но мне хотелось пойти дальше и получить финальные показатели для каждого юнита. А значит, придется как-то поделить расходы на сотрудников юнитов.

Для этого есть 2 варианта:

Использовать таблицу принадлежности к юнитам (у нас такая есть)
Считать принадлежность динамически на основе данных по учету времени

Я выбрал второй вариант. Первый вариант хорош, но не учитывает возможные переходы сотрудников между юнитами (у нас такое иногда случается) и зависит от актуальности таблицы принадлежности к юнитам. Ее ведут другие люди, поэтому она может содержать ошибки. Мне хотелось иметь единый «источник правды».

В итоге я решил, что для каждого человека буду искать юнит, на который он потратил больше всего времени. Интуитивно кажется, что это правильно: если я потратил 70% времени в юните Х, а еще 30% в разделе «общие задачи» (например, готовил лекции к школе), то я скорее отношусь к юниту Х. И наоборот, если 70% времени я потратил на подготовку лекций, то несправедливо, что за меня платит юнит Х.

В SQL решить такую задачку было бы просто: нужно сгруппировать данные по сотруднику, для каждой группы получить максимум часов, а затем сджоинить исходную таблицу с промежуточной по условию, что совпадают ФИО и число часов. Но проблема в том, что Google-таблицы join не поддерживают. Точнее, поддерживают, но в виде какой-то хитрой операции над массивами, быстро изучить которую у меня не получилось.

Зато я нашел изящное решение — в простонародии «костыль». Оно помогло добиться нужного результата. Я сделал еще одну таблицу с группировкой по сотруднику и юниту и с суммой часов для каждой группы. Получилось, что я знаю, сколько часов каждый сотрудник потратил на каждый юнит. Эту таблицу я сортирую по убыванию так, чтобы сначала для каждого сотрудника шел юнит с максимальными временными затратами.

A — ФИО
D — Юнит
C — Время

Остается для каждого сотрудника найти первое появление в этой таблице. Для этого сначала я ввожу еще один столбец со следующей формулой:

=IF (COUNTIF (1:$M5; M5)=1; «Первое»; «Не первое»)

В столбце M — ФИО сотрудников. Берется диапазон с первой строки до текущей.

Для каждой строки формула подсчитывает количество строк выше, где ФИО совпадает с текущей строкой. Если таких строк одна (текущая), то в столбец пишется «Первое» (нахождение), если нет — «Не первое».

Теперь нужно найти только те строки, где написано «Первое». В примере выше Сотрудник 3 потратил больше всего времени на Юнит 3, поэтому для него это первая группа, которую нужно взять в итоговую сводку:

=QUERY (M: P; «SELECT M, N WHERE P = 'Первое' ORDER BY N»;0)

M — ФИО
N — юнит
P — вхождение

Благодаря этим манипуляциям я автоматически получаю принадлежность каждого человека к юниту и могу перейти к расчетам затрат по описанной выше схеме.

Расчет затрат

Но прежде чем посчитать затраты, нужно учесть их вторую их часть, общие расходы компании: офис, курьеры, доступы к сервисам и тд. Их я просто выписываю из нашей CRM и отмечаю в графе «Комментарий», на что именно потрачены деньги:

Затем группирую по юниту:
=QUERY (U2: W; «SELECT U, SUM (V) GROUP BY U»;0)

И формирую финальную таблицу с затратами.

В нашей схеме мне нужно поделить общие затраты между всеми юнитами пропорционально их размеру (количеству человек). Поэтому сначала нужно это количество человек посчитать:

=QUERY (R2: S; «SELECT S, COUNT (S) GROUP BY S»;0)

S — юнит.

Чтобы посчитать удельные общие затраты на человека в юните, нужно поделить всю сумму затрат на «производителей». Так я назвал всех людей, которые напрямую относятся в какой-то юнит. Противоположность им — HR, бэкофис и другие подобные сотрудники, которые нужны для жизнедеятельности компании.

=SUMIF (AB: AB;»<>Общие»; AC: AC)

Дальше выписываем уже имеющиеся у нас данные по общим и непроизводственным расходам, суммируем и делим на число «производителей». Умножаем количество человек в юните на полученный «налог», а также добавляем допрасходы юнитов и их ФОТ из первого шага:

Теперь мы наглядно видим все расходы юнита с учетом нашей схемы подсчета.

Итоги юнита

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

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

Заключение

В итоге мы автоматизировали подсчет показателей бизнес юнитов. Конечно, эта схема расчета специфична для нас, но может быть, она будет полезна кому-то еще. Мы использовали ее весь 2021 год и собираемся использовать дальше. Особенно хорошо она ложится на бизнесы, где у сотрудников есть разные затраты на «общую» деятельность.

Поиграть с цифрами можно в этой таблице. Автоматизацию, описанную в статье, мы внедрили и обкатали на январских данных 2022 года.

Надеюсь, было интересно!