Дашборд управления бизнесом (P&L) в Metabase за 5 дней
Один из важнейших инструментов в моей работе — дашборд с данными о прибылях и убытках компании P&L (он же БДР, он же отчет о финансовых результатах). Он помогает контролировать рентабельность бизнеса, эффективность отделов компании, прогнозировать прибыль и строить гипотезы для развития. В этой статье расскажу, как его настроить и использовать при помощи Open-Source BI-системы Metabase.
Зачем и кому нужен этот дашборд
На какие вопросы помогает ответить P&L:
Какая прибыль была в первом квартале?
Сколько доходов на этой неделе мы планируем на ближайшие месяцы?
Почему план по прибыли снизился за неделю? Какие именно затраты и доходы на это повлияли в каждом месяце?
В каких месяцах убытки? Не пора ли переходить к антикризисному управлению? Будут ли кассовые разрывы?
Почему во втором квартале резко подскочили затраты на зарплаты, хотя доходы не росли?
Почему не занесены планы по доходам от недавно проданного контракта и затраты на нанятого PM-а?
Когда наступит точка безубыточности? А окупаемости?
Это примерный список. Зачастую такие вопросы волнуют исполнительных и финансовых директоров, продакт-менеджеров, CTO, руководителей маркетинга и продаж.
Ниже пошагово расскажу, как настроить этот дашборд. А детальные технические аспекты — в статье нашего разработчика Александра Ежкова.
Что представляет собой дашборд
Чтобы говорить предметно, сначала покажу, как он выглядит.
При клике на любое значение таблицы можно «провалиться» к деталям. Показатели отчета напрямую влияют на прибыль и рентабельность компании, а рентабельность — важная для нас метрика. С помощью дашборда мы следим, чтобы она оставалась на уровне 20%.
Настроить такой дашборд можно за неделю.
День 1. Настраиваем инфраструктуру
Для работы дашборда мы используем систему сервисов:
Grist — редактор таблиц, «серверный гуглдок»;
Authentik — управление пользователями;
Apache Airflow — оркестратор;
PostgreSQL — база данных;
Metabase — BI-платформа для визуализации данных.
К Postgres подключены разные источники: CRM, таск-трекер, таблицы с данными производственных отделов и маркетинга, финансы.
Настроить взаимосвязь сервисов, аккаунты и доступ ко всем данным можно примерно за день. В статье Александра Ежкова даны необходимые инструкции и пояснения.
День 2. Готовим данные
Для этого нам нужно ввести данные в Grist и наполнить базу данных. Мы туда заносим зарплаты, закупки, капитальные вложения и доходы. Факт и план.
Единица планирования доходов — отгрузка работы клиенту в разрезе специалистов, подразделений, статусов, планируемых дат сдачи/оплаты и так далее.
Мы ведем управленческий учет не кассовым методом, а методом начислений. Когда мы говорим о расходах, мы имеем в виду начисления. Мы руководствуемся принципом соответствия затрат и доходов: расходы признаются в момент получения дохода. А учитываем доход, когда работа принята, а не когда нам заплатили аванс.
Grist позволяет нам делать неограниченное количество таблиц, создавать справочники для выпадающих полей, делать цветовые пометки, добавлять календари, фильтры. Всё как в Google Таблицах, но с возможностью удобно сохранять информацию в нашу базу данных и без рисков блокировки от внешнего вендора.
День 3. Создаем SQL-отчет в Metabase, добавляем сравнение с периодами для анализа истории в динамике
Когда в БД настроены типы и зависимости, переходим к созданию дашборда. Вот как выглядит SQL-запрос, где выбираем нужные таблицы, джоиним их по месяцам и рассчитываем прибыль.
/*Выбираем нужные таблицы в WITH*/
WITH
Revenue_bymonth AS (
SELECT
date_trunc('month', "Task_Sum_Date") as rev_MONTH, sum("Plan_Revenue") as Revenue
FROM magia__income_revenue
group by rev_MONTH
),
…
SELECT
Purchases_bymonth.cost_MONTH,
/*Используем coalesce, чтобы вместо null были 0*/
coalesce(Revenue_bymonth.Revenue,'0') Revenue, -- Доход
coalesce(Purchases_bymonth.Purchases,'0') Purchases, -- Закупки
coalesce(Salary_bymonth.Salarys,'0') Salarys, -- Зарплаты
coalesce(Revenue_bymonth.Revenue,'0')-coalesce(Purchases_bymonth.Purchases,'0')-coalesce(Salary_bymonth.Salarys,'0') Profit, -- Прибыль
(Revenue_bymonth.Revenue-Purchases_bymonth.Purchases-Salary_bymonth.Salarys)/NULLIF(Revenue_bymonth.Revenue,0) as Profitability -- Рентабельность
FROM purchases_bymonth
LEFT JOIN Revenue_bymonth
on Purchases_bymonth.cost_MONTH = Revenue_bymonth.rev_MONTH
LEFT JOIN Salary_bymonth
on Purchases_bymonth.cost_MONTH = Salary_bymonth.salary_MONTH
ORDER by Purchases_bymonth.cost_MONTH
Теперь у нас есть сводная таблица, которая автоматически считает фактическую прибыль и обновляется по расписанию из Airflow. Сейчас нужно проанализировать, как данные менялись в динамике. Опять обратимся к примеру.
Мы сделали прогноз, что в следующем месяце прибыль будет 1000 рублей. Прошла неделя, и мы видим, что в дашборде не 1000 рублей, а 900. Кто-то скорректировал прогноз. Нужно понять, за счет чего прибыль уменьшилась и что можно сделать, чтобы ее компенсировать. Для этого сравниваем фактические данные с историческими.
Эта информация даст пищу для анализа — мы поймем, какие инструменты, продукты, услуги работают, а какие нет. В этом помогут DIM-таблицы. По сути, это копии (снапшоты) исходной таблицы, которые создаются каждый день.
Во второй статье есть инструкция о том, как настроить расписание таких снимков (раздел «Grist API и Airflow»).
Дополним SQL-запрос:
/*Выбираем DIM-таблицы в WITH*/
/*DIM*/
dim_Revenue_bymonth AS (
SELECT
date_trunc('month', "Task_Sum_Date") as rev_MONTH, sum("Plan_Revenue") as Revenue,
ds -- Дата снапшота
FROM magia__income_revenue__dim
[[where ds = {{ds}}]] -- Выбираем дату снапшота для сравнения. В фигурных скобках — переменные для фильтрации прямо в дашборде Metabase
group by ds, rev_MONTH
),
…
/* Джоиним DIM-таблицы */
/*DIM*/
LEFT JOIN dim_Revenue_bymonth
…
/* Добавляем фильтр по годам */
where Purchases_bymonth.cost_MONTH >= '1.1.{{Year}}' AND Purchases_bymonth.cost_MONTH <= '12.31.{{Year}}'Вуаля, дашборд с возможностью анализа исторических данных готов:
Выбираем год и дату через выпадающие фильтры Metabase и сравниваем.
День 4. Создаем кнопку «Обновить» и настраиваем алертинг
Основа дашборда готова, но у него есть недостаток: он обновляется по расписанию. Это неудобно: вносишь данные и ждешь.
Поэтому нужно добавить кнопку «Обновить». Как это сделать, читайте в технической статье (раздел «Как создать кнопку на дашборде»).
Заодно стоит настроить алертинг и второстепенные отчеты. Алертинг позволяет держать руку на пульсе. Если кто-то вносит изменения в таблицу, всем заинтересованным приходит уведомление на почту или в Slack.
Например, сделаем алертинг по просроченным платежам. В редакторе Metabase создаем отчет с выборкой строк, где дата оплаты <= Today(). Если в отчете появляются данные — улетает отбивка бухгалтерии и менеджеру.
Теперь расставляем ссылки на дашборде.
Каждой колонке в отчете можно задать ссылку, по клику на которую откроется отчет с детальными данными, отфильтрованными исходя из значений в таблице.
День 5. Проводим первый срез, расставляем задачи и запускаем регулярную работу
Чтобы мотивировать руководителей направлений работать с данными, мы проводим с ними регулярные срезы по прибыли перед дашбордом по P&L. Польза от таких встреч двойная: с одной стороны, мы показываем удобство системы, с другой — стимулируем их внимательнее относиться к данным в целом.
На этих встречах мы находим отклонения от планов, выявляем проблемы, ставим задачи.
P&L руководителя подразделения
Создаем копию основного P&L и фильтруем по подразделению: выбираем соответствующие доходы, закупки и затраты. Есть сложность в распределении общих для всех подразделений расходов (косвенных). Мы распределяем косвенные пропорционально сумме затрат в отделе (в том числе зарплат). О системе начисления косвенных подробнее рассказывали на VC.
Снова пишем SQL-запрос:
/*Добавляем Косвенные затраты в With (которые не относятся ни к одному Цеху)*/
WITH
/*INDIRECT COSTS*/
purch AS (
SELECT
date_trunc('month', "Cost_Plan_Date") as cost_month, sum("Task_Sum_Cost") as indirect_purchases
FROM magia__income_expenses
WHERE "Department" NOT LIKE '%Цех -%'
GROUP BY cost_month
),
Salary AS (
SELECT
date_trunc('month', "Month_Date") as salary_month, sum("Total") as indirect_salarys
FROM magia__profit_salary
WHERE "Purpose" = 'Непроизводственное' -- Отфильтровываем Непроизводственные
GROUP BY salary_month
),
/*Выбираем все Зарплаты и Закупки по цехам*/
purch_dev AS (
SELECT
date_trunc('month', "Cost_Plan_Date") as cost_month, sum("Task_Sum_Cost") as purch_dev
FROM magia__income_expenses
WHERE "Department" = 'Цех - Разработка'
GROUP BY cost_month
),
purch_projection AS (
SELECT
date_trunc('month', "Cost_Plan_Date") as cost_month, sum("Task_Sum_Cost") as purch_projection
FROM magia__income_expenses
WHERE "Department" = 'Цех - Проектирование'
GROUP BY cost_month
),
…
/*Джоиним все таблицы с затратам*/
…
/*Добавляем колонку с рассчитанными Косвенными в P&L и корректируем формулу расчета прибыли в SELECT*/
(coalesce(pb.indirect_purchases,'0') + coalesce(sb.indirect_salarys,'0')) *
(coalesce(pbd.purch_dev,'0') + coalesce(sdv.indirect_salarys_dev,'0')) / (
coalesce(pbanalytics.purch_analytics,'0') + coalesce(sa.indirect_salarys_analytics,'0') +
coalesce(pbprojection.purch_projection,'0') + coalesce(sp.indirect_salarys_projection,'0') +
coalesce(pbdesign.purch_design,'0') + coalesce(sd.indirect_salarys_design,'0') +
coalesce(pbd.purch_dev,'0') + coalesce(sdv.indirect_salarys_dev,'0') +
coalesce(pbm.purch_PM,'0') + coalesce(spm.indirect_salarys_pm,'0')
) dev_indirect_costs,
Мы следим за показателями каждого отдела не только ради осведомленности. Бонусы многих топ-менеджеров зависят от эффективности их подразделений. Поэтому с помощью дашборда мы рассчитываем рентабельность отделов и премии руководителей.
А что дальше?
Теперь культуру работы с Metabase нужно распространить на всю компанию и разграничить права доступа. Об этом подробно рассказываем во второй статье (см. раздел «Metabase и psql-http»).
С помощью тех же инструментов дашборды строят наша Sales Team, отдел QA, маркетологи AGIMA, менеджеры проектов и т. д. Через дашборд мы следим за эффективностью продаж, менеджеров и отдельных команд.
Что в итоге вы получаете
Описанная система позволяет аккуратно и четко вести управленческий учет. Мы не сразу к этому пришли. Но в итоге мы можем принимать решения на основании конкретных данных, которые всегда под рукой. За какими показателями следить, мы выбираем через пирамиду метрик.
Если хотите посмотреть, как работает Grist, специально сделали демо-версию.
Вот доступы:
Логин: demo@example.com
Пароль: demo1234
Вы можете скопировать документ и попробовать поработать с ним. Также во второй статье найдете ссылку на репозиторий с файлами, которые помогут настроить все сервисы (раздел «Полезная ссылка и демо-версия»).
Если остались вопросы, задавайте в комментариях. Постараюсь на все ответить. Еще у нас есть телеграм-канал для продактов, в котором рассказываем о других инструментах работы с данными. Приходите читать.
