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

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

Итак, клиентская задолженность отражается на счете 62 бухгалтерского учета. Для эффективной работы описываемого механизма важно регулярно (в идеале — ежедневно) отражать по бухгалтерским счетам операции отгрузки и оплаты товаров и услуг. И если с отражением поступления денежных средств от клиентов обычно проблем не возникает (банковская выписка загружается из системы клиент-банк и разносится ежедневно), то отгрузки во многих компаниях разносятся в бухгалтерской системе значительно реже. Если организационным путем решить проблему не удастся, информацию об отгрузках можно извлекать из корпоративной CRM-системы или других источников информации.

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

Допустим, наше ООО «Ромашка» состоит из двух юридических лиц (ЮЛ1 и ЮЛ2) и трех центров финансовой ответственности (ЦФО1, ЦФО2 и ЦФО3). Рассмотрим на его примере, как сформировать отчет о дебиторской задолженности.

Для загрузки информации из «1С» необходимо сформировать оборотно-сальдовую ведомость по счету 62 в разрезе контрагентов и отдельных первичных документов. Данную ведомость следует загрузить в Excel и сформировать реестр счетов дебиторов, как показано на рис. 1.

Рисунок 1. Реестр счетов дебиторов

Самое сложное при подготовке такого реестра — обеспечить присутствие в нем всей необходимой аналитики (по ЦФО, продуктам и т. п.). В идеале такую аналитику следует иметь непосредственно на счете 62, чтобы уже на этапе ввода документов в «1С» она заносилась в систему и затем выгружалась без каких-либо корректировок в Excel. Если же в «1С» такой аналитики нет (что весьма вероятно, так как бухгалтерия обычно поддерживает только ту аналитику, которая нужна ей для работы), ее необходимо будет внести в реестр на стадии его формирования в Excel.

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

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

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

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

Сводный отчет о дебиторской задолженности по состоянию на отчетную дату (15 июня 2015 года) является основным и представлен на рис. 2. Как видно из структуры этого отчeта, он легко формируется из реестра счетов дебиторов с помощью функций СУММЕСЛИМН, СЧЕТЕСЛИМН и аналогичных. В отчете представлена информация о величине дебиторской задолженности (как в целом, так и в разбивке по срокам возникновения) в двух разрезах: по юридическим лицам и по ЦФО, а также такие аналитические показатели, как:

• количество дебиторов;
• количество счетов;
• средний долг дебитора;
• средняя сумма счета.

Рисунок 2. Сводный отчет по состоянию на отчетную дату

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

Рисунок 3. Сводный отчет по состоянию на предыдущую отчетную дату

Отчет с отклонениями текущей недели от предыдущей (а не абсолютными значениями показателей) необходим для создания еще более прозрачной картины. Его тоже добавим на отдельном листе Excel с такой же структурой, как у предыдущих отчетов (см. рис. 4). Ведь наиболее интересны не абсолютные значения показателей, а их динамика. И именно данный отчет дает нам много пищи для размышлений. Так, мы видим, что сумма дебиторской задолженности сократилась на 9 млн рублей, причем наибольший вклад в это внес ЦФО3, а наименьший — ЦФО2. Количество неоплаченных счетов сократилось на 115, количество должников — на 17, а в структуре задолженности наиболее существенно сократилась задолженность сроком менее 1 месяца, а больше всего выросла задолженность сроком от 5 до 6 месяцев.

Рисунок 4. Сводный отчет о динамике дебиторской задолженности

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

Рисунок 5. Отчет о задолженности на начало каждой недели

Рисунок 6. Динамика задолженности

Для анализа динамики задолженности на более продолжительном горизонте необходимо сохранять данные на начало каждой недели на отдельном листе отчета, как показано на рис. 5. Для большей наглядности данные этого отчета можно представить в виде графика (см. рис. 6). Аналогично можно контролировать динамику задолженности в разрезе ЦФО и формировать соответствующий график. Помимо динамики задолженности можно более наглядно отображать и ее структуру (см. рис. 7).

Рисунок 7. Структура задолженности в графическом представлении

Также в нашем отчете можно использовать такой удобный и мощный инструмент, как сводные таблицы. Ранее мы незаслуженно мало его использовали, но это было связано с тем, что функция СУММЕСЛИМН справлялась с задачей лучше. В данном же случае сводные таблицы могут оказаться весьма полезны, например, при формировании отчета о крупнейших дебиторах, который приведен на рис. 8.

Рисунок 8. Сводная таблица с крупнейшими дебиторами

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

© Материал из БСС «Система Главбух».
Подробнее: http://vip.1gl.ru/#/document/126/404088/33f5d4c021/?of=copy-5e8cfbb147

Рисунок 9. Опрос в действии

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

Рисунок 10. Подтверждение получения ответа

Рисунок 11. Файл Excel с результатами опроса

Заключение

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

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

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

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

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

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

В Excel вся логика модели — перед глазами. Когда вы вводите данные в «1С», вы можете только догадываться, как они отражаются в учетных регистрах и влияют на результаты отчетов. Конечно, вы можете залезть в конфигуратор и посмотреть на исходный код в модулях метаданных, но вряд ли вы, находясь в здравом уме, решитесь на столь опрометчивый шаг. С Excel все гораздо проще. Каждая ячейка таблицы представляет собой либо значение, либо формулу (значение которой также сразу видно). Отследить взаимосвязь ячеек и понять, каким образом получился результат, проще простого. Значительно упрощает понимание логики работы модели и тот факт, что все ячейки автоматически пересчитываются при изменении значения любой из них — с помощью варьирования исходных параметров можно разобраться даже в самых сложных расчетных моделях.

Excel — кроссплатформенная система. Она работает и под Windows, и под MacOS. Файлы открываются для чтения и редактирования даже на смартфонах под управлением iOS и Android, причем не только с помощью самой Excel, но и посредством продуктов сторонних разработчиков.

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

Функционал Excel можно легко наращивать с помощью надстроек и макросов. «1С» тоже можно дорабатывать, но собственными силами сделать это финансовому директору практически нереально — требуются специальные программисты «1С», взаимодействие с которыми — это вообще особая песня. А в Excel при наличии соответствующих навыков и практики написать макрос в 10–20 строк — вопрос считаных минут.

Вместе с тем у «1С» тоже есть ряд козырей в рукаве. Это функциональное прикладное решение. То есть, в отличие от Excel, «1 °C» с самого начала, сразу после установки умеет вести управленческий, бухгалтерский и налоговый учет, формировать отчетность, давать разнообразную аналитику и т. д. Excel на этом фоне — просто белый лист бумаги, на котором, конечно, можно написать что угодно, однако этот процесс во многих случаях будет сродни изобретению велосипеда. Но у Excel есть своя ниша, в которой у нее нет конкурентов. Эту программу оптимально использовать в следующих случаях.

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

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

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

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

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

Собственно говоря, это все, что я хотел рассказать вам про Excel. А если у вас возникли вопросы, вы можете написать мне по адресу: [email protected].