Как создать отчет о старении дебиторской задолженности в Excel (30, 60, 90 дней)

Отчет о старении дебиторской задолженности распределяет неоплаченные счета по группам в зависимости от срока просрочки, обычно: 0–30, 31–60, 61–90 и более 90 дней. Правильность вашего отчета зависит от двух решений. Первое — ведете ли вы расчет от даты оплаты или от даты выставления счета. Второе — отображается ли частично оплаченный счет на полную сумму или только на остаток задолженности.

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

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

Почему отчет о старении ломает электронную таблицу

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

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

Вторая проблема — частичные платежи. Счет на $10,000, по которому получено $7,000, представляет собой дебиторскую задолженность в размере $3,000, и он должен отображаться как $3,000 ровно в одной группе. Отчеты о старении, построенные на основе общего списка счетов, а не списка открытых позиций, незаметно завышают все показатели.

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

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

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

Во что это вам обходится

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

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

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

Отчету о старении доверяют только тогда, когда его итог совпадает с главной книгой. Если этого нет, все остальное уже не имеет значения.

Способы решения, которые используют на практике

Вариант 1: Зафиксируйте определения до того, как начнете писать формулы

Запишите четыре вещи в верхней части листа: от какой даты ведется расчет, каковы границы групп, указаны ли суммы до или после вычета платежей, и на какую дату (as-of date) составляется отчет.

Это займет десять минут, но предотвратит самые частые споры. В журнале Journal of Accountancy описывается тот же процесс построения с тем же акцентом на важности правильной первоначальной настройки.

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

Ограничение в том, что сами определения ничего не вычисляют. Они лишь не дают вам рассчитать неверные показатели.

Вариант 2: Создайте столбец с группами, а затем сведите итоги

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

Для самого обозначения группы функция IFS будет более читаемой через полгода, чем вложенные операторы IF. Затем подведите итоги по клиентам и группам с помощью SUMIFS, что позволит проверять расчеты построчно.

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

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

Вариант 3: Создайте вкладку с правилами рядом с цифрами

Соберите все сложные решения в одном месте: как учитываются кредит-ноты, исключаются ли или помечаются спорные счета, как конвертируются валютные балансы и по какому курсу.

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

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

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

Как построить отчет о старении с помощью Powerdrill Bloom

Шаг 1: Загрузите данные о счетах и платежах

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

Шаг 2: Опишите правила группировки простыми словами

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

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

Шаг 3: Экспортируйте диаграмму, отчет или презентацию

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

Почему это лучше, чем перестраивать отчет каждый месяц

	Ручной способ	Powerdrill Bloom
Объединение счетов и платежей	Формулы поиска для каждого файла	Загрузить оба файла и сделать запрос
Изменение отчетной даты	Пересчитать и перепроверить	Указать новую дату
Учет частичных платежей	Ручное создание столбца остатка	Запросить остатки за вычетом платежей
Сопоставление итогов с главной книгой	Ручная проверка в каждом цикле	Спросить, сходятся ли итоги

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

Распространенные ошибки

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

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

Использование функции TODAY, которая пересчитывает сроки в отправленном файле. Зафиксируйте отчетную дату перед отправкой отчета, иначе два человека увидят разные цифры в одном и том же файле.

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

Группировка по клиентам, а не по счетам. Группы должны определяться для каждого счета отдельно, а затем суммироваться по клиенту. Усреднение сроков задолженности клиента скрывает самый старый неоплаченный счет — а ведь именно он вам и нужен.

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

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

Заключение

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

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

Если на это уходит все ваше время в конце месяца, попробуйте Powerdrill Bloom для обработки выгрузок счетов и платежей. Также ознакомьтесь с нашим руководством по преобразованию финансовой отчетности из PDF в диаграммы и страницей анализа денежных потоков с помощью ИИ.

Часто задаваемые вопросы

Каковы стандартные группы в отчете о старении дебиторской задолженности?

В большинстве отчетов используются интервалы 0–30, 31–60, 61–90 и более 90 дней, часто с добавлением столбца для текущей или еще не просроченной задолженности. Границы групп — это скорее соглашение, чем строгое правило, поэтому обязательно укажите, какие именно интервалы вы использовали.

Стоит ли рассчитывать старение счетов от даты выставления или от даты оплаты?

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

Как обрабатывать частичные платежи?

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

Какие функции Excel мне понадобятся?

TODAY или фиксированная дата для отчетной даты, а также DATEDIF для расчета дней просрочки. IFS присваивает название группы, а SUMIFS суммирует итоги по клиентам и группам. Ни одна из этих функций не является сложной; трудность заключается именно в правилах и определениях.

Как часто нужно обновлять отчет?

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