учет дебиторской задолженности в excel

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

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

Расчет количества просроченных дней

В ячейке B2 текущая дата прописана не цифрами, а формулой, чтобы при открытии документа всегда проставлялась актуальная дата. Столбец с датами отгрузки товара представлен в формате ДАТА, а с суммами задолженности – в финансовом.

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

По дебиторке получилось, что, ИП «Карпов», например, должен был выплатить задолженность еще 4 февраля, а сегодня уже 3 марта. А вот у предприятия ИП «Стригунова» еще есть 6 дней для выплаты, т.к. ее крайний срок – 9 марта.

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

Расчет пени за период просрочки

Просрочка клиента не должна оставаться безнаказанной. Поэтому начисляем пеню, которая составляет 0,1% за каждый просроченный день. Умножим 0,1% на сумму долга и количество дней просрочки.

Двоих клиентов без долга, выделенных красным, пока скроем. Но убирать из списка не будем, чтобы при открытии этого же документа через неделю, задолженность просчиталась автоматически. Выделим обе строки, кликнем правой кнопкой и выберем СКРЫТЬ.

О том, что у нас есть еще два клиента, напоминает нарушенная последовательность строк.

Расчет ставки рефинансирования на день расчета

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

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

Как вычленить недобросовестных клиентов

Осталось главное: определить искомых клиентов. Для начала сложим долг и пени, начисленные через 0,1%.

Предположим, что судебные издержки составляют 5000 рублей на одного клиента. Подсчитаем внизу сумму, которую мы сможем получить после подачи в суд на тех, у кого задолженность более 5000. Для этого понадобится функция СУММЕСЛИ.

Первый аргумент: диапазон, в котором будет искаться критерий. Второй: собственно, критерий, (>5000). Третий: диапазон суммирования (он совпадает с первым). И не забываем вычесть скрытых Стригунову и Малышева (H12 и H13). Получаем 73984 рубля.

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

Прочитать формулу можно так: если общая сумма задолженности превышает 5000 рублей (H4>5000), то выводим «в суд». В противном случае выводим пробел. Таким образом, мы вычленили клиентов, чья сумма задолженности превышает судебные издержки.

Источник

Дебиторская задолженность в Excel с условным форматированием

Финансово-бухгалтерские или ERP системы преимущественно предлагают инструменты служащие для анализа дебиторской задолженности за период времени определяемый пользователем. Если же нет такого инструмента или он не соответствует всем требованиям пользователя, то лицо занимающиеся взысканием по дебиторской задолженности существенно ограничивается в своей профессиональной деятельности. Тогда на помощь приходит условное форматирование ячеек, которое позволяет выполнить и упростить анализ дебиторской задолженности в Exel. В данном примере покажем, как в Excel сделать отчет по дебиторской задолженности клиентов.

Простейший отчет по дебиторской задолженности в Excel

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

Ниже на рисунке представлена таблица со списком фактор из истории взаиморасчетов с клиентами фирмы, который имеет 4 столбца:

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

Для актуализации текущей даты перейдите в ячейку F1 и выберите инструмент: «ФОРМУЛЫ»-«Библиотека функций»-«Дата и время»-«СЕГОДНЯ». В результате будет введена функция =СЕГОДНЯ().

Данная функция не требует аргументов. Она возвращает дату на сегодняшний день. В Excel любая дата — это число, которое является порядковым номером дня начиная от 1 января 1900 года. Если ячейка F1 имела «Общий» формат до введения функции СЕГОДНЯ, то после ввода ее формат автоматически изменяется на «Дата». Если нужно узнать какое число присвоено для текущей даты, то достаточно изменить формат ячейки F1 на «Числовой». Для пользователя значение, которое возвращает функция СЕГОДНЯ более полезнее если оно будет отображаться в формате «Дата». А для формул более важно числовое значение, которое возвращает эта функция.

После ввода функции в ячейке F1 отображается актуальная дата на сегодняшний день. Благодаря этому теперь при каждом открытии данной рабочей книги Excel автоматически будет вводиться дата текущего дня в ячейку F1. И нет необходимости постоянно помнить об ручном обновлении текущей даты.

Работа с дебиторской задолженностью в Excel

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

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

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

Теперь необходимо создать еще 3 правила условного форматирования для текущей табличной части:

Готов отчет по дебиторской задолженности с четырьмя разными цветами выделения строк по условию просроченных документов для каждого диапазона дат:

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

Если нам вдруг потребуется изменить диапазоны дат для просроченных дебиторских задолженностей следует выделить диапазон ячеек A2:D15 и выбрать инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

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

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

Источник

БДДС косвенным методом в Excel: как найти ошибки в отчетности, показать собственнику, куда делись деньги

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

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

Обратите внимание!

Бывают ситуации, когда управленческая отчетность состоит только из отчета о движении денежных средств (Форма №4) или отчета о прибылях и убытках (Форма №2). Причем управленческий баланс не составляется, а отчетность формируется в Excel. Когда нет связующих звеньев, сложно проверить правильность и корректность отчетности.

Удостовериться в корректности отчетности помогает проверочный лист Потоки, который нам предстоит внедрить в учетные формы Excel. Он основан на косвенном БДДС или, другими словами, расчете БДДС косвенным методом.

Обратите внимание!

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

Также лист Потоки даст вам возможность наглядно объяснить собственнику, в чем разница между значениями по Форме№2 и Форме№4. Проще говоря, показать, куда деньги делись, если прибыль есть, а на счетах пусто, и наоборот. Ниже расскажем, как составить бюджет движения денежных средств (пример в Еxcel) и приведем пример расчета БДДС косвенным методом.

Что такое БДДС косвенным методом

Денежный Поток = Чистая прибыль +/- корректировки

Смысл корректировок в следующем.

Амортизация основных средств уменьшает прибыль, но денежный поток не сокращается, поскольку мы не оплачиваем ее каждый месяц. Значит, амортизацию прибавляем к чистой прибыли.
Увеличение кредиторской задолженности – это рост долга компании перед поставщиками. То есть мы получаем, например, товар, но не платим. Денежный поток от этой операции не уменьшается, значит, корректировка чистой прибыли также со знаком «плюс».
Уменьшение кредиторской задолженности – мы больше платим, чем получаем, погашаем старые долги, значит, корректировка со знаком «минус».
Увеличение дебиторской задолженности – мы кредитуем покупателей, а значит, и остаток денежных средств сокращается. Корректировка со знаком «минус».
Уменьшение дебиторской задолженности – нам возвращают долги, денежный поток увеличивается (прибавляем).
Приобретение запасов или товара – из чистой прибыли вычитаем эту сумму, поскольку покупая активы, мы уменьшаем остаток денежных средств.
Помимо операционной деятельности компания может заниматься финансовой, инвестиционной. Принцип тот же – нужно понять, как конкретная операция влияет на денежный поток, и сделать соответствующую корректировку.

Пример управленческой отчетности в Excel: исходные данные

Российская компания ООО «Солнышко» занимается оптовой торговлей без ведения внешнеэкономической деятельности. Имеет свой собственный склад и остатки товаров на нем. Начисляет амортизацию на основные средства. Расчеты ведет в рублях без учета курсовых разниц. Учет производит в 1С:Бухгалтерия, а управленческую отчетность – в Excel по следующим листам:

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

Поскольку у нас есть информация о движении денежных средств (Форма№4), нам не нужно вычислять денежный поток косвенным методом.

Значение чистой прибыли по балансовому листу (Форма№1, табл. 1) нарастающим итогом с начала года составляет 364 970, 35 руб. Исходя из таблиц 2 и 3, балансовая стоимость основных средств – 117 975 рублей. Остаток денежных средств на 1 июня 2016 года равен 272 445, 60 руб. (по Форме№4 и по балансу). То есть денежный поток меньше прибыли на 92 525, 75 руб. (272 444, 60 минус 364 970,35). Посмотрим, из чего образовалась эта разница.

Источник

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

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

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

Методика анализа дебиторской и кредиторской задолженности (ДЗ и КЗ)

Сразу оговоримся: финансовый анализ – это всегда некое усреднение и обобщение:

И еще момент: ДЗ и КЗ включают не только остатки по расчетам с покупателями и поставщиками. Там еще находятся работники, учредители, бюджет, внебюджетные фонды и т.д. В статье же сделаем бóльший упор на первых. Под ДЗ и КЗ в рамках данного материала будем понимать, прежде всего, не вообще всю их величину, а ту часть, которая приходится на покупателей/заказчиков, поставщиков/подрядчиков.

Для изучения системы расчетов понадобятся:

На схеме показали, где взять нужные цифры. А еще предлагаем скачать Excel-файл. Введите в отмеченные ячейки исходные данные, и он сам рассчитает показатели из статьи.

Рисунок 1. Финансовый анализ дебиторской и кредиторской задолженности: информационная основа

Этапы анализа предлагаем такие:

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

Самый простой способ узнать об особенностях системы расчетов у контрагента – определить удельные веса ДЗ и КЗ в валюте баланса по простым формулам:

де ББ – бухгалтерский баланс.

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

От общей структуры в балансе переходим к оценке состава самих долгов. Вспомним, какими они бывают.

Таблица 1. Виды задолженности

Долго- и краткосрочные

· В финансовой отчетности водораздел по сроку проходит на 12 месяцах. Все, что свыше, относится к долгосрочным долгам.

· Заметьте, речь не идет о просрочке. Это значит, что задолженность с договоренностью о погашении через 2 месяца, которую просрочили на 1,5 года, все равно останется краткосрочной.

· В балансе нет специальных строк для долгосрочных ДЗ и КЗ. Те, что показываются во II и V разделах, считаются краткосрочными по умолчанию. Организация должна сама вводить подобные статьи, если у нее есть «долгие» средства в расчетах и обязательства

Текущие и просроченные

· Если ДЗ и КЗ не закрыты в договорной срок, то они становятся просроченными.

· В балансе эти суммы не увидеть. Ищите их в пояснениях. Приказ Минфина РФ «О формах бухгалтерской отчетности организаций» рекомендует для них таблицы 5.2 и 5.4. Однако предприятия вправе иначе раскрыть данные значения

Обеспеченные и без обеспечения

· Залог, поручительство и банковская гарантия – то, что формирует обеспечение долга по ГК РФ. А еще по бухгалтерским правилам к ним относятся выданные и полученные векселя. Вероятность погашения обеспеченной задолженности увеличивается, значит, ее качество растет.

· Суммы приводятся в таблице 8 пояснений к балансу и отчету о финансовых результатах

Сомнительные и безнадежные

· Термины применяются к ДЗ. Логично, ведь в своих долгах сомневаться не будешь, и теряет надежду на их получение кто-то другой.

· Сомнительная – это просроченная и ничем необеспеченная ДЗ. Сюда же причисляется текущая, по которой высока вероятность неполучения средств вовремя. К безнадежной относится с истекшим сроком исковой давности.

· По первой создается резерв по сомнительным долгам. Он минусует ее суммы из балансовой строки. Вторая списывается на прочие расходы. Выходит, в балансе нет ни той, ни другой.

· Приводятся в столбцах таблицы 5.1 пояснений. Сомнительная – там, где величина резерва. Безнадежная – в колонке «Списание на финансовый результат»

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

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

Рисунок 2. Показатели первого этапа анализа

Этап 2. Сравнительный анализ дебиторской и кредиторской задолженности

Выражается в одной формуле. Вот такой:

Соотношение КЗ и ДЗ = КЗ / ДЗ ≈ 1,1.

Ее логика проста: хорошо, когда обязательства самой организации больше, чем долги перед ней. В идеале примерно на 10%. Корректный расчет предполагает исключение из числителя и знаменателя просроченных сумм.

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

Этап 3. Анализ динамики дебиторской и кредиторской задолженности

На данном этапе предлагаем рассчитать темпы роста для ДЗ и КЗ, а затем сравнить их:

Напомним, как рассчитывается темп роста:

Рассмотрим, о чем расскажут полученные цифры.

Тр ДЗ и КЗ. Здесь работает та же логика, что на предыдущем этапе. Если в числе долгов нет просроченных, то превалирующий рост кредиторки по сравнению с дебиторкой – положительная характеристика.

Тр ДЗ, активов и выручки. Выше уже отмечали: дебиторская задолженность в активах – неотъемлемый, но не самый желательный элемент. Поэтому если ее прирост обгоняет увеличение имущества, то доля отсроченных (иначе – выведенных из оборота) средств растет.

Чтобы понять возможные причины ситуации, сравните также Тр для ДЗ и выручки. Когда они примерно равны, то увеличение/уменьшение долгов дебиторов связано с ростом/снижением продаж компании. Если же дебиторка существенно обгоняет выручку, значит, дело в изменении схемы расчетов с контрагентами. Организация стала предоставлять отсрочку покупателям/заказчикам на более долгий период. Ну или еще вариант: увеличились объемы выданных авансов поставщикам и подрядчикам. Они ведь тоже находятся в ДЗ.

Тр КЗ, пассивов и неденежных расходов. Про оптимальное соотношение кредиторки и пассива определенно не скажешь. С одной стороны, ее превышающий рост по сравнению с источниками говорит, что оборот предприятия увеличивается за счет средств чужого бизнеса. Это хорошо. С другой, возникает вопрос: а точно ли это не начало будущего краха – невозможности разобраться с целой горой долгов и последующее банкротство?

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

А еще сравнивайте темпы роста КЗ и неденежных расходов, чтобы понять, менялось ли что-то в расчетах с поставщиками и подрядчиками. Здесь появляется вопрос: почему неденежных? Потому что в расходах значимую часть может занимать амортизация. За нее не надо никому платить. Исключите ее из вычислений, и сравнение станет корректнее.

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

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

Таблица 2. Выводы о контрагенте по динамике ДЗ и КЗ

Значительный рост ДЗ

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

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

Значительный рост КЗ

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

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

· непокрытый долг потенциального контрагента сформировался перед его основным поставщиком материалов;

· эта организация приостанавливает дальнейшую отгрузку;

· ваш поставщик прекращает производственный процесс из-за отсутствия материалов;

· вы не получаете товары, работы или услуги вовремя

Этап 4. Анализ оборачиваемости дебиторской и кредиторской задолженности

Оборачиваемость оценивается двумя видами показателей:

Их формулы связаны между собой. Расчет одного значения поможет легко получить на его основе другое. Рассмотрим по отдельности для ДЗ и КЗ.

Оборачиваемость ДЗ. Дебиторка обычно формируется в результате реализации*. Это, по сути, неоплаченный покупательскими деньгами доход продавца. Чтобы понять, сколько раз за год обернется каждый рубль средств в расчетах, разделите выручку на ДЗ. Формула такая:

где ОФР – отчет о финансовых результатах;

н.п. – начало периода;

Примечание: *говоря о том, что ДЗ формируется при реализации, переходим в упрощенно-условную плоскость финансового анализа, о которой писали в начале статьи. Причина: если поставщику перечислить аванс, то тоже сформируется дебиторка. Но не в результате продажи, а из-за покупки.

Заметьте: в знаменателе формулы используется средняя величина долгов дебиторов за период. Это так, потому что ДЗ приводится в ББ, который составляется на дату. А выручка находится в ОФР, формируемом за период. Для корректной состыковки моментальной и периодической величин ДЗ усредняют, превращая в некий аналог значения за год.

Чтобы посчитать продолжительность одного оборота, разделите количество дней в периоде на количество оборотов. Будет так:

О чем расскажут полученные цифры в отношении контрагента? О примерной продолжительности срока, по истечении которого закрываются долги перед ним. Какое отношение это имеет к вашему предприятию? Большое. Если средний период поступления денег на счета свыше, например, полугода, то резонны вопросы:

Оборачиваемость КЗ. Здесь все по аналогии с дебиторкой. Только вместо выручки используются расходы. Причина состоит в допущении: кредиторская задолженность появляется в связи с формированием затрат или расходов. По большей части это справедливо. Исключение: КЗ в результате полученных от покупателей авансов. Но здесь опять исходим из присущих анализу условностей.

И еще момент: какие расходы включать в расчет? Есть несколько вариантов:

Любой из способов имеет право на существование. Мы остановимся на синтезе второго и третьего вариантов. В итоге расчетный алгоритм для оборачиваемости кредиторки окажется таким:

Для самогó потенциального партнера малое количество оборотов кредиторки за год и длительный период в днях выгодны. С точки зрения вашего предприятия – не очень. Почему? Потому что такая организация:

Как узнать о наличии просроченной задолженности у клиентов в «1С:ERP Управление предприятием»

Наличие или отсутствие просроченной задолженности — важный критерий оценки соблюдения клиентами платежной дисциплины. В «1С:ERP Управление предприятием» есть специальный отчет, который показывает информацию о просроченной задолженности «Платежная дисциплина клиентов». Он покажет список клиентов с указанием суммы просроченной задолженности. При необходимости можно расшифровать сумму задолженности.

Подрбнее о возможностях «1С:ERP Управление предприятием» смотрите здесь >>

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

Давайте применим теорию на практике. Воспользуемся финансовой отчетностью ПАО «Синарский трубный завод» (ПАО «СинТЗ») за 2019 год. Пройдемся по всем четырем этапам. В таблице 3 смотрите расчеты для первого.

Таблица 3. Анализ структуры ДЗ и КЗ

Источник