Как контролировать дебиторку и кредиторку в Excel

В нашей компании эффективно работает финансовая модель построения отчетов об оборачиваемости задолженности и запасов (здесь и далее по тексту будет рассматриваться файл Excel, который приложен к этой статье). Она помогает оперативно провести анализ изменений в финансовом состоянии контрагентов, выявить внутренние резервы ускорения оборачиваемости расчетов и повышения ликвидности. Среди прочего модель дает возможность проводить анализ задолженности, рассчитывать объем сомнительных долгов и решать еще ряд важнейших задач. Рассмотрим каждую из этих задач подробнее.

Выявление контрагентов с замедлением/ускорением оборачиваемости

Замедление/ускорение оборачиваемости оплаты рассчитывается в три шага.

1. Всех контрагентов мы делим на две категории: с «разовыми операциями» и с «регулярными операциями», поскольку для них действуют собственные правила прогнозирования. Отмечу, что выявить контрагентов с замедлением/ускорением оплат можно только среди тех, которые относятся ко второй категории - с «регулярными операциями». Регулярность операций (для целей нашей модели регулярность – это наличие более двух поставок за последние полгода) характеризуют два показателя: «количество оплат» и «количество поставок».

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

=СЧЁТЕСЛИ(E45:P45;«<0»)

Для поставок принцип расчета аналогичный. В файле Excel эти расчеты представлены в столбцах R, S, то есть формула расположена в ячейках: R4:R14, S4:S14.

2. Считаем среднее значение показателя «период оборота» по данным за 12 последних месяцев и по данным трех последних месяцев. Формула для расчета трехмесячного среднего показателя:

=СРЗНАЧ(N4:P4)

Расчеты трехмесячного и 12-месячного среднего показателя приведены в столбцах AD, AE файла Excel.

3. Присваиваем контрагенту то или иное значение: «ускорение» либо «замедление» (в файле Excel столбец V) формулой:

=ЕСЛИ(И(P4>AD4;AD4>AE4;R4>1);«замедление»;ЕСЛИ(И(AD4<AE4;R4>1);«ускорение»;«-»))

Контроль взаимного соотношения задолженности

Нужно стремиться, чтобы общий объем задолженности, приходящийся на контрагентов с ускорением оборачиваемости, превышал объем задолженности с замедлением оборачиваемости. Такое соотношение обеспечит увеличение денежного потока от реализации в последующих периодах. Изменение вышеуказанного соотношения достигается стимулированием контрагентов к быстрой оплате (скидки за быструю оплату, штрафы за просрочки в договорах) и более интенсивной работой по контролю взыскания просроченной и сомнительной задолженности. На основе таблицы «сальдо задолженности» и маркера «ускорение/замедление» рассчитывается данное соотношение и его изменение в течение года при помощи формул (соответственно строки 100, 101 файла Excel):

=СУММЕСЛИ($V$4:$V$14;«замедление»;E$19:E$29)

=СУММЕСЛИ($V$4:$V$14;«ускорение»;E$19:E$29)

РИСУНОК 1. Контроль замедления и ускорения оборачиваемости задолженности

Теперь построим график, изображенный на рисунке 1. На нем мы можем видеть следующее:

1. В период с сентября по ноябрь объем задолженности с ускорением оборачиваемости превышал объем задолженности с замедлением расчетов.

2. В период с января по август разрыв между этими показателями постепенно снижался, но показатель задолженности с замедлением превышал задолженность с ускорением расчетов.

Таким образом, модель помогает оценить эффективность работы по оптимизации сроков оплаты и взысканию задолженности по долгосрочным договорам.

Читайте также: ВИДЕО: Как определить кредитный лимит для клиента

Анализ структуры текущей задолженности

На основе таблицы «период оборота» определяем, как давно (то есть сколько месяцев) с нашей компанией сотрудничает контрагент (см. колонка Х файла Excel). Для этого используем формулу, которая пересчитывает количество ненулевых значений показателя «период оборота» (иными словами, это и есть количество месяцев, в течение которых с нами работает контрагент):

=СЧЁТЕСЛИ(E4:P4;«>0»)

Сопоставив этот показатель с суммой задолженности контрагентов, построим круговую диаграмму «Структура задолженности». Она показывает структуру текущей задолженности по критерию продолжительности сотрудничества: 69 процентов задолженности приходится на долгосрочные договоры. Объем новых договоров, заключенных в течение шести последних месяцев, небольшой и составляет всего 12 процентов. Задолженность по долгосрочным договорам, а также повторным обращениям контрагентов предоставляет больше возможностей для ее оптимизации, так как степень лояльности таких контрагентов выше, и в рамках долгосрочного сотрудничества вы можете устанавливать специфические условия отсрочки платежа и скидки за быструю оплату.

Поиск внутренних резервов оптимизации задолженности

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

Этап 1. Определение локальных максимумов. То есть такого месяца, в котором период оборота больше или равен периоду оборота в последующем месяце. С помощью анализа локальных максимумов показателя «период оборота» определяется средняя периодичность оплат по договорам с регулярными операциями и рассрочками платежа.

Найдем локальные максимумы функции «период оборота» по всем контрагентам, имеющим две оплаты и более за последние 12 месяцев. Отмечу, что период оборота задолженности – это отношение задолженности на отчетную дату к объему реализации за период действия договора, умноженное на время с момента заключения договора (в днях).

Если задолженность не погашается, то время с момента заключения договора ежемесячно увеличивается на 30 дней, и период оборота также будет расти. В момент каждого погашения задолженности период оборота уменьшается. Таким образом, отметив локальные максимумы показателя «период оборота» задолженности в динамике, мы увидим частоту погашения задолженности и какой объем расчетов остается непогашенным каждый раз.

РИСУНОК 2. Изменение периода оборота дебиторской задолженности за 12 месяцев

На рисунке 2 красным цветом отмечены «локальные максимумы» показателя «период оборота» задолженности. Рассмотрим его подробнее:

• Контрагент 10 обычно полностью оплачивает поставки, так как отсчет периода оборота задолженности каждый раз после локального максимума начинается с 30 дней;
• Контрагент 11 задерживает оплату, поскольку локальный максимум растет (90/115/170/170 дней). То есть каждый раз до следующей оплаты проходит все больше времени. Либо контрагент платит регулярно, но все в меньшем и меньшем объеме по сравнению с остатком непогашенной задолженности.

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

Для того чтобы вывести локальные максимумы в отдельную таблицу, используем формулу, которая выявляет их, сравнивая показатель «период оборота» в соседних (справа и слева от него) клетках (таблица «локальные максимумы», строка 85 файла Excel):

=ЕСЛИ(ИЛИ(E4=«.»);«»;ЕСЛИ(И(Ч(E4)<=F4;F4>=Ч(G4));F4;«»))

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

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

Резерв оптимизации задолженности рассчитывается по формуле:

З_тек × (Т_рег : Т_тек – 1)

где Т_тек – период оборота задолженности в текущем месяце;

Т_рег – средний период погашения задолженности по контрагенту (определяется как среднее значение локальных максимумов оборачиваемости);

З_тек – текущее сальдо задолженности/аванса по контрагенту.

В файле Excel эта формула представлена в столбце W и имеет следующий вид:

= – (ЕСЛИ(И(P7>T7;T7>0);Q7*T7/P7-Q7;0)

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

Анализ карты структуры дебиторской задолженности

В файле Excel таблицу «период оборота» дополним определенными символами, характеризующими состояние дебиторской задолженности либо его изменение (см подробнее про дебиторскую задолженность компании, ее виды и способы анализа). Например:

• «Н» – динамика возникновения и погашения сомнительных долгов;
• «З» – задолженность, погашенная зачетом (и месяц ее погашения).

Кроме того, можно вести сравнительную характеристику периодичности оплат по разным контрагентам на основе показателя «период оборота» и цветов «зеленый – желтый – красный». То есть чем реже и меньшую долю задолженности оплачивает контрагент, тем больше показатель «период оборота» и краснее показатель оборачиваемости.

Таким образом, после занесения нужных символов («Н», «З») модель автоматически выявляет просроченную и сомнительную задолженность. Для построения такой карты используем формулу (строки 71–78 файла Excel):

=ЕСЛИ(И(Ч(P7)>150;СУММ(N48:P48)=0);«Н»;ЕСЛИ(И(ABS(P62)>ABS(P48);ABS(P62)>ABS(P22/2));«З»;P7))

В нашем примере сформируется вариант карты, приведенный на рисунке 3). Как из него видно, по Контрагенту 5 сумма задолженности, погашенной путем взаимозачета в марте, превышает сумму оплат в этом месяце и составляет не менее 50 процентов текущего сальдо задолженности, поэтому формула проставила в этом месяце знак «З».

РИСУНОК. 3 Карта структуры дебиторской задолженности

Для признания задолженности «сомнительной» (то есть знак «Н») обычно настраиваются два условия:

1. Превышение показателем «период оборота» определенного порогового значения, например двукратного значения «средней периодичности регулярных оплат» (в нашем случае значение задано вручную – 150 дней).

2. Отсутствие оплат в течение нескольких последних месяцев (например, 3 или 6). Если обычно контрагенты оплачивают поставки при достижении показателем «период оборота» значения в 75 дней, то формула считает задолженность сомнительной при условии, когда этот показатель превышает значение в 150 дней по какому-либо контрагенту (то есть не погашена задолженность, равная среднему объему поставок за 5 последних месяцев).

Цветовая схема раскраски карты настраивается при помощи функции «условное форматирование». Она автоматически закрашивает ячейки с цифрами, лежащими в определенном диапазоне в заданный цвет, а также дает возможность закрасить в отдельный специфический цвет ячейки с особыми значениями (например, буквенными).

Построение графика возрастной структуры задолженности

График строим в несколько этапов на основе карты структуры задолженности, связывая ее с таблицей «сальдо задолженности».

1. Суммируем задолженность, период оборота которой лежит в определенном интервале, при помощи формул (столбцы W68:H72 файла Excel соответственно):

=СУММЕСЛИ(E$72:E$82;«<46»;E$19:E$29)

=СУММЕСЛИ(E$72:E$82;«<91»;E$19:E$29)-W68

=СУММЕСЛИ(E$72:E$82;«<360»;E$19:E$29)-W68-W69

=СУММ(E$19:E$29)-W68-W69-W70-W72

2. В результате использования формул получаем таблицу, изображенную на рисунке 4. В ней задолженность сгруппирована по величине показателя «период оборота».

РИСУНОК 4. Группировка задолженности по величине показателя «период оборота»

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

РИСУНОК 5. Динамика изменения возрастной структуры задолженности в течение последнего года

Например, если поставка была 180 дней назад и сейчас оплачены 3/4 ее стоимости, то остаток задолженности составляет 1/4 ее стоимости, следовательно, период оборота составит 1/4 от 180 дней. То есть поставка за 45 дней не оплачена. На диаграмме отражена интенсивность оплат, измеренная в днях.

В нашем же примере диаграмма характеризует дебиторскую задолженность так:

• объем задолженности, который погашается в течение 45 дней, стабилен, а в последние месяцы даже растет;
• объем задолженности, которая погашается в течение более чем 90 дней также растет;
• в последние четыре месяца стабильно присутствует задолженность с периодом оборота 45–90 дней.

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

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

2. В результате возрос как общий объем неоплаченной задолженности, так и объем по каждой категории задолженности в отдельности. Обычно такие ситуации возникают в бизнесе с выраженной сезонностью поставок.

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

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

• Управление кредиторской задолженностью в условиях санкций

Кроме того, при оптимизации работы с кредиторской задолженностью главная цель – замедление периода ее оборота. Тогда вместо показателя «резерв сокращения дебиторской задолженности» можно использовать показатель «резерв отсрочки оплаты кредиторской задолженности», который определяет, насколько раньше по сравнению с обычным периодом расчетов была погашена кредиторская задолженность по регулярным операциям.

Расчет объема сомнительных долгов

Данные по сомнительным долгам можно также автоматизировать для каждого контрагента при помощи формулы (колонка AF файла Excel):

=СУММЕСЛИ(E$72:E$82;«Н»;E$19:E$29) =ЕСЛИ(P72=«Н»;Q4;0)

Формула сопоставляет таблицу «карта задолженности» с таблицей «сальдо». И если для какого-то контрагента в карте задолженности стоит символ «Н», то в столбец «сомнительный долг» выводится сальдо именно по этому контрагенту.

• Переуступайте сомнительную задолженность, если необходимо срочно признать убыток.

Автор — Лев Шуклов, заместитель начальника управления бухгалтерской и налоговой отчетности ООО «НГК “ИТЕРА”», д.э.н.