Хочу поделиться интересным кейсом на стыке финансового менеджера и аналитики: стояла задача выполнить план по EBITDA, практически все показатели на период взяли под контроль, но была проблема, а какой объем новых портфелей нужно привлечь в компанию.
Средний выход – известен, объем в работе известен, а вот сколько нужно привлечь, чтобы выполнить план – сложный вопрос. Продажи пытаются сделать план меньше, а операционка и финансовый менеджер говорит, что надо больше. Решили послушать аналитиков.
В общем задача: какой объем нужно привлечь, учитывая органический отток существующих, чтобы выполнить план с какой-то вероятностью.
В этой статье мы рассмотрим, как можно прогнозировать необходимый объем привлечения клиентов, чтобы достичь заданного уровня выручки с определенной вероятностью.
Прежде чем углубиться в детали, хочу синхронизировать понятийный аппарат, иногда нюансы дают большую погрешность:
- Органический отток — это естественная потеря клиентов, которая происходит по различным причинам, таким как полный уход, приостановка или не успели продлить договор и т.д.
- Целевое привлечение — это новый объем, который необходимо привлечь для достижения заданного уровня выручки.
- Вероятность достижения плана по выручке — это вероятность того, что фактическая выручка достигнет запланированного уровня.
Последний пункт — это отдельная задача, как объяснить некоторым руководителям, что 100% вероятность – это будет очень завышенный план. А вот как объяснить и согласовать уровень от 70% и до 90% со всеми заинтересованными сторонами…
Для меня это была нетривиальная задача, в ход пошли графики, объяснения, рисунки, схемы, формулы(так для страшилки просто в один момент отчаяния привел в презентации) и такое впечатление, что некоторые руководители просто отчаялись понять и поверили)
Модель прогнозирования
Для прогнозирования целевого привлечения клиентов мы будем использовать метод, основанный на статистическом анализе. В частности, мы применим z-оценку для определения необходимого объема привлечения клиентов с учетом органического оттока.
Здесь все проще, чем было на предыдущем уровне, итак:
Используем python и импортируем библиотеки
import pandas as pd
import numpy as np
import scipy
from scipy.stats import norm
Функция для расчета необходимого объема привлечения
Создадим функцию, которая будет рассчитывать необходимый объем привлечения клиентов на основе заданной вероятности, среднего значения органического оттока и его дисперсии.
def portfel_target_mean(N, mean_churn, disp_churn):
z_score = scipy.stats.norm.ppf(N) – z-оценка для вероятности, ключевой показатель здесь
V_treb_mean = mean_churn + z_score * disp_churn**0.5
return V_targ_mean
Подготовка данных
Загрузим данные о входах и выходах клиентов, а также данные о выручке, для простоты возьмем excel, если данные из 1C, то скорее всего так и будет:
in_out = pd.read_excel(‘in_out.xlsx’).fillna(0)
income = pd.read_excel(‘income.xlsx’).fillna(0)
Обеспечиваем сопоставимость по месяцам:
in_out[‘period’] = in_out[‘ period ‘] + ‘_01’
in_out[‘ period ‘] = pd.to_datetime(in_out[‘ period ‘], format=’%Y_%m_%d’)
Расчет текущего объема входа
Определим текущий объем входа и необходимый объем для достижения целевого портфеля.
Далее считаем необходимые цифры:
in_out[‘mean_debt’] = in_out[‘volume_sum’]/in_out[‘volume_counts’]
in_out[‘mean_pay’] = in_out[‘pay’]/in_out[‘volume_counts’]
mean_debt = in_out[‘volume_sum’].sum()/in_out[‘volume_counts’].sum()
mean_pay = in_out[‘pay’].sum()/in_out[‘volume_counts’].sum()
mean_income = income[‘sale’].sum()/in_out[‘pay’].sum()
mean_efficiency_month = (in_out[‘pay’].sum()/in_out[‘volume_sum’].iloc[-1])/len(in_out[‘pay’])
И проверяем на логику получившиеся данные, не закралась ли где ошибка:
print(f’\n Среднее АВ: {mean_income:.2%}’,
f’\n Среднее эффективность: {mean_efficiency_month:.2%}’,
f’\n Средний Долг: {mean_debt:.2f}’
f’\n Средний Платеж: {mean_pay:.2f}’
)
Немного деталей:
plan – план который установили
befor – период для анализа
befor_sum – сумма до
N = 0.90 – вероятность целевая
remainder = plan – befor_sum
print(remainder)
remainder = remainder/mean_efficiency_month
Находим объем портфеля на сейчас:
Объем портфеля = in_out[‘volume_sum’].iloc[-1]
Прогнозирование необходимого объема привлечения
Теперь мы можем рассчитать необходимый объем ежемесячных входов для достижения целевого портфеля.
output_coverage = portfel_target_mean(
N, in_out[‘out_counts’].mean(), np.var(in_out[‘out_counts’]))*mean_debt
print(f’\n Целевой портфель {remainder / 1000000:.2f} млн. руб.’
f’\n Необходимый объем ежемесячных входов в рублях, для достижения необходимого портфеля {output_coverage / 1000000:.2f} млн. руб.’)
Я опустил несколько важных моментов, в расчете брались средние показатели, например по эффективности работы с портфелем, средний отток и т.д., почему так было сделано:
- Некоторые показатели при небольшом горизонте имеют очень небольшую дисперсию или небольшое влияние и этим можно пренебречь.
- Эти показатели стабильны при определенной структуре и сложившихся инструментах.
- На часть показателей можно повлиять и подтянуть.
Самое главное – наращивать сложность модели, на основе которой принимаются решения – нужно постепенно. И по-хорошему нужно делать сценарное планирование.
Выводы
Что мы сделали: задали средние значения для портфеля в работе, объем портфеля в работе и другие параметры. Нашли для 90% вероятности, что отток не превысит этого значения (да, завышено, возможно) исходя из среднего оттока и дисперсии. Требуемый портфель – Портфель в работе – Отток = Требуемый объем привлечения.
На основе проведенного анализа мы можем сделать несколько выводов:
- Необходимость учета органического оттока: При планировании привлечения клиентов важно учитывать не только новых клиентов, но и тех, кто покидает компанию. Это позволит более точно прогнозировать необходимые объемы привлечения.
- Использование статистических методов: Применение z-оценки и других статистических методов позволяет более точно оценить риски и вероятности, что в свою очередь помогает в принятии более обоснованных бизнес-решений.
- Дальнейшее развитие модели: В будущем можно рассмотреть возможность использования более сложных моделей, таких как машинное обучение, для прогнозирования оттока клиентов и привлечения новых. Это может включать в себя анализ поведения клиентов, сегментацию и т.д..
Заключение
Прогнозирование целевого привлечения клиентов с учетом органического оттока — это задача сложная, но сложная с точки зрения объяснения и согласования, но это важная задача для бизнеса. Используя статистические методы и анализ данных, компании могут более точно планировать свои действия и достигать поставленных целей. Это не всегда необходимо, но если есть жесткие планы, то лучше усложнить жизнь для понимания всей картины.