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

Обновлено: 13.05.2024

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

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

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

Если все делать правильно, залог успешности предприятия, нацеленного на развитие и получение прибыли гарантирован.

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

Учет дебиторской задолженности в типовом функционале «1С:Бухгалтерия 8 редакция 3.0 для Казахстана» зависит от совокупности таких настроек, как:

ведение учета по договорам;
установка индивидуальных сроков погашения задолженности покупателей по договору;
формирование резервов по сомнительным долгам;
использование инструментов корректировка долга, зачет авансов, списание задолженностей и др.;
управление сроками с помощью документа «Счет на оплату покупателю» и т. д.

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

Способ № 1. Индивидуальные сроки исполнения обязательств по договору

Если компания ведет аналитический учет по договорам (Путь: вкладка Предприятие >> меню Настройка параметров Учета >> закладка Аналитический учет расчета с контрагентами >> в настройках прописаны сроки оплаты ), то срок погашения задолженности по договору можно указать в карточке договора с конкретным контрагентом.

Способ № 2. Единый срок для всех покупателей

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

Путь: подсистема Предприятие >> Параметры учета >> поле настройки Аналитический учет расчетов с контрагентами , Сроки оплаты долга покупателями\ поставщиками

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

Индивидуальные сроки исполнения обязательств перед поставщиком по договору

Если учет ведется по договорам, то срок оплаты поставщику можно указать непосредственно в карточке договора с поставщиком. Для этого необходимо установить реквизит «Установлен срок оплаты».

Если Вам нужна помощь в выполнении указанных настроек, обратитесь к специалистам Первого Бита .

Автоматическое выявление дебиторской задолженности в одном отчете

Компании с большой реализацией столкнулись с проблемой отсутствия единого отчета по всем контрагентам в типовом функционале программы «1С:Бухгалтерия 8 редакция 3.0 для Казахстана» . Чтобы облегчить работу клиентов, компания «Первый Бит» разработала Реестр дебиторов/кредиторов, который позволяет увидеть задолженности сразу по всем контрагентам.

Автоматическое выявление просроченной кредиторской задолженности

«1С:Бухгалтерия 8 редакция 3.0 для Казахстана» предусматривает ряд настроек для учета кредиторской задолженности:

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

Реализовано три способа указать допустимые сроки оплаты нашей задолженности перед поставщиками:

Типовые отчеты для анализа дебиторской и кредиторской задолженности

Подсистема «Отчеты»

Оборотно-сальдовая ведомость по счету

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

Отчет Анализ субконто

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

Подсистема «Руководителю»:

В этой подсистеме «1С:Бухгалтерия 8 для Казахстана», редакции 3.0 собраны аналитические отчеты на языке, понятном любому сотруднику – «без дебетов и кредитов».

Отчет «Задолженность по поставщикам»

Отчет покажет по какому контрагенту наша организация имеет неоплаченные обязательства или авансы (можно поставить группировку Поставщик, Договор и Документ).

Отчет «Задолженность покупателей»

Акт сверки взаимных расчетов с контрагентами

В спорных ситуациях, прежде чем оплату требовать от должников, следует провести инвентаризацию расчетов с контрагентами. Для выявления возможных расхождений в «1С:Бухгалтерия 8 редакция 3.0 для Казахстана» применяется документ Акт сверки расчетов с контрагентом.

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

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

Корректировка долга

Для частных случаев предназначен документ «Корректировка долга», который позволяет решить следующие учетные задачи:

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

ФОРМИРУЕМ ЖУРНАЛ РЕАЛИЗАЦИИ ПРОДУКЦИИ И ЖУРНАЛ ОПЛАТ

Чтобы наладить Excel-модель, в первую очередь следует создать информационное поле. Для этого понадобятся два журнала — источника информации:

• Журнал реализации продукции;

Журнал реализации продукции (рис. 1) заполняют на основании первичных учетных документов на прием-передачу продукции. Как правило, это накладные и акты. Например, заказу 449 соответствует накладная № 112 от 22.01.2021. Плановая дата погашения дебиторской задолженности — 01.02.2021. Дебитор ООО «Меркурий» относится к категории оптовых покупателей. Ответственный за операцию поставки и дебиторскую задолженность — менеджер Зимин С. И.

Заказы вносят в Журнал реализации в хронологическом порядке, по возрастанию. Дублирование номеров заказов не допускается.

Журнал оплат (рис. 2) заполняют на основании банковских выписок по расчетным счетам. Например, 25 января согласно платежному поручению № 463 поступила оплата от ООО «Парус» на сумму 300 тыс. руб. в счет погашения дебиторской задолженности по заказу 428. Это частичная оплата, так как сумма по накладной составляет 483,7 тыс. руб.

Один и тот же заказ клиент может оплачивать несколько раз частичными платежами. Согласно рис. 2 заказ 430 индивидуальный предприниматель Дубравин А. Е. оплачивал в три этапа.

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

• журналы созданы как плоские таблицы, отформатированы в умные таблицы, (вкладка Главная → Стили → Форматировать как таблицу), которым присвоены имена;

• заполнение журналов стандартизировано, задан выбор из выпадающих списков-справочников или автозаполнение по формулам согласно указанным критериям (например, по наименованию дебитора). Помогут формулы ВПР, ИНДЕКС, ПОИСКПОЗ и др.

Важный момент: журналы можно подготовить непосредственно в Excel-модели или экспортировать из другой учетной системы.

СОСТАВЛЯЕМ ЖУРНАЛ ДЕБИТОРСКОЙ ЗАДОЛЖЕННОСТИ

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

В. С. Волкова, эксперт

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 9, 2021.

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

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

2. Факторы изменения объемов дебиторской задолженности.

3. Отчет о кредитных лимитах.

4. Мониторинг эффективности управления расчетами.

5. Структура дебиторской задолженности.

6. План инкассации дебиторской задолженности.

ТРЕБОВАНИЯ

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

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

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

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

Если перечисленные требования в компании соблюдены, переходят к формированию отчетности по дебиторской задолженности.

Отчет № 1. Аналитика дебиторской задолженности

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

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

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

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

Чтобы настроить расчет дней просрочки в Excel, используют формулу: =ДНЕЙ360(G3;$B$1) (см. рисунок). Функция «ДНЕЙ360» вычисляет количество дней между двумя датами на основе 360-дневного года (двенадцать месяцев по 30 дней).

Выводы о положении дел в компании на основании данных табл. 1:

дебиторская задолженность ООО «Градиент» (10 300 руб.) представлена двумя отгрузочными накладными, одна из которых частично погашена. Период просрочки — 5 и 13 дней соответственно. Покупателю приостанавливают дальнейшие отгрузки, напоминают о просрочке. Если это не единичный случай, ужесточают требования к дебиторам;
дебиторская задолженность ООО «Визит» (103,4 тыс. руб.) находится в исковой стадии (юридическая служба подала иск в суд). Переговоры и досудебные меры результата не дали, однако юристы компании считают возможным вернуть средства фирмы в судебном порядке, если у дебитора есть имущество. Подача иска в суд не всегда целесообразна. Например, расчетный счет дебитора пуст, а основные средства — арендованные. По этой причине задолженность ООО «ТД Радуга» (53,9 тыс. руб.), образованная еще в 2017 г., признана мертвой;
если неплатежи клиента связаны с разногласиями, то до их устранения такую «дебиторку» полезно выделять в отдельную группу. К дебиторской задолженности со статусом «Разногласия» отнесены предприниматели Зимин Ю. Д. (накладная № 113 от 05.05.2019) и Васильева О. В. (накладная № 105 от 09.05.2019).

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

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

– ужесточают технический контроль производимой продукции;

– зачисляют в штат комплектовщика с целью двойного контроля (комплектовщик плюс кладовщик);

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

На основании табл. 1 финансист компании может спланировать поступление денежных средств:

в период с 20 по 31 мая приток денежных средств составит 102,9 тыс. руб. (табл. 2 — применен инструмент «Сводные таблицы» к табл. 1). Если в указанный период будут устранены разногласия с дебиторами, то дополнительные поступления составят еще 66,7 тыс. руб. Итого план поступлений — 169,6 тыс. руб. Уточняют положение дел по мерам, которые предприняты к должнику ООО «Градиент». Однако включать 10 300 руб. в план поступлений и планировать расходы на эту сумму не рекомендуется;

в период с 1 по 10 июня сумма поступлений из дебиторской задолженности составит 149 тыс. руб. (табл. 3). Даже если на плановый период вся дебиторская задолженность текущая, опытные финансисты все равно планируют приток денежных средств с учетом коэффициента инкассации.

Если коэффициент инкассации равен 90 %, то в период с 1 по 10 июня компания получит 134,1 тыс. руб. (149 тыс. руб. × 0,9). Это связано с тем, что даже надежные партнеры могут допускать незначительные просрочки.

ЭТО ВАЖНО

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

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

рабочая ДЗ — переговоры и досудебная работа;
рабочая ДЗ — передана в юридическое управление;
рабочая ДЗ — исковая по решению суда;
рабочая ДЗ — исполнительное производство.

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

просрочка более 10 дней, а задолженность отображается со статусом текущая (регламентируют сроки и порядок досудебной работы, передачи в юридическое управление);
высокий удельный вес «дебиторки» с разногласиями;
просрочена реструктуризированная задолженность;
рост безнадежной задолженности и т. д.

Отчет № 2. Факторы изменения объемов дебиторской задолженности

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

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

Рассмотрим результаты факторного анализа изменений объемов дебиторской задолженности (табл. 4).

Согласно данным табл. 4 снижение дебиторской задолженности с 7230 до 3410 тыс. руб. произошло одновременно с ростом объема продаж до 14 080 тыс. руб. Положительным результатом управления дебиторской задолженностью является снижение ее уровня с 62,8 до 24,2 % в общем объеме продаж. Данное снижение в основном произошло по прочим дебиторам и не связано со стратегическими партнерами компании — ООО «Альфа», ООО «Сигма», ООО «Дельта».

Несмотря на абсолютное снижение, рост дебиторской задолженности имел место в отчетном периоде и был вызван рядом положительных факторов. Главные факторы роста дебиторской задолженности на 2810 тыс. руб.:

на 960 тыс. руб. — рост цен на отгружаемую продукцию;
на 1330 тыс. руб. — увеличение размера партий;
на 520 тыс. руб. — продажа низколиквидной продукции.

Снизить дебиторскую задолженность на 6630 тыс. руб. удалось за счет ужесточения кредитной политики. Покупателей категории «С» перевели на 100%-ную предоплату, категории В — на 50%-ную. Изменения в кредитной политике затронули прочих дебиторов.

Сокращение периода отсрочки затронуло все категории покупателей, включая стратегических:

ООО «Альфа» — снижение задолженности составило 116 тыс. руб.;
ООО «Сигма» — 90 тыс. руб.;
ООО «Дельта» — 130 тыс. руб.

Если оценивать качество управления дебиторской задолженностью за отчетный период по результатам факторного анализа, то снижение «дебиторки» составило не 3820 тыс. руб. (7230 – 3410), а 6630 тыс. руб. (пп. 5–8 табл. 4). Это значительный показатель: 91,7 % от уровня дебиторской задолженности на начало месяца.

О. Цветаева, эксперт

Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 5, 2019.

Прогноз платежей и дебиторской задолженности — задача, которая давно решается при помощи специализированных бухгалтерских программ. Однако при желании учет cash flow можно вести и в обычной таблице в Excel. Такой документ позволяет отслеживать отгрузки по контрагентам и объем полученных и ожидаемых платежей за выполненные заказы. Как создать такой документ расскажет Мурат Хабаху, руководитель подразделения по работе с клиентами «Факторинг ПЛЮС».

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

Создать форму заявки на списание средств (с обязательной графой назначение платежа) и реестр списаний. Создать форму заявки на списание средств (с обязательной графой назначение платежа) и реестр списаний.

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

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

Создать документ для контроля дебиторской задолженности

Дебиторская задолженность формируется в ситуациях, когда компания осуществила отгрузку или выполнила услуги, а оплата за выполненные обязательства не поступила. Это совершенно нормальная ситуация, особенно для организаций, работающих с крупными заказчиками на условиях отсрочки платежа. Однако контроль необходим - отсрочка может затянуться дольше оговоренного срока, что приводит к риску кассового разрыва. Мониторинг просрочки оплаты можно также вести в Excel. В документе должны быть прописана дата проведения анализа (если в ячейке написать «=сегодня()», то она будет обновляться автоматически), условия оплаты, дата отгрузки, планируемый срок оплаты (дата отгрузки+срок отсрочки), фактический срок оплаты, количество дней отсрочки.

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

Главная → Стили → Условное форматирование → Управление правилами → Создать правило.
Затем прописываем нужное количество дней , например, > 60, выбрав в формате заливку красным.

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

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

Читайте также: